Reading a Query Plan to Find a Missing Database Index

The classic pattern: a query filtering on a specific column performed fine when the table had a modest row count, because Postgres could scan the whole table faster than the overhead of using an index would have been worth. As the table grows, that same full scan gets proportionally slower, and at some point crosses from imperceptible to noticeably slow, often without any code change triggering it.

This is why the first response shouldn't be assuming the code broke something. Run an EXPLAIN ANALYZE on the actual slow query first, with real production-like data volume if you can, since a query plan on a small local development database often looks completely different from the same query's plan against your real table size.

A query plan reads as a tree, and the innermost, deepest nodes execute first, with results flowing up to the nodes above them. Read from the bottom, not the top, to find where time is actually being spent. Each node reports its own actual time separately from the cumulative time of everything below it, and the node where that difference is largest is usually where the real cost lives, not necessarily the outermost node with the largest total time.

Compare the planner's estimated row count against the actual row count at each node. A large mismatch between estimated and actual rows is one of the most common signals that the planner's statistics are stale or that the query is structured in a way the planner can't optimize well, both of which point toward different fixes than simply adding an index.

A sequential scan in the plan isn't automatically a bug. For a query that legitimately needs to touch most of a small or moderately sized table, a sequential scan can be faster than the overhead of using an index, and Postgres's planner is generally right about this tradeoff when its statistics are current. The problem case is a sequential scan on a large table for a query that's only supposed to touch a small, selective subset of rows.

Check selectivity before assuming an index will help: how small a fraction of the table does this specific filter actually match. An index helps most when a query is highly selective, touching a small slice of a large table. An index on a column with low selectivity, where most rows match the filter anyway, often doesn't get used by the planner at all, because a sequential scan is genuinely faster in that case.

Before adding an index, confirm what it actually costs: every index slows down writes to that table, since each insert, update, or delete has to maintain the index alongside the table data, and it adds ongoing storage. An index that speeds up an infrequent report query but meaningfully slows down a high-frequency write path can be a net loss depending on your actual traffic mix.

Check whether an existing index already covers this case in a different order, or whether a composite index across the columns this specific query filters and sorts by would serve multiple slow queries at once rather than adding a narrow, single-purpose index for each one. After adding it, re-run the plan against the same query to confirm the planner is actually choosing to use the new index, since adding an index doesn't guarantee the planner will pick it.

After adding an index, watch for these signs that it isn't delivering the benefit you expected:

• The query plan still shows a sequential scan after the index exists, which usually means the planner's statistics need updating or the query isn't written in a way the index can actually help with.
• The index exists on the right column but in a data type or collation that doesn't match how the query filters, which silently prevents the planner from using it.
• Write latency on the table increased noticeably after adding the index, without a corresponding read improvement large enough to justify the tradeoff.
• The index was added for one specific query that changed or was removed later, and now exists purely as unused write overhead nobody's audited in a while.

Periodically review your indexes against actual query patterns, not just at the moment each one was added. An index that made sense a while back can become pure overhead as your application's real query patterns shift.