⚙️Query execution
From text to iterators
Engines parse SQL into an internal tree, resolve names and types, rewrite views and subqueries, optimize into a physical plan, then execute operators, scans, joins, sorts, over buffer pools and storage. Latency spikes usually map to one phase: bad estimates, lock waits, redo/fsync, or memory spills, not “mystery slowness.”
🪜Classic pipeline
Names differ by vendor; the story repeats.
| Phase | Output |
|---|---|
| Parse | Syntax tree, catches typos and grammar before semantics. |
| Bind / analyze | Resolved identifiers, types, privileges, and correlation rules. |
| Optimize | Physical plan with cost estimates and chosen algorithms. |
| Execute | Row iterators, vector batches, parallel workers, tuple stream begins. |
EXPLAIN (PostgreSQL): plan as text
EXPLAIN
SELECT o.order_id
FROM orders o
WHERE o.customer_id = 441;| plan |
|---|
| Index Scan using ix_orders_customer on orders o |
Wall-clock only
- Blaming CPU when the dominant wait is log fsync, buffer eviction, or lock queues.
- Ignoring parallel skew, few workers doing almost all the work.
- Measuring end-to-end time without per-operator row counts in traces.
Stage-aware observability
- Learn the top wait events / pg_stat_activity fields for your engine.
- Compare actual rows to estimates after statistics or parameter changes.
- Correlate sporadic latency with lock graphs and transaction age.
Blocking operators
Sorts and hash builds can materialize full intermediate sets, when memory is tight they spill to disk and become I/O bound. That is why work_mem-style knobs move symptoms between operators.
TL;DR
Query execution walks a physical plan tree: scans/seeks read rows, joins combine them, sorts order them, aggregates fold them. Operators can be streaming or blocking (must finish input before emitting). Memory grants and parallelism change wall time, tune with measured plans, not folklore.