Index Advisor helps you determine which columns need to be indexed to improve the performance in a specified workload. Index Advisor recognizes only single-column or composite B-tree index type, but cannot identify other index types that can improve performance, such as GIN, GiST, and Hash.

Overview

Index Advisor attempts to make indexing recommendations on INSERT, UPDATE, DELETE, and SELECT statements. When you invoke Index Advisor, you provide the workload in the form of an EXPLAIN statement. Index Advisor displays the query plan and the estimated execution overhead for a provided query, but does not execute the query.

During the analysis, Index Advisor compares the query execution overheads when a hypothetical index is used and not used. Assume that the execution overhead of the query that uses a hypothetical index is lower than that of the query that does not use a hypothetical index. Both query plans are reported in the EXPLAIN statement output, and metrics that quantify the improvement are calculated. Index Advisor also generates the CREATE INDEX statement that is required to create the index.

If no hypothetical index that reduces the execution overhead is found, Index Advisor displays only the original query plan output of the EXPLAIN statement. Index Advisor does not create indexes on the tables. You can execute the CREATE INDEX statement provided by Index Advisor to add recommended indexes to your tables.

After you execute the CREATE EXTENSION index_advisor statement, the index_advisor_log table, the show_index_recommendations() function, and the index_recommendations view are generated. In the table, Index Advisor stores the indexing recommendations that are generated by the analysis. You can use the function and the view to simplify the retrieval and interpretation of the results.

Considerations

  • Index Advisor does not consider index-only scans. Index Advisor considers index scans when it makes recommendations.
  • Index Advisor ignores all the calculations that are found in the WHERE clause. The index field in a recommendation is a simple column name instead of an expression.
  • Index Advisor does not consider inheritance when hypothetical indexes are recommended. If your query references a parent table, Index Advisor does not make indexing recommendations on the child table.
  • When a pg_dump backup file is being restored, the index_advisor_log table may be disconnected from the restored table due to changes in the object identifier (OID). The pg_dump backup file contains the index_advisor_log table or tables that have indexing recommendations in the index_advisor_log table. The restored table references rows in the index_advisor_log table.
  • If you need to display the recommendations before the backup, you can execute the UPDATE index_advisor_log SET reloid = new_oid WHERE reloid = old_oid; statement. This statement is used to replace the old OID in the reloid column of the index_advisor_log table with the new OID of the referenced table.

Components of Index Advisor

Component Description
index_advisor_log Index Advisor records indexing recommendations in the index_advisor_log table.
show_index_recommendations() show_index_recommendations() is a PL/pgSQL function that interprets and displays the recommendations made during a specific Index Advisor session. The session is identified by its backend process ID.
index_recommendations Index Advisor creates the index_recommendations view based on the information that is stored in the index_advisor_log table during query analysis. The format of the view output is the same as that of the show_index_recommendations() function. The view output contains Index Advisor recommendations for all the storage sessions. However, the result set that is returned by the show_index_recommendations() function contains Index Advisor recommendations for only the specified session.

Use Index Advisor

The table that is used in this example is created by executing the following 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 table contains the following rows:
   a   |   b
-------+-------
     0 | 99999
     1 | 99998
     2 | 99997
     3 | 99996
       .
       .
       .
 99997 |     2
 99998 |     1
 99999 |     0
  1. Create the Index Advisor plug-in.
    create extension index_advisor;
  2. Load the Index Advisor plug-in.
    LOAD 'index_advisor';
  3. Execute each SQL statement that you want Index Advisor to analyze in the PostgreSQL command-line interface (CLI).
    Note Index Advisor stores all the recommendations on queries in the index_advisor_log table. This table is automatically generated when the plug-in is created.

After the Index Advisor plug-in is loaded, Index Advisor analyzes all SQL statements and records all the indexing recommendations during the session:

  • If you want Index Advisor to analyze the query and make indexing recommendations when you do not execute the query, use the EXPLAIN keyword as the prefix of the SQL statement.
  • If the statement is not prefixed with the EXPLAIN keyword, Index Advisor analyzes the statement when the query is being executed and writes indexing recommendations to the index_advisor_log table.