Index Advisor: Getting Started

The pganalyze Index Advisor is a tool to analyze your queries, determine how Postgres will fetch data from the referenced tables, and suggest indexes to make those scans more efficient. The Index Advisor uses information pganalyze already collects about your schema and queries in order to provide an analysis for any table and its query workload.

The Index Advisor considers the whole database when it creates opportunities. We do not recommend adding indexes to every query, since that impacts database performance, especially I/O. Extra indexes compete for space in the Postgres buffer cache, affecting reads, and must be updated for inserts and updates to the table, affecting writes. Rather, we recommend good enough indexes that strike the balance between performance and operational efficiency.

The Index Advisor is powered by the pganalyze Indexing Engine, and you can read about its internals if you want to understand all the details. It's especially important to be aware of the limitations of the Indexing Engine in order to use the Index Advisor effectively.

What you get with Index Advisor

• Save time manually tracking down slow queries, and focus on opportunities highlighting suboptimal execution plans that will become more of a problem as your database grows
• Review opportunities distilled from hundreds of different potential index combinations modeled by our Indexing Engine
• Check statistics for affected queries and use the included “CREATE INDEX” command to easily reason about and test the index opportunities
• Optimize for the smallest amount of total I/O required to execute the queries in your database
• Keep Index Write Overhead low by maintaining a small number of good-enough indexes for each table, with as few columns as possible

If you have not yet done so, you will need to set up monitoring for your database server. After you start seeing stats from your database in the pganalyze UI, it can still take 24-48h for opportunities to show up due to the extra processing required. Once this is set up, you can navigate to the Index Advisor page in the left-hand menu to view missing and unused index opportunities.

Optionally, you can set up alerts and get automated notifications when we find new indexing opportunities.

Set up the column stats helper for better recommendations

Column statistics are used by pganalyze to improve Index Advisor recommendations. For best results, make sure you are running collector 0.41.2 or later, and set up the get_column_stats helper function.

You can check which collector version you are running on the Settings page for your server, and can check if the column stats helpers are installed by looking for warnings in the collector test output (pganalyze-collector --test --verbose).

How to navigate the opportunities overview

At the top of the overview page you find KPIs that frame our opportunity findings for you. You can reference the same metrics when you look into the missing and unused index opportunities below.

Total Data Size

The total data size across all tables in your database.

Total Index Size

The total index size across all indexes in your database.

Total Table Writes

The number of tables writes (inserts and updates) per second in your database.

Avg. Index Write Overhead

The average index write overhead across all indexes in your database.

View all opportunities for your databases

Opportunities are listed below and split into two types: Missing indexes and unused indexes. By default opportunities are sorted by impact which highlights the most important items to address.

Missing indexes

Detects scans on tables that have potential to be improved by adding an index. Queries fetch data from tables through different types of scans, and some scans can be executed more efficiently if an appropriate index is present. The Index Advisor attempts to infer what indexes would be helpful for your query workload. See more details in the Indexing Engine documentation.

The impact of a missing index opportunity is measured by cost improvement.

Unused indexes

Every index causes additional write overhead when its table is written to, and sometimes indexes are created but never used, or cease to be used. This can happen either because a better index is available, the current index cannot be used even though it looked applicable, or the query workload changed.

The unused index check detects indexes that haven't been used in the last 2 weeks, as determined by Postgres statistics, and notes these indexes as potentially being candidates for removal.

The impact of an unused index opportunity is measured by reduced Index Write Overhead.

In addition to these checks that are performed as part of the Index Advisor, pganalyze can also help you alert on invalid indexes.

From the overview page you can click through to issue pages that explain in detail why the Index Advisor created an opportunity.

View opportunities on query detail pages

Index Advisor is also available from query detail pages. Any query that may be affected by an index opportunity will include a link to the opportunity details.