Core Code
Core Code Modern Dev Handbook
DATABASE-ADVANCED Contents
Storage & Index Internals
DB Page Layouts B-Tree Internals Hash, GIN, and GiST Indexes Selectivity & Cardinality Index Maintenance & Bloat Covering & Partial Indexes WAL & Write Amplification MVCC and HOT Updates
Query Planner & Execution
Query Lifecycle Cost-Based Optimizer Basics Reading EXPLAIN ANALYZE Join Strategies Index Scan vs Seq Scan Statistics & Misestimation Plan Regressions Query Optimization Checklist
Transactions & Isolation
ACID Realities Isolation Levels Deep Dive Phantoms, Lost Updates, and Anomalies Deadlocks: Detection and Retries Locking Mechanisms Long-Running Transactions Optimistic vs Pessimistic Concurrency Transaction Safety Checklist
Replication & High Availability
Replication Types Postgres Replication Model MySQL Replication Model Replica Lag and Read-After-Write Failover Strategies Backup Strategies Point-in-Time Recovery HA Architecture Patterns
Sharding & Horizontal Scale
Vertical vs Horizontal Scaling Sharding Patterns Shard Key Design Cross-Shard Queries Rebalancing and Resharding Multi-Region Data Architectures Global Consistency Tradeoffs Sharding Operational Checklist
Postgres vs MySQL Tradeoffs
Storage Engine Differences JSON Support Comparison Indexing Capability Differences Replication Differences Ecosystem and Extensions Interpreting Benchmarks Choosing Between Postgres and MySQL Migration Surface Area
Caching & Redis Strategies
Caching Patterns Redis Data Structures in Production Cache Invalidation Cache Stampede and Thundering Herd TTL Design Strategies Read-Heavy Optimization Cache Consistency Models Cache Observability Checklist
Data Modeling at Scale
Normalization vs Denormalization Soft Delete Patterns Event-Driven Modeling Time-Series Modeling Multi-Tenant Schema Design Audit Trail Design Schema Migration Strategies Data Modeling Checklist

Query Lifecycle

Parse, rewrite, plan, execute: where time goes and where optimization actually matters.
On this page

The Four Phases of a Query

Every SQL query goes through four major phases: Parse, Rewrite, Plan, Execute. Most engineers only see the final execution time. Production debugging requires understanding all four stages.

  • Parse: syntax validation and building an internal representation.
  • Rewrite: query transformations (views expansion, rule application).
  • Plan: choose an execution strategy using statistics and cost model.
  • Execute: run operators and produce rows.

If performance is bad, the problem is usually in the Plan phase — but not always.

Phase 1: Parse

Parsing converts SQL text into an internal tree structure. It validates syntax, resolves table and column names, and assigns data types.

Parsing errors are obvious. Parsing performance issues are rare unless queries are dynamically generated at massive scale.

Phase 2: Rewrite

The rewrite stage can transform your query before planning. Common transformations:

  • Expanding views into their base queries
  • Flattening subqueries
  • Simplifying predicates
  • Applying query rewrite rules (engine-specific)

Important: the query you write is not always the query the planner sees.

Example: View Expansion

CREATE VIEW active_users AS
SELECT id, email
FROM users
WHERE status = 'ACTIVE';
SELECT *
FROM active_users
WHERE email LIKE '%example.com';

The planner sees the expanded query, not the view abstraction.

Phase 3: Planning

The planner generates possible execution plans and estimates their cost. It chooses the lowest-cost plan according to its model.

Key decisions:

  • Index scan vs sequential scan
  • Join order
  • Join strategy (nested loop, hash, merge)
  • Parallel execution or not

Planning relies entirely on statistics and cost assumptions. If estimates are wrong, the plan is wrong.

Phase 4: Execution

The executor runs the chosen plan. Operators pull rows from child operators in a tree structure.

Execution can still fail or degrade due to:

  • Memory spills (sort/hash overflow)
  • Lock waits
  • IO saturation
  • Concurrency effects not visible at plan time

Reading the Plan Shape

You must understand operator trees to debug performance.

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= '2026-01-01';

Look at:

  • Join order (which table scanned first)
  • Estimated vs actual rows
  • Loops (nested loop multiplier effect)
  • Buffer hits vs reads

Why Plan Quality Dominates Performance

A bad plan can be 100x slower than a good plan. No amount of hardware will save a consistently wrong plan.

Examples:

  • Nested loop over millions of rows
  • Hash join with underestimated build side
  • Sequential scan chosen due to misestimated selectivity

Planning Time vs Execution Time

Planning usually takes milliseconds. Execution can take minutes. However, complex queries with many joins can spend significant time in planning.

In OLTP systems, execution time dominates. In analytics queries, planning complexity can matter more.

Failure Modes in Production

  • Wrong join order: explosion of intermediate rows.
  • Plan instability: same query different plan across nodes.
  • Rewrite surprises: view expansion creates unexpected complexity.
  • Parallelism misfire: planner enables parallelism that increases contention.
  • Parameter sensitivity: plan good for one value, terrible for another.

Operational Checklist

  • Always capture EXPLAIN ANALYZE for slow queries.
  • Compare estimated vs actual rows at each node.
  • Identify where row count explodes.
  • Check join order matches data distribution reality.
  • Watch for memory spills and temp file usage.
  • Validate performance under representative parameters.
  • After upgrades, revalidate critical query plans.
  • Keep statistics fresh; planner depends on them.
  • Understand view expansion and CTE behavior.
  • Educate application engineers: plan quality matters more than syntax beauty.

Summary

Every query flows through Parse → Rewrite → Plan → Execute. Performance problems almost always originate in planning decisions. If you can read plan trees and reason about row flow, you can debug most production query incidents.

Cost-Based Optimizer Basics →