Cost-Based Optimization
Cost-based optimization (CBO) uses statistics to estimate the cost of each execution plan (I/O + CPU + memory) and picks the cheapest plan.
Purpose
To understand the core of modern optimizers and analyze how statistics influence plan selection.
Mechanism
Statistics: number of distinct values per column (NDV), most common values (MCV), histograms, correlation. The optimizer estimates selectivity and cardinality, then derives each operator cost. Stale statistics degrade plans; run ANALYZE periodically.
Use cases
- Execution plan analysis
- Statistics management
- Optimizer tuning