Late materialization and sampling
When does reading payload columns late pay, and what makes sampling different from every other reduction?
Late materialization
Inside a columnar scan, columns have two roles. Predicate columns decide which rows qualify. Payload columns produce the output.
- Early materialization builds a full row from all the columns, and then filters it.
- Late materialization evaluates the predicates on the predicate columns only. This produces a position list (or selection vector, an array of the row numbers that passed). Payload columns are then fetched only at the qualifying positions.
The saving is (1 − s) × payload bytes, where s is the selectivity (the fraction that passes). The cost is that fetching by position inside one column is scattered access, not a sequential decode of the whole column. Set the two equal and you get a crossover: above some selectivity, late materialization loses, because decoding everything in order is cheaper than fetching most of it one piece at a time.
The crossover is set by the ratio of sequential to random cost. It is the same ratio, and the same inequality, as the choice between an index and a full scan. One decides whether to touch a table; the other decides whether to touch positions inside a column. The structure is the same, at two different granularities. This suggests that the index-versus-scan inequality is not really about indexes. It is about any decision to gather at a finer granularity than the medium's natural transfer unit: granularity mismatch plus fixed cost per operation, in combination.
Two combinations matter:
- Late materialization stacks with dictionary encoding: predicates run on codes, and in aggressive engines joins and group-bys also run on codes. Decoding is deferred to the final output.
- It depends on position density. A clustered position list is nearly sequential and cheap. A scattered one is not. Granularity mismatch again, now inside a column.
How selection vectors are evaluated by vectorized operators is a matter for the execution engine. The decision and its crossover are the planner's.
In one sentence: late materialization trades a sequential decode for a scattered gather, and its crossover is the same inequality as index-versus-scan, evaluated one granularity down.
Sampling: reducing data without keeping the answer
One more mechanism deserves its own place. Sampling (TABLESAMPLE, block sampling, approximate query processing) is a deliberate data-reduction step. It is the only mechanism in this article that reduces data without preserving the exact answer. That makes it the natural home of the trade between exactness and approximation, and a clean contrast with everything else here. Bloom filters are approximate too, but their error only costs extra reads, never a wrong answer. A sample's error appears in the answer itself. A dashboard that shows "about 1.2 million" can use it; a financial close cannot.
Dynamic or static? Two positions on a dial
Every mechanism in this article sits on one dial: early versus late binding, meaning whether a decision is made from the query text (before seeing data) or from the data (after). Static versus dynamic pruning is one position on it. Early versus late materialization is another. Metadata is space bought to save time; statistics written at commit time are precomputation; and on engines that bill by bytes scanned, every byte not read is money.