Materializing an intermediate result costs memory and delays the first row, so why would a query optimizer deliberately insert a node that buffers an intermediate result instead of recomputing or re-scanning it?

## The trade being made Pipelining is the default for a good reason: no intermediate storage, bounded memory, early first rows. A deliberate materialize (also called spool, temp result, or buffer) node inverts that: it consumes a subtree once, stores the rows, and serves them from storage afterwards. The optimizer inserts it only when *reproducing* rows would cost more than *storing* them. ## Reason 1: the intermediate is read many times The dominant case is a nested-loop join whose inner input is not a cheap index lookup. Semantically, the inner subtree is executed once per outer row. If the inner is a filtered scan producing a small result, executing it a thousand times means a thousand scans. A spool executes it once, keeps the rows, and replays them for each subsequent outer row. The saving scales with the outer cardinality; the cost is one buffered copy. A related case is a recursive or iterative construct where the same working set is revisited, and correlated subqueries whose uncorrelated part can be computed once. ## Reason 2: one producer, several consumers Some plans are DAGs rather than trees: a common subexpression referenced twice, a grouping-set query that aggregates the same input at several granularities, several window functions over the same partitioning. Without materialization the engine either recomputes the shared input per consumer or duplicates the subtree. Materializing once and feeding all consumers converts N computations into one computation plus N reads. This is also the mechanism behind optimizer decisions to materialize a common table expression instead of inlining it into each reference. ## Reason 3: correctness — separating read from write When a statement modifies rows it is also reading — updating a column that is itself part of the access path, for example — a fully pipelined plan can re-read a row it already changed and process it again, potentially forever. This is the **Halloween problem**. The standard remedy is to break the pipeline: materialize the set of rows to be modified (or their identifiers) first, then apply modifications from that frozen set. Here materialization is not an optimization at all; it is required for correct semantics. Recognizing that some breakers exist for correctness rather than performance is a strong answer in interviews. ## Reason 4: pinning expensive, remote or volatile evaluation If a subplan calls an expensive user function, reads a foreign or remote table, or must be evaluated exactly once for semantic reasons, materializing bounds the number of evaluations. It also stabilizes the result: repeated evaluation of something non-deterministic would otherwise produce inconsistent rows across consumers. ## Reason 5: rewind, scroll and reuse A scrollable cursor that can move backwards, or an operator that must restart its input from the beginning, needs the rows to still exist. Some engines can restart a subtree cheaply (re-scan an index), and prefer that; where restart is expensive or impossible (a non-repeatable source), the engine buffers. ## What it costs - **Memory, then temp storage.** Materialization is a blocking step: the buffer must be complete before it can be read repeatedly. If it exceeds its grant it spills, and now the "cheap replay" is disk reads. - **Latency.** The first output row of anything above it waits for the whole subtree. - **Wasted work under early termination.** If the consumer stops after a few rows, buffering everything was pure loss. Optimizers sometimes use a *lazy* spool that fills only as far as consumption requires, precisely to avoid this. - **Estimation sensitivity.** The decision hinges on the estimated number of re-reads and the estimated size of the buffer. Underestimate the size and you spill; overestimate the repetitions and you materialize for nothing. ## How to reason about it when tuning Seeing a materialize/spool node is not automatically bad. Ask: how many times is this consumed, and how big is it? A small buffer replayed thousands of times is an excellent trade. A huge buffer read once is almost always a symptom — either a misestimate, or a plan shape that should have used a hash join (which does its own build-side buffering more efficiently) or an index that removes the repetition. Conversely, if you see the same expensive subtree executed once per outer row with no buffering, the estimate for the outer side is probably far too low.