Memoize

Description

Caches results from a parameterized child and reuses them when the same parameter values recur.

Core node tag

T_Memoize

Structured EXPLAIN Node Type

Memoize

Inputs

One parameterized child plan

Output

Cached or newly produced child tuples

Executor initializer

ExecInitMemoize

Memory mechanism

eviction

EXPLAIN names and attributes

Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.

Text names recorded by this source: Memoize.

Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node.

Memory and temporary storage

The Memoize cache evicts least-recently-used entries instead of spilling cached tuples to disk. If a result cannot fit, caching can enter bypass mode for that scan.

The method of cache we use is a hash table. When the cache fills, we never spill tuples to disk, instead, we choose to evict the least recently used cache entry from the cache. We remember the least recently used entry by always pushing new entries and entries we look for onto the tail of a doubly linked list. This means that older items always bubble to the top of this LRU list.

It's possible when we're filling the cache for a given set of parameters that we're unable to free enough memory to store any more tuples. If this happens then we'll have already evicted all other cache entries. When caching another tuple would cause us to exceed our memory budget, we must free the entry that we're currently populating and move the state machine into MEMO_CACHE_BYPASS_MODE. This means that we'll not attempt to cache any further tuples for this particular scan. We don't have the memory for it. The state machine will be reset again on the next rescan. If the memory requirements to cache the next parameter's tuples are less demanding, then that may allow us to start putting useful entries back into the cache again.

cache_lookup Perform a lookup to see if we've already cached tuples based on the scan's current parameters. If we find an existing entry we move it to the end of the LRU list, set *found to true then return it. If we don't find an entry then we create a new one and add it to the end of the LRU list. We also update cache memory accounting and remove older entries if we go over the memory budget. If we managed to free enough memory we return the new entry, else we return NULL.

If we've gone over our memory budget, then we'll free up some space in the cache.

Parallel execution and instrumentation

The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.

Callbacks in this build: ExecMemoizeEstimate, ExecMemoizeInitializeDSM, ExecMemoizeInitializeWorker, ExecMemoizeRetrieveInstrumentation.

Executor implementation notes

nodeMemoize.c Routines to handle caching of results from parameterized nodes

Memoize nodes are intended to sit above parameterized nodes in the plan tree in order to cache results from them. The intention here is that a repeat scan with a parameter value that has already been seen by the node can fetch tuples from the cache rather than having to re-scan the inner node all over again. The query planner may choose to make use of one of these when it thinks rescans for previously seen values are likely enough to warrant adding the additional node.

The method of cache we use is a hash table. When the cache fills, we never spill tuples to disk, instead, we choose to evict the least recently used cache entry from the cache. We remember the least recently used entry by always pushing new entries and entries we look for onto the tail of a doubly linked list. This means that older items always bubble to the top of this LRU list.

Sometimes our callers won't run their scans to completion. For example a semi-join only needs to run until it finds a matching tuple, and once it does, the join operator skips to the next outer tuple and does not execute the inner side again on that scan. Because of this, we must keep track of when a cache entry is complete, and by default, we know it is when we run out of tuples to read during the scan. However, there are cases where we can mark the cache entry as complete without exhausting the scan of all tuples. One case is unique joins, where the join operator knows that there will only be at most one match for any given outer tuple. In order to support such cases we allow the "singlerow" option to be set for the cache. This option marks the cache entry as complete after we read the first tuple from the subnode.

It's possible when we're filling the cache for a given set of parameters that we're unable to free enough memory to store any more tuples. If this happens then we'll have already evicted all other cache entries. When caching another tuple would cause us to exceed our memory budget, we must free the entry that we're currently populating and move the state machine into MEMO_CACHE_BYPASS_MODE. This means that we'll not attempt to cache any further tuples for this particular scan. We don't have the memory for it. The state machine will be reset again on the next rescan. If the memory requirements to cache the next parameter's tuples are less demanding, then that may allow us to start putting useful entries back into the cache again.

EXPLAIN identity in core source

case T_Memoize:
			pname = sname = "Memoize";
			break;

Related entries

• EXPLAIN
• Using EXPLAIN
• Parallel plans
• enable_memoize
• hash_mem_multiplier

Documentation and source

• src/backend/commands/explain.c:1519
• src/backend/executor/execProcnode.c:330
• src/backend/executor/nodeMemoize.c
• src/include/nodes/plannodes.h

Compare versions

PostgreSQL 17 → 18: unchanged.

Compares recorded interfaces and attributes. Source fingerprints and build metadata are excluded; an absent sample is not proof of the introduction or removal release.

Related entries

• MaterializeMaterial