7.3.4 Reviewing the Index Advisor Recommendations

Table of Contents Previous Next


7 Performance Analysis and Tuning : 7.3 Index Advisor : 7.3.4 Reviewing the Index Advisor Recommendations

Query the index_advisor_log table.
Run the show_index_recommendations function.
Query the index_recommendations view.
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:
Where 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.
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:
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. 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.
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).
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:
benefit = (Seq Scan on t cost) - (Index Scan using <hypothetical-index>)
benefit = 1693.00 - 8.28
benefit = 1684.72
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.
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 as shown below:
Using the example shown in the previous section (Querying the index_advisor_log Table), the index_recommendations view displays the following:
gain = SUM(benefit of each query) / MAX(index size of all queries)
The metrics displayed from the index_recommendations view for backend_pid 3506 are:
Index Advisor ignores any computations found in the WHERE clause. Effectively, the index field in the recommendations will not be any kind of expression; the field will be a simple column name.
Restoration of a pg_dump backup file that includes the index_advisor_log table or any tables for which indexing recommendations were made and stored in the index_advisor_log table, may result in "broken links" between the index_advisor_log table and the restored tables referenced by rows in the index_advisor_log table because of changes in object identifiers (OIDs).
If it is necessary to display the recommendations made prior to the backup, you can replace the old OIDs in the reloid column of the index_advisor_log table with the new OIDs of the referenced tables using the SQL UPDATE statement:

7 Performance Analysis and Tuning : 7.3 Index Advisor : 7.3.4 Reviewing the Index Advisor Recommendations

Table of Contents Previous Next