How the query planner works

Estimates in, plan out

The planner enumerates ways to execute a query and picks the cheapest by its cost model. That model runs on statistics about your data — how many rows, how distinct the values are, how they are distributed.

When statistics are stale, the estimates are wrong and the plan is wrong with them. A query that was fast yesterday and is slow today, with no code change, is very often this.

The gap between estimated and actual rows is your main diagnostic. An estimate of ten rows against an actual of a million explains almost any bad plan choice that follows.