Query Execution Internals
The problem
A query plan says what strategy the database chose (which join algorithm, which index) but not how that plan actually executes row by row underneath — understanding that execution model explains real performance characteristics (like when results start streaming back) that the plan alone doesn't reveal.
Why now
Joins and query planning, and query optimization and EXPLAIN plans, already covered choosing and reading a plan; this topic is the last mile — how the chosen plan actually runs, connecting each plan node to real, executing code, closing the gap between 'the planner decided this' and 'this is what actually happens.'
Mental model
Most databases execute a query plan as a tree of operators, each pulling rows from its children one at a time (the iterator, or 'Volcano,' model) — a join operator calls next() on each input, combines matching rows, and yields them upward, which is exactly why a query with a LIMIT can stop early without ever touching the whole dataset: execution is lazy and pull-based, not compute-everything-then-filter.
Requires
- Joins & Query PlanningDatabase Engineering
Query execution runs exactly the join strategy joins-and-query-planning already covers choosing; you need the strategy itself understood before its row-by-row execution mechanics add anything beyond restating the choice.
- Query Optimization & EXPLAIN PlansDatabase Engineering
This topic explains what actually happens beneath the EXPLAIN output already covered reading; you need to already be able to read a plan before connecting each of its nodes to the executing operator underneath is meaningful.
Used in
- Understanding why LIMIT queries can be fast even against enormous tables when execution is pull-based
- Vectorized query execution engines (columnar databases), an alternative to row-at-a-time iteration for analytical workloads
- Diagnosing why a query's EXPLAIN plan looks reasonable but actual execution is still slow
- Query engines in analytics tools (Spark, DuckDB) built on the same operator-tree execution model
Projects
- Diagram a query plan as a tree of operators (scan, filter, join, sort) and trace how a single row flows from the leaves up to the final result
- Compare EXPLAIN ANALYZE's estimated versus actual row counts for a query, and explain what a large discrepancy implies about the query planner's assumptions
Examples
- SELECT * FROM huge_table LIMIT 10 can return almost instantly because the pull-based executor stops requesting rows from its child operators the moment 10 have been produced
- A large gap between a plan's estimated and actual row counts (visible in EXPLAIN ANALYZE) usually means the planner's statistics are stale, leading it to choose a suboptimal strategy
Resources
Mastery checklist
- I can explain the pull-based (iterator) model of query execution
- I can explain why LIMIT queries can be fast against very large tables
- I can compare EXPLAIN ANALYZE's estimated and actual values to diagnose planner misestimation
- I can trace a query plan tree and explain what each operator does to the rows flowing through it