{"Entry":{"collection":"plan","key":"materialize","name":"Materialize","aliases":["Material","Materialize","T_Material"],"metadata":{"aliases":["Material","Materialize","T_Material"],"category":"Materialization","content_hash":"69abc3bfb2a3c9678e45280109d1dc0cab0cacb9b0c08d2ee0cd6c581d2154be","imported_at":"2026-09-30T00:40:44.151445+08:00","name":"Materialize","name_zh":"Material","slug":"materialize","summary":"Stores its child output so the rows can be read again."}},"Definition":{"Collection":"plan","Key":"materialize","SourceDatabase":"center","Version":"18","SourceTable":"plan_node","SourceKey":"materialize","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","Facts":{"comparison_data":{"initializer":"ExecInitMaterial","memory_mechanism":"tuplestore","node_tag":"T_Material","parallel_callbacks":[],"partial_modes":[],"strategies":[],"text_names":["Materialize"]},"comparison_hash":"c1d37bf334409fe6ded8ce560fe45fd495ff89ceda2808b6f5ef18daca520393","description":["Stores its child output so the rows can be read again."],"evidence_kind":"source and documentation","explain_names":["Materialize"],"explain_prefixes":["Parallel","Async"],"facts":[{"label":"Core node tag","value":"T_Material"},{"label":"Structured EXPLAIN Node Type","value":"Materialize"},{"label":"Inputs","value":"One child plan"},{"label":"Output","value":"Materialized tuples"},{"label":"Executor initializer","value":"ExecInitMaterial"},{"label":"Memory mechanism","value":"tuplestore"}],"memory":{"description":"This node creates a tuplestore with work_mem. The tuplestore can move stored tuples to temporary files; this does not make work_mem a cap on every allocation made by the node.","evidence":[{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/executor/nodeMaterial.c","path":"src/backend/executor/nodeMaterial.c","sha256":"410b7129ca7e86ce3cf59ed7afed813fc6a47d68f5195e14df36559c75c7ef91","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/utils/sort/tuplestore.c","path":"src/backend/utils/sort/tuplestore.c","sha256":"27a85416bafed557006c705ab5d0c5dce3d3c1702812bb54d136f2ac4c41055a","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"}],"mechanism":"tuplestore","source_notes":[]},"node_tag":"T_Material","parallel_callbacks":[],"partial_modes":[],"related":[{"label":"EXPLAIN","url":"/wiki/sql/explain/?v=18"},{"label":"Using EXPLAIN","url":"/docs/18/using-explain.html"},{"label":"Parallel plans","url":"/docs/18/parallel-plans.html"},{"label":"enable_material","url":"/wiki/guc/enable_material/?v=18"},{"label":"work_mem","url":"/wiki/guc/work_mem/?v=18"}],"release":{"channel":"stable","label":"18.6","major":"18","ref":"PostgreSQL 18.6 source archive","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_snapshot_utc":"","source_url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},"runtime_verified":false,"sections":[{"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: Materialize.","Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."],"title":"EXPLAIN names and attributes"},{"paragraphs":["This node creates a tuplestore with work_mem. The tuplestore can move stored tuples to temporary files; this does not make work_mem a cap on every allocation made by the node."],"title":"Memory and temporary storage"},{"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: none extracted from this node implementation."],"title":"Parallel execution and instrumentation"},{"paragraphs":["Notice that here the planner has chosen to “ materialize ” the inner relation of the join, by putting a Materialize plan node atop it. This means that the t2 index scan will be done just once, even though the nested-loop join node needs to read that data ten times, once for each row from the outer relation. The Materialize node saves the data in memory as it's read, and then returns the data from memory on each subsequent pass."],"title":"Same-version manual discussion"},{"blocks":[{"code":"EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 \u003c 10 AND t2.unique2 \u003c 10 AND t1.hundred \u003c t2.hundred;\n\n                                         QUERY PLAN\n---------------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..49.36 rows=33 width=488)\n   Join Filter: (t1.hundred \u003c t2.hundred)\n   -\u003e  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)\n         Recheck Cond: (unique1 \u003c 10)\n         -\u003e  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 \u003c 10)\n   -\u003e  Materialize  (cost=0.29..8.51 rows=10 width=244)\n         -\u003e  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)\n               Index Cond: (unique2 \u003c 10)","paragraphs":["Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.","In this example the join's output row count is the same as the product of the two scans' row counts, but that's not true in all cases because there can be additional WHERE clauses that mention both tables and so can only be applied at the join point, not to either input scan. Here's an example:"],"source":{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-BASICS"}}],"title":"Examples from this manual build"},{"paragraphs":["As long as we are at the end of the data collected in the tuplestore, we collect one new row from the subplan on each call, and stash it aside in the tuplestore before returning it. The tuplestore is only read if we are asked to scan backwards, rescan, or mark/restore.","If first time through, and we need a tuplestore, initialize it.","Allocate a second read pointer to serve as the mark. We know it must have index 1, so needn't store that.","If we are not at the end of the tuplestore, or are going backwards, try to fetch a tuple from tuplestore.","When reversing direction at tuplestore EOF, the first gettupleslot call will fetch the last-added tuple; but we want to return the one before that, if possible. So do an extra fetch."],"title":"Executor implementation notes"},{"code":"case T_Material:\n\t\t\tpname = sname = \"Materialize\";\n\t\t\tbreak;","title":"EXPLAIN identity in core source"}],"source_inventory":{"executor":"src/backend/executor/execProcnode.c","explain":"src/backend/commands/explain.c","implementation":"src/backend/executor/nodeMaterial.c"},"sources":[{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/commands/explain.c:1516","line":1516,"path":"src/backend/commands/explain.c","sha256":"34c86d6070224a0e981efef51f79101d6d505e5874f1684ace183034bab14bb4","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/executor/execProcnode.c:315","line":315,"path":"src/backend/executor/execProcnode.c","sha256":"f8a06a3f539077249b20664b2812433db6d7bd12b2c0ca633525db43d06f112a","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/executor/nodeMaterial.c","path":"src/backend/executor/nodeMaterial.c","sha256":"410b7129ca7e86ce3cf59ed7afed813fc6a47d68f5195e14df36559c75c7ef91","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/include/nodes/plannodes.h","path":"src/include/nodes/plannodes.h","sha256":"52422b327a8049fbbb20d8b96008a0fc0a6fafa60f7eff3c695d5b2e83830120","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/utils/sort/tuplestore.c","path":"src/backend/utils/sort/tuplestore.c","sha256":"27a85416bafed557006c705ab5d0c5dce3d3c1702812bb54d136f2ac4c41055a","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-BASICS"}],"strategies":[],"tables":[{"columns":[{"key":"label","label":"Text-format label"},{"key":"identity","label":"Structured node identity"}],"key":"explain-labels","rows":[{"identity":"Materialize","label":"Materialize"}],"title":"EXPLAIN labels in this source build"}]},"ManualEvidence":{"release":{"channel":"stable","label":"18.6","major":"18","ref":"PostgreSQL 18.6 source archive","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_snapshot_utc":"","source_url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},"sources":[{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/commands/explain.c:1516","line":1516,"path":"src/backend/commands/explain.c","sha256":"34c86d6070224a0e981efef51f79101d6d505e5874f1684ace183034bab14bb4","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/executor/execProcnode.c:315","line":315,"path":"src/backend/executor/execProcnode.c","sha256":"f8a06a3f539077249b20664b2812433db6d7bd12b2c0ca633525db43d06f112a","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/executor/nodeMaterial.c","path":"src/backend/executor/nodeMaterial.c","sha256":"410b7129ca7e86ce3cf59ed7afed813fc6a47d68f5195e14df36559c75c7ef91","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/include/nodes/plannodes.h","path":"src/include/nodes/plannodes.h","sha256":"52422b327a8049fbbb20d8b96008a0fc0a6fafa60f7eff3c695d5b2e83830120","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/utils/sort/tuplestore.c","path":"src/backend/utils/sort/tuplestore.c","sha256":"27a85416bafed557006c705ab5d0c5dce3d3c1702812bb54d136f2ac4c41055a","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-BASICS"}]},"MeasuredEvidence":{"runtime_verified":false}},"Text":{"Collection":"plan","Key":"materialize","SourceDatabase":"center","Version":"18","Locale":"en","Title":"Materialize","Summary":"Stores its child output so the rows can be read again.","BodyHTML":"\u003cp\u003eStores its child output so the rows can be read again.\u003c/p\u003e","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","ContentHash":"8a8b6b19e46f2b8aae4cd4715eb4acba1ae56cc093392e516243faa97df9b01d","Payload":{"description":["Stores its child output so the rows can be read again."],"related":[{"label":"EXPLAIN","url":"/wiki/sql/explain/?v=18"},{"label":"Using EXPLAIN","url":"/docs/18/using-explain.html"},{"label":"Parallel plans","url":"/docs/18/parallel-plans.html"},{"label":"enable_material","url":"/wiki/guc/enable_material/?v=18"},{"label":"work_mem","url":"/wiki/guc/work_mem/?v=18"}],"sections":[{"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: Materialize.","Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."],"title":"EXPLAIN names and attributes"},{"paragraphs":["This node creates a tuplestore with work_mem. The tuplestore can move stored tuples to temporary files; this does not make work_mem a cap on every allocation made by the node."],"title":"Memory and temporary storage"},{"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: none extracted from this node implementation."],"title":"Parallel execution and instrumentation"},{"paragraphs":["Notice that here the planner has chosen to “ materialize ” the inner relation of the join, by putting a Materialize plan node atop it. This means that the t2 index scan will be done just once, even though the nested-loop join node needs to read that data ten times, once for each row from the outer relation. The Materialize node saves the data in memory as it's read, and then returns the data from memory on each subsequent pass."],"title":"Same-version manual discussion"},{"blocks":[{"code":"EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 \u003c 10 AND t2.unique2 \u003c 10 AND t1.hundred \u003c t2.hundred;\n\n                                         QUERY PLAN\n---------------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..49.36 rows=33 width=488)\n   Join Filter: (t1.hundred \u003c t2.hundred)\n   -\u003e  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)\n         Recheck Cond: (unique1 \u003c 10)\n         -\u003e  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 \u003c 10)\n   -\u003e  Materialize  (cost=0.29..8.51 rows=10 width=244)\n         -\u003e  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)\n               Index Cond: (unique2 \u003c 10)","paragraphs":["Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.","In this example the join's output row count is the same as the product of the two scans' row counts, but that's not true in all cases because there can be additional WHERE clauses that mention both tables and so can only be applied at the join point, not to either input scan. Here's an example:"],"source":{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-BASICS"}}],"title":"Examples from this manual build"},{"paragraphs":["As long as we are at the end of the data collected in the tuplestore, we collect one new row from the subplan on each call, and stash it aside in the tuplestore before returning it. The tuplestore is only read if we are asked to scan backwards, rescan, or mark/restore.","If first time through, and we need a tuplestore, initialize it.","Allocate a second read pointer to serve as the mark. We know it must have index 1, so needn't store that.","If we are not at the end of the tuplestore, or are going backwards, try to fetch a tuple from tuplestore.","When reversing direction at tuplestore EOF, the first gettupleslot call will fetch the last-added tuple; but we want to return the one before that, if possible. So do an extra fetch."],"title":"Executor implementation notes"},{"code":"case T_Material:\n\t\t\tpname = sname = \"Materialize\";\n\t\t\tbreak;","title":"EXPLAIN identity in core source"}],"tables":[{"columns":[{"key":"label","label":"Text-format label"},{"key":"identity","label":"Structured node identity"}],"key":"explain-labels","rows":[{"identity":"Materialize","label":"Materialize"}],"title":"EXPLAIN labels in this source build"}]}},"RequestedLocale":"zh-Hans","Fallback":true,"Versions":["10","11","12","13","14","15","16","17","18","19","20"],"Locales":["en"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}