Query Optimization
Turning SQL into a cheap plan: statistics, join order, and access paths.
Level: advanced
What is it?
Query optimization transforms a parsed SQL query into an execution plan expected to return the required result at reasonable cost. A cost-based optimizer estimates cardinalities and compares alternatives such as scans, index lookups, join orders, and join algorithms.
Why does it matter?
The declarative SQL result can remain the same while execution cost differs by orders of magnitude. Optimization helps databases use indexes, statistics, memory, and join strategies without requiring callers to hand-code every physical operation.
Mental model
SQL is the specification; the execution plan is the chosen recipe. The optimizer estimates how many rows flow through each operator and the resources those operators consume.
How it works
- The optimizer considers logically equivalent rewrites and alternative physical operators.
- Table statistics, histograms, and cardinality estimates strongly influence plan choice.
- Join order matters because intermediate result sizes can multiply.
- Index selectivity, clustering, memory, and I/O cost affect whether an index path is attractive.
- Bad estimates from stale statistics or correlated predicates can produce poor plans.
Steps
- Write a query joining Customers and Orders with a filter on order date.
- The optimizer estimates filtered row counts using table/index statistics.
- It chooses join order and algorithms such as nested-loop or hash join.
- Use EXPLAIN (and EXPLAIN ANALYZE where safe) to compare estimates with actual row counts and execution behavior.
Real-world usage
- Diagnosing a query that becomes slow as data volume grows.
- Choosing useful indexes based on real predicates and selectivity.
- Understanding why two equivalent SQL formulations can receive different plans.
Common mistakes
- Optimizing from intuition alone without looking at actual plans.
- Assuming an index is always cheaper than a sequential scan.
- Ignoring stale statistics, parameter sensitivity, or data skew.
Interview questions
What is cardinality estimation?
It predicts the number of rows emitted by a table access or plan operator and helps the optimizer compare costs.
Why can inaccurate estimates cause a bad join order?
The optimizer may expect a small intermediate result that is actually huge, making its chosen join algorithm or order much more expensive.