The problem
Every backend depends on a database, but most engineers treat the planner as a black box. Building one end to end shows exactly why a query is fast or slow — and what an index really buys you.
How it works
- 01SQL text
- 02Lexer + parser
- 03Planner
- 04Index or full scan
- 05Join
- 06Aggregate + sort
- 07Result + plan
What was hard
- A B+ tree with linked leaves for range scans, duplicate keys for non-unique indexes, and a structural checker the tests run after thousands of random inserts and deletes.
- The planner pushes each WHERE condition down to its table, uses an index for =, IN, <, >, BETWEEN — and skips it when it would match over 30% of rows, because a full scan is then cheaper.
- Joins pick a strategy from what is indexed: index nested loop, hash join, or nested loop. A property test checks all three return identical results.
- BEGIN / COMMIT / ROLLBACK with an undo log that keeps every index in step; SQL NULL semantics; errors that point to the exact line and column.
The result
12 automated tests, including property tests that compare index plans with full scans on 60 random queries. Every query in the live console shows the plan that ran, with real row counts and timings.