Memoize

A Memoize node sits on the inner side of a Nested Loop and caches results per distinct lookup key. When the same key recurs, it serves the cached rows instead of re-running the inner scan. Added in PostgreSQL 14.

When the outer side repeats join keys, so caching inner results pays off — especially with skewed, low-cardinality keys.

Often a big win for repetitive nested-loop lookups. Watch the cache hit ratio and evictions in EXPLAIN ANALYZE: low hits or many evictions mean the keys are too varied for caching to help.

A cache the planner can prove pays off

Memoize (PostgreSQL 14+) sits between the outer side of a Nested Loop and the inner side, intercepting each lookup. On first encounter with a given key it runs the inner subplan and stores the result; on a repeat of that key it returns the cached rows without re-running the subplan.

For nested-loop joins driven by skewed, low-cardinality outer values it rescues an otherwise-bad plan: instead of probing the inner side once per outer row, you probe it once per distinct outer value.

Reading the cache stats

Memoize  (cost=… rows=…) (actual time=… rows=… loops=…)
  Cache Key: u.org_id
  Cache Mode: logical
  Hits: 982   Misses: 18   Evictions: 0   Overflows: 0

• Hits / Misses — the headline. A high hit ratio is the whole point.
• Evictions — distinct keys exceeded work_mem (× hash_mem_multiplier), so older entries were thrown out. Some are fine; many means the cache is thrashing and Memoize is helping less than the planner thought.
• Overflows — even individual entries don't fit; effectively useless.

When it doesn't help

If outer keys are mostly unique, Memoize is overhead with no payoff — the planner usually figures that out, but bad estimates can mislead it. Watch for low Hits + high Misses; the fix is usually a better row estimate (ANALYZE, extended statistics) so the planner picks a hash join instead.

Memoize vs Materialize

Materialize buffers one input stream and replays it as-is. Memoize is a parameterized cache keyed by lookup values — the difference that makes it valuable inside a Nested Loop with repeating keys.

work_memhash_mem_multiplierenable_memoize

How we tune these →

Memoize vs Materialize — what's the difference?

Materialize buffers one input stream and replays it as-is. Memoize is a parameterized cache: it stores results per lookup key and reuses them when that key repeats, which is what makes it valuable inside nested-loop joins.

• Nested Loop Join
• Materialize