You profiled the slow endpoint, found the query, added exactly the index it should need, and deployed. Nothing changed. You check, and the database is still doing a sequential scan across four million rows while your beautiful index sits unused.
The planner is not broken and it is not ignoring you. It considered your index, estimated what using it would cost, estimated what scanning would cost, and picked the cheaper one. Whether it picked correctly depends almost entirely on whether its estimates were any good.
What changed in 2026
- Managed databases hid the tuning knobs. Fewer teams adjust planner settings directly, which makes understanding the estimates more important, not less.
- Statistics collection got more automatic. Better defaults reduced the frequency of stale-statistics disasters without eliminating them, particularly after bulk loads.
- Plan regression monitoring spread. Tracking when a query's plan changes — rather than only when it gets slow — became a standard part of database observability.
- Query plan visualisation tools improved. Reading nested plan output stopped requiring quite so much practice.
How the decision is made
The planner enumerates ways to execute your query — which index to use or none, what order to join tables, which join algorithm — and assigns each a cost. Cost is an abstract number combining estimated disk pages read and CPU work. Lowest cost wins.
Every one of those costs depends on an estimate of how many rows each step will produce, and those estimates come from statistics: sampled summaries of your data's distribution, collected periodically.
The chain is short and brittle. Statistics feed row estimates, row estimates feed costs, costs pick the plan. Bad statistics produce bad estimates produce a bad plan — even though the planner reasoned perfectly from what it was told.
Why a sequential scan is often right
The instinct that an index is always faster is wrong, and understanding why explains most "ignored index" cases.
An index scan finds matching entries, then fetches each row from the table — a separate, effectively random read per row. A sequential scan reads the table straight through in large sequential chunks, which storage handles far better.
So when a query matches a large fraction of the table, the index scan does nearly as many row fetches as a sequential scan does, but randomly, plus the index traversal on top. The crossover is lower than people expect — often somewhere in the single-digit percentages of the table.
| Situation |
Likely plan |
Why |
| Matches 0.1% of rows |
Index scan |
Few random fetches |
| Matches 30% of rows |
Sequential scan |
Random fetches exceed a straight read |
| Table is small |
Sequential scan |
Whole table fits in a few pages |
| Index covers all needed columns |
Index-only scan |
No table fetch at all |
| Statistics are stale |
Anyone's guess |
Estimates are wrong |
The index-only scan row is worth noting. If every column the query needs is in the index, the table is never touched and the random-fetch cost disappears. This is why adding a column to an index sometimes produces a dramatic speedup that adding a whole new index does not — see database indexing explained.
Reading EXPLAIN ANALYZE
EXPLAIN shows the plan and the estimates. EXPLAIN ANALYZE actually runs the query and shows estimates and actual numbers. The second is what you want, because the single most valuable diagnostic is the gap between them.
Look for a node where estimated rows and actual rows differ by a large factor. That is where the planner was misled, and every decision above that node was made on a wrong assumption. A step estimating 10 rows and returning 400,000 explains a bad plan completely — the planner chose a strategy appropriate for 10 rows.
Common causes of that gap:
Stale statistics. The most frequent, and the first thing to check. After a bulk load, a large delete, or any big data shift, statistics describe a table that no longer exists. Refresh them and re-check before doing anything else.
Correlated columns. The planner generally assumes conditions are independent. If city and postcode are filtered together, it multiplies their selectivities and estimates far fewer rows than reality, because those columns are not independent at all. Extended statistics on the column group fix this where supported.
Expressions the planner cannot see through. A condition wrapping a column in a function is opaque to column statistics, so the planner falls back to a generic guess.
Skewed distributions. A column where one value covers most rows breaks average-based estimates unless the sample captured it.
Common mistakes
- Adding an index without checking whether it gets used. Unused indexes cost write performance and space for nothing.
- Using EXPLAIN without ANALYZE. You see estimates and no way to know they were wrong.
- Forcing plans with hints before checking statistics. Freezes a plan that may be wrong later.
- Not refreshing statistics after a bulk load. The single most common cause of a sudden bad plan.
- Wrapping indexed columns in functions. Prevents index use unless you built a matching expression index.
- Reading only the top of the plan. The problem is usually at a leaf node where estimates diverged.
- Optimising against an empty test database. Plans on a thousand rows tell you nothing about four million.
FAQ
How do I make it use my index?
First find out why it is not. Run EXPLAIN ANALYZE, find the estimate-versus-actual gap, and refresh statistics. If estimates are accurate and it still declines the index, the planner is probably right and your index does not suit that query — often because it matches too many rows or lacks a needed column.
Why did a query get slow with no code change?
Data changed and the plan flipped. A query fast at 10,000 rows can pick a different plan at 10 million, and the tipping point arrives without warning. Plan-change monitoring catches this before users do.
Are query hints a bad idea?
A reasonable last resort, and a bad first one. They pin a decision that was correct for today's data shape. Where the planner is persistently wrong for structural reasons and you have exhausted statistics fixes, they earn their place — with a comment explaining why.
Does this apply to NoSQL databases?
Those with query planners, yes, in the same shape. Simpler key-value stores have no planner because there is nothing to choose between, which trades flexibility for predictability.
Does connection pooling affect plans?
Indirectly. Prepared statements reused across a pool can retain a plan chosen for different parameters — the generic-plan problem. See connection pooling explained.
Where to go next
For the indexes the planner chooses between, read database indexing explained. For why those indexes degrade over time, index bloat, and for the version churn underneath your row counts, MVCC explained.