SQL Execution Flow¶

From a string to rows¶

When an application sends SELECT ... to PostgreSQL, the text goes through a pipeline of stages inside the server, each turning one representation into the next. Knowing the stages explains where time goes (parsing vs. planning vs. execution), why prepared statements help and sometimes hurt, and why a query's clauses resolve names in the order they do.

1. Connection: a process per client¶

PostgreSQL forks one backend process per client connection (the postmaster accepts connections and forks). Each backend has its own memory for sorts, hashes, caches of catalog data and plans — typically several MB to tens of MB each. That is why PostgreSQL deployments put a connection pooler (PgBouncer, or an application-side pool) in front: thousands of mostly idle client connections share tens of backends. See Connection Pooling. MySQL instead uses a thread per connection, with similar pooling motivations.

2. Parser: text to a parse tree¶

The parser checks syntax only, turning the text into a tree of nodes: a SELECT with a target list, a FROM list, a WHERE expression. It does not know whether tables exist. A syntax error is reported here.

3. Analyzer: names, types, and meaning¶

The analyzer resolves every name against the system catalogs: which table orders is (taking search_path into account), which column customer_id is and its type, which function lower(text) resolves to, which implicit casts apply. Output: a query tree. "Column does not exist" and type errors come from here.

The logical order of clauses¶

Name resolution follows the logical order the SQL standard defines for evaluating a SELECT:

A name can only refer to something defined by an earlier step:

-- Error: alias `total` is defined in step 6; WHERE is step 2
SELECT price * qty AS total FROM orders WHERE total > 100;

-- Works: repeat the expression (or use a subquery / CTE)
SELECT price * qty AS total FROM orders WHERE price * qty > 100;

-- Works: ORDER BY (step 8) comes after SELECT (step 6)
SELECT price * qty AS total FROM orders ORDER BY total DESC;

-- Aggregates exist only from step 3 on: they can't appear in WHERE
SELECT customer_id, sum(amount) FROM orders
GROUP BY customer_id
HAVING sum(amount) > 1000;

-- Window functions are computed in step 5: filter on them via a subquery
SELECT * FROM (
  SELECT id, row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders
) t WHERE rn = 1;

This order defines meaning, not physical execution. The planner may evaluate a WHERE condition before a join, read rows already sorted from an index instead of sorting at step 8, or stop early because of LIMIT — as long as the result equals what the logical order defines.

WHERE vs. HAVING: a condition that doesn't involve an aggregate belongs in WHERE, where it can use indexes and reduces the rows entering grouping. (PostgreSQL moves such conditions from HAVING to WHERE itself in simple cases, but writing them in WHERE makes the intent and the plan obvious.)

4. Rewriter: views and rules¶

The rewriter replaces references to views with their defining queries (a view is a stored query, expanded inline, then optimized together with the outer query), applies rewrite rules, and adds row-level security policies as extra conditions. That is why a condition on a view can be pushed down into the view's tables and use their indexes.

5. Planner: choosing how¶

The planner enumerates ways to compute the query tree — access paths for each table (seq scan, each usable index, bitmap combinations), join algorithms and join orders, where to sort or hash, whether to aggregate by hashing or by sorting, whether to use parallel workers — estimates each one's cost from statistics, and keeps the cheapest. It also simplifies: constant folding, flattening subqueries into joins, converting EXISTS into semi joins, removing unneeded joins. The details are in Query Optimization.

Planning Time in EXPLAIN ANALYZE measures this stage. For a simple indexed lookup it was 0.07 ms in our measurements — comparable to the 0.085 ms execution. For short OLTP queries, planning can be a large share of total time, which is what prepared statements address.

6. Executor: the iterator (Volcano) model¶

The plan is a tree of nodes; each node implements the same interface:

Init()      prepare (open indexes, allocate hash tables)
Next()      return the next row, or "done"
End()       release resources

The client-facing top node calls Next() on its child, which calls Next() on its children, and so on down to the scans: rows are pulled up the tree one at a time.

Limit (10)
  └── Sort (by created_at)
        └── Hash Join
              ├── Seq Scan on orders
              └── Hash
                    └── Seq Scan on customers

This Volcano (Graefe, 1994) design makes every operator composable, and naturally supports pipelining: a Limit stops calling Next() after 10 rows, so a nested loop or index scan beneath it stops early. Some nodes are blocking — Sort, Hash (the build side), aggregation by sort — and must consume their whole input before returning their first row, which is why the same LIMIT 10 can be instant over an index scan and slow over a sort.

The cost of the model is one virtual function call per row per node. Analytical engines (DuckDB, ClickHouse, Velox) instead process vectors of thousands of values per call (vectorized execution) or compile the plan into machine code (HyPer, Umbra; PostgreSQL's optional JIT compiles expressions), gaining 10x or more on scans and aggregations over many rows.

Modifications (INSERT, UPDATE, DELETE) run through the same executor: a ModifyTable node at the top consumes rows from the plan below (which finds the rows to change) and writes new tuple versions, index entries, and WAL records.

Simple vs. extended protocol, and prepared statements¶

Plan caching: a prepared statement runs its first five executions with custom plans (planned with the actual parameter values), then switches to a generic plan (planned once, without values) if that plan's estimated cost isn't significantly higher. Generic plans save planning time but can be wrong for skewed data — the plan that is right for status = 'paid' (49% of rows: seq scan) is wrong for status = 'cancelled' (1%: index scan). plan_cache_mode controls the choice.

Transaction-pooling connection poolers historically broke session-level prepared statements (the next transaction may run on a different backend); PgBouncer 1.21+ tracks them.

Observing each stage¶

Common pitfall¶

Relying on clause order quirks of one database. PostgreSQL and MySQL allow a SELECT alias in GROUP BY as an extension, although by the logical order GROUP BY (step 3) runs before aliases exist (step 6); the same query fails on databases that follow the standard strictly. More subtly, MySQL historically allowed selecting non-aggregated columns not listed in GROUP BY and returned values from an arbitrary row of each group — a silently non-deterministic result (rejected by default since ONLY_FULL_GROUP_BY became default in 5.7). Write queries whose meaning follows from the standard order, and they will mean the same thing everywhere.