The Cost Based Optimizer
A cost based optimizer chooses a physical plan by estimating how expensive each candidate is and keeping the cheapest. Rather than always using one strategy, it lets the data decide.
What cost means
Cost is a model of work, usually blending disk reads, CPU effort, and memory use into a single number. The optimizer does not run the query to measure this. It estimates cost from statistics about table sizes and value distributions.
Searching the plan space
The optimizer enumerates candidate plans, prices each one, and prunes branches that cannot win. Because the space of plans is huge, it uses pruning and heuristics rather than checking every option.
- Cost approximates total work for a plan.
- Estimates come from stored statistics, not from running the query.
- The optimizer searches many plans and keeps the cheapest.
Key idea
A cost based optimizer prices many candidate plans using statistics and runs the cheapest, so plan choice adapts to the actual data.