Query Execution

From SQL text to query plan — parsing, optimization, and execution operators

Why

Understanding how a database turns your SQL into a physical execution plan explains why the same logical query can run in 10ms or 10 minutes depending on how it's written. The query optimizer makes cost-based decisions that seem opaque — until you understand what inputs it uses (table statistics, index availability, row estimates) and what operators it assembles (scans, joins, sorts, aggregates).

Intuition

SQL is declarative — you say what you want, not how to get it. The query engine's job is to figure out the most efficient "how." It parses SQL into a logical plan (a tree of relational algebra operators), then uses table statistics to estimate the cost of many physical plans, and picks the cheapest one. The physical plan uses concrete operators: sequential scan, index scan, hash join, nested loop, sort, hash aggregate.

Explanation

Query processing pipeline

SQL text ↓ Parser → Abstract Syntax Tree (AST), syntax validation ↓ Analyzer → Resolve table/column names, type checking ↓ Rewriter → Expand views, apply rules ↓ Planner → Generate logical plan → enumerate physical plans → estimate costs using statistics (pg_statistic) → pick lowest-cost plan ↓ Executor → Execute physical plan operators, return rows Physical operators: Seq Scan → full table scan, no index Index Scan → traverse B-tree, fetch rows by pointer Index Only Scan → serve query entirely from index (covering) Hash Join → build hash table on smaller table, probe with larger Nested Loop → for each outer row, scan inner (good for small sets) Merge Join → merge two pre-sorted inputs (good for sorted data) Hash Aggregate → GROUP BY using a hash table Sort → ORDER BY, feeds Merge Join

Statistics and plan stability

-- Statistics the optimizer uses (Postgres) pg_statistic: per-column histograms, most common values, null fraction pg_class: row count estimate (reltuples), page count (relpages) -- Statistics go stale as data changes → optimizer makes bad estimates ANALYZE orders; -- refresh statistics for one table ANALYZE; -- refresh all tables -- Plan regression: after a data volume change, the optimizer may -- choose a different (worse) plan because row estimates changed. -- Force a specific plan for debugging: SET enable_seqscan = off; -- discourage seq scans SET enable_hashjoin = off; -- discourage hash joins -- (always reset after debugging — never leave these in production)

Stale statistics are the most common cause of sudden query regressions. When a table's data distribution changes dramatically (large import, purge), run ANALYZE immediately. Autovacuum includes ANALYZE, but it may lag behind bulk operations.

← Previous Next →