Backend / Databases / SQL / 03_sql_processing.md

How a query is processed

Updated 6 interview angles 4 min read source
On this page5
  1. The pipeline before execution
  2. The logical order of clauses
  3. Reading what it actually did
  4. Related
  5. Interview angle

How a query is processed

Two orders matter and they are different: the order the engine does its work in before executing anything, and the logical order the clauses evaluate in, which is not the order you wrote them.

The pipeline before execution

text 
SQL text
  ├─ parse      syntax tree; a syntax error stops here
  ├─ bind       resolve names against the catalogue
  ├─ rewrite    expand views, apply rules
  ├─ plan       choose access paths and join order by cost
  └─ execute    run the physical plan

The plan stage is where performance is decided. Two queries returning identical rows can differ by a factor of a thousand because the planner chose a sequential scan over an index, or a nested loop over a hash join. Nothing you do in application code affects that; the statistics do.

The logical order of clauses

Written order and evaluation order barely overlap:

Written Evaluated
SELECT 5
FROM / JOIN 1
WHERE 2
GROUP BY 3
HAVING 4
ORDER BY 6
LIMIT 7

This is not trivia — it explains rules people otherwise memorise:

sql 
SELECT author, count(*) AS n
FROM books
WHERE n > 1            -- ERROR: column "n" does not exist
GROUP BY author;

WHERE runs at step 2 and the alias n is not created until step 5, so it does not exist yet. The same alias works in ORDER BY, which runs at step 6:

sql 
SELECT author, count(*) AS n
FROM books
GROUP BY author
HAVING count(*) > 1    -- filter groups, step 4
ORDER BY n DESC        -- alias is fine here, step 6
LIMIT 10;

WHERE versus HAVING

That is also the whole WHERE versus HAVING distinction: WHERE filters rows before grouping, HAVING filters groups after. A non-aggregate condition written in HAVING costs nothing extra in PostgreSQL, which moves it into the scan (SQL fundamentals); it belongs in WHERE for the reader.

PostgreSQL and MySQL also accept a SELECT alias in GROUP BY (GROUP BY c), an extension to the standard; HAVING n > 1 still fails with column "n" does not exist.

Gotcha: the logical order is a semantic definition, not a promise about execution. The planner reorders freely as long as the result is identical — it will push a WHERE predicate down into an index scan, or stop early on a LIMIT. Do not reason about performance from this table.

Reading what it actually did

sql 
EXPLAIN ANALYZE
SELECT c.name FROM orders2 o JOIN customer c ON c.id = o.customer_id
WHERE o.status = 'pending';
text 
Nested Loop  (rows=1) (actual rows=49975.00 loops=1)
  ->  Seq Scan on orders2 o  (rows=1) (actual rows=49975.00 loops=1)
  ->  Index Scan using customer_pkey on customer c
        (actual rows=1.00 loops=49975)

The number that matters is rows=1 estimated against rows=49975 actual. Believing one order would match, the planner chose a nested loop that probes the customer index once per order, the right plan for one row and the wrong one for fifty thousand: 150,466 buffers instead of 1,082. The statistics had been taken when the table held 50 rows.

The fix is usually ANALYZE orders2; rather than rewriting the query — stale statistics are the most common cause of a bad plan. See Reading a query plan and VACUUM, autovacuum, table bloat, and transaction wraparound.

Interview angle 6

  • “What happens between submitting SQL and getting rows?” - parse into a syntax tree, bind and validate against the catalogue, rewrite (views, rules), plan and optimise by estimated cost, then execute. The optimiser stage is where performance is decided.
  • “What is the logical order of evaluation?” - FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT - not the written order. That is why a SELECT alias cannot be used in WHERE but can in ORDER BY.
  • “WHERE or HAVING?” - WHERE filters rows before grouping, HAVING filters groups after. A non-aggregate condition in HAVING is moved into the scan by PostgreSQL, so it is a readability issue, not a cost.
  • “Why does the planner pick a bad plan?” - stale or insufficient statistics, so its row estimates are wrong. A sequential scan where you expected an index usually means it estimated the result set as large. Refresh statistics before rewriting the query.
  • “How do you read a plan?” - EXPLAIN ANALYZE and compare estimated against actual rows. A large discrepancy points at statistics; the most expensive node and the join method tell you where the time went.
  • “Does the logical order tell you how it executes?” - no. It defines the semantics; the planner reorders freely as long as the result is identical, pushing predicates into scans and stopping early on a LIMIT.
Previous
How a B-tree index works