Choosing a Plan
For any query there are many ways to fetch and join data. The query optimizer enumerates candidate plans and assigns each an estimated cost, then runs the cheapest one. Cost is a model of expected effort, not real time.
What Goes Into Cost
- Statistics about tables, such as row counts, distinct values, and value histograms.
- Cardinality estimates for how many rows each step will produce.
- Per operation costs for sequential page reads, random reads, and CPU work.
The planner multiplies estimated row counts by per row costs across each step to score a plan.
Why Estimates Drift
Cost models rely on stale or skewed statistics, and they often assume columns are independent. A bad estimate near the bottom of a plan compounds upward, so a join that looks cheap may explode. Running analyze to refresh statistics is the usual first fix.
Key idea
The optimizer scores plans with a cost model built on table statistics and cardinality estimates, so keeping statistics fresh is the main way to get good plans.