A nested-loop join can execute its inner plan many times. When that inner plan is parameterized by values from the outer side, repeated outer values can trigger the same inner lookup again and again. PostgreSQL can place a Memoize node above the parameterized scan so a later lookup with the same parameter key can reuse rows already produced.
Memoization does not change join semantics and it does not create a persistent cache. It is an executor-level optimization attached to a particular query plan, with entries that exist only for that execution.
Parameterized scans create a cacheable boundary
A parameterized inner scan depends on values supplied by the current outer row. Consider two relations where many orders refer to the same customer:
SELECT o.id, c.status
FROM orders AS o
JOIN customers AS c
ON c.id = o.customer_id
WHERE o.created_at >= DATE '2026-09-01';One possible plan is a nested loop that reads qualifying orders rows and probes an index on customers.id for each customer_id. If 500 outer rows contain only 40 distinct customer IDs, a plain parameterized index scan can repeat probes for customer IDs that have already appeared.
A Memoize node can sit between the nested loop and that inner scan. The current customer_id becomes a cache key. On the first occurrence of a key, PostgreSQL executes the child scan and stores its result. A later occurrence can return the stored result without running the child scan again.
The optimization is tied to parameterization. Memoize is not a general replacement for the shared buffer cache, an application cache, or a materialized relation. It remembers the output associated with parameter values used by its child plan.
Repeated outer values determine the useful work
The main opportunity comes from reuse. If every outer row supplies a different parameter value, the cache sees misses but few or no hits. The extra bookkeeping then has little work to eliminate.
When many outer rows reuse a smaller set of keys, the balance changes. An inner index scan that would otherwise run once per outer row may run once per distinct cached key, subject to cache capacity and eviction.
This makes outer-side cardinality alone an incomplete signal. Two nested-loop plans can process the same number of outer rows but present very different reuse patterns to Memoize. A stream with substantial key repetition offers more cache reuse than a stream whose parameter values are mostly unique.
The planner estimates costs and decides whether a memoized path is attractive. enable_memoize controls whether the planner may use memoize plans and is enabled by default in supported PostgreSQL releases.
Cache entries hold result sets, not single tuples
A parameter key does not have to map to exactly one inner row. The child plan can return zero, one, or multiple rows, and the cached entry represents the result for that key.
That detail matters for joins against non-unique inner keys. Suppose an outer row supplies a category ID and the inner plan returns several matching configuration rows. Reusing that category ID can reuse the complete cached result instead of executing the parameterized child again.
An empty result can also be useful. If a parameter value produces no child rows, remembering that result can avoid repeating an unsuccessful scan when the same value appears later.
Memoize therefore targets repeated execution of a parameterized result-producing operation, not merely repeated index tuple access.
Memory limits can turn hits into evictions
The cache is bounded. PostgreSQL can evict less commonly accessed entries when it needs room for new ones. A workload with a large working set of parameter keys can therefore cycle entries through the cache instead of retaining every result until query completion.
EXPLAIN (ANALYZE) exposes runtime counters for a Memoize node, including cache hits, misses, evictions, and overflows when applicable. Those counters show what happened during that execution rather than what the planner estimated in advance.
A high miss count is not automatically evidence of a bad plan. Each miss may still populate an entry that receives several later hits. Likewise, a cache with many hits is only one component of total query cost. The surrounding scans, join cardinalities, row widths, filters, and I/O still shape the complete execution profile.
Evictions are especially useful context when expected reuse does not appear as sustained cache retention. If the active key set exceeds the space available to the node, a value can be evicted before it is requested again.
Memoize and Materialize solve different repetition patterns
Materialize and Memoize can both prevent repeated work, but they preserve different things.
A Materialize node stores a child result so that the same result stream can be read again. It is suited to rescans where the child output itself is unchanged between executions.
Memoize separates cached results by parameter key. Its child output can differ for customer_id = 10 and customer_id = 25, so the cache associates each stored result with the parameters that produced it. A hit is valid only when the current key matches an existing cache entry.
That distinction is central to parameterized nested loops. The inner plan is not globally constant across outer rows; it is constant for repeated occurrences of the same parameter values.
Plan shape still depends on the broader join choice
Memoize does not imply that a nested loop is preferable to a hash join or merge join. It improves a specific form of nested-loop execution when cached parameter results are expected to be reused enough to justify the node.
A hash join may still be cheaper when building a hash table for the inner relation and probing it from the outer side fits the estimated data sizes and predicates. A merge join may fit ordered inputs. The planner compares available paths using its cost model, and Memoize adds another candidate within that search rather than replacing the other join strategies.
This also means a schema change, new statistics, different constants, or a major shift in data distribution can remove Memoize from a plan without any configuration change. The selected plan reflects the planner’s current estimates for the statement.
Runtime counters expose the actual reuse pattern
The most useful property of Memoize is visible directly in an analyzed plan: it turns parameter reuse into measurable hits and misses. Those counters connect the logical shape of a nested-loop join with the executor work that was actually skipped.
A Memoize node is most meaningful when read together with its cache key, its child scan, and the outer relation driving those parameters. That context shows whether the cache is serving a compact repeated key set or absorbing a stream with little reuse. The node is not a blanket signal of a faster query; it is evidence that PostgreSQL found a parameterized rescan pattern worth caching.