Check out our free resources and eBooks like: "Effective Indexing in Postgres"

Index Advisor: How to Reason About Missing Index Opportunities

With our recommendations we also give you all the context you need to reason about them

Index Advisor creates issues for newly detected opportunities. They give you details on the opportunity 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

scans

On specific index opportunities, 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 opportunity 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

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

existing indexes

To reason about recommendations, the Index Advisor opportunity 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.


Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →