Walk through how the classic Volcano (iterator) execution model runs a query plan — what open(), next() and close() do on each operator — and explain where its CPU overhead comes from.

## What a plan is before we talk about execution After parsing and optimization, a SQL statement becomes a *physical plan*: a tree of operators. Leaves read data (sequential scan, index scan); interior nodes transform it (filter, project, join, sort, aggregate, limit); the root hands rows to the client. Nothing in the tree is SQL any more — each node is a small piece of executable machinery with a fixed interface. ## The iterator interface The Volcano model (from the Volcano research system; also called the *iterator model*) gives every operator the same three methods: - **open()** — allocate state and recursively open children. A scan positions itself at the start of the heap or index; a hash join may build its hash table here. - **next()** — return exactly one tuple, or an end-of-stream marker. To produce it, the operator calls `next()` on its child (or children) as often as needed. - **close()** — free buffers, latches and temp files, and recursively close children. Because every operator both *implements* and *calls* this interface, any operator plugs above any other. That uniformity is why one executor runs both a two-table plan and a thirty-table plan. ## Demand-driven control flow Control flows top-down and *pulls*: the client asks the root for a row, the root asks its child, and so on until a scan finally touches a page. The tuple then returns back up as a return value. Producing one row at the root costs one call per operator level. A filter is essentially: ``` next(): loop: t = child.next() if t is EOF: return EOF if predicate(t): return t ``` ## Why this design won and still ships - **Pipelining.** Rows flow through without materializing intermediate results, so memory is O(operator state), not O(rows). - **Early first rows.** The root can return row 1 before the scan has read page 2. - **Free early termination.** A row limit simply stops calling `next()`; everything below unwinds. - **Composability.** New operators need no knowledge of their neighbours. - **Blocking operators still fit.** Sort or a hash build just consume their entire input inside the first `next()` (or in `open()`), then stream results out afterwards. ## Where the overhead comes from For an OLTP statement touching a handful of rows this bookkeeping is invisible. On a 50-million-row scan it dominates: 1. **One indirect (virtual) call per row per operator level.** A five-deep plan over 50M rows is 250M calls the CPU cannot inline and often mispredicts. 2. **Interpreted expressions.** A predicate like `a + b > 10` is usually a small expression tree walked per row, with more dispatch and generic typed values. 3. **State save/restore.** Because `next()` returns after one row, cursors and counters live in operator fields rather than registers. 4. **Poor locality.** Running operator A's code, then B's, then C's for every single row thrashes the instruction cache and blocks loop unrolling, SIMD and effective branch prediction. 5. **Tuple handling.** Per-row copying or deforming into a generic tuple representation, plus null bookkeeping. The vectorized-execution literature measures roughly an order of magnitude of overhead versus a hand-written loop for simple scan-and-aggregate work: most cycles go to the machinery, not the data. ## What engines do about it Two families of fixes, both keeping the operator tree: - **Vectorized execution** — `next()` returns a *batch* (say 1024 rows, column-wise) instead of one row, amortizing the call and letting inner loops be tight and SIMD-friendly. - **Query compilation** — generate machine code that fuses adjacent operators into one loop so tuples stay in registers and there is no per-row call at all. Row-at-a-time iteration survives where it is genuinely right: OLTP plans, index nested-loop lookups, and any plan whose row counts are small enough that latency and simplicity beat raw throughput.