7.3 Index Advisor
7 Performance Analysis and Tuning : 7.3 Index Advisor
7.3 Index AdvisorThe Index Advisor utility helps determine which columns you should index to improve performance in a given workload. Index Advisor considers B-tree (single-column or composite) index types, and does not identify other index types (GIN, GiST, Hash) that may improve performance. Index Advisor is installed with EDB Postgres Advanced Server.Index Advisor works with Advanced Server's query planner by creating hypothetical indexes that the query planner uses to calculate execution costs as if such indexes were available. Index Advisor identifies the indexes by analyzing SQL queries supplied in the workload.
• Invoke the Index Advisor utility program, supplying a text file containing the SQL queries that you wish to analyze; Index Advisor will generate a text file with CREATE INDEX statements for the recommended indexes.Index Advisor will attempt to make indexing recommendations on INSERT, UPDATE, DELETE and SELECT statements. When invoking Index Advisor, you supply the workload in the form of a set of queries (if you are providing the command in an SQL file) or an EXPLAIN statement (if you are specifying the SQL statement at the psql command line). Index Advisor displays the query plan and estimated execution cost for the supplied query, but does not actually execute the query.During the analysis, Index Advisor compares the query execution costs with and without hypothetical indexes. If the execution cost using a hypothetical index is less than the execution cost without it, both plans are reported in the EXPLAIN statement output, metrics that quantify the improvement are calculated, and Index Advisor generates the CREATE INDEX statement needed to create the index.If no hypothetical index can be found that reduces the execution cost, Index Advisor displays only the original query plan output of the EXPLAIN statement.Index Advisor does not actually create indexes on the tables. Use the CREATE INDEX statements supplied by Index Advisor to add any recommended indexes to your tables.A script supplied with Advanced Server creates the table in which Index Advisor stores the indexing recommendations generated by the analysis; the script also creates a function and a view of the table to simplify the retrieval and interpretation of the results.If you choose to forego running the script, Index Advisor will log recommendations in a temporary table that is available only for the duration of the Index Advisor session.7.3.1 Index Advisor ComponentsThe Index Advisor shared library interacts with the query planner to make indexing recommendations. The Advanced Server installer creates the following shared library in the libdir subdirectory of your Advanced Server home directory:Please note that libraries in the libdir directory can only be loaded by a superuser. A database administrator can allow a non-superuser to use Index Advisor by manually copying the Index Advisor file from the libdir directory into the libdir/plugins directory (under your Advanced Server home directory). Only a trusted non-superuser should be allowed access to the plugin; this is an unsafe practice in a production environment.pg_advise_index is a utility program that reads a user-supplied input file containing SQL queries and produces a text file containing CREATE INDEX statements that can be used to create the indexes recommended by the Index Advisor. The pg_advise_index program is located in the bin subdirectory of the Advanced Server home directory.index_advisor.sql is a script that creates a permanent Index Advisor log table along with a function and view to facilitate reporting of recommendations from the log table. The script is located in the share/contrib subdirectory of the Advanced Server directory.The index_advisor.sql script creates the index_advisor_log table, the show_index_recommendations() function and the index_recommendations view. These database objects must be created in a schema that is accessible by, and included in the search path of the role that will invoke Index Advisor.Index Advisor logs indexing recommendations in the index_advisor_log table. If Index Advisor does not find the index_advisor_log table in the user's search path, Index Advisor will store any indexing recommendations in a temporary table of the same name. The temporary table exists only for the duration of the current session.show_index_recommendations() is a PL/pgSQL function that interprets and displays the recommendations made during a specific Index Advisor session (as identified by its backend process ID).Index Advisor creates the index_recommendations view based on information stored in the index_advisor_log table during a query analysis. The view produces output in the same format as the show_index_recommendations() function, but contains Index Advisor recommendations for all stored sessions, while the result set returned by the show_index_recommendations() function are limited to a specified session.Index Advisor does not require any configuration to generate recommendations that are available only for the duration of the current session; to store the results of multiple sessions, you must create the index_advisor_log table (where Advanced Server will store Index Advisor recommendations). To create the index_advisor_log table , you must run the index_advisor.sql script.When selecting a storage schema for the Index Advisor table, function and view, keep in mind that all users that invoke Index Advisor (and query the result set) must have USAGE privileges on the schema. The schema must be in the search path of all users that are interacting with the Index Advisor.
1. Place the selected schema at the start of your search_path parameter. For example, if your search path is currently:and you want the Index Advisor objects to be created in a schema named advisor, use the command:
2. Run the index_advisor.sql script to create the database objects. If you are running the psql client, you can use the command:\i full_pathname/index_advisor.sqlSpecify the pathname to the index_advisor.sql script in place of full_pathname.
3. Grant privileges on the index_advisor_log table to all Index Advisor users; this step is not necessary if the Index Advisor user is a superuser, or the owner of these database objects.
• Grant SELECT and INSERT privileges on the index_advisor_log table to allow a user to invoke Index Advisor.
• Grant DELETE privileges on the index_advisor_log table to allow the specified user to delete the table contents.
• Grant SELECT privilege on the index_recommendations view.The following example demonstrates the creation of the Index Advisor database objects in a schema named ia, which will then be accessible to an Index Advisor user with user name ia_user:SET
edb=# \i /opt/edb/as9.6/share/contrib/index_advisor.sql
edb=# GRANT USAGE ON SCHEMA ia TO ia_user;
edb=# GRANT SELECT, INSERT, DELETE ON index_advisor_log TO ia_user;
edb=# GRANT SELECT ON index_recommendations TO ia_user;
GRANTWhile using Index Advisor, the specified schema (ia) must be included in ia_user's search_path parameter.
7 Performance Analysis and Tuning : 7.3 Index Advisor