In a query execution plan, what distinguishes a streaming (pipelined) operator from a blocking, pipeline-breaking one? Give examples of each and explain the practical consequences of a blocking operator.

## The two behaviours Execution plans are trees of operators through which rows travel from the leaves (scans) to the root (client). Each operator has an inherent timing property. **Streaming / pipelined** — after seeing one input row the operator can already emit output (or decide to discard it). Examples: filter, projection, index scan, index nested-loop join, merge join over already-sorted inputs, limit, union all, and streaming (sorted-input) aggregation. **Blocking / pipeline-breaking** — the operator's semantics require the entire input before any correct output exists. Examples: - **Sort.** Row 1 of the output cannot be known until every input row has been examined, since the smallest value might be last. - **Hash-join build side.** The hash table must be complete before probing can be correct; otherwise a probe row could miss a match that arrives later. - **Hash aggregation and hash DISTINCT.** A group's final value depends on rows that may arrive at any point. - **Explicit materialize / spool / temp-table nodes.** Deliberately buffer an intermediate result. - Some **window functions** and grouping-set implementations that need a full pass. Note the asymmetry inside one operator: a hash join *blocks on the build input* but *streams on the probe input*. Once the table exists, probe rows flow through and produce output immediately. That is why the optimizer's choice of build side matters for both memory and time-to-first-row. ## Consequence 1: latency The first output row of the whole query cannot appear before every blocking operator beneath it has drained its input. A query returning ten rows through a sort still reads and sorts the entire input first. Users experience it as "the query hangs, then everything appears at once", versus a pipelined plan that starts painting rows immediately. This is why *first-row cost* and *total cost* are distinct in cost models, and why some optimizers accept a higher total cost to get a pipelined plan when only a few rows are wanted. ## Consequence 2: memory and spilling A blocking operator holds its input (or a digest of it — a hash table, a sorted run) in a memory grant. Two failure modes follow: - **Underestimated cardinality** → too small a grant → the operator spills to temporary files: external merge sort, or partitioned hash spill. Suddenly the query does multiple passes of I/O and can slow down by an order of magnitude. - **Concurrency multiplication** → each concurrent query holds its own working memory; N sessions each sorting a large input can exhaust memory or push the machine into swap. This is why per-operation working-memory settings and the maximum concurrency are effectively one combined budget, not two independent knobs. ## Consequence 3: plan structure and scheduling Breakers cut a plan into **pipelines**: a maximal chain of streaming operators ending at a breaker. Modern executors treat a pipeline as the unit of scheduling and parallel execution — build the hash table in one pipeline, then run the probe pipeline. Blocking points are also natural checkpoints for adaptive execution: with the build side complete, the engine knows the true row count and can re-decide the join strategy. ## Consequence 4: bounded versus unbounded input A blocking operator cannot be applied to an unbounded stream at all without windowing, since "all input" never arrives. For a database this shows up as: a cursor over a pipelined plan can be held open cheaply and abandoned early, whereas a plan with a sort has already paid for everything by the time you fetch row 1. ## Partially blocking variants Good answers mention that blocking is sometimes avoidable: - **Top-N sort** keeps only N rows in a heap instead of sorting everything — still blocking on input, but memory becomes O(N) rather than O(rows), and no spill. - **Merge join over indexed inputs** avoids a sort entirely, keeping the whole join streaming. - **Sorted-input (streaming) aggregation** emits each group as soon as the key changes, so it is pipelined, unlike hash aggregation. - Pre-sorted or pre-clustered data (an index providing the required ordering) turns a blocking sort into no operator at all. ## How to recognize it in practice Read the plan and mark the breakers: sorts, hash builds, hash aggregates, explicit materialize/spool nodes. Then ask, for each, how many rows it must hold and where the estimate came from. Most "query is unexpectedly slow and hammers temp storage" incidents resolve to a blocking operator whose input was estimated far too small.