Reading a Query Plan to Find the Index You're Actually Missing

Running an explain command with the analyze option gives you the plan the database actually executed, including real row counts and real timing at each step, rather than the planner's estimate of what it expects to happen. The estimated plan alone can be misleading when the database's statistics are out of date, so always reach for the analyzed version when you're diagnosing a genuinely slow query rather than exploring hypothetically.

A query plan is a tree of steps, and the slow one is rarely the whole query, it's usually one specific step within it. Read through the plan looking for the step with the largest gap between its own time and its children's combined time, since that gap is where that particular step's own work, not its inputs, is actually costing you.

A sequential scan reads every row in a table to find the ones matching your query's condition, which is fine for a small table and expensive for a large one. If the slow step is a sequential scan on a table with a meaningful row count, and the query filters on a specific column, that column is very likely your missing index, assuming the filter is selective enough that an index would actually narrow the search meaningfully rather than still touching most of the table.

An index on a column where most rows share the same value, like a boolean flag that's true for nearly every row, won't help much, because the database still has to check nearly every row either way, and it may reasonably choose to skip the index and scan the table anyway. Check how many distinct values the column actually has and how evenly rows are distributed across them before assuming an index is the fix, since a low-selectivity index is dead weight that still costs write performance without buying you the read performance you wanted.

Adding an index and assuming it worked isn't verification, rerunning the same analyzed explain command and confirming the plan now uses an index scan instead of a sequential scan for that step is. Sometimes the planner still chooses not to use a new index, often because the table is small enough that a sequential scan is genuinely faster, or because the query's actual filter doesn't match what you assumed it was doing. Either way, the plan tells you the truth, not your assumption about what should have happened.

The whole process fits into five moves:

1. Run explain with the analyze option to get the plan that actually executed, with real row counts and timings at each step.
2. Find the step whose own time is largest compared with its children's combined time, since that gap is where the real work happens.
3. Check whether that step is a sequential scan on a large table filtered by a specific column.
4. Check the column's selectivity, meaning how many distinct values it has and how evenly rows are spread across them.
5. Add the index, rerun the same analyzed explain, and confirm the plan now uses an index scan for that step.

Say a team adds an index on a status column after a slow query complaint, but the query plan still shows a sequential scan afterward. Reading the plan more closely reveals the actual slow step was a join further down the tree, filtering on a different column entirely, one the team never looked at because the status column was the first thing that looked suspicious. The new index wasn't wrong to consider, it just wasn't the fix, and only reading the plan's actual bottleneck, rather than guessing from the query's surface, would have pointed at the join condition that mattered.