Verifying Index Usage with PostgreSQL EXPLAIN ANALYZE

To check whether PostgreSQL uses an index for a query, run EXPLAIN (ANALYZE, BUFFERS) and read the plan for the table that index covers. Confirm that PostgreSQL chose the intended index, see which conditions it used to find rows, and check whether the plan fits the query’s filtering and ordering needs.

A plan is a tree of steps PostgreSQL takes to answer a query. A scan node shows how PostgreSQL reads a table. An index scan follows an index, a structure that helps PostgreSQL find rows, while a sequential scan reads the table directly. ANALYZE runs the query and reports actual results alongside estimates, so use it with care for statements that change data.

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

The index name in a scan node is only the starting point. Check the conditions, row counts, and any remaining sort to see whether PostgreSQL used the index as intended. The PostgreSQL documentation on EXPLAIN explains these plan details.

Read the scan node and its conditions

Start with the scan node for the table you care about. Index Scan using ... and Index Only Scan using ... name an index PostgreSQL chose. A Bitmap Index Scan on ... also uses an index. It usually appears in a larger plan subtree with a Bitmap Heap Scan, which uses a list of matching table locations to retrieve rows. The heap is PostgreSQL’s table storage.

Then check the condition lines:

• Index Cond shows which condition PostgreSQL applies through the index to find or narrow candidate entries.
• Filter shows a condition PostgreSQL applies to rows after the scan produces candidates.
• Recheck Cond shows a condition the heap scan checks again, as can happen with bitmap scans.

When a condition that matches only a small number of rows appears only as a filter, PostgreSQL fetches candidate rows before applying it. A Filter alone does not establish that the index is a poor choice. For example, if an index supports one condition but a second condition that matches few rows appears only under Filter, the index may not narrow the scan as intended. PostgreSQL’s plan examples illustrate this distinction.

Bitmap plans can use recheck conditions, and some index methods return candidates that PostgreSQL verifies against the table; the condition alone does not show that an index is defective.

Judge whether the path fits the query

Check whether the plan matches the rows the query returns and the order it requests. A plain index scan often fits a lookup that matches relatively few rows. A bitmap plan can combine results from multiple indexes, then retrieve matching rows in the order they appear in table storage. This uses indexes, but the bitmap path does not preserve index order and can require a separate Sort for an ORDER BY. See PostgreSQL’s explanation of combining multiple indexes.

A sequential scan does not automatically mean something is wrong. When a query needs many rows from a table, reading the table directly can cost less than visiting many index entries and fetching the matching rows. PostgreSQL’s index-usage guidance uses small and large result sets to show why table size and the number of matching rows affect the choice. Those examples are not universal thresholds.

Compare the estimated and actual row counts at the relevant scan and parent nodes. A large difference can help explain why PostgreSQL chose an unexpected plan. When a node runs repeatedly, actual rows and timing are averages per execution. Check loops to see how many times it ran. Also check Rows Removed by Filter. A high count can mean the scan produced more candidate rows than expected, though filters can still fit the chosen plan.

Buffer counts show page activity for a node and its children, so read them alongside actual rows and execution time rather than treating them as a pass-or-fail measure. For an Index Only Scan, check Heap Fetches if the plan reports them. The node can still access table pages when PostgreSQL needs visibility information that the index alone cannot provide; the index-only scan documentation explains that dependency.

Check the index definition against the query

If PostgreSQL uses the intended index but still filters or sorts important results, compare the index definition with the query’s conditions and output. For a multicolumn B-tree index, column order matters. A condition that checks a leading column for one exact value narrows the scan. A condition that checks the first column without an exact-value condition, such as a comparison against a range of values, can also narrow it. Conditions on later columns do not necessarily reduce the scanned portion. PostgreSQL 18 also supports skip scans in some cases, so an index can still be used when the query has no condition on the leading column. The multicolumn index documentation describes these rules.

Check the index method and operator too. An index supports only the operators its method and operator class handle. Having a condition on the right column does not guarantee PostgreSQL can use it as an Index Cond. For a partial index, PostgreSQL must recognize that the query’s condition guarantees the condition used to define the index. For an expression index, check whether the query uses the indexed expression in a form that matches the index definition. PostgreSQL describes these designs in its index chapter and explains the condition requirement for partial indexes.

The plan can therefore confirm index use while exposing a design mismatch. For example, the intended index might appear in the scan node, but a key predicate could remain a filter, an index-only scan could still fetch heap rows, or an extra sort could remain for the requested order.

Investigate an unexpected plan

First check whether PostgreSQL has up-to-date statistics for the table. Statistics summarize table data so the planner can estimate row counts and compare costs. If they are stale or do not represent the data, PostgreSQL can choose a plan that does not fit the current table. PostgreSQL recommends using ANALYZE when examining index usage. In the default configuration, autovacuum normally collects statistics, but recent data changes can make them outdated. Check whether they reflect the data you are testing.

Next, compare the query’s conditions with the index definition. Check the column order, supported operators, expression form, and any condition that defines a partial index. Then compare estimated and actual row counts, and note how many rows the filters remove. If the estimates are far off, investigate the statistics before concluding that the index design is the cause.

PostgreSQL documents temporarily disabling sequential scans as a diagnostic way to see whether an index-based alternative exists. A forced alternative does not prove that it is the better production plan. Compare actual behavior under representative data and query conditions; the planner may have chosen the sequential scan because it estimates that path to be cheaper. The index examination guidance recommends testing with realistic data rather than drawing conclusions from small test tables.

Remember that EXPLAIN ANALYZE runs the statement. For a data-changing query, use a transaction and roll it back if you need to prevent its changes from persisting. Instrumentation also adds overhead, so treat reported elapsed time as a measurement under EXPLAIN ANALYZE, not an exact prediction of normal execution. PostgreSQL covers these effects in its EXPLAIN command documentation.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com