Reading an EXPLAIN Plan to Find the Index You're Actually Missing

A sequential scan on a large table isn't automatically wrong; Postgres's planner chooses one deliberately when it estimates that scanning the whole table is cheaper than using an available index, which is often correct for a query that's going to return a large fraction of the table's rows anyway. The problem case is a sequential scan against a large table for a query that should only be touching a small, targeted subset of rows, which usually means no useful index exists for that filter condition, not that the planner made a mistake.

Run EXPLAIN ANALYZE, not just EXPLAIN, since ANALYZE actually executes the query and reports real row counts and real timing, not just the planner's estimate. A big gap between the planner's estimated row count and the actual row count returned is itself a useful signal, often pointing at stale table statistics rather than a missing index.

A single column index on the field in a WHERE clause is the obvious first fix, but it's often incomplete if the query also sorts or joins on additional columns. A composite index covering the filter column and the sort column together can let Postgres avoid a separate sort step entirely, which shows up in the plan as the sort operation disappearing rather than just the scan type changing.

Column order in a composite index matters: put the column used for equality filtering first and the column used for range filtering or sorting after it, since that order is what lets Postgres use the index efficiently for both parts of the query rather than falling back to a less efficient partial usage of the index.

Adding an index and never confirming Postgres actually chooses to use it for the target query is a common way an automated suggestion turns into wasted write overhead with no read benefit. Run EXPLAIN ANALYZE again after adding the candidate index, in a non production environment first, and confirm the plan now shows an index scan or index only scan against the new index specifically, not just a plan that happens to look different.

Check whether the new index makes an existing index redundant rather than simply adding to the pile. A table with several overlapping indexes that all cover similar columns adds real write cost for every insert and update without providing proportional read benefit, which is exactly the failure mode automated indexing tools produce when suggestions are applied without this kind of review.

Verify a candidate index before shipping it:

1. Add the candidate index in a non production environment first.
2. Run EXPLAIN ANALYZE again on the target query.
3. Confirm the plan now shows an index scan or index only scan against the new index specifically.
4. If you built a composite index for filter and sort, check that the separate sort step disappeared from the plan.
5. Drop the index if the planner ignores it, since an unused index only adds write overhead.

Some slow queries aren't index problems even after applying a good one; a query that fundamentally has to touch a large fraction of a huge table, an aggregate over a wide date range with no practical way to narrow it, is going to be slow regardless of indexing and needs a different approach, like a precomputed rollup table or a caching layer in front of the query. An EXPLAIN plan that still shows a large row count being processed even against the best available index is the signal that indexing alone has hit its limit for that particular query.

An automated tool that only ever suggests indexes will miss this category of problem entirely, since it's optimizing within the wrong solution space for that specific case. Reading the plan yourself, rather than trusting a tool's suggestion at face value, is what catches the difference between a genuinely missing index and a query that needs a structurally different approach.