Table of Contents Previous Next



When you invoke Index Advisor, you must supply a workload; the workload is either a query (specified at the command line), or a file that contains a set of queries (executed by the pg_advise_index() function). After analyzing the workload, Index Advisor will either store the result set in a temporary table, or in a permanent table. You can review the indexing recommendations generated by Index Advisor and use the CREATE INDEX statements generated by Index Advisor to create the recommended indexes.
The following examples assume that superuser enterprisedb is the Index Advisor user, and the Index Advisor database objects have been created in a schema in the search_path of superuser enterprisedb.
CREATE TABLE t( a INT, b INT );
INSERT INTO t SELECT s, 99999 - s FROM generate_series(0,99999) AS s;
When invoking the pg_advise_index utility, you must include the name of a file that contains the queries that will be executed by pg_advise_index; the queries may be on the same line, or on separate lines, but each query must be terminated by a semicolon. Queries within the file should not begin with the EXPLAIN keyword.
Run the pg_advise_index program as shown in the code sample below:
In the code sample, the -d, -h, and -U options are psql connection options.
-s is an optional parameter that limits the maximum size of the indexes recommended by Index Advisor. If Index Advisor does not return a result set, -s may be set too low.
The information displayed by the pg_advise_index program is logged in the index_advisor_log table. In response to the command shown in the example, Index Advisor writes the following CREATE INDEX statements to the advisory.sql output file
You can create the recommended indexes at the psql command line with the CREATE INDEX statements in the file, or create the indexes by executing the advisory.sql script.
1.
Connect to the server with the edb-psql command line utility, and load the Index Advisor plugin:
2.
Use the edb-psql command line to invoke each SQL command that you would like Index Advisor to analyze. Index Advisor stores any recommendations for the queries in the index_advisor_log table. If the index_advisor_log table does not exist in the user's search_path, a temporary table is created with the same name. This temporary table exists only for the duration of the user's session.
If you do not preface the statement with the EXPLAIN keyword, Index Advisor will analyze the statement while the statement executes, writing the indexing recommendations to the index_advisor_log table for later review.
In the example that follows, the EXPLAIN statement displays the normal query plan, followed by the query plan of the same query, if the query were using the recommended hypothetical index:
After loading the Index Advisor plugin, the default value of index_advisor.enabled is on. The Index Advisor plugin must be loaded to use a SET or SHOW command to display the current value of index_advisor.enabled.
You can use the index_advisor.enabled parameter to temporarily disable Index Advisor without interrupting the psql session:


Table of Contents Previous Next