Reviewing the Index Advisor Recommendations v10
There are several ways to review the index recommendations generated by Index Advisor. You can:
- Query the
- Run the
- Query the
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:
pid is the process ID of the current session. If you do not know the process ID of your current session, passing a value of
NULL will also return a result set for the current session.
The following code fragment shows an example of a row in a result set:
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 will result from implementing the index.
You can display the results of all Index Advisor sessions from the following view:
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.
|OID of the base table for the index|
|Name of the base table for the index|
|Recommended index columns (identified by column number)|
|Calculated benefit of the index for this query|
|Estimated index size in disk-pages|
|Process ID of the process generating this recommendation|
|Date/Time when the recommendation was generated|
You can query the
index_advisor_log table at the psql command line. The following 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 below) by a different
backend_pid value. For each session, Index Advisor generated two index recommendations.
Index Advisor added the first two rows to the table after analyzing the following two queries executed by the
The value of
3442 in column
backend_pid identifies these results as coming from the session with process ID
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
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
Index Advisor added the last two rows to the table after analyzing the following two queries (executed at the psql command line):
The values in the
benefit column of the
index_advisor_log table are calculated using the following formula:
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:
The execution costs of the different execution plans are evaluated and compared:
and the benefit is added to the table:
You can delete rows from the
index_advisor_log table when you no longer have the need to review the results of the queries stored in the row.
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 as shown below:
Using the example shown in the previous section (
Querying the index_advisor_log Table), the
index_recommendations view displays the following:
Within 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
The formulas for the fields are as follows:
So for example, using the following query results from the process with a
The metrics displayed from the
index_recommendations view for
backend_pid 3506 are:
The metrics from the view are calculated as follows:
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.