Using Index Advisor v14

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.

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 enterprisedb.

The examples use the table created with this statement:

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 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 EXPLAIN keyword.

This 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:

$ 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

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.

-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 you can 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

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.

To load 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

2. Use the edb-psql command 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_log table. If the index_advisor_log table 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:

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

• On this page
• Using the pg_advise_index utility
• Using Index Advisor at the psql command line