Skip to content

Late-Materialization Query Plan

Query-execution method — instantiates Demand-Triggered Deferred Evaluation

Carries lightweight column positions through a query and reconstructs full rows only at the end, for only the rows and columns that survive.

In a columnar query engine, late materialization keeps data in its compact, column-at-a-time form for as long as possible and defers materialization — stitching columns back into whole rows — to the latest point in the plan. Filters, joins, and aggregations run over column segments and lightweight row positions (or dictionary codes); only the rows that survive all of that, and only the columns the output actually projects, are ever reassembled into full records. The distinguishing idea is that the expensive reconstruction is not a fixed early step but a placement decision pushed downstream, so the engine reconstructs the few surviving tuples rather than the many it starts with.

Example

A dashboard query asks for the names and totals of customers in one region whose lifetime spend exceeds a threshold — over a fact table with 300 columns and roughly a billion rows. An early-materialization plan would rebuild wide rows first and then filter; late materialization does the opposite. It scans just the region column and produces a bitmap of matching positions; scans lifetime_spend at those positions, narrowing to the ~0.5% that clear the threshold; and only then, holding a few million surviving positions, fetches customer_name and the aggregate for exactly those rows. The 300-column width never enters the hot path — the plan touched three columns and assembled full records once, at the projection on top.

The win is that cost tracks output size, not input size. Had the query instead selected nearly everything, the planner would notice the projection is wide and the filter barely selective and switch to materializing earlier: lateness is a default, not a dogma.

How it works

  • Pass positions, not rows. Operators exchange column vectors and row-position lists (or selection bitmaps), so a filter narrows a set of positions without ever assembling a tuple.
  • Track which columns are truly needed. The plan records, per operator, the minimal column set downstream consumes, and fetches nothing else.
  • Reconstruct last, for survivors only. Full-row assembly happens at or near the output, applied to the positions that survived every filter and join.
  • Escalate to early materialization when lateness stops paying — a column feeding many operators, or a filter so unselective that carrying positions costs more than carrying rows.

Tuning parameters

  • Materialization point — where in the plan reconstruction happens. Later saves work when filters are selective; earlier avoids re-fetching a column that many operators each need.
  • Position representation — row-id lists vs. selection bitmaps vs. dictionary codes. Denser encodings cost less to carry but constrain which operators can act on them directly.
  • Selectivity estimate — how selective the planner believes each filter is; it decides whether lateness pays, and a wrong estimate is what makes lateness backfire.
  • Column-fetch access — fetch a needed column in full vs. only at surviving positions (random access). Random access wins when few rows survive but is slower per element and thrashes if survivors are scattered.

When it helps, and when it misleads

Its strength is needle-in-haystack analytical queries over wide tables, where the output is a tiny fraction of what was scanned. Carrying positions rather than rows keeps the wide, mostly-irrelevant record out of every intermediate operator.

It misleads when the filter is weak or the projection wide: carrying positions and doing scattered late fetches can cost more than an early rebuild, and random-access re-fetching at sparse surviving positions can thrash storage. The classic misuse is treating "late is always better" as a rule and skipping the selectivity check, so the plan defers reconstruction that should have happened early. The discipline is to let a cost model choose the materialization point per query from real selectivity estimates rather than hard-coding lateness.[1]

How it implements the components

Late-Materialization Query Plan fills the when-to-realize side of the archetype for a query engine — placement and thrift, not demand detection or predicate logic:

  • realization_path — the placement of the materialization step: pushed as late as the plan allows, applied to survivors.
  • demand_boundary — the projection/output is the boundary that finally demands whole tuples.
  • latency_and_waste_budget — reconstruct only surviving rows and needed columns; never pay to assemble records a filter will discard.
  • prefetch_or_eager_exception_rule — the escape to early materialization when selectivity or column reuse makes lateness the costlier choice.

It does not decide which rows to read at the source — moving the filter down to the scan is Predicate Pushdown — nor hold a whole query as a deferred object, which is the Deferred Database Query Object.

Notes

Late materialization and predicate pushdown are complementary, not alternatives: pushdown reduces the rows entering the plan at the bottom; late materialization reduces the rows reassembled leaving it at the top. Mature columnar engines apply both, and a plan that does one but not the other usually leaves the larger win on the table.

References

[1] Late materialization is a core technique of column-store databases (the C-Store / MonetDB line of engines), where keeping data columnar and reconstructing tuples as late as possible is a principal source of their analytical speed. Whether it beats early materialization is query-dependent and decided by a cost model. ↩