Query Planners
Cost-based vs rule-based planners, EXPLAIN, and why the optimizer is the real engine.
Level: advanced
What is it?
A query planner selects a physical execution strategy for a logical query. Modern relational databases commonly use cost-based optimization, comparing estimates for access paths and join operators, while rule-based transformations still simplify and normalize query expressions.
Why does it matter?
Knowing how a plan was selected helps distinguish SQL correctness from runtime cost. A slow query may need better statistics, a suitable index, fewer rows, or a different query shape rather than merely a different syntax.
Mental model
Read the plan as a tree or pipeline of operators. Leaves access data; intermediate operators filter, join, sort, or aggregate; the root produces result rows. Actual row counts and timing reveal where estimates diverged.
How it works
- EXPLAIN shows the chosen plan; EXPLAIN ANALYZE executes the query and reports actual information, with possible side effects for modifying statements.
- Nested-loop, hash, and merge joins suit different input sizes, indexes, and ordering.
- Cost is an estimate based on statistics and configuration, not a promise of actual runtime.
- Row estimates at inner plan nodes affect parent decisions.
- Plan stability can change as data, statistics, parameters, cache state, or database versions change.
Steps
- Capture the plan for a slow query.
- Compare estimated rows against actual rows at each node.
- Identify the first major cardinality error or expensive operator.
- Make a targeted change—statistics, index, query shape, or configuration—and measure again under representative conditions.
Real-world usage
- Using EXPLAIN to diagnose slow SQL.
- Reviewing estimated versus actual row counts.
- Explaining plan changes after data growth or a database upgrade.
Common mistakes
- Reading only the total estimated cost as elapsed milliseconds.
- Running EXPLAIN ANALYZE on a destructive statement without a safe transaction or test environment.
- Adding indexes before understanding the filter, ordering, join, and write workload.
Interview questions
What is the difference between EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN shows the estimated plan; EXPLAIN ANALYZE executes it and reports actual runtime/row information for executed operators.
Which plan value is often revealing when debugging?
A large gap between estimated and actual row counts, especially near the first divergence in the plan tree.