Reviewing the Index Advisor recommendations v14

You can review the index recommendations generated by Index Advisor in several ways. You can:

• Query the index_advisor_log table.
• Run the show_index_recommendations function.
• Query the index_recommendations view.

Using the show_index_recommendations() function

To review the recommendations of the Index Advisor utility using the show_index_recommendations() function, call the function, specifying the process ID of the session:

SELECT show_index_recommendations( pid );

Where pid is the process ID of the current session. If you don't know the process ID of your current session, passing a value of NULL also returns a result set for the current session.

This code fragment shows an example of a row in a result set:

edb=# SELECT show_index_recommendations(null);
                      show_index_recommendations
---------------------------------------------------------------------
create index idx_t_a on t(a);/* size: 2184 KB, benefit: 3040.62,
gain: 1.39222666981456 */
(1 row)

In the example, create index idx_t_a on t(a) is the SQL statement needed to create the index suggested by Index Advisor. Each row in the result set shows:

• The command required to create the recommended index.
• The maximum estimated size of the index.
• The calculated benefit of using the index.
• The estimated gain that results from implementing the index.

You can display the results of all Index Advisor sessions from the following view:

SELECT * FROM index_recommendations;

Querying the index_advisor_log table

Index Advisor stores indexing recommendations in a table named index_advisor_log. Each row in the index_advisor_log table contains the result of a query where Index Advisor determines it can recommend a hypothetical index to reduce the execution cost of that query.

You can query the index_advisor_log table at the psql command line. This example shows the index_advisor_log table entries resulting from two Index Advisor sessions. Each session contains two queries and can be identified in the table by a different backend_pid value. For each session, Index Advisor generated two index recommendations.

edb=# SELECT * FROM index_advisor_log;
 reloid  | relname | attrs | benefit | index_size | backend_pid | timestamp
---------+---------+-------+---------+------------+-------------+------------
----------------------
 16651   |    t    |  {1}  | 1684.72 |    2184    |     3442    | 22-MAR-11
16:44:32.712638 -04:00
 16651   |    t    |  {2}  | 1655.52 |    2184    |     3442    | 22-MAR-11
16:44:32.759436 -04:00
 16651   |    t    |  {1}  | 1355.9  |    2184    |     3506    | 22-MAR-11
16:48:28.317016 -04:00
 16651   |    t    |  {1}  | 1684.72 |    2184    |     3506    | 22-MAR-11
16:51:45.927906 -04:00
(4 rows)

Index Advisor added the first two rows to the table after analyzing the following two queries executed by the pg_advise_index utility:

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

The value of 3442 in column backend_pid identifies these results as coming from the session with process ID 3442.

The value of 1 in column attrs in the first row indicates that the hypothetical index is on the first column of the table (column a of table t).

The value of 2 in column attrs in the second row indicates that the hypothetical index is on the second column of the table (column b of table t).

Index Advisor added the last two rows to the table after analyzing the following two queries executed at the psql command line:

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)

The values in the benefit column of the index_advisor_log table are calculated using the following formula:

benefit = (normal execution cost) - (execution cost with hypothetical index)

The value of the benefit column for the last row of the index_advisor_log table shown in the example is calculated using the query plan for the following SQL statement:

EXPLAIN SELECT * FROM t WHERE a = 100;

The execution costs of the different execution plans are evaluated and compared:

benefit = (Seq Scan on t cost) - (Index Scan using <hypothetical-index>)

The benefit is added to the table:

benefit = 1693.00 - 8.28

benefit = 1684.72

You can delete rows from the index_advisor_log table when you no longer need to review the results of the queries stored in the row.

Querying the index_recommendations view

The index_recommendations view contains the calculated metrics and the CREATE INDEX statements to create the recommended indexes for all sessions whose results are currently in the index_advisor_log table. You can display the results of all stored Index Advisor sessions by querying the index_recommendations view:

SELECT * FROM index_recommendations;

Using the example shown in Querying the index_advisor_log table, the index_recommendations view displays the following:

edb=# SELECT * FROM index_recommendations;
 backend_pid |                                 show_index_recommendations
-------------+---------------------------------------------------------------
------------------------------
     3442    | create index idx_t_a on t(a);/* size: 2184 KB, benefit:
1684.72, gain: 0.771392654586624 */
     3442    | create index idx_t_b on t(b);/* size: 2184 KB, benefit:
1655.52, gain: 0.758021539820856 */
     3506    | create index idx_t_a on t(a);/* size: 2184 KB, benefit:
3040.62, gain: 1.39222666981456 */
(3 rows)

In each session, the results of all queries that benefit from the same recommended index are combined to produce one set of metrics per recommended index, reflected in the fields named benefit and gain.

The formulas for the fields are:

size = MAX(index size of all queries)
benefit = SUM(benefit of each query)
gain = SUM(benefit of each query) / MAX(index size of all queries)

So, for example, using the following query results from the process with a backend_pid of 3506:

 reloid  | relname | attrs | benefit | index_size | backend_pid | timestamp
---------+---------+-------+---------+------------+-------------+------------
----------------------
 16651   |    t    |  {1}  |  1355.9 |    2184    |    3506     | 22-MAR-11
16:48:28.317016 -04:00
 16651   |    t    |  {1}  |  1684.72 |   2184    |    3506     | 22-MAR-11
16:51:45.927906 -04:00

The metrics displayed from the index_recommendations view for backend_pid 3506 are:

backend_pid  |                                 show_index_recommendations
-------------+---------------------------------------------------------------
------------------------------
    3506     | create index idx_t_a on t(a);/* size: 2184 KB, benefit:
3040.62, gain: 1.39222666981456 */

The metrics from the view are calculated like this:

benefit = (benefit from 1st query) + (benefit from 2nd query)
benefit = 1355.9 + 1684.72
benefit = 3040.62

As well as the following:

gain = ((benefit from 1st query) + (benefit from 2nd query)) / MAX(index
size of all queries)
gain = (1355.9 + 1684.72) / MAX(2184, 2184)
gain = 3040.62 / 2184
gain = 1.39223

The gain metric is useful when comparing the relative advantage of the different recommended indexes derived during a given session. The larger the gain value, the better the cost effectiveness derived from the index weighed against the possible disk space consumption of the index.

• On this page
• Using the show_index_recommendations() function
• Querying the index_advisor_log table
• Querying the index_recommendations view