Backend / Databases / SQL / 03_sql_processing.md

How a query is processed

Updated 6 interview angles 3 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;

That is also the whole WHERE versus HAVING distinction: WHERE filters rows before grouping, HAVING filters groups after. Putting a condition in HAVING that could have gone in WHERE means the engine grouped rows it was about to discard.

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, BUFFERS)
SELECT * FROM books WHERE author = 'Le Guin';
text 
Seq Scan on books  (cost=0.00..2891.00 rows=1 width=68)
                   (actual time=0.02..18.4 rows=214 loops=1)
  Filter: (author = 'Le Guin'::text)
  Rows Removed by Filter: 99786

The number that matters is rows=1 estimated against rows=214 actual. The planner believed one row would match, so a sequential scan looked cheap. Being wrong by two orders of magnitude is how a query that was fast in staging becomes a sequential scan in production.

The fix is usually ANALYZE books; rather than rewriting the query — stale statistics are the most common cause of a bad plan. See EXPLAIN and EXPLAIN ANALYZE 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 condition in HAVING that belongs in WHERE makes the engine group rows it is about to throw away.
  • “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
Database Indexes — Fundamentals