Query Optimizer
The query optimizer converts SQL into an optimal execution plan, using statistics to estimate costs and choose join orders and access paths.
Purpose
To understand the core database performance component and analyze slow queries and execution plans.
Mechanism
Parse SQL → logical plan → physical plan. Rule-based optimization (predicate pushdown, column pruning) + cost-based optimization (CBO). It generates the space of equivalent plans, estimates I/O/CPU cost per plan, and picks the best.
Use cases
- Slow query analysis
- Execution plan tuning
- Database internals