Using Index Advisor v15
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. These queries are executed by the
pg_advise_index() function. After analyzing the workload, Index Advisor stores the result set in either 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.
Don't run Index Advisor in read-only transactions.
The following examples assume that superuser
enterprisedb is the Index Advisor user. The Index Advisor database objects were created in a schema in the
search_path of superuser
The examples use the table created with this statement:
The resulting table contains the following rows:
When invoking the
pg_advise_index utility, you must include the name of a file that contains the queries executed by
pg_advise_index. The queries can be on the same line or on separate lines, but each query must be terminated by a semicolon. Queries in the file can't begin with the
This example shows the contents of a sample
In the code sample, the
-U options are psql connection options.
An optional parameter that limits the maximum size of the indexes recommended by Index Advisor. If Index Advisor doesn't return a result set,
-s might be set too low.
The recommended indexes are written to the file specified after the
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 you can create the indexes by executing the
pg_advise_index asks the backend process to load the index_advisor plugin first from
$libdir/plugins. If not found, then it writes the error in the server log file and attempts to load from
You can use Index Advisor to analyze SQL statements entered at the edb-psql or psql command line.
To load the Index Advisor plugin and use Index Advisor:
- Connect to the server with the
edb-psqlcommand line utility, and load the Index Advisor plugin:
- Use the
edb-psqlcommand line to invoke each SQL command that you want Index Advisor to analyze. Index Advisor stores any recommendations for the queries in the
index_advisor_logtable. If the
index_advisor_logtable doesn't exist in the user's
search_path, a temporary table is created with the same name. This temporary table exists only for the rest of the user's current session.
After you load the Index Advisor plugin, Index Advisor analyzes all SQL statements and logs any indexing recommendations for the rest of the session.
If you want Index Advisor to analyze a query and make indexing recommendations without executing the query, preface the SQL statement with the
EXPLAIN keyword. If you don't preface the statement with the
EXPLAIN keyword, Index Advisor analyzes the statement while the statement executes. It writes the indexing recommendations to the
index_advisor_log table for later review.
In this example, the
EXPLAIN statement displays the normal query plan. It's 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
on. The Index Advisor plugin must be loaded to use a
SHOW command to display the current value of
You can use the
index_advisor.enabled parameter to temporarily disable Index Advisor without interrupting the psql session:
To enable Index Advisor, set the parameter to