Compile or interpret, materialize or pipeline

What does the planner decide about how a plan runs, and why do barriers make other features free?

How a plan runs (interpreted, vectorized or compiled, pulled or pushed) is execution-runtime's subject. Two decisions about it fall to the planner.

Compile or interpret

Compilation is a fixed cost per query and saves a little per tuple, so it pays only above a break-even row count, n* = C_compile / Δc (50 ms ÷ 20 ns = 2.5 million rows). As vectorized interpretation got faster, Δc shrank and the threshold rose, which is why engines now compile adaptively: interpret at once, compile in the background, and switch when the code is ready, so the planner never has to predict n. Compiled query execution derives it.

Materialize or pipeline

A pipeline breaker is an operator that must consume its whole input before it emits anything: a sort, a hash-table build, a full aggregation. Between breakers, tuples flow along without being stored anywhere.

Pipelining's benefits are the obvious ones: O(1) state per stage instead of O(n), a low time to the first tuple, and no cost of writing intermediate data out and reading it back.

Materialization's benefits are usually left off the list. There are five:

1. Observable cardinality. The true size is now known and not estimated. This is the input that adaptivity needs and cannot get any other way.
2. A restart point. After a failure, recovery resumes from the boundary and not from the source.
3. Shareability. Two consumers can read the result. This is the precondition for sharing common sub-expressions inside a plan.
4. Decoupled parallelism. The producer and the consumer can run at different degrees of parallelism. Without a boundary they must match.
5. Backpressure removal. The producer no longer has to run at the consumer's pace.

Read the list again and the picture inverts. These are not consolation prizes for having been forced to block. They are option value, and the price is latency plus space. A pipelined plan has declined to buy the option. A materialized plan has bought it, whether or not it intends to use it.

This is also where the barrier observation, noted in Data reduction, becomes general enough to name. In a shuffle-based distributed engine the boundary exists anyway, for data-movement reasons, so all five benefits arrive free. In a pipelined single-node engine, getting any one of them means inserting a breaker on purpose and paying for it. That is why adaptivity, runtime filtering and cross-operator sharing appeared first and most naturally in stage-based engines. Their designers were not wiser. Their execution model had already paid.

The pattern is useful for prediction: when you want a system to observe itself, correct itself or share work, look first for a stopping point it already has. If there is none, the real price of the feature is the barrier, not the feature. There is a design warning too. A system with no barriers is not just fast. It is unobservable, and every diagnostic you want later must be paid for separately.

In one sentence: compile when rows exceed compile cost over per-tuple saving; materialization buys observability, restartability, sharing, decoupled parallelism and backpressure relief, priced in latency and space, and blocking operators are where that bundle has already been paid for.