Predicate pushdown and late materialization

A filter near a scan may reduce rows reaching a join or aggregate even if the scan still reads every relevant page. An index condition or storage-level pruning can avoid different work: searching qualifying keys, skipping a partition, or rejecting an entire block using metadata. A residual filter applied after retrieval is different again. Read the plan's condition placement and the storage format before reporting an I/O reduction. The same logical selectivity can lead to very different bytes moved when qualifying rows are packed together versus scattered across many pages.

The semantic restrictions from relational rewrites remain in force. An outer join can preserve rows that a moved right-side predicate would handle differently; a LIMIT or window calculation can change which rows qualify when evaluation order changes. Volatile expressions and expressions that can error also prevent simple total-function reasoning. Security barriers impose another boundary: evaluating a user expression before authorization can expose information through its behavior. PostgreSQL documents special treatment for leakproof functions in row-security evaluation. A seemingly faster placement is not valid if it changes permitted observation or the required result.

A column-oriented execution path can scan a narrow predicate column, retain the positions of qualifying records, and fetch wide output columns for those positions later. That avoids reconstructing full tuples that will soon be rejected. A position list, range set, or bitmap must identify the same logical rows across all participating columns. Updates, compaction, deleted rows, and snapshot visibility make that identity a storage-engine contract, not an assumption that the nth physical value always belongs to the nth current row.

Late materialization can trade sequential value movement for gathering scattered values. If most rows survive, positional bookkeeping and repeated column access may cost more than early reconstruction. Compressed representations and vector operators can sometimes keep positions cheap; variable-length values and repeated joins can make reconstruction harder. The Abadi materialization paper examines these design choices in column stores, rather than claiming one universal best point. PostgreSQL index-only access illustrates a related separation between available index values and heap visibility, but it should not be described as the same late-materializing column engine.

State whether an estimate concerns storage-device reads, memory-controller traffic, decoded values, operator payload, or final network bytes. Those quantities can differ sharply. Skipping payload decoding may save CPU without reducing already-prefetched device bytes; avoiding tuple reconstruction may reduce allocations while leaving output volume unchanged. Compare the same final rows and ordering under a stable snapshot. Include position-buffer lifetime and the effect of selectivity on locality. A smaller intermediate row count is an explanation to test, not an automatic statement about elapsed time.

The cache lab isolates one access-pattern consideration without modeling a column store. Reset, set both C=4 and s=4, predict twelve hits in the initial sixteen requests, then Play or Step. Separately Reset, set both C=4 and s=1, predict no initial hits, then Play or Step. The fixed modulo-sixteen stride changes the address cycle; it does not generate positions from a SQL predicate or map logical rows to disk pages. Explain why actual late-fetch traffic needs a real survivor-position distribution rather than substituting the toy stride for a query's selectivity.