mailwal

Issue-26: What Happens Before Your Query Touches a Single Page

Bytes & Database's

Issue-26: What Happens Before Your Query Touches a Single Page

Bytes & B-trees · Issue #026 — Query Parsing | Parse tree → analyzer → rewriter → planner

Oct 25
∙
Preview

You already know indexes, WAL, and MVCC decide how work lands on disk. This lesson opens the door before all of that: the moment a SQL string becomes a plan the executor can run.

Big shops burn real CPU here. Oracle calls a cache miss a hard parse. MySQL’s Performance_schema tracks SQL/parse. Postgres caches paths for prepared statements so the same text does not rebuild a tree on every execute. If you only read EXPLAIN, you are looking at the end of this pipeline—not the start.

What you will walk out with: a working local simulator of lexer → parse tree → bind → rewrite → plan choice, plus a live metrics board you can poke.

Success criteria

You are done when all of these are true:

  1. python3 query_parsing_demo.py finishes and writes non-zero metrics under metrics/.

  2. ./start.sh serves the board at

http://127.0.0.1:8096/

  1. and Run query parsing demo moves the counters.

  2. You can point at one sample query and name which stage flattened IN (SELECT …) and which stage chose IndexScan vs SeqScan.

  3. python3 -m pytest tests -q is green.

The pipeline in one breath

SQL text is not a plan. It is ink. The engine walks five jobs in order:

Industry wrinkle most tutorials skip: parse ≠ plan. Postgres’s raw parse tree is almost never what you see in EXPLAIN (FORMAT JSON). Production debugging that mixes “bad SQL text” with “bad plan” wastes hours. Separate the stages and the bug class becomes obvious.

Another quiet tax: search_path and privileges fail in analyze, not in the planner. A missing relation error is a bind failure. A slow query with a correct tree is a later problem.

Diagrams

Diagram 1 — Pipeline architecture

Read it: SQL enters on the left.

Parse packs lex + tree + bind.

Rewrite reshapes the tree.

Plan is the handoff into the rest of the engine (indexes, buffers, WAL from earlier issues). The dashed line is the bridge to the storage stack you already studied—not a new product, the same database continuing its day...

User's avatar

Continue reading this post for free in the Substack app

© 2026 System Design Roadmap
548 Market Street PMB 72296, San Francisco, CA 94104