{"kind": "plan", "major": "18", "item": {"slug": "memoize", "name": "Memoize", "name_zh": "Memoize", "category": "Materialization", "summary": "Caches results from a parameterized child and reuses them when the same parameter values recur.", "aliases": ["Memoize", "T_Memoize"], "content_hash": "f89fcc4f6ea3f2e1708296ae392fd1c44fe7caa8a6c932d46ae40983f766b96b", "versions": {"14": {"facts": [{"label": "Core node tag", "value": "T_Memoize"}, {"label": "Structured EXPLAIN Node Type", "value": "Memoize"}, {"label": "Inputs", "value": "One parameterized child plan"}, {"label": "Output", "value": "Cached or newly produced child tuples"}, {"label": "Executor initializer", "value": "ExecInitMemoize"}, {"label": "Memory mechanism", "value": "eviction"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "path": "src/backend/executor/nodeMemoize.c", "label": "src/backend/executor/nodeMemoize.c", "sha256": "4fa625c2308bc3e2124c1b0ed1e1401e38549d87f5413f3a8137ea3f0bb999d6", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}], "mechanism": "eviction", "description": "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.", "source_notes": ["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."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Memoize", "identity": "Memoize"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=14", "label": "EXPLAIN"}, {"url": "/docs/14/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/14/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_memoize/?v=14", "label": "enable_memoize"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=14", "label": "hash_mem_multiplier"}], "release": {"ref": "PostgreSQL 14.24 source archive", "label": "14.24", "major": "14", "channel": "stable", "revision": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897", "source_url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "line": 1309, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1309", "sha256": "e091be4e2a083b8dea39ccd09beedede22c1716ef974da66c214a44f48be8c41", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "line": 330, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:330", "sha256": "72da1c5ad457f1d92a39ab73531701794df858419e3b89d6e6cb7079634e68fa", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "path": "src/backend/executor/nodeMemoize.c", "label": "src/backend/executor/nodeMemoize.c", "sha256": "4fa625c2308bc3e2124c1b0ed1e1401e38549d87f5413f3a8137ea3f0bb999d6", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "302f51a16b570dba7ec4e7bc045f7df5800d21630280354d1a24025f3baec75d", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}], "node_tag": "T_Memoize", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["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."]}, {"title": "Memory and temporary storage", "paragraphs": ["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."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["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."]}, {"title": "Executor implementation notes", "paragraphs": ["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."]}, {"code": "case T_Memoize:\n\t\t\tpname = sname = \"Memoize\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Caches results from a parameterized child and reuses them when the same parameter values recur."], "evidence_kind": "source and documentation", "explain_names": ["Memoize"], "partial_modes": [], "comparison_data": {"node_tag": "T_Memoize", "strategies": [], "text_names": ["Memoize"], "initializer": "ExecInitMemoize", "partial_modes": [], "memory_mechanism": "eviction", "parallel_callbacks": ["ExecMemoizeEstimate", "ExecMemoizeInitializeDSM", "ExecMemoizeInitializeWorker", "ExecMemoizeRetrieveInstrumentation"]}, "comparison_hash": "ce79083064802c6772e741ce60e19e9c932b05d1f1a5ed00e8f8f2e86d678f5b", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeMemoize.c"}, "parallel_callbacks": ["ExecMemoizeEstimate", "ExecMemoizeInitializeDSM", "ExecMemoizeInitializeWorker", "ExecMemoizeRetrieveInstrumentation"]}, "15": {"facts": [{"label": "Core node tag", "value": "T_Memoize"}, {"label": "Structured EXPLAIN Node Type", "value": "Memoize"}, {"label": "Inputs", "value": "One parameterized child plan"}, {"label": "Output", "value": "Cached or newly produced child tuples"}, {"label": "Executor initializer", "value": "ExecInitMemoize"}, {"label": "Memory mechanism", "value": "eviction"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "path": "src/backend/executor/nodeMemoize.c", "label": "src/backend/executor/nodeMemoize.c", "sha256": "6bdaef16826b37dfa1dffc57537267a99e45ef17828b566d000e3a235cfef612", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}], "mechanism": "eviction", "description": "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.", "source_notes": ["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."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Memoize", "identity": "Memoize"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=15", "label": "EXPLAIN"}, {"url": "/docs/15/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/15/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_memoize/?v=15", "label": "enable_memoize"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=15", "label": "hash_mem_multiplier"}], "release": {"ref": "PostgreSQL 15.19 source archive", "label": "15.19", "major": "15", "channel": "stable", "revision": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89", "source_url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "line": 1312, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1312", "sha256": "bb3b442d0f1b098aa8707335250102f027a596cd94117308bd16d1d36b258f5c", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "line": 330, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:330", "sha256": "19836c50a272741a4eac653541e655437c2e00710a541e5348d6a277d0669d7c", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "path": "src/backend/executor/nodeMemoize.c", "label": "src/backend/executor/nodeMemoize.c", "sha256": "6bdaef16826b37dfa1dffc57537267a99e45ef17828b566d000e3a235cfef612", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "fb4a4c8165495299131173680bc02a950d88e1ff610231fd97997bc0c9afc1d7", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}], "node_tag": "T_Memoize", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["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."]}, {"title": "Memory and temporary storage", "paragraphs": ["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."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["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."]}, {"title": "Executor implementation notes", "paragraphs": ["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."]}, {"code": "case T_Memoize:\n\t\t\tpname = sname = \"Memoize\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Caches results from a parameterized child and reuses them when the same parameter values recur."], "evidence_kind": "source and documentation", "explain_names": ["Memoize"], "partial_modes": [], "comparison_data": {"node_tag": "T_Memoize", "strategies": [], "text_names": ["Memoize"], "initializer": "ExecInitMemoize", "partial_modes": [], "memory_mechanism": "eviction", "parallel_callbacks": ["ExecMemoizeEstimate", "ExecMemoizeInitializeDSM", "ExecMemoizeInitializeWorker", "ExecMemoizeRetrieveInstrumentation"]}, "comparison_hash": "ce79083064802c6772e741ce60e19e9c932b05d1f1a5ed00e8f8f2e86d678f5b", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeMemoize.c"}, "parallel_callbacks": ["ExecMemoizeEstimate", "ExecMemoizeInitializeDSM", "ExecMemoizeInitializeWorker", "ExecMemoizeRetrieveInstrumentation"]}, "16": {"facts": [{"label": "Core node tag", "value": "T_Memoize"}, {"label": "Structured EXPLAIN Node Type", "value": "Memoize"}, {"label": "Inputs", "value": "One parameterized child plan"}, {"label": "Output", "value": "Cached or newly produced child tuples"}, {"label": "Executor initializer", "value": "ExecInitMemoize"}, {"label": "Memory mechanism", "value": "eviction"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "path": "src/backend/executor/nodeMemoize.c", "label": "src/backend/executor/nodeMemoize.c", "sha256": "dcfe9d85b37288d38c271933437c0a4574a20afbe8748869814f9419a964679b", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}], "mechanism": "eviction", "description": "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.", "source_notes": ["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."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Memoize", "identity": "Memoize"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=16", "label": "EXPLAIN"}, {"url": "/docs/16/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/16/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_memoize/?v=16", "label": "enable_memoize"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=16", "label": "hash_mem_multiplier"}], "release": {"ref": "PostgreSQL 16.15 source archive", "label": "16.15", "major": "16", "channel": "stable", "revision": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed", "source_url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "line": 1345, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1345", "sha256": "8e017f0116dbea471339b40c37a667cc9f95039e7e0329c783e5e8ce194de7e1", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "line": 330, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:330", "sha256": "e48c08e555f8cb4e4bb43df516c4b8906ce9bc374b2a745d98a1fc8c22cc5099", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "path": "src/backend/executor/nodeMemoize.c", "label": "src/backend/executor/nodeMemoize.c", "sha256": "dcfe9d85b37288d38c271933437c0a4574a20afbe8748869814f9419a964679b", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "97db47353db76326b874589a5ad0a04501cc74cd72e237e7bd956e7472c41f1f", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}], "node_tag": "T_Memoize", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["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."]}, {"title": "Memory and temporary storage", "paragraphs": ["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."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["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."]}, {"title": "Executor implementation notes", "paragraphs": ["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."]}, {"code": "case T_Memoize:\n\t\t\tpname = sname = \"Memoize\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Caches results from a parameterized child and reuses them when the same parameter values recur."], "evidence_kind": "source and documentation", "explain_names": ["Memoize"], "partial_modes": [], "comparison_data": {"node_tag": "T_Memoize", "strategies": [], "text_names": ["Memoize"], "initializer": "ExecInitMemoize", "partial_modes": [], "memory_mechanism": "eviction", "parallel_callbacks": ["ExecMemoizeEstimate", "ExecMemoizeInitializeDSM", "ExecMemoizeInitializeWorker", "ExecMemoizeRetrieveInstrumentation"]}, "comparison_hash": "ce79083064802c6772e741ce60e19e9c932b05d1f1a5ed00e8f8f2e86d678f5b", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeMemoize.c"}, "parallel_callbacks": ["ExecMemoizeEstimate", "ExecMemoizeInitializeDSM", "ExecMemoizeInitializeWorker", "ExecMemoizeRetrieveInstrumentation"]}, "17": {"facts": [{"label": "Core node tag", "value": "T_Memoize"}, {"label": "Structured EXPLAIN Node Type", "value": "Memoize"}, {"label": "Inputs", "value": "One parameterized child plan"}, {"label": "Output", "value": "Cached or newly produced child tuples"}, {"label": "Executor initializer", "value": "ExecInitMemoize"}, {"label": "Memory mechanism", "value": "eviction"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "path": "src/backend/executor/nodeMemoize.c", "label": "src/backend/executor/nodeMemoize.c", "sha256": "09591408c1fef29e67a4a8a4aab85d85405b3eb79ae3e941f5dd32a8b804f992", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}], "mechanism": "eviction", "description": "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.", "source_notes": ["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."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Memoize", "identity": "Memoize"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=17", "label": "EXPLAIN"}, {"url": "/docs/17/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/17/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_memoize/?v=17", "label": "enable_memoize"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=17", "label": "hash_mem_multiplier"}], "release": {"ref": "PostgreSQL 17.11 source archive", "label": "17.11", "major": "17", "channel": "stable", "revision": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979", "source_url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "line": 1534, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1534", "sha256": "741251b1a3b6d269a52a673d42eb63b02e13a5872db7b359b137086ab21b63c8", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "line": 330, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:330", "sha256": "a77576e158b94cb01fa8c5174ba133004eabdd727660323f8afc66c8d2e757b8", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "path": "src/backend/executor/nodeMemoize.c", "label": "src/backend/executor/nodeMemoize.c", "sha256": "09591408c1fef29e67a4a8a4aab85d85405b3eb79ae3e941f5dd32a8b804f992", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "d390dd69e2d3f5085beb42b33e46ff0676a2959b916a12b82118a7e545f8e562", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}], "node_tag": "T_Memoize", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["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."]}, {"title": "Memory and temporary storage", "paragraphs": ["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."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["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."]}, {"title": "Executor implementation notes", "paragraphs": ["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."]}, {"code": "case T_Memoize:\n\t\t\tpname = sname = \"Memoize\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Caches results from a parameterized child and reuses them when the same parameter values recur."], "evidence_kind": "source and documentation", "explain_names": ["Memoize"], "partial_modes": [], "comparison_data": {"node_tag": "T_Memoize", "strategies": [], "text_names": ["Memoize"], "initializer": "ExecInitMemoize", "partial_modes": [], "memory_mechanism": "eviction", "parallel_callbacks": ["ExecMemoizeEstimate", "ExecMemoizeInitializeDSM", "ExecMemoizeInitializeWorker", "ExecMemoizeRetrieveInstrumentation"]}, "comparison_hash": "ce79083064802c6772e741ce60e19e9c932b05d1f1a5ed00e8f8f2e86d678f5b", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeMemoize.c"}, "parallel_callbacks": ["ExecMemoizeEstimate", "ExecMemoizeInitializeDSM", "ExecMemoizeInitializeWorker", "ExecMemoizeRetrieveInstrumentation"]}, "18": {"facts": [{"label": "Core node tag", "value": "T_Memoize"}, {"label": "Structured EXPLAIN Node Type", "value": "Memoize"}, {"label": "Inputs", "value": "One parameterized child plan"}, {"label": "Output", "value": "Cached or newly produced child tuples"}, {"label": "Executor initializer", "value": "ExecInitMemoize"}, {"label": "Memory mechanism", "value": "eviction"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeMemoize.c", "label": "src/backend/executor/nodeMemoize.c", "sha256": "1dec8adecc70763c97957ed94510c4734a399223fd2f81f5534a30351c06c5d6", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}], "mechanism": "eviction", "description": "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.", "source_notes": ["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."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Memoize", "identity": "Memoize"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=18", "label": "EXPLAIN"}, {"url": "/docs/18/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/18/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_memoize/?v=18", "label": "enable_memoize"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=18", "label": "hash_mem_multiplier"}], "release": {"ref": "PostgreSQL 18.6 source archive", "label": "18.6", "major": "18", "channel": "stable", "revision": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f", "source_url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 1519, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1519", "sha256": "34c86d6070224a0e981efef51f79101d6d505e5874f1684ace183034bab14bb4", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 330, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:330", "sha256": "f8a06a3f539077249b20664b2812433db6d7bd12b2c0ca633525db43d06f112a", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeMemoize.c", "label": "src/backend/executor/nodeMemoize.c", "sha256": "1dec8adecc70763c97957ed94510c4734a399223fd2f81f5534a30351c06c5d6", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "52422b327a8049fbbb20d8b96008a0fc0a6fafa60f7eff3c695d5b2e83830120", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}], "node_tag": "T_Memoize", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["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."]}, {"title": "Memory and temporary storage", "paragraphs": ["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."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["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."]}, {"title": "Executor implementation notes", "paragraphs": ["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."]}, {"code": "case T_Memoize:\n\t\t\tpname = sname = \"Memoize\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Caches results from a parameterized child and reuses them when the same parameter values recur."], "evidence_kind": "source and documentation", "explain_names": ["Memoize"], "partial_modes": [], "comparison_data": {"node_tag": "T_Memoize", "strategies": [], "text_names": ["Memoize"], "initializer": "ExecInitMemoize", "partial_modes": [], "memory_mechanism": "eviction", "parallel_callbacks": ["ExecMemoizeEstimate", "ExecMemoizeInitializeDSM", "ExecMemoizeInitializeWorker", "ExecMemoizeRetrieveInstrumentation"]}, "comparison_hash": "ce79083064802c6772e741ce60e19e9c932b05d1f1a5ed00e8f8f2e86d678f5b", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeMemoize.c"}, "parallel_callbacks": ["ExecMemoizeEstimate", "ExecMemoizeInitializeDSM", "ExecMemoizeInitializeWorker", "ExecMemoizeRetrieveInstrumentation"]}, "19": {"facts": [{"label": "Core node tag", "value": "T_Memoize"}, {"label": "Structured EXPLAIN Node Type", "value": "Memoize"}, {"label": "Inputs", "value": "One parameterized child plan"}, {"label": "Output", "value": "Cached or newly produced child tuples"}, {"label": "Executor initializer", "value": "ExecInitMemoize"}, {"label": "Memory mechanism", "value": "eviction"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "path": "src/backend/executor/nodeMemoize.c", "label": "src/backend/executor/nodeMemoize.c", "sha256": "52e94b532ab2c703dc1232d6daa5268edd2e763054a9f6a34d0e7571d08ba7f8", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}], "mechanism": "eviction", "description": "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.", "source_notes": ["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."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Memoize", "identity": "Memoize"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=19", "label": "EXPLAIN"}, {"url": "/docs/19/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/19/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_memoize/?v=19", "label": "enable_memoize"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=19", "label": "hash_mem_multiplier"}], "release": {"ref": "PostgreSQL 19beta4 source archive", "label": "19beta4", "major": "19", "channel": "preview", "revision": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86", "source_url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "line": 1531, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1531", "sha256": "8b115b1c194a4b54ae630209a741e293b1df49a9052f10b2de9ca092a48998e3", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "line": 330, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:330", "sha256": "5e39b2037bed672da55104229ecc32da5abde44c26bcad01479edcfa044d09ed", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "path": "src/backend/executor/nodeMemoize.c", "label": "src/backend/executor/nodeMemoize.c", "sha256": "52e94b532ab2c703dc1232d6daa5268edd2e763054a9f6a34d0e7571d08ba7f8", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "1c65d5d6b6c81c71531685843647869bcae630779d815a5036b06e070c6c06c7", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}], "node_tag": "T_Memoize", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["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."]}, {"title": "Memory and temporary storage", "paragraphs": ["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."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["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."]}, {"title": "Executor implementation notes", "paragraphs": ["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."]}, {"code": "case T_Memoize:\n\t\t\tpname = sname = \"Memoize\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Caches results from a parameterized child and reuses them when the same parameter values recur."], "evidence_kind": "source and documentation", "explain_names": ["Memoize"], "partial_modes": [], "comparison_data": {"node_tag": "T_Memoize", "strategies": [], "text_names": ["Memoize"], "initializer": "ExecInitMemoize", "partial_modes": [], "memory_mechanism": "eviction", "parallel_callbacks": ["ExecMemoizeEstimate", "ExecMemoizeInitializeDSM", "ExecMemoizeInitializeWorker", "ExecMemoizeRetrieveInstrumentation"]}, "comparison_hash": "ce79083064802c6772e741ce60e19e9c932b05d1f1a5ed00e8f8f2e86d678f5b", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeMemoize.c"}, "parallel_callbacks": ["ExecMemoizeEstimate", "ExecMemoizeInitializeDSM", "ExecMemoizeInitializeWorker", "ExecMemoizeRetrieveInstrumentation"]}, "20": {"facts": [{"label": "Core node tag", "value": "T_Memoize"}, {"label": "Structured EXPLAIN Node Type", "value": "Memoize"}, {"label": "Inputs", "value": "One parameterized child plan"}, {"label": "Output", "value": "Cached or newly produced child tuples"}, {"label": "Executor initializer", "value": "ExecInitMemoize"}, {"label": "Memory mechanism", "value": "eviction"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "path": "src/backend/executor/nodeMemoize.c", "label": "src/backend/executor/nodeMemoize.c", "sha256": "e120ca0070bfc617c3b129ec82ec5695accbb554dfebed46b275ddf2835949f6", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}], "mechanism": "eviction", "description": "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.", "source_notes": ["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."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Memoize", "identity": "Memoize"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=20", "label": "EXPLAIN"}, {"url": "/docs/devel/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/devel/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_memoize/?v=20", "label": "enable_memoize"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=20", "label": "hash_mem_multiplier"}], "release": {"ref": "PostgreSQL 20devel source archive", "label": "20devel", "major": "20", "channel": "devel", "revision": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41", "source_url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "source_snapshot_utc": "26-Sep-2026 20:22"}, "sources": [{"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "line": 1531, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1531", "sha256": "13402758013520451539427b5993db06d463ca11c4e2d4cc5444e82367688077", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "line": 330, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:330", "sha256": "5e39b2037bed672da55104229ecc32da5abde44c26bcad01479edcfa044d09ed", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "path": "src/backend/executor/nodeMemoize.c", "label": "src/backend/executor/nodeMemoize.c", "sha256": "e120ca0070bfc617c3b129ec82ec5695accbb554dfebed46b275ddf2835949f6", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "7a94ed1652f0d74d50c39971d1cd3e8051dbc0d6058f31b6de71a433ca343521", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}], "node_tag": "T_Memoize", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["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."]}, {"title": "Memory and temporary storage", "paragraphs": ["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."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["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."]}, {"title": "Executor implementation notes", "paragraphs": ["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."]}, {"code": "case T_Memoize:\n\t\t\tpname = sname = \"Memoize\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Caches results from a parameterized child and reuses them when the same parameter values recur."], "evidence_kind": "source and documentation", "explain_names": ["Memoize"], "partial_modes": [], "comparison_data": {"node_tag": "T_Memoize", "strategies": [], "text_names": ["Memoize"], "initializer": "ExecInitMemoize", "partial_modes": [], "memory_mechanism": "eviction", "parallel_callbacks": ["ExecMemoizeEstimate", "ExecMemoizeInitializeDSM", "ExecMemoizeInitializeWorker", "ExecMemoizeRetrieveInstrumentation"]}, "comparison_hash": "ce79083064802c6772e741ce60e19e9c932b05d1f1a5ed00e8f8f2e86d678f5b", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeMemoize.c"}, "parallel_callbacks": ["ExecMemoizeEstimate", "ExecMemoizeInitializeDSM", "ExecMemoizeInitializeWorker", "ExecMemoizeRetrieveInstrumentation"]}}}, "snapshot": {"facts": [{"label": "Core node tag", "value": "T_Memoize"}, {"label": "Structured EXPLAIN Node Type", "value": "Memoize"}, {"label": "Inputs", "value": "One parameterized child plan"}, {"label": "Output", "value": "Cached or newly produced child tuples"}, {"label": "Executor initializer", "value": "ExecInitMemoize"}, {"label": "Memory mechanism", "value": "eviction"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeMemoize.c", "label": "src/backend/executor/nodeMemoize.c", "sha256": "1dec8adecc70763c97957ed94510c4734a399223fd2f81f5534a30351c06c5d6", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}], "mechanism": "eviction", "description": "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.", "source_notes": ["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."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Memoize", "identity": "Memoize"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=18", "label": "EXPLAIN"}, {"url": "/docs/18/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/18/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_memoize/?v=18", "label": "enable_memoize"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=18", "label": "hash_mem_multiplier"}], "release": {"ref": "PostgreSQL 18.6 source archive", "label": "18.6", "major": "18", "channel": "stable", "revision": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f", "source_url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 1519, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1519", "sha256": "34c86d6070224a0e981efef51f79101d6d505e5874f1684ace183034bab14bb4", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 330, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:330", "sha256": "f8a06a3f539077249b20664b2812433db6d7bd12b2c0ca633525db43d06f112a", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeMemoize.c", "label": "src/backend/executor/nodeMemoize.c", "sha256": "1dec8adecc70763c97957ed94510c4734a399223fd2f81f5534a30351c06c5d6", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "52422b327a8049fbbb20d8b96008a0fc0a6fafa60f7eff3c695d5b2e83830120", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}], "node_tag": "T_Memoize", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["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."]}, {"title": "Memory and temporary storage", "paragraphs": ["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."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["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."]}, {"title": "Executor implementation notes", "paragraphs": ["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."]}, {"code": "case T_Memoize:\n\t\t\tpname = sname = \"Memoize\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Caches results from a parameterized child and reuses them when the same parameter values recur."], "evidence_kind": "source and documentation", "explain_names": ["Memoize"], "partial_modes": [], "comparison_data": {"node_tag": "T_Memoize", "strategies": [], "text_names": ["Memoize"], "initializer": "ExecInitMemoize", "partial_modes": [], "memory_mechanism": "eviction", "parallel_callbacks": ["ExecMemoizeEstimate", "ExecMemoizeInitializeDSM", "ExecMemoizeInitializeWorker", "ExecMemoizeRetrieveInstrumentation"]}, "comparison_hash": "ce79083064802c6772e741ce60e19e9c932b05d1f1a5ed00e8f8f2e86d678f5b", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeMemoize.c"}, "parallel_callbacks": ["ExecMemoizeEstimate", "ExecMemoizeInitializeDSM", "ExecMemoizeInitializeWorker", "ExecMemoizeRetrieveInstrumentation"]}, "comparison": {"left": "17", "right": "18", "status": "unchanged", "diff": ""}}