Two enable_* toggles for two plan nodes that both, loosely, cache rows to avoid recomputing them — which is exactly why they get confused, and why they’re worth taking together. They are not variations on one idea. Materialize is a dumb buffer; Memoize is a smart cache. Pinning that difference down is the point of this post. Both default on, both user context, same family framing as enable_async_append: diagnostic instruments, not tuning knobs.
Materialize: buffer the whole thing, once
A Materialize node runs its child plan once, stashes every row the child produces, and then serves those rows from the stash on subsequent reads. It’s an unconditional, undifferentiated buffer — it doesn’t look at values, doesn’t have keys, doesn’t decide what’s worth keeping. It just holds the entire child output so something above it can scan that output more than once without re-executing the child.
That’s useful in a few specific spots. The classic is a merge join whose inner side needs to be rescanned when the outer side has duplicate keys: rather than re-run the inner scan, the planner drops a Materialize over it so the rescans hit the buffer. It also shows up to decouple an expensive subplan from a node above that would otherwise execute it repeatedly. When the buffered data outgrows work_mem, Materialize spills the overflow to a temporary file on disk — it will hold the whole thing no matter how big, paying I/O to do so.
A note on the switch, shared with a couple of other family members: enable_material = off doesn’t truly forbid materialization, because some plans require it for correctness. It prevents the planner from inserting Materialize as a mere optimization, while still allowing it where the plan won’t work otherwise. You’re discouraging the optional uses, not banning the node.
Memoize: a keyed cache for repeated lookups
Memoize, added in PostgreSQL 14, looks superficially similar — it also sits in a plan and avoids re-running a child — but underneath it is a different animal. It is a cache keyed on the parameters of a parameterized inner scan inside a nested loop, and it exists for one situation: the outer side of a nested loop feeds the same values into the inner side over and over, and the inner lookup for a given value always returns the same rows. Cache those rows under that value, and every repeat of the value is a cache hit that skips the inner scan entirely.
The canonical shape is a nested-loop join where the outer relation has few distinct values in the join column but many rows. Join 100,000 outer rows against an inner table on a column with only 5 distinct values, and a plain nested loop does 100,000 inner index scans; with Memoize, it does 5 — one real lookup per distinct value, 99,995 cache hits. In EXPLAIN, the node appears as Memoize with a Cache Key: line naming the parameter, and an actual line carrying the diagnostic gold: Hits, Misses, Evictions, and Overflows.
Three differences from Materialize are the whole distinction, and they’re worth stating flatly. First, Materialize stores its child’s entire output as one undifferentiated blob; Memoize stores separate row sets per parameter value, addressed by key. Second, Materialize caches unconditionally; Memoize only pays off when values repeat, and the planner chooses it based on a statistical estimate of how few distinct values there are. Third — and this is the sharp one — Materialize spills to disk when it overflows work_mem, but Memoize never spills: if a value’s row set is too big to cache, Memoize simply doesn’t cache it, because writing a cache to disk would defeat the entire purpose of being a fast in-memory shortcut. Memoize’s memory budget is work_mem × hash_mem_multiplier, since it’s hash-keyed; Materialize uses plain work_mem.
So: same surface (“avoid re-doing work”), opposite mechanism. Materialize is order-agnostic bulk buffering for rescans; Memoize is value-aware caching for skewed repeated lookups.
Symptoms that warrant flipping them
The two have different tells, fitting their different jobs.
enable_memoize = off is the one you’ll reach for more often, because Memoize’s payoff hinges entirely on a distinct-values estimate that can be wrong. Its diagnostic output makes the failure legible: a Memoize node whose EXPLAIN (ANALYZE) shows a poor hit rate — many Misses relative to Hits, or nonzero Evictions and Overflows — is a cache that isn’t earning its keep, usually because the planner underestimated the number of distinct key values, so the cache thrashes (evicting entries it needs again) or overflows (giving up on caching large row sets). When you see that, SET enable_memoize = off for the session, re-run, and compare the plain nested loop (or whatever the planner picks instead) against the memoized version. If the un-memoized plan is faster, the cache was overhead, and the real fix is statistics — ANALYZE or a higher statistics target on the join column so the planner estimates the distinct-value count correctly and only reaches for Memoize when it genuinely helps.
enable_material = off is rarer, and the tell is a Materialize node spilling — Disk usage on the materialize, or a buffered child far larger than work_mem — feeding a plan you suspect would be better as something else (often this is really a sign the planner chose a merge join where a hash join belonged). Flip it off for the session to see what the planner does without the optional buffer; if a different join strategy wins, the Materialize was a symptom of the deeper join-method choice, and that — driven by statistics, work_mem, or cost constants — is what to address.
For both, the family rule holds in full force. Memoize and Materialize are each the right plan for their particular shape — skewed repeated lookups and rescan buffering respectively — and disabling either cluster-wide robs the planner of a tool it uses correctly far more often than not. Diagnose with the switch, read the cache counters or the spill, fix the underlying cause (almost always statistics), and set it back.