After optimization, the executor is handed a plan tree. Describe what that tree contains and how a typical relational executor consumes it to produce rows, including what changes when an operator cannot stream its output.

## What the executor receives The optimizer's output is not code and not SQL - it is a **tree of physical operators**. Leaves produce rows from storage; interior nodes consume rows from their children and produce rows for their parent; the root delivers rows to the client protocol. Typical leaves: sequential/table scan, index range scan, index-only scan, or a lookup by key. Typical interior nodes: filter, projection/expression evaluation, nested loop join, hash join, merge join, hash or sorted aggregation, sort, limit, set operations, and materialization nodes. Each node carries parameters chosen at planning time - which index, which predicate, which join keys, which sort keys, how much memory it may use, and its estimated row count. ## The iterator (Volcano) model The long-standing execution model is demand-driven and uniform: every operator exposes the same three-call interface. - `open()` - initialize state, open children. - `next()` - return the next row or an end-of-stream marker. - `close()` - release resources. The root's `next()` recurses down the tree; each operator calls `next()` on its children as many times as it needs to produce one output row. A filter calls its child repeatedly until a row passes. A nested loop join calls the outer child once, then drives the inner child for each outer row. Three consequences follow directly: 1. **Pipelining.** Rows flow through without materializing the whole intermediate result, so memory use stays bounded for streaming plans. 2. **Early termination.** A `LIMIT` at the root simply stops calling `next()`, and the entire subtree stops working - which is why plans differ so much when a small limit is present. 3. **Incremental delivery.** A client can begin reading rows before the query has finished. ## Streaming versus blocking operators The most important operational property of a plan tree is where its **pipeline breakers** are. **Streaming operators** produce output as they consume input: filter, projection, nested loop join, merge join (given sorted inputs), the probe phase of a hash join, limit. **Blocking operators** must consume their *entire* input before producing the first output row: - **Sort** - cannot know the first row until it has seen the last. - **Hash aggregation and hash join build side** - the hash table must be complete before probing or emitting groups. - **Materialization / spooling nodes** - deliberately buffer a subtree for reuse. Blocking operators hold state proportional to their input, take a memory budget, and **spill to disk** when they exceed it, which turns a CPU-bound operator into an I/O-bound one and is a common source of cliff-edge slowdowns. They also destroy time-to-first-row: with a sort near the root, `LIMIT 10` still requires the full input to be sorted. ## Where transactional semantics enter The executor, not the optimizer, applies visibility: as scans read rows, they check whether each version is visible to this statement's snapshot or acquire the appropriate locks, depending on the concurrency model. It also enforces constraints on writes, fires triggers, and maintains indexes for modified rows. The plan says which rows to touch; the executor decides which of them this transaction may see. ## Modern variations on the same shape - **Vectorized execution.** Instead of one row per `next()`, operators exchange **batches** of hundreds or thousands of rows in columnar form. This amortizes the per-call overhead and enables CPU-friendly tight loops. The tree and its blocking semantics are unchanged. - **Compiled execution.** The plan tree is translated into native code that fuses pipelines between breakers, eliminating interpretation overhead entirely. - **Push-based execution.** Instead of parents pulling, producers push rows into consumers; this maps better to pipeline fusion and parallelism, but the pipeline segments are still delimited by the same blocking operators. - **Parallelism.** A subtree can be executed by several workers with an exchange/gather operator merging their outputs; degree of parallelism is decided at planning time and materializes as extra nodes in the tree. ## Reading a plan tree operationally When a plan is slow, the tree tells you where to look: - Follow the leaves: is a huge scan feeding a selective filter that should have been an index lookup? - Find the breakers: how much data reaches each sort or hash build, and does the memory budget cover it? - Check estimate versus reality per node: a badly under-estimated build side is the usual reason a hash operation spills or a nested loop is chosen where a hash join was needed. - Ask about time-to-first-row: for interactive queries, a breaker high in the tree is often the actual complaint even when total runtime is acceptable. The unifying point for an interview: the plan tree is not a script executed top to bottom - it is a dataflow graph consumed on demand, and its performance character is set by where data must stop and pile up.