What happens when you run a SELECT?

Every SELECT runs a miniature compiler: text in, execution plan out, rows back. The pipeline has five stages before a single row moves, and each stage's failure mode is different — parse errors are yours, plan mistakes are the optimizer's, and execution slowness is usually the storage's.

Step 1 — parse

sql

SELECT u.name, count(*) FROM users u JOIN orders o ON u.id = o.user_id
WHERE o.total > 100 GROUP BY u.name;

The SQL text is tokenized and parsed into a parse tree (a C structure, not a string). The parser does no semantic checking — count(*) isn't known to be an aggregate yet. Cost: ~10-100µs depending on query size; grammar errors surface here (syntax error at or near "...").

Step 2 — analyze (bind)

The analyzer resolves every identifier against the catalog: users is table 16385 in schema public, u.name is column 1 of type text, > is an operator with an implementation. Type mismatches are caught here (operator does not exist: text > integer). This is where your mistakes are found — and where a misspelled column costs a catalog lookup per node.

Step 3 — rewrite

The query tree is rewritten through the rule system: views expand into their defining SELECTs, rules and security barriers apply, and common subqueries get flattened. You think you wrote a view query; the executor sees the underlying join. Mostly invisible, sometimes surprising (view permissions are enforced here).

Step 4 — plan

The planner's job: find the cheapest execution plan. For each join it considers join orders (up to a limit — beyond 12 relations it switches to heuristics via join_collapse_limit/GEQO), and for each table it costs alternatives: seq scan vs index scan vs bitmap scan, using statistics: reltuples, n_distinct, histograms of column distributions (from ANALYZE), plus cost constants (random_page_cost, cpu_tuple_cost, seq_page_cost). The estimate — "rows = 13.4" — comes from those histograms. The output is a plan tree:

plaintext

HashAggregate  (cost=...)
  ->  Hash Join  (cost=...)
        ->  Seq Scan on users
        ->  Hash
              ->  Seq Scan on orders
                    Filter: (total > 100)

Cost numbers are in abstract units; the shape of the plan is what matters. Planning: ~100µs-1ms on a real system — cheap once, expensive if you run it 10,000 times a second (prepared statements exist for exactly this).

Step 5 — execute: the volcano model

The executor runs the plan tree with pull-based iteration: the root node asks its children for tuples, they ask theirs, recursively — each node is a "next()" call. The data flows up the tree: Seq Scan produces a tuple, Hash Join matches it against the build side's hash table, GroupAggregate accumulates. Tuple-at-a-time means a function call per tuple per node — that's why "simple query" overhead (~10-50µs) is mostly this plumbing, and why vectorized engines (DuckDB, ClickHouse) are faster per tuple on wide scans: they pull batches, not tuples.

Step 6 — storage: the buffer pool

Every heap and index page access goes through the buffer pool: a page-cache hit is a memory copy (~1µs); a miss means reading from disk (~50µs-5ms). The executor doesn't care which it is — pg_buffercache shows the split. The planner's estimates include a guess at this (defaulting to assuming uncached pages — hence random_page_cost tuning).

Step 7 — output

Filter predicates, expressions, and projections run per tuple; the final result is copied into the wire protocol and streamed to the client. With a slow client, the send can dominate — that's why LIMIT doesn't make a query fast, it just stops it early.

What it costs

Parse+analyze: ~20-200µs. Plan: ~50µs-1ms. Execute (cached, small result): ~10-100µs.
Real-world: a single-row SELECT by PK with warm cache ≈ 0.1-0.5ms wall time; of which the actual lookup is ~1% — the rest is the pipeline.
EXPLAIN ANALYZE turns every number real:

sql

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM orders WHERE total > 100;
                                            QUERY PLAN
----------------------------------------------------------------------------------------------
 Aggregate  (cost=2338.00..2338.01 rows=1 width=8) (actual time=12.344..12.345 rows=1 loops=1)
   ->  Seq Scan on orders  (cost=0.00..2235.00 rows=41200 width=0)
         (actual time=0.023..10.192 rows=41234 loops=1)
         Filter: (total > 100)  Rows Removed by Filter: 58766
 Planning Time: 0.180 ms
 Execution Time: 12.345 ms

The lesson in one line: 58,766 rows scanned and discarded by the filter — the planner guessed 41,200 matches, reality was 41,234, fine. When these numbers diverge by 10x, that's when you get the 10-minute query. The plan is a bet; statistics are the odds; ANALYZE is how you keep them honest.