Cardinality Estimation
The planner's prediction of how many rows each step of a query will produce — the input that determines every other choice it makes.
The optimiser chooses access methods, join strategies and join order from estimated row counts. Get the estimate wrong and every subsequent decision is wrong, which is why a query can be a thousand times slower than it should be while the SQL looks fine.
Estimates come from statistics: histograms of value distribution, distinct-value counts, and correlation data, all sampled periodically. They go wrong in specific, recognisable ways.
Stale statistics after a bulk load or a data shift — the most common cause and the cheapest fix
(ANALYZE). Correlated columns: the planner assumes independence, so filtering on city and
postcode multiplies two selectivities and estimates far fewer rows than reality; extended or
multi-column statistics address this. Opaque expressions: a function on a column, or a parameter
whose value is unknown at plan time, forces a default guess. Skew, where the average is not
representative of the value you actually queried.
The diagnostic is always the same and it is the first thing to look at: run the plan with actual execution and compare estimated rows against actual rows. A large divergence tells you the problem is information, not the index.