Index Advisor limitations v15

Prior to running the Index Advisor feature, review the following limitations:

  • Index Advisor doesn't consider index-only scans. It does consider index scans when making recommendations.

  • Index Advisor ignores any computations found in the WHERE clause. Effectively, the index field in the recommendations isn't any kind of expression. The field is a simple column name.

  • Index Advisor doesn't consider inheritance when recommending hypothetical indexes. If a query references a parent table, Index Advisor doesn't make any index recommendations on child tables.

  • Suppose you're restoring 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. Changes in object identifiers (OIDs) can result in broken links between the index_advisor_log table and the restored tables referenced by rows in the index_advisor_log table.

  • If you need 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:

UPDATE index_advisor_log SET reloid = new_oid WHERE reloid = old_oid;