Query Planning
Query planning translates SQL into an execution plan by choosing access paths, join orders, and algorithms using statistics and cost estimates.
Definition
Query planning is the optimizer stage that converts a parsed and rewritten query into an executable plan. It enumerates access paths and join orders, estimates the cost of each using statistics and system parameters, and selects the cheapest plan. The plan determines which indexes, joins, sorts, and aggregations actually run.
Context and scope
Two SQL statements that return the same rows can differ by orders of magnitude in latency because of their plans. Understanding planning is how you diagnose a query that suddenly became slow after a data change or deployment.
Common misconceptions
- The optimizer always finds the best plan, so query performance is independent of how SQL is written.
- Adding an index guarantees the planner will use it.
Anti-patterns
- Disabling the optimizer or forcing hints everywhere instead of fixing statistics and query shape.
- Tuning only on a small development dataset whose distribution differs from production.
Related concepts
Related examples
Authoritative source
How it works
The parser turns SQL into a tree. The rewriter applies views, rules, and security policies. The planner then explores alternatives: sequential scan versus index scan versus bitmap scan, nested loop versus hash join versus merge join, and different join orders. Each alternative gets an estimated cost from row counts, page counts, and CPU factors. The cheapest plan is compiled and executed.
The plan is a tree of operators. EXPLAIN shows estimated rows and costs; EXPLAIN ANALYZE adds actual time and rows. A large gap between estimated and actual rows usually points to a statistics or modeling problem, not a missing index.
Reading a plan
Look for the operator that consumes the most time, then compare its estimated and actual row counts. A nested loop that repeats millions of times often means the planner expected few outer rows. A sequential scan on a selective predicate may mean the index is missing, unusable, or not selective enough. Sort and hash spills indicate insufficient memory. Estimates propagate upward, so one bad scan can distort every join above it.
When to use this knowledge
Use it when a query is slow, when a plan changes after a deployment, or when designing indexes. Plans are also useful for reviewing ORM-generated SQL, which often contains unnecessary joins or repeated subqueries. For simple primary-key lookups, planning is rarely the issue; for analytical queries with many joins, it usually is.
Trade-offs and example
Plan stability competes with adaptivity. Prepared statements can reuse a generic plan that is bad for skewed parameter values. Statistics add maintenance cost and can be stale between refreshes. More indexes give the planner more choices but slow writes.
Example: an order search filters by status = 'open' and created_at > now() - interval '1 day'. With stale statistics saying open is rare, the planner chooses a nested loop over an index. In reality most orders are open, so a sequential scan or a different join order would be faster. Running ANALYZE and reviewing actual row counts exposes the mismatch.