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:

CREATE TABLE t( a INT, b INT );
INSERT INTO t SELECT s, 99999 - s FROM generate_series(0,99999) AS s;
ANALYZE t;

The resulting table contains the following rows:

a     | b
-------+-------
  0   | 99999
  1   | 99998
  2   | 99997
  3   | 99996
      .
      .
      .
99997 | 2
99998 | 1
99999 | 0

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:

SELECT * FROM t WHERE a = 500;
SELECT * FROM t WHERE b < 1000;

Run the pg_advise_index program as shown in the code sample below:

$ pg_advise_index -d edb -h localhost -U enterprisedb -s 100M -o advisory.sql 
workload.sql
poolsize = 102400 KB
load workload from file 'workload.sql'
Analyzing queries .. done.
size = 2184 KB, benefit = 1684.720000
size = 2184 KB, benefit = 1655.520000
/* 1. t(a): size=2184 KB, benefit=1684.72 */
/* 2. t(b): size=2184 KB, benefit=1655.52 */
/* Total size = 4368KB */

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.

create index idx_t_1 on t (a);
create index idx_t_2 on t (b);

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.

$ edb-psql -d edb -h localhost -U enterprisedb -e -f advisory.sql
create index idx_t_1 on t (a);
CREATE INDEX
create index idx_t_2 on t (b);
CREATE INDEX
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:

  1. Connect to the server with the edb-psql command line utility, and load the Index Advisor plugin:
$ edb-psql -d edb -U enterprisedb
...
edb=# LOAD 'index_advisor';
LOAD
  1. 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.

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:

edb=# EXPLAIN SELECT * FROM t WHERE a < 10000;

                        QUERY PLAN
-----------------------------------------------------------------------------
Seq Scan on t (cost=0.00..1693.00 rows=10105 width=8)
  Filter: (a < 10000)
Result (cost=0.00..337.10 rows=10105 width=8)
  One-Time Filter: '===[ HYPOTHETICAL PLAN ]==='::text
  -> Index Scan using "<hypothetical-index>:1" on t
     (cost=0.00..337.10 rows=10105 width=8)
       Index Cond: (a < 10000)
(6 rows)

edb=# EXPLAIN SELECT * FROM t WHERE a = 100;
                        QUERY PLAN
-----------------------------------------------------------------------------
Seq Scan on t (cost=0.00..1693.00 rows=1 width=8)
  Filter: (a = 100)
Result (cost=0.00..8.28 rows=1 width=8)
  One-Time Filter: '===[ HYPOTHETICAL PLAN ]==='::text
  -> Index Scan using "<hypothetical-index>:3" on t
     (cost=0.00..8.28 rows=1 width=8)
       Index Cond: (a = 100)
(6 rows)

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:

edb=# SET index_advisor.enabled TO off;
SET

To enable Index Advisor, set the parameter to on:

edb=# SET index_advisor.enabled TO on;
SET