Using Index Advisor v11
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.
Note
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 enterprisedb
.
The examples in the following sections use the table created with the statement shown below:
The resulting table contains the following rows:
Using the pg_advise_index Utility
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.
The following example shows the contents of a sample workload.sql
file:
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
-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.
-o
The recommended indexes are written to the file specified after the -o
option.
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.
Note
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 $libdir
.
Using Index Advisor at the psql Command Line
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-psql
command line utility, and load the Index Advisor plugin:
- 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 theindex_advisor_log
table. If theindex_advisor_log
table does not exist in the user'ssearch_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 EXPLAIN
keyword.
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:
To enable Index Advisor, set the parameter to on
: