Materialize
Appears in EXPLAIN asMaterialize
Buffers a child's rows once so they can be re-read cheaply.
What it is
A Materialize node runs its child once, stashes the rows (in memory or a temp file), and replays them on subsequent reads — useful when a parent (often a join) needs to scan the same input repeatedly.
When the planner picks it
Most often on the inner side of a Nested Loop or Merge Join, to avoid re-executing an expensive child for every outer row / mark-restore.
Is it good or bad?
Helpful only when the child is actually re-scanned. If it ran with loops = 1, the buffering paid off nothing — usually a hint that the surrounding join choice rests on a bad row estimate.
In depth
What it actually does
A Materialize runs its child once, stores the rows (in memory, with the option
to spill to a temp file), and serves them up cheaply on each subsequent read.
It exists to make repeated scans of the same input free instead of re-executing
the underlying subtree.
You'll most often see it:
- on the inner side of a Nested Loop, when the planner expects the inner to be scanned for every outer row;
- under a Merge Join, so the inner side can support the mark/restore pattern Merge Join uses to handle equal-key streaks.
When Materialize wastes its time
loops = 1 on a Materialize is the tell: the planner buffered the rows but
nothing re-read them. The buffering itself paid for nothing.
Materialize (actual rows=1000 loops=1)
Our analyzer surfaces this as "Materialize with no rescans." It almost always points back to a bad row estimate on the outer side: the planner thought many outer rows would come, so Materialize would amortize across them; in reality there was one (or a handful), so the buffering was overhead.
The fix usually isn't to disable Materialize but to fix the estimate
(ANALYZE, extended statistics) so the planner picks a different join shape
in the first place.
Materialize vs Memoize
Materialize buffers one stream and replays it as-is. Memoize is a parameterized cache keyed by lookup values, which is what makes it useful on the inner of a Nested Loop whose outer side repeats keys. Different problem, different fix.
What the analyzer flags here
- Materialize with no rescans — buffered but never re-read (likely a planning artifact)
Settings that influence it
work_memenable_material