Using Index Advisor v10
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.
You should not run Index Advisor in read-only transactions.
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
The examples in the following sections use the table created with the statement shown below:
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 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
The following example shows the contents of a sample
pg_advise_index program as shown in the code sample below:
In the code sample, the
-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 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 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; the following steps detail loading the Index Advisor plugin and using 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 would like Index Advisor to analyze. Index Advisor stores any recommendations for the queries in the
index_advisor_logtable. If the
index_advisor_logtable 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.
After loading the Index Advisor plugin, Index Advisor will analyze all SQL statements and log any indexing recommendations for the duration of the session.
If you would like Index Advisor to analyze a query (and make indexing recommendations) without actually executing the query, preface the SQL statement with the
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
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