Index Advisor: How to Reason About Missing Index Insights

Index Advisor creates issues for newly detected insights. They give you details on the insight and the context to evaluate the suggestion. When reasoning about an index recommendation, you may want to refer to existing indexes on the table, queries affected, and table scans.

Different variants of indexes are tried during the Index Advisor recommendation process that produces the lowest cost (and thus the lowest estimated I/O when executing the query). When Index Advisor finds a recommendation, it will show an expected cost improvement.

The cost improvement is based on the Postgres query execution cost model. It is an average across the individual cost improvements for each scan that can benefit from the query, weighted by the frequency of the scan (based on the queries that contain the scan). More frequent queries will have a more significant impact on the overall weighted cost improvement.

Table scans

On specific index insights, query detail pages, and schema statistics table detail pages, you will see a section called “Scans”. Each scan has an associated estimated cost that is averaged across all queries it is referenced in. For more information about how this works, read our Indexing Engine documentation.

On the Index Advisor insight page, the scan list includes the following:

• the current cost
• estimated scans per minute
• estimated cost improvement if you were to apply the recommendation

Cost improvements are expressed as a multiplier, for example, 1.2× faster than the old estimated cost. The table sorts by cost improvement.

On schema statistics table detail pages, the list includes:

• the scan method
• the current cost
• estimated scans per minute

On query detail pages, the list includes:

• the scan method (with a check icon if using an index and a warning icon for sequential scans)
• the current cost
• the size of the table being scanned (scans are okay for small tables)

Affected queries

Affected queries include all queries on a particular table that contain at least one condition in their WHERE or JOIN clause that would be affected by the index recommendation. E.g., if a column in a proposed index is used as a filter condition in a WHERE clause, the query may benefit from this index.

Existing indexes

To reason about recommendations, the Index Advisor insight page also shows all existing indexes on the table. E.g., you may want to avoid adding a proposed index if it's very similar to an existing index, and instead you may want to rewrite the affected query so that it can take advantage of the existing index.