Database Query Planner — How SQL Becomes an Execution Plan

Why it matters

Fixing a bad plan often reduces query time from minutes to milliseconds. Understanding the planner turns opaque slow queries into diagnosable problems.

The architecture

Stages: parse SQL, generate logical plan, optimize (rewrite + cost-based decisions), pick physical plan, execute.

Cost model: estimates I/O and CPU based on table stats. Uses row counts, distinct values, NULL fraction, histogram.

How it works end to end

Join algorithms: nested loop (small tables), hash join (large equi-join), merge join (sorted). Planner picks based on stats.

Access methods: index scan (selective), sequential scan (unselective), index only scan (if index covers).

Statistics: run ANALYZE to keep planner informed. Stale stats cause bad plans.