{"kind": "plan", "major": "18", "item": {"slug": "modifytable", "name": "ModifyTable", "name_zh": "ModifyTable", "category": "Modification", "summary": "Performs the data-modification operation selected by the plan and produces RETURNING rows when requested.", "aliases": ["Delete", "Insert", "Merge", "ModifyTable", "T_ModifyTable", "Update"], "content_hash": "68bbce37f45404930fee9ad5ae3daa94be8681a1f255e3a0e86f0d3d67324dcf", "versions": {"10": {"facts": [{"label": "Core node tag", "value": "T_ModifyTable"}, {"label": "Structured EXPLAIN Node Type", "value": "ModifyTable"}, {"label": "Inputs", "value": "Modification input plan or plans"}, {"label": "Output", "value": "RETURNING tuples when requested"}, {"label": "Executor initializer", "value": "ExecInitModifyTable"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "1af40230615a448062a76fa6289f8fef4768e6956cb6f95ddd45744876f5f661", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": []}, "tables": [{"key": "explain-labels", "rows": [{"label": "Insert", "identity": "ModifyTable"}, {"label": "Update", "identity": "ModifyTable"}, {"label": "Delete", "identity": "ModifyTable"}], "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=10", "label": "EXPLAIN"}, {"url": "/docs/10/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/10/parallel-plans.html", "label": "Parallel plans"}], "release": {"ref": "PostgreSQL 10.23 source archive", "label": "10.23", "major": "10", "channel": "historical", "revision": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9", "source_url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "line": 888, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:888", "sha256": "a785298532047cfeda969e78c3597a343dc1c56d61ba85830b0f16a02a14b5a1", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "line": 174, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:174", "sha256": "cea76648bb38ae55f18f989768bee1a4ee025691ceea0f86bccb29dcdc166acc", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "1af40230615a448062a76fa6289f8fef4768e6956cb6f95ddd45744876f5f661", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "d562c321108844798cd234303fffb618f13d4ee3f3a5ac79bfd963b077e47c22", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "/docs/10/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 10.23 \u00b7 using-explain", "sha256": "a4b4304aedb0a2da0145cc7c35b03b319a5c7ee3df35a8ef84ab6d3a610f98f3"}], "node_tag": "T_ModifyTable", "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: Insert, Update, Delete.", "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": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider."]}, {"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: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["As seen in this example, when the query is an INSERT , UPDATE , or DELETE command, the actual work of applying the table changes is done by a top-level Insert, Update, or Delete plan node. The plan nodes underneath this node perform the work of locating the old rows and/or computing the new data. So above, we see the same sort of bitmap table scan we've seen already, and its output is fed to an Update node that stores the updated rows. It's worth noting that although the data-modifying node can take a considerable amount of run time (here, it's consuming the lion's share of the time), the planner does not currently add anything to the cost estimates to account for that work. That's because the work to be done is the same for every correct query plan, so it doesn't affect planning decisions.", "When an UPDATE or DELETE command affects an inheritance hierarchy, the output might look like this:", "In this example the Update node needs to consider three child tables as well as the originally-mentioned parent table. So there are four input scanning subplans, one per table. For clarity, the Update node is annotated to show the specific target tables that will be updated, in the same order as the corresponding subplans. (These annotations are new as of PostgreSQL 9.5; in prior versions the reader had to intuit the target tables by inspecting the subplans.)", "The Execution time shown by EXPLAIN ANALYZE includes executor start-up and shut-down time, as well as the time to run any triggers that are fired, but it does not include parsing, rewriting, or planning time. Time spent executing BEFORE triggers, if any, is included in the time for the related Insert, Update, or Delete node; but time spent executing AFTER triggers is not counted there because AFTER triggers are fired after completion of the whole plan. The total time spent in each trigger (either BEFORE or AFTER ) is also shown separately. Note that deferred constraint triggers will not be executed until end of transaction and are thus not considered at all by EXPLAIN ANALYZE ."]}, {"title": "Examples from this manual build", "blocks": [{"code": "BEGIN;\n\nEXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;\n\n                                                           QUERY PLAN\n--------------------------------------------------------------------------------------------------------------------------------\n Update on tenk1  (cost=5.07..229.46 rows=101 width=250) (actual time=14.628..14.628 rows=0 loops=1)\n   ->  Bitmap Heap Scan on tenk1  (cost=5.07..229.46 rows=101 width=250) (actual time=0.101..0.439 rows=100 loops=1)\n         Recheck Cond: (unique1 < 100)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0) (actual time=0.043..0.043 rows=100 loops=1)\n               Index Cond: (unique1 < 100)\n Planning time: 0.079 ms\n Execution time: 14.727 ms\n\nROLLBACK;", "source": {"url": "/docs/10/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 10.23 \u00b7 using-explain", "sha256": "a4b4304aedb0a2da0145cc7c35b03b319a5c7ee3df35a8ef84ab6d3a610f98f3"}, "paragraphs": ["Example copied from the PostgreSQL 10.23 manual; it was not executed for this collection.", "Keep in mind that because EXPLAIN ANALYZE actually runs the query, any side-effects will happen as usual, even though whatever results the query might output are discarded in favor of printing the EXPLAIN data. If you want to analyze a data-modifying query without changing your tables, you can roll the command back afterwards, for example:"]}, {"code": "EXPLAIN UPDATE parent SET f2 = f2 + 1 WHERE f1 = 101;\n                                    QUERY PLAN\n-----------------------------------------------------------------------------------\n Update on parent  (cost=0.00..24.53 rows=4 width=14)\n   Update on parent\n   Update on child1\n   Update on child2\n   Update on child3\n   ->  Seq Scan on parent  (cost=0.00..0.00 rows=1 width=14)\n         Filter: (f1 = 101)\n   ->  Index Scan using child1_f1_key on child1  (cost=0.15..8.17 rows=1 width=14)\n         Index Cond: (f1 = 101)\n   ->  Index Scan using child2_f1_key on child2  (cost=0.15..8.17 rows=1 width=14)\n         Index Cond: (f1 = 101)\n   ->  Index Scan using child3_f1_key on child3  (cost=0.15..8.17 rows=1 width=14)\n         Index Cond: (f1 = 101)", "source": {"url": "/docs/10/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 10.23 \u00b7 using-explain", "sha256": "a4b4304aedb0a2da0145cc7c35b03b319a5c7ee3df35a8ef84ab6d3a610f98f3"}, "paragraphs": ["Example copied from the PostgreSQL 10.23 manual; it was not executed for this collection.", "When an UPDATE or DELETE command affects an inheritance hierarchy, the output might look like this:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["NOTES Each ModifyTable node contains a list of one or more subplans, much like an Append node. There is one subplan per result relation. The key reason for this is that in an inherited UPDATE command, each result relation could have a different schema (more or different columns) requiring a different plan tree to produce it. In an inherited DELETE, all the subplans should produce the same output rowtype, but we might still find that different plans are appropriate for different child relations.", "If the query specifies RETURNING, then the ModifyTable returns a RETURNING tuple after completing each row insert, update, or delete. It must be called again to continue the operation. Without RETURNING, we just loop within the node until all the work is done, then return NULL. This avoids useless call/return overhead.", "Verify that the tuples to be produced by INSERT or UPDATE match the target relation's rowtype", "We do this to guard against stale plans. If plan invalidation is functioning properly then we should never get a failure here, but better safe than sorry. Note that this is called after we have obtained lock on the target rel, so the rowtype can't change underneath us.", "The plan output is represented by its targetlist, because that makes handling the dropped-column case easier."]}, {"code": "case T_ModifyTable:\n\t\t\tsname = \"ModifyTable\";\n\t\t\tswitch (((ModifyTable *) plan)->operation)\n\t\t\t{\n\t\t\t\tcase CMD_INSERT:\n\t\t\t\t\tpname = operation = \"Insert\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_UPDATE:\n\t\t\t\t\tpname = operation = \"Update\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_DELETE:\n\t\t\t\t\tpname = operation = \"Delete\";\n\t\t\t\t\tbreak;\n\t\t\t\tdefault:\n\t\t\t\t\tpname = \"???\";\n\t\t\t\t\tbreak;\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Performs the data-modification operation selected by the plan and produces RETURNING rows when requested."], "evidence_kind": "source and documentation", "explain_names": ["Insert", "Update", "Delete"], "partial_modes": [], "comparison_data": {"node_tag": "T_ModifyTable", "strategies": [], "text_names": ["Insert", "Update", "Delete"], "initializer": "ExecInitModifyTable", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "1d8fe2fc647b2bc0f8065366231bca50559a2445da7884a9bdaaca008b67b926", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeModifyTable.c"}, "parallel_callbacks": []}, "11": {"facts": [{"label": "Core node tag", "value": "T_ModifyTable"}, {"label": "Structured EXPLAIN Node Type", "value": "ModifyTable"}, {"label": "Inputs", "value": "Modification input plan or plans"}, {"label": "Output", "value": "RETURNING tuples when requested"}, {"label": "Executor initializer", "value": "ExecInitModifyTable"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "346194491bfd0af1e4394c8e03002128c5598c0903faf234954f40e49213bba2", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": []}, "tables": [{"key": "explain-labels", "rows": [{"label": "Insert", "identity": "ModifyTable"}, {"label": "Update", "identity": "ModifyTable"}, {"label": "Delete", "identity": "ModifyTable"}], "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=11", "label": "EXPLAIN"}, {"url": "/docs/11/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/11/parallel-plans.html", "label": "Parallel plans"}], "release": {"ref": "PostgreSQL 11.22 source archive", "label": "11.22", "major": "11", "channel": "historical", "revision": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0", "source_url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "line": 1013, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1013", "sha256": "9df8400c1a4377179572ceb916d6020fca4e2760f74bf416d77ed97476523bbd", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "line": 174, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:174", "sha256": "95ef4d4a5df4c29f14af9763fae2c530449bdacdf3853d9ff297adf1fed6153b", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "346194491bfd0af1e4394c8e03002128c5598c0903faf234954f40e49213bba2", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "5e0511194183800e8d6eb293fd4b40639c7d3118e2d199c8e7865ba4fa4cf67f", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "/docs/11/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 11.22 \u00b7 using-explain", "sha256": "8411bc78085d4737e32d7cca103d859da6c6539e33ec5e2fba46e74c6f199d8a"}], "node_tag": "T_ModifyTable", "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: Insert, Update, Delete.", "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": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider."]}, {"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: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["As seen in this example, when the query is an INSERT , UPDATE , or DELETE command, the actual work of applying the table changes is done by a top-level Insert, Update, or Delete plan node. The plan nodes underneath this node perform the work of locating the old rows and/or computing the new data. So above, we see the same sort of bitmap table scan we've seen already, and its output is fed to an Update node that stores the updated rows. It's worth noting that although the data-modifying node can take a considerable amount of run time (here, it's consuming the lion's share of the time), the planner does not currently add anything to the cost estimates to account for that work. That's because the work to be done is the same for every correct query plan, so it doesn't affect planning decisions.", "When an UPDATE or DELETE command affects an inheritance hierarchy, the output might look like this:", "In this example the Update node needs to consider three child tables as well as the originally-mentioned parent table. So there are four input scanning subplans, one per table. For clarity, the Update node is annotated to show the specific target tables that will be updated, in the same order as the corresponding subplans. (These annotations are new as of PostgreSQL 9.5; in prior versions the reader had to intuit the target tables by inspecting the subplans.)", "The Execution time shown by EXPLAIN ANALYZE includes executor start-up and shut-down time, as well as the time to run any triggers that are fired, but it does not include parsing, rewriting, or planning time. Time spent executing BEFORE triggers, if any, is included in the time for the related Insert, Update, or Delete node; but time spent executing AFTER triggers is not counted there because AFTER triggers are fired after completion of the whole plan. The total time spent in each trigger (either BEFORE or AFTER ) is also shown separately. Note that deferred constraint triggers will not be executed until end of transaction and are thus not considered at all by EXPLAIN ANALYZE ."]}, {"title": "Examples from this manual build", "blocks": [{"code": "BEGIN;\n\nEXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;\n\n                                                           QUERY PLAN\n--------------------------------------------------------------------------------------------------------------------------------\n Update on tenk1  (cost=5.07..229.46 rows=101 width=250) (actual time=14.628..14.628 rows=0 loops=1)\n   ->  Bitmap Heap Scan on tenk1  (cost=5.07..229.46 rows=101 width=250) (actual time=0.101..0.439 rows=100 loops=1)\n         Recheck Cond: (unique1 < 100)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0) (actual time=0.043..0.043 rows=100 loops=1)\n               Index Cond: (unique1 < 100)\n Planning time: 0.079 ms\n Execution time: 14.727 ms\n\nROLLBACK;", "source": {"url": "/docs/11/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 11.22 \u00b7 using-explain", "sha256": "8411bc78085d4737e32d7cca103d859da6c6539e33ec5e2fba46e74c6f199d8a"}, "paragraphs": ["Example copied from the PostgreSQL 11.22 manual; it was not executed for this collection.", "Keep in mind that because EXPLAIN ANALYZE actually runs the query, any side-effects will happen as usual, even though whatever results the query might output are discarded in favor of printing the EXPLAIN data. If you want to analyze a data-modifying query without changing your tables, you can roll the command back afterwards, for example:"]}, {"code": "EXPLAIN UPDATE parent SET f2 = f2 + 1 WHERE f1 = 101;\n                                    QUERY PLAN\n-----------------------------------------------------------------------------------\n Update on parent  (cost=0.00..24.53 rows=4 width=14)\n   Update on parent\n   Update on child1\n   Update on child2\n   Update on child3\n   ->  Seq Scan on parent  (cost=0.00..0.00 rows=1 width=14)\n         Filter: (f1 = 101)\n   ->  Index Scan using child1_f1_key on child1  (cost=0.15..8.17 rows=1 width=14)\n         Index Cond: (f1 = 101)\n   ->  Index Scan using child2_f1_key on child2  (cost=0.15..8.17 rows=1 width=14)\n         Index Cond: (f1 = 101)\n   ->  Index Scan using child3_f1_key on child3  (cost=0.15..8.17 rows=1 width=14)\n         Index Cond: (f1 = 101)", "source": {"url": "/docs/11/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 11.22 \u00b7 using-explain", "sha256": "8411bc78085d4737e32d7cca103d859da6c6539e33ec5e2fba46e74c6f199d8a"}, "paragraphs": ["Example copied from the PostgreSQL 11.22 manual; it was not executed for this collection.", "When an UPDATE or DELETE command affects an inheritance hierarchy, the output might look like this:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["NOTES Each ModifyTable node contains a list of one or more subplans, much like an Append node. There is one subplan per result relation. The key reason for this is that in an inherited UPDATE command, each result relation could have a different schema (more or different columns) requiring a different plan tree to produce it. In an inherited DELETE, all the subplans should produce the same output rowtype, but we might still find that different plans are appropriate for different child relations.", "If the query specifies RETURNING, then the ModifyTable returns a RETURNING tuple after completing each row insert, update, or delete. It must be called again to continue the operation. Without RETURNING, we just loop within the node until all the work is done, then return NULL. This avoids useless call/return overhead.", "Verify that the tuples to be produced by INSERT or UPDATE match the target relation's rowtype", "We do this to guard against stale plans. If plan invalidation is functioning properly then we should never get a failure here, but better safe than sorry. Note that this is called after we have obtained lock on the target rel, so the rowtype can't change underneath us.", "The plan output is represented by its targetlist, because that makes handling the dropped-column case easier."]}, {"code": "case T_ModifyTable:\n\t\t\tsname = \"ModifyTable\";\n\t\t\tswitch (((ModifyTable *) plan)->operation)\n\t\t\t{\n\t\t\t\tcase CMD_INSERT:\n\t\t\t\t\tpname = operation = \"Insert\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_UPDATE:\n\t\t\t\t\tpname = operation = \"Update\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_DELETE:\n\t\t\t\t\tpname = operation = \"Delete\";\n\t\t\t\t\tbreak;\n\t\t\t\tdefault:\n\t\t\t\t\tpname = \"???\";\n\t\t\t\t\tbreak;\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Performs the data-modification operation selected by the plan and produces RETURNING rows when requested."], "evidence_kind": "source and documentation", "explain_names": ["Insert", "Update", "Delete"], "partial_modes": [], "comparison_data": {"node_tag": "T_ModifyTable", "strategies": [], "text_names": ["Insert", "Update", "Delete"], "initializer": "ExecInitModifyTable", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "1d8fe2fc647b2bc0f8065366231bca50559a2445da7884a9bdaaca008b67b926", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeModifyTable.c"}, "parallel_callbacks": []}, "12": {"facts": [{"label": "Core node tag", "value": "T_ModifyTable"}, {"label": "Structured EXPLAIN Node Type", "value": "ModifyTable"}, {"label": "Inputs", "value": "Modification input plan or plans"}, {"label": "Output", "value": "RETURNING tuples when requested"}, {"label": "Executor initializer", "value": "ExecInitModifyTable"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "8b8aeaf5d8d1a5af806a2c7c10df2214930994581e015dd94efdc0c3a7aa30e7", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": []}, "tables": [{"key": "explain-labels", "rows": [{"label": "Insert", "identity": "ModifyTable"}, {"label": "Update", "identity": "ModifyTable"}, {"label": "Delete", "identity": "ModifyTable"}], "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=12", "label": "EXPLAIN"}, {"url": "/docs/12/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/12/parallel-plans.html", "label": "Parallel plans"}], "release": {"ref": "PostgreSQL 12.22 source archive", "label": "12.22", "major": "12", "channel": "historical", "revision": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b", "source_url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "line": 1084, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1084", "sha256": "d02ea84fdaa201de5d9360645a9f24bfbd2c31f7d45a639e09560ac0e6b6471d", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "line": 174, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:174", "sha256": "311b17379fe54e3f342fe5ad41c43afbdfa1b844978db2bb2eb22b82520d3256", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "8b8aeaf5d8d1a5af806a2c7c10df2214930994581e015dd94efdc0c3a7aa30e7", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "b0c4a0aeb48660ce06e5e700d5529ca9066fd16682bd15783d6e71b5420b07b0", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "/docs/12/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 12.22 \u00b7 using-explain", "sha256": "06297525f2180b07e752837a3351be9c871b56559f535bcd0a67dec9baac10c0"}], "node_tag": "T_ModifyTable", "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: Insert, Update, Delete.", "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": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider."]}, {"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: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["As seen in this example, when the query is an INSERT , UPDATE , or DELETE command, the actual work of applying the table changes is done by a top-level Insert, Update, or Delete plan node. The plan nodes underneath this node perform the work of locating the old rows and/or computing the new data. So above, we see the same sort of bitmap table scan we've seen already, and its output is fed to an Update node that stores the updated rows. It's worth noting that although the data-modifying node can take a considerable amount of run time (here, it's consuming the lion's share of the time), the planner does not currently add anything to the cost estimates to account for that work. That's because the work to be done is the same for every correct query plan, so it doesn't affect planning decisions.", "When an UPDATE or DELETE command affects an inheritance hierarchy, the output might look like this:", "In this example the Update node needs to consider three child tables as well as the originally-mentioned parent table. So there are four input scanning subplans, one per table. For clarity, the Update node is annotated to show the specific target tables that will be updated, in the same order as the corresponding subplans. (These annotations are new as of PostgreSQL 9.5; in prior versions the reader had to intuit the target tables by inspecting the subplans.)", "The Execution time shown by EXPLAIN ANALYZE includes executor start-up and shut-down time, as well as the time to run any triggers that are fired, but it does not include parsing, rewriting, or planning time. Time spent executing BEFORE triggers, if any, is included in the time for the related Insert, Update, or Delete node; but time spent executing AFTER triggers is not counted there because AFTER triggers are fired after completion of the whole plan. The total time spent in each trigger (either BEFORE or AFTER ) is also shown separately. Note that deferred constraint triggers will not be executed until end of transaction and are thus not considered at all by EXPLAIN ANALYZE ."]}, {"title": "Examples from this manual build", "blocks": [{"code": "BEGIN;\n\nEXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;\n\n                                                           QUERY PLAN\n--------------------------------------------------------------------------------------------------------------------------------\n Update on tenk1  (cost=5.07..229.46 rows=101 width=250) (actual time=14.628..14.628 rows=0 loops=1)\n   ->  Bitmap Heap Scan on tenk1  (cost=5.07..229.46 rows=101 width=250) (actual time=0.101..0.439 rows=100 loops=1)\n         Recheck Cond: (unique1 < 100)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0) (actual time=0.043..0.043 rows=100 loops=1)\n               Index Cond: (unique1 < 100)\n Planning time: 0.079 ms\n Execution time: 14.727 ms\n\nROLLBACK;", "source": {"url": "/docs/12/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 12.22 \u00b7 using-explain", "sha256": "06297525f2180b07e752837a3351be9c871b56559f535bcd0a67dec9baac10c0"}, "paragraphs": ["Example copied from the PostgreSQL 12.22 manual; it was not executed for this collection.", "Keep in mind that because EXPLAIN ANALYZE actually runs the query, any side-effects will happen as usual, even though whatever results the query might output are discarded in favor of printing the EXPLAIN data. If you want to analyze a data-modifying query without changing your tables, you can roll the command back afterwards, for example:"]}, {"code": "EXPLAIN UPDATE parent SET f2 = f2 + 1 WHERE f1 = 101;\n                                    QUERY PLAN\n-----------------------------------------------------------------------------------\n Update on parent  (cost=0.00..24.53 rows=4 width=14)\n   Update on parent\n   Update on child1\n   Update on child2\n   Update on child3\n   ->  Seq Scan on parent  (cost=0.00..0.00 rows=1 width=14)\n         Filter: (f1 = 101)\n   ->  Index Scan using child1_f1_key on child1  (cost=0.15..8.17 rows=1 width=14)\n         Index Cond: (f1 = 101)\n   ->  Index Scan using child2_f1_key on child2  (cost=0.15..8.17 rows=1 width=14)\n         Index Cond: (f1 = 101)\n   ->  Index Scan using child3_f1_key on child3  (cost=0.15..8.17 rows=1 width=14)\n         Index Cond: (f1 = 101)", "source": {"url": "/docs/12/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 12.22 \u00b7 using-explain", "sha256": "06297525f2180b07e752837a3351be9c871b56559f535bcd0a67dec9baac10c0"}, "paragraphs": ["Example copied from the PostgreSQL 12.22 manual; it was not executed for this collection.", "When an UPDATE or DELETE command affects an inheritance hierarchy, the output might look like this:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["NOTES Each ModifyTable node contains a list of one or more subplans, much like an Append node. There is one subplan per result relation. The key reason for this is that in an inherited UPDATE command, each result relation could have a different schema (more or different columns) requiring a different plan tree to produce it. In an inherited DELETE, all the subplans should produce the same output rowtype, but we might still find that different plans are appropriate for different child relations.", "The relation to modify can be an ordinary table, a view having an INSTEAD OF trigger, or a foreign table. Earlier processing already pointed ModifyTable to the underlying relations of any automatically updatable view not using an INSTEAD OF trigger, so code here can assume it won't have one as a modification target. This node does process ri_WithCheckOptions, which may have expressions from those automatically updatable views.", "If the query specifies RETURNING, then the ModifyTable returns a RETURNING tuple after completing each row insert, update, or delete. It must be called again to continue the operation. Without RETURNING, we just loop within the node until all the work is done, then return NULL. This avoids useless call/return overhead.", "Verify that the tuples to be produced by INSERT or UPDATE match the target relation's rowtype", "We do this to guard against stale plans. If plan invalidation is functioning properly then we should never get a failure here, but better safe than sorry. Note that this is called after we have obtained lock on the target rel, so the rowtype can't change underneath us."]}, {"code": "case T_ModifyTable:\n\t\t\tsname = \"ModifyTable\";\n\t\t\tswitch (((ModifyTable *) plan)->operation)\n\t\t\t{\n\t\t\t\tcase CMD_INSERT:\n\t\t\t\t\tpname = operation = \"Insert\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_UPDATE:\n\t\t\t\t\tpname = operation = \"Update\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_DELETE:\n\t\t\t\t\tpname = operation = \"Delete\";\n\t\t\t\t\tbreak;\n\t\t\t\tdefault:\n\t\t\t\t\tpname = \"???\";\n\t\t\t\t\tbreak;\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Performs the data-modification operation selected by the plan and produces RETURNING rows when requested."], "evidence_kind": "source and documentation", "explain_names": ["Insert", "Update", "Delete"], "partial_modes": [], "comparison_data": {"node_tag": "T_ModifyTable", "strategies": [], "text_names": ["Insert", "Update", "Delete"], "initializer": "ExecInitModifyTable", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "1d8fe2fc647b2bc0f8065366231bca50559a2445da7884a9bdaaca008b67b926", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeModifyTable.c"}, "parallel_callbacks": []}, "13": {"facts": [{"label": "Core node tag", "value": "T_ModifyTable"}, {"label": "Structured EXPLAIN Node Type", "value": "ModifyTable"}, {"label": "Inputs", "value": "Modification input plan or plans"}, {"label": "Output", "value": "RETURNING tuples when requested"}, {"label": "Executor initializer", "value": "ExecInitModifyTable"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "1d59f6402f55ee8a2f2a12f0d531d92b39840d6ab948b9f846effa940840b958", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": []}, "tables": [{"key": "explain-labels", "rows": [{"label": "Insert", "identity": "ModifyTable"}, {"label": "Update", "identity": "ModifyTable"}, {"label": "Delete", "identity": "ModifyTable"}], "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=13", "label": "EXPLAIN"}, {"url": "/docs/13/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/13/parallel-plans.html", "label": "Parallel plans"}], "release": {"ref": "PostgreSQL 13.23 source archive", "label": "13.23", "major": "13", "channel": "historical", "revision": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6", "source_url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "line": 1142, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1142", "sha256": "541713e0e7f1c9cc352c2b6028964d440c19d2678a4463000094c24a88c1e730", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "line": 174, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:174", "sha256": "d085ee99acfa00587e6ade3a1d9f8108a0566beedbbee3f54a50c9fc0cc2e875", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "1d59f6402f55ee8a2f2a12f0d531d92b39840d6ab948b9f846effa940840b958", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "dcb296833777b02008c4b6bae8e8f7c6423b7ffba21f36702597c9d596d039ab", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "/docs/13/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 13.23 \u00b7 using-explain", "sha256": "650fd8629382d5dc8f9f8412c50ec5f32442a9ad2f98ca88e5348a7c2bd0ac7a"}], "node_tag": "T_ModifyTable", "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: Insert, Update, Delete.", "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": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider."]}, {"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: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["As seen in this example, when the query is an INSERT , UPDATE , or DELETE command, the actual work of applying the table changes is done by a top-level Insert, Update, or Delete plan node. The plan nodes underneath this node perform the work of locating the old rows and/or computing the new data. So above, we see the same sort of bitmap table scan we've seen already, and its output is fed to an Update node that stores the updated rows. It's worth noting that although the data-modifying node can take a considerable amount of run time (here, it's consuming the lion's share of the time), the planner does not currently add anything to the cost estimates to account for that work. That's because the work to be done is the same for every correct query plan, so it doesn't affect planning decisions.", "When an UPDATE or DELETE command affects an inheritance hierarchy, the output might look like this:", "In this example the Update node needs to consider three child tables as well as the originally-mentioned parent table. So there are four input scanning subplans, one per table. For clarity, the Update node is annotated to show the specific target tables that will be updated, in the same order as the corresponding subplans. (These annotations are new as of PostgreSQL 9.5; in prior versions the reader had to intuit the target tables by inspecting the subplans.)", "The Execution time shown by EXPLAIN ANALYZE includes executor start-up and shut-down time, as well as the time to run any triggers that are fired, but it does not include parsing, rewriting, or planning time. Time spent executing BEFORE triggers, if any, is included in the time for the related Insert, Update, or Delete node; but time spent executing AFTER triggers is not counted there because AFTER triggers are fired after completion of the whole plan. The total time spent in each trigger (either BEFORE or AFTER ) is also shown separately. Note that deferred constraint triggers will not be executed until end of transaction and are thus not considered at all by EXPLAIN ANALYZE ."]}, {"title": "Examples from this manual build", "blocks": [{"code": "BEGIN;\n\nEXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;\n\n                                                           QUERY PLAN\n--------------------------------------------------------------------------------------------------------------------------------\n Update on tenk1  (cost=5.07..229.46 rows=101 width=250) (actual time=14.628..14.628 rows=0 loops=1)\n   ->  Bitmap Heap Scan on tenk1  (cost=5.07..229.46 rows=101 width=250) (actual time=0.101..0.439 rows=100 loops=1)\n         Recheck Cond: (unique1 < 100)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0) (actual time=0.043..0.043 rows=100 loops=1)\n               Index Cond: (unique1 < 100)\n Planning time: 0.079 ms\n Execution time: 14.727 ms\n\nROLLBACK;", "source": {"url": "/docs/13/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 13.23 \u00b7 using-explain", "sha256": "650fd8629382d5dc8f9f8412c50ec5f32442a9ad2f98ca88e5348a7c2bd0ac7a"}, "paragraphs": ["Example copied from the PostgreSQL 13.23 manual; it was not executed for this collection.", "Keep in mind that because EXPLAIN ANALYZE actually runs the query, any side-effects will happen as usual, even though whatever results the query might output are discarded in favor of printing the EXPLAIN data. If you want to analyze a data-modifying query without changing your tables, you can roll the command back afterwards, for example:"]}, {"code": "EXPLAIN UPDATE parent SET f2 = f2 + 1 WHERE f1 = 101;\n                                    QUERY PLAN\n-----------------------------------------------------------------------------------\n Update on parent  (cost=0.00..24.53 rows=4 width=14)\n   Update on parent\n   Update on child1\n   Update on child2\n   Update on child3\n   ->  Seq Scan on parent  (cost=0.00..0.00 rows=1 width=14)\n         Filter: (f1 = 101)\n   ->  Index Scan using child1_f1_key on child1  (cost=0.15..8.17 rows=1 width=14)\n         Index Cond: (f1 = 101)\n   ->  Index Scan using child2_f1_key on child2  (cost=0.15..8.17 rows=1 width=14)\n         Index Cond: (f1 = 101)\n   ->  Index Scan using child3_f1_key on child3  (cost=0.15..8.17 rows=1 width=14)\n         Index Cond: (f1 = 101)", "source": {"url": "/docs/13/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 13.23 \u00b7 using-explain", "sha256": "650fd8629382d5dc8f9f8412c50ec5f32442a9ad2f98ca88e5348a7c2bd0ac7a"}, "paragraphs": ["Example copied from the PostgreSQL 13.23 manual; it was not executed for this collection.", "When an UPDATE or DELETE command affects an inheritance hierarchy, the output might look like this:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["NOTES Each ModifyTable node contains a list of one or more subplans, much like an Append node. There is one subplan per result relation. The key reason for this is that in an inherited UPDATE command, each result relation could have a different schema (more or different columns) requiring a different plan tree to produce it. In an inherited DELETE, all the subplans should produce the same output rowtype, but we might still find that different plans are appropriate for different child relations.", "The relation to modify can be an ordinary table, a view having an INSTEAD OF trigger, or a foreign table. Earlier processing already pointed ModifyTable to the underlying relations of any automatically updatable view not using an INSTEAD OF trigger, so code here can assume it won't have one as a modification target. This node does process ri_WithCheckOptions, which may have expressions from those automatically updatable views.", "If the query specifies RETURNING, then the ModifyTable returns a RETURNING tuple after completing each row insert, update, or delete. It must be called again to continue the operation. Without RETURNING, we just loop within the node until all the work is done, then return NULL. This avoids useless call/return overhead.", "Verify that the tuples to be produced by INSERT or UPDATE match the target relation's rowtype", "We do this to guard against stale plans. If plan invalidation is functioning properly then we should never get a failure here, but better safe than sorry. Note that this is called after we have obtained lock on the target rel, so the rowtype can't change underneath us."]}, {"code": "case T_ModifyTable:\n\t\t\tsname = \"ModifyTable\";\n\t\t\tswitch (((ModifyTable *) plan)->operation)\n\t\t\t{\n\t\t\t\tcase CMD_INSERT:\n\t\t\t\t\tpname = operation = \"Insert\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_UPDATE:\n\t\t\t\t\tpname = operation = \"Update\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_DELETE:\n\t\t\t\t\tpname = operation = \"Delete\";\n\t\t\t\t\tbreak;\n\t\t\t\tdefault:\n\t\t\t\t\tpname = \"???\";\n\t\t\t\t\tbreak;\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Performs the data-modification operation selected by the plan and produces RETURNING rows when requested."], "evidence_kind": "source and documentation", "explain_names": ["Insert", "Update", "Delete"], "partial_modes": [], "comparison_data": {"node_tag": "T_ModifyTable", "strategies": [], "text_names": ["Insert", "Update", "Delete"], "initializer": "ExecInitModifyTable", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "1d8fe2fc647b2bc0f8065366231bca50559a2445da7884a9bdaaca008b67b926", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeModifyTable.c"}, "parallel_callbacks": []}, "14": {"facts": [{"label": "Core node tag", "value": "T_ModifyTable"}, {"label": "Structured EXPLAIN Node Type", "value": "ModifyTable"}, {"label": "Inputs", "value": "Modification input plan or plans"}, {"label": "Output", "value": "RETURNING tuples when requested"}, {"label": "Executor initializer", "value": "ExecInitModifyTable"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "27a898121c72a40aa3ce5cfca04e0da75be0a12171f7974aa6a7793458e670fd", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": ["If the FDW supports batching, and batching is requested, accumulate rows and insert them in batches. Otherwise use the per-row inserts.", "Initialize the batch slots. We don't know how many slots will be needed, so we initialize them as the batch grows, and we keep them across batches. To mitigate an inefficiency in how resource owner handles objects with many references (as with many slots all referencing the same tuple descriptor) we copy the appropriate tuple descriptor for each slot."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Insert", "identity": "ModifyTable"}, {"label": "Update", "identity": "ModifyTable"}, {"label": "Delete", "identity": "ModifyTable"}], "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"}], "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": 1178, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1178", "sha256": "e091be4e2a083b8dea39ccd09beedede22c1716ef974da66c214a44f48be8c41", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "line": 176, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:176", "sha256": "72da1c5ad457f1d92a39ab73531701794df858419e3b89d6e6cb7079634e68fa", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "27a898121c72a40aa3ce5cfca04e0da75be0a12171f7974aa6a7793458e670fd", "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"}, {"url": "/docs/14/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 14.24 \u00b7 using-explain", "sha256": "7f5ab59cb21a035ada45ea3426c5d1cca3f781273483677f73fdd76753555206"}], "node_tag": "T_ModifyTable", "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: Insert, Update, Delete.", "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": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "If the FDW supports batching, and batching is requested, accumulate rows and insert them in batches. Otherwise use the per-row inserts.", "Initialize the batch slots. We don't know how many slots will be needed, so we initialize them as the batch grows, and we keep them across batches. To mitigate an inefficiency in how resource owner handles objects with many references (as with many slots all referencing the same tuple descriptor) we copy the appropriate tuple descriptor for each slot."]}, {"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: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["As seen in this example, when the query is an INSERT , UPDATE , or DELETE command, the actual work of applying the table changes is done by a top-level Insert, Update, or Delete plan node. The plan nodes underneath this node perform the work of locating the old rows and/or computing the new data. So above, we see the same sort of bitmap table scan we've seen already, and its output is fed to an Update node that stores the updated rows. It's worth noting that although the data-modifying node can take a considerable amount of run time (here, it's consuming the lion's share of the time), the planner does not currently add anything to the cost estimates to account for that work. That's because the work to be done is the same for every correct query plan, so it doesn't affect planning decisions.", "When an UPDATE or DELETE command affects an inheritance hierarchy, the output might look like this:", "In this example the Update node needs to consider three child tables as well as the originally-mentioned parent table. So there are four input scanning subplans, one per table. For clarity, the Update node is annotated to show the specific target tables that will be updated, in the same order as the corresponding subplans.", "The Execution time shown by EXPLAIN ANALYZE includes executor start-up and shut-down time, as well as the time to run any triggers that are fired, but it does not include parsing, rewriting, or planning time. Time spent executing BEFORE triggers, if any, is included in the time for the related Insert, Update, or Delete node; but time spent executing AFTER triggers is not counted there because AFTER triggers are fired after completion of the whole plan. The total time spent in each trigger (either BEFORE or AFTER ) is also shown separately. Note that deferred constraint triggers will not be executed until end of transaction and are thus not considered at all by EXPLAIN ANALYZE ."]}, {"title": "Examples from this manual build", "blocks": [{"code": "BEGIN;\n\nEXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;\n\n                                                           QUERY PLAN\n--------------------------------------------------------------------------------------------------------------------------------\n Update on tenk1  (cost=5.08..230.08 rows=0 width=0) (actual time=3.791..3.792 rows=0 loops=1)\n   ->  Bitmap Heap Scan on tenk1  (cost=5.08..230.08 rows=102 width=10) (actual time=0.069..0.513 rows=100 loops=1)\n         Recheck Cond: (unique1 < 100)\n         Heap Blocks: exact=90\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.05 rows=102 width=0) (actual time=0.036..0.037 rows=300 loops=1)\n               Index Cond: (unique1 < 100)\n Planning Time: 0.113 ms\n Execution Time: 3.850 ms\n\nROLLBACK;", "source": {"url": "/docs/14/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 14.24 \u00b7 using-explain", "sha256": "7f5ab59cb21a035ada45ea3426c5d1cca3f781273483677f73fdd76753555206"}, "paragraphs": ["Example copied from the PostgreSQL 14.24 manual; it was not executed for this collection.", "Keep in mind that because EXPLAIN ANALYZE actually runs the query, any side-effects will happen as usual, even though whatever results the query might output are discarded in favor of printing the EXPLAIN data. If you want to analyze a data-modifying query without changing your tables, you can roll the command back afterwards, for example:"]}, {"code": "EXPLAIN UPDATE parent SET f2 = f2 + 1 WHERE f1 = 101;\n                                              QUERY PLAN\n------------------------------------------------------------------------------------------------------\n Update on parent  (cost=0.00..24.59 rows=0 width=0)\n   Update on parent parent_1\n   Update on child1 parent_2\n   Update on child2 parent_3\n   Update on child3 parent_4\n   ->  Result  (cost=0.00..24.59 rows=4 width=14)\n         ->  Append  (cost=0.00..24.54 rows=4 width=14)\n               ->  Seq Scan on parent parent_1  (cost=0.00..0.00 rows=1 width=14)\n                     Filter: (f1 = 101)\n               ->  Index Scan using child1_pkey on child1 parent_2  (cost=0.15..8.17 rows=1 width=14)\n                     Index Cond: (f1 = 101)\n               ->  Index Scan using child2_pkey on child2 parent_3  (cost=0.15..8.17 rows=1 width=14)\n                     Index Cond: (f1 = 101)\n               ->  Index Scan using child3_pkey on child3 parent_4  (cost=0.15..8.17 rows=1 width=14)\n                     Index Cond: (f1 = 101)", "source": {"url": "/docs/14/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 14.24 \u00b7 using-explain", "sha256": "7f5ab59cb21a035ada45ea3426c5d1cca3f781273483677f73fdd76753555206"}, "paragraphs": ["Example copied from the PostgreSQL 14.24 manual; it was not executed for this collection.", "When an UPDATE or DELETE command affects an inheritance hierarchy, the output might look like this:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["NOTES The ModifyTable node receives input from its outerPlan, which is the data to insert for INSERT cases, or the changed columns' new values plus row-locating info for UPDATE cases, or just the row-locating info for DELETE cases.", "The relation to modify can be an ordinary table, a view having an INSTEAD OF trigger, or a foreign table. Earlier processing already pointed ModifyTable to the underlying relations of any automatically updatable view not using an INSTEAD OF trigger, so code here can assume it won't have one as a modification target. This node does process ri_WithCheckOptions, which may have expressions from those automatically updatable views.", "If the query specifies RETURNING, then the ModifyTable returns a RETURNING tuple after completing each row insert, update, or delete. It must be called again to continue the operation. Without RETURNING, we just loop within the node until all the work is done, then return NULL. This avoids useless call/return overhead.", "Verify that the tuples to be produced by INSERT match the target relation's rowtype", "We do this to guard against stale plans. If plan invalidation is functioning properly then we should never get a failure here, but better safe than sorry. Note that this is called after we have obtained lock on the target rel, so the rowtype can't change underneath us."]}, {"code": "case T_ModifyTable:\n\t\t\tsname = \"ModifyTable\";\n\t\t\tswitch (((ModifyTable *) plan)->operation)\n\t\t\t{\n\t\t\t\tcase CMD_INSERT:\n\t\t\t\t\tpname = operation = \"Insert\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_UPDATE:\n\t\t\t\t\tpname = operation = \"Update\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_DELETE:\n\t\t\t\t\tpname = operation = \"Delete\";\n\t\t\t\t\tbreak;\n\t\t\t\tdefault:\n\t\t\t\t\tpname = \"???\";\n\t\t\t\t\tbreak;\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Performs the data-modification operation selected by the plan and produces RETURNING rows when requested."], "evidence_kind": "source and documentation", "explain_names": ["Insert", "Update", "Delete"], "partial_modes": [], "comparison_data": {"node_tag": "T_ModifyTable", "strategies": [], "text_names": ["Insert", "Update", "Delete"], "initializer": "ExecInitModifyTable", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "1d8fe2fc647b2bc0f8065366231bca50559a2445da7884a9bdaaca008b67b926", "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/nodeModifyTable.c"}, "parallel_callbacks": []}, "15": {"facts": [{"label": "Core node tag", "value": "T_ModifyTable"}, {"label": "Structured EXPLAIN Node Type", "value": "ModifyTable"}, {"label": "Inputs", "value": "Modification input plan or plans"}, {"label": "Output", "value": "RETURNING tuples when requested"}, {"label": "Executor initializer", "value": "ExecInitModifyTable"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "2b801f382f287e6b4dac19e45adf25b0f022a208e1b56a5c9b8b6be771e84277", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": ["If the FDW supports batching, and batching is requested, accumulate rows and insert them in batches. Otherwise use the per-row inserts.", "Initialize the batch slots. We don't know how many slots will be needed, so we initialize them as the batch grows, and we keep them across batches. To mitigate an inefficiency in how resource owner handles objects with many references (as with many slots all referencing the same tuple descriptor) we copy the appropriate tuple descriptor for each slot."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Insert", "identity": "ModifyTable"}, {"label": "Update", "identity": "ModifyTable"}, {"label": "Delete", "identity": "ModifyTable"}, {"label": "Merge", "identity": "ModifyTable"}], "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"}], "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": 1178, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1178", "sha256": "bb3b442d0f1b098aa8707335250102f027a596cd94117308bd16d1d36b258f5c", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "line": 176, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:176", "sha256": "19836c50a272741a4eac653541e655437c2e00710a541e5348d6a277d0669d7c", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "2b801f382f287e6b4dac19e45adf25b0f022a208e1b56a5c9b8b6be771e84277", "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"}, {"url": "/docs/15/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 15.19 \u00b7 using-explain", "sha256": "d1f509457c647da453d2575c772d91022a0f115dd84f9a3b20f5ff25a243648f"}, {"url": "/docs/15/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 15.19 \u00b7 using-explain", "sha256": "d1f509457c647da453d2575c772d91022a0f115dd84f9a3b20f5ff25a243648f"}, {"url": "/docs/15/using-explain.html#USING-EXPLAIN-CAVEATS", "path": "using-explain.html", "label": "PostgreSQL 15.19 \u00b7 using-explain", "sha256": "d1f509457c647da453d2575c772d91022a0f115dd84f9a3b20f5ff25a243648f"}], "node_tag": "T_ModifyTable", "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: Insert, Update, Delete, Merge.", "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": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "If the FDW supports batching, and batching is requested, accumulate rows and insert them in batches. Otherwise use the per-row inserts.", "Initialize the batch slots. We don't know how many slots will be needed, so we initialize them as the batch grows, and we keep them across batches. To mitigate an inefficiency in how resource owner handles objects with many references (as with many slots all referencing the same tuple descriptor) we copy the appropriate tuple descriptor for each slot."]}, {"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: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["Merge join requires its input data to be sorted on the join keys. In this plan the tenk1 data is sorted by using an index scan to visit the rows in the correct order, but a sequential scan and sort is preferred for onek , because there are many more rows to be visited in that table. (Sequential-scan-and-sort frequently beats an index scan for sorting many rows, because of the nonsequential disk access required by the index scan.)", "As seen in this example, when the query is an INSERT , UPDATE , DELETE , or MERGE command, the actual work of applying the table changes is done by a top-level Insert, Update, Delete, or Merge plan node. The plan nodes underneath this node perform the work of locating the old rows and/or computing the new data. So above, we see the same sort of bitmap table scan we've seen already, and its output is fed to an Update node that stores the updated rows. It's worth noting that although the data-modifying node can take a considerable amount of run time (here, it's consuming the lion's share of the time), the planner does not currently add anything to the cost estimates to account for that work. That's because the work to be done is the same for every correct query plan, so it doesn't affect planning decisions.", "When an UPDATE , DELETE , or MERGE command affects an inheritance hierarchy, the output might look like this:", "In this example the Update node needs to consider three child tables as well as the originally-mentioned parent table. So there are four input scanning subplans, one per table. For clarity, the Update node is annotated to show the specific target tables that will be updated, in the same order as the corresponding subplans.", "The Execution time shown by EXPLAIN ANALYZE includes executor start-up and shut-down time, as well as the time to run any triggers that are fired, but it does not include parsing, rewriting, or planning time. Time spent executing BEFORE triggers, if any, is included in the time for the related Insert, Update, or Delete node; but time spent executing AFTER triggers is not counted there because AFTER triggers are fired after completion of the whole plan. The total time spent in each trigger (either BEFORE or AFTER ) is also shown separately. Note that deferred constraint triggers will not be executed until end of transaction and are thus not considered at all by EXPLAIN ANALYZE .", "Merge joins also have measurement artifacts that can confuse the unwary. A merge join will stop reading one input if it's exhausted the other input and the next key value in the one input is greater than the last key value of the other input; in such a case there can be no more matches and so no need to scan the rest of the first input. This results in not reading all of one child, with results like those mentioned for LIMIT . Also, if the outer (first) child contains rows with duplicate key values, the inner (second) child is backed up and rescanned for the portion of its rows matching that key value. EXPLAIN ANALYZE counts these repeated emissions of the same inner rows as if they were real additional rows. When there are many outer duplicates, the reported actual row count for the inner child plan node can be significantly larger than the number of rows that are actually in the inner relation."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, onek t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n                                        QUERY PLAN\n------------------------------------------------------------------------------------------\n Merge Join  (cost=198.11..268.19 rows=10 width=488)\n   Merge Cond: (t1.unique2 = t2.unique2)\n   ->  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..656.28 rows=101 width=244)\n         Filter: (unique1 < 100)\n   ->  Sort  (cost=197.83..200.33 rows=1000 width=244)\n         Sort Key: t2.unique2\n         ->  Seq Scan on onek t2  (cost=0.00..148.00 rows=1000 width=244)", "source": {"url": "/docs/15/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 15.19 \u00b7 using-explain", "sha256": "d1f509457c647da453d2575c772d91022a0f115dd84f9a3b20f5ff25a243648f"}, "paragraphs": ["Example copied from the PostgreSQL 15.19 manual; it was not executed for this collection.", "Another possible type of join is a merge join, illustrated here:"]}, {"code": "SET enable_sort = off;\n\nEXPLAIN SELECT *\nFROM tenk1 t1, onek t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n                                        QUERY PLAN\n------------------------------------------------------------------------------------------\n Merge Join  (cost=0.56..292.65 rows=10 width=488)\n   Merge Cond: (t1.unique2 = t2.unique2)\n   ->  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..656.28 rows=101 width=244)\n         Filter: (unique1 < 100)\n   ->  Index Scan using onek_unique2 on onek t2  (cost=0.28..224.79 rows=1000 width=244)", "source": {"url": "/docs/15/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 15.19 \u00b7 using-explain", "sha256": "d1f509457c647da453d2575c772d91022a0f115dd84f9a3b20f5ff25a243648f"}, "paragraphs": ["Example copied from the PostgreSQL 15.19 manual; it was not executed for this collection.", "One way to look at variant plans is to force the planner to disregard whatever strategy it thought was the cheapest, using the enable/disable flags described in Section 20.7.1 . (This is a crude tool, but useful. See also Section 14.3 .) For example, if we're unconvinced that sequential-scan-and-sort is the best way to deal with table onek in the previous example, we could try"]}]}, {"title": "Executor implementation notes", "paragraphs": ["NOTES The ModifyTable node receives input from its outerPlan, which is the data to insert for INSERT cases, the changed columns' new values plus row-locating info for UPDATE and MERGE cases, or just the row-locating info for DELETE cases.", "The relation to modify can be an ordinary table, a view having an INSTEAD OF trigger, or a foreign table. Earlier processing already pointed ModifyTable to the underlying relations of any automatically updatable view not using an INSTEAD OF trigger, so code here can assume it won't have one as a modification target. This node does process ri_WithCheckOptions, which may have expressions from those automatically updatable views.", "MERGE runs a join between the source relation and the target table; if any WHEN NOT MATCHED clauses are present, then the join is an outer join. In this case, any unmatched tuples will have NULL row-locating info, and only INSERT can be run. But for matched tuples, then row-locating info is used to determine the tuple to UPDATE or DELETE. When all clauses are WHEN MATCHED, then an inner join is used, so all tuples contain row-locating info.", "If the query specifies RETURNING, then the ModifyTable returns a RETURNING tuple after completing each row insert, update, or delete. It must be called again to continue the operation. Without RETURNING, we just loop within the node until all the work is done, then return NULL. This avoids useless call/return overhead. (MERGE does not support RETURNING.)", "Context struct for a ModifyTable operation, containing basic execution state and some output variables populated by ExecUpdateAct() and ExecDeleteAct() to report the result of their actions to callers."]}, {"code": "case T_ModifyTable:\n\t\t\tsname = \"ModifyTable\";\n\t\t\tswitch (((ModifyTable *) plan)->operation)\n\t\t\t{\n\t\t\t\tcase CMD_INSERT:\n\t\t\t\t\tpname = operation = \"Insert\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_UPDATE:\n\t\t\t\t\tpname = operation = \"Update\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_DELETE:\n\t\t\t\t\tpname = operation = \"Delete\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_MERGE:\n\t\t\t\t\tpname = operation = \"Merge\";\n\t\t\t\t\tbreak;\n\t\t\t\tdefault:\n\t\t\t\t\tpname = \"???\";\n\t\t\t\t\tbreak;\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Performs the data-modification operation selected by the plan and produces RETURNING rows when requested."], "evidence_kind": "source and documentation", "explain_names": ["Insert", "Update", "Delete", "Merge"], "partial_modes": [], "comparison_data": {"node_tag": "T_ModifyTable", "strategies": [], "text_names": ["Insert", "Update", "Delete", "Merge"], "initializer": "ExecInitModifyTable", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "cf664334c8d3479ccec4439895ce74d4602368021778f85473730f30aa4ff4db", "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/nodeModifyTable.c"}, "parallel_callbacks": []}, "16": {"facts": [{"label": "Core node tag", "value": "T_ModifyTable"}, {"label": "Structured EXPLAIN Node Type", "value": "ModifyTable"}, {"label": "Inputs", "value": "Modification input plan or plans"}, {"label": "Output", "value": "RETURNING tuples when requested"}, {"label": "Executor initializer", "value": "ExecInitModifyTable"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "f59813a20cd984f3607ced2ad39ce98263dede986222dadbd89be41ce0bb21c5", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": ["If the FDW supports batching, and batching is requested, accumulate rows and insert them in batches. Otherwise use the per-row inserts.", "Initialize the batch slots. We don't know how many slots will be needed, so we initialize them as the batch grows, and we keep them across batches. To mitigate an inefficiency in how resource owner handles objects with many references (as with many slots all referencing the same tuple descriptor) we copy the appropriate tuple descriptor for each slot."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Insert", "identity": "ModifyTable"}, {"label": "Update", "identity": "ModifyTable"}, {"label": "Delete", "identity": "ModifyTable"}, {"label": "Merge", "identity": "ModifyTable"}], "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"}], "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": 1211, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1211", "sha256": "8e017f0116dbea471339b40c37a667cc9f95039e7e0329c783e5e8ce194de7e1", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "line": 176, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:176", "sha256": "e48c08e555f8cb4e4bb43df516c4b8906ce9bc374b2a745d98a1fc8c22cc5099", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "f59813a20cd984f3607ced2ad39ce98263dede986222dadbd89be41ce0bb21c5", "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"}, {"url": "/docs/16/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 16.15 \u00b7 using-explain", "sha256": "bd8b86e5281cf0e52ad6e0e4bb8b6ff6510dac61982b44f0c1dd07d54012db3c"}, {"url": "/docs/16/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 16.15 \u00b7 using-explain", "sha256": "bd8b86e5281cf0e52ad6e0e4bb8b6ff6510dac61982b44f0c1dd07d54012db3c"}, {"url": "/docs/16/using-explain.html#USING-EXPLAIN-CAVEATS", "path": "using-explain.html", "label": "PostgreSQL 16.15 \u00b7 using-explain", "sha256": "bd8b86e5281cf0e52ad6e0e4bb8b6ff6510dac61982b44f0c1dd07d54012db3c"}], "node_tag": "T_ModifyTable", "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: Insert, Update, Delete, Merge.", "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": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "If the FDW supports batching, and batching is requested, accumulate rows and insert them in batches. Otherwise use the per-row inserts.", "Initialize the batch slots. We don't know how many slots will be needed, so we initialize them as the batch grows, and we keep them across batches. To mitigate an inefficiency in how resource owner handles objects with many references (as with many slots all referencing the same tuple descriptor) we copy the appropriate tuple descriptor for each slot."]}, {"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: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["Merge join requires its input data to be sorted on the join keys. In this plan the tenk1 data is sorted by using an index scan to visit the rows in the correct order, but a sequential scan and sort is preferred for onek , because there are many more rows to be visited in that table. (Sequential-scan-and-sort frequently beats an index scan for sorting many rows, because of the nonsequential disk access required by the index scan.)", "As seen in this example, when the query is an INSERT , UPDATE , DELETE , or MERGE command, the actual work of applying the table changes is done by a top-level Insert, Update, Delete, or Merge plan node. The plan nodes underneath this node perform the work of locating the old rows and/or computing the new data. So above, we see the same sort of bitmap table scan we've seen already, and its output is fed to an Update node that stores the updated rows. It's worth noting that although the data-modifying node can take a considerable amount of run time (here, it's consuming the lion's share of the time), the planner does not currently add anything to the cost estimates to account for that work. That's because the work to be done is the same for every correct query plan, so it doesn't affect planning decisions.", "When an UPDATE , DELETE , or MERGE command affects an inheritance hierarchy, the output might look like this:", "In this example the Update node needs to consider three child tables as well as the originally-mentioned parent table. So there are four input scanning subplans, one per table. For clarity, the Update node is annotated to show the specific target tables that will be updated, in the same order as the corresponding subplans.", "The Execution time shown by EXPLAIN ANALYZE includes executor start-up and shut-down time, as well as the time to run any triggers that are fired, but it does not include parsing, rewriting, or planning time. Time spent executing BEFORE triggers, if any, is included in the time for the related Insert, Update, or Delete node; but time spent executing AFTER triggers is not counted there because AFTER triggers are fired after completion of the whole plan. The total time spent in each trigger (either BEFORE or AFTER ) is also shown separately. Note that deferred constraint triggers will not be executed until end of transaction and are thus not considered at all by EXPLAIN ANALYZE .", "Merge joins also have measurement artifacts that can confuse the unwary. A merge join will stop reading one input if it's exhausted the other input and the next key value in the one input is greater than the last key value of the other input; in such a case there can be no more matches and so no need to scan the rest of the first input. This results in not reading all of one child, with results like those mentioned for LIMIT . Also, if the outer (first) child contains rows with duplicate key values, the inner (second) child is backed up and rescanned for the portion of its rows matching that key value. EXPLAIN ANALYZE counts these repeated emissions of the same inner rows as if they were real additional rows. When there are many outer duplicates, the reported actual row count for the inner child plan node can be significantly larger than the number of rows that are actually in the inner relation."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, onek t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n                                        QUERY PLAN\n------------------------------------------------------------------------------------------\n Merge Join  (cost=198.11..268.19 rows=10 width=488)\n   Merge Cond: (t1.unique2 = t2.unique2)\n   ->  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..656.28 rows=101 width=244)\n         Filter: (unique1 < 100)\n   ->  Sort  (cost=197.83..200.33 rows=1000 width=244)\n         Sort Key: t2.unique2\n         ->  Seq Scan on onek t2  (cost=0.00..148.00 rows=1000 width=244)", "source": {"url": "/docs/16/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 16.15 \u00b7 using-explain", "sha256": "bd8b86e5281cf0e52ad6e0e4bb8b6ff6510dac61982b44f0c1dd07d54012db3c"}, "paragraphs": ["Example copied from the PostgreSQL 16.15 manual; it was not executed for this collection.", "Another possible type of join is a merge join, illustrated here:"]}, {"code": "SET enable_sort = off;\n\nEXPLAIN SELECT *\nFROM tenk1 t1, onek t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n                                        QUERY PLAN\n------------------------------------------------------------------------------------------\n Merge Join  (cost=0.56..292.65 rows=10 width=488)\n   Merge Cond: (t1.unique2 = t2.unique2)\n   ->  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..656.28 rows=101 width=244)\n         Filter: (unique1 < 100)\n   ->  Index Scan using onek_unique2 on onek t2  (cost=0.28..224.79 rows=1000 width=244)", "source": {"url": "/docs/16/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 16.15 \u00b7 using-explain", "sha256": "bd8b86e5281cf0e52ad6e0e4bb8b6ff6510dac61982b44f0c1dd07d54012db3c"}, "paragraphs": ["Example copied from the PostgreSQL 16.15 manual; it was not executed for this collection.", "One way to look at variant plans is to force the planner to disregard whatever strategy it thought was the cheapest, using the enable/disable flags described in Section 20.7.1 . (This is a crude tool, but useful. See also Section 14.3 .) For example, if we're unconvinced that sequential-scan-and-sort is the best way to deal with table onek in the previous example, we could try"]}]}, {"title": "Executor implementation notes", "paragraphs": ["NOTES The ModifyTable node receives input from its outerPlan, which is the data to insert for INSERT cases, the changed columns' new values plus row-locating info for UPDATE and MERGE cases, or just the row-locating info for DELETE cases.", "The relation to modify can be an ordinary table, a view having an INSTEAD OF trigger, or a foreign table. Earlier processing already pointed ModifyTable to the underlying relations of any automatically updatable view not using an INSTEAD OF trigger, so code here can assume it won't have one as a modification target. This node does process ri_WithCheckOptions, which may have expressions from those automatically updatable views.", "MERGE runs a join between the source relation and the target table; if any WHEN NOT MATCHED clauses are present, then the join is an outer join. In this case, any unmatched tuples will have NULL row-locating info, and only INSERT can be run. But for matched tuples, then row-locating info is used to determine the tuple to UPDATE or DELETE. When all clauses are WHEN MATCHED, then an inner join is used, so all tuples contain row-locating info.", "If the query specifies RETURNING, then the ModifyTable returns a RETURNING tuple after completing each row insert, update, or delete. It must be called again to continue the operation. Without RETURNING, we just loop within the node until all the work is done, then return NULL. This avoids useless call/return overhead. (MERGE does not support RETURNING.)", "Context struct for a ModifyTable operation, containing basic execution state and some output variables populated by ExecUpdateAct() and ExecDeleteAct() to report the result of their actions to callers."]}, {"code": "case T_ModifyTable:\n\t\t\tsname = \"ModifyTable\";\n\t\t\tswitch (((ModifyTable *) plan)->operation)\n\t\t\t{\n\t\t\t\tcase CMD_INSERT:\n\t\t\t\t\tpname = operation = \"Insert\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_UPDATE:\n\t\t\t\t\tpname = operation = \"Update\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_DELETE:\n\t\t\t\t\tpname = operation = \"Delete\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_MERGE:\n\t\t\t\t\tpname = operation = \"Merge\";\n\t\t\t\t\tbreak;\n\t\t\t\tdefault:\n\t\t\t\t\tpname = \"???\";\n\t\t\t\t\tbreak;\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Performs the data-modification operation selected by the plan and produces RETURNING rows when requested."], "evidence_kind": "source and documentation", "explain_names": ["Insert", "Update", "Delete", "Merge"], "partial_modes": [], "comparison_data": {"node_tag": "T_ModifyTable", "strategies": [], "text_names": ["Insert", "Update", "Delete", "Merge"], "initializer": "ExecInitModifyTable", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "cf664334c8d3479ccec4439895ce74d4602368021778f85473730f30aa4ff4db", "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/nodeModifyTable.c"}, "parallel_callbacks": []}, "17": {"facts": [{"label": "Core node tag", "value": "T_ModifyTable"}, {"label": "Structured EXPLAIN Node Type", "value": "ModifyTable"}, {"label": "Inputs", "value": "Modification input plan or plans"}, {"label": "Output", "value": "RETURNING tuples when requested"}, {"label": "Executor initializer", "value": "ExecInitModifyTable"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "4757e3ce690f5370f1cda6fdcdd10106ca31e31b5e5aa89b233d1297cadac38b", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": ["If the FDW supports batching, and batching is requested, accumulate rows and insert them in batches. Otherwise use the per-row inserts.", "Initialize the batch slots. We don't know how many slots will be needed, so we initialize them as the batch grows, and we keep them across batches. To mitigate an inefficiency in how resource owner handles objects with many references (as with many slots all referencing the same tuple descriptor) we copy the appropriate tuple descriptor for each slot."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Insert", "identity": "ModifyTable"}, {"label": "Update", "identity": "ModifyTable"}, {"label": "Delete", "identity": "ModifyTable"}, {"label": "Merge", "identity": "ModifyTable"}], "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"}], "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": 1400, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1400", "sha256": "741251b1a3b6d269a52a673d42eb63b02e13a5872db7b359b137086ab21b63c8", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "line": 176, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:176", "sha256": "a77576e158b94cb01fa8c5174ba133004eabdd727660323f8afc66c8d2e757b8", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "4757e3ce690f5370f1cda6fdcdd10106ca31e31b5e5aa89b233d1297cadac38b", "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"}, {"url": "/docs/17/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 17.11 \u00b7 using-explain", "sha256": "8e3422c77496cc53bfc225ccadda3e82eb23c8c362b8eb95e7fb02cdb315ea78"}, {"url": "/docs/17/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 17.11 \u00b7 using-explain", "sha256": "8e3422c77496cc53bfc225ccadda3e82eb23c8c362b8eb95e7fb02cdb315ea78"}], "node_tag": "T_ModifyTable", "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: Insert, Update, Delete, Merge.", "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": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "If the FDW supports batching, and batching is requested, accumulate rows and insert them in batches. Otherwise use the per-row inserts.", "Initialize the batch slots. We don't know how many slots will be needed, so we initialize them as the batch grows, and we keep them across batches. To mitigate an inefficiency in how resource owner handles objects with many references (as with many slots all referencing the same tuple descriptor) we copy the appropriate tuple descriptor for each slot."]}, {"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: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["Merge join requires its input data to be sorted on the join keys. In this example each input is sorted by using an index scan to visit the rows in the correct order; but a sequential scan and sort could also be used. (Sequential-scan-and-sort frequently beats an index scan for sorting many rows, because of the nonsequential disk access required by the index scan.)", "One way to look at variant plans is to force the planner to disregard whatever strategy it thought was the cheapest, using the enable/disable flags described in Section 19.7.1 . (This is a crude tool, but useful. See also Section 14.3 .) For example, if we're unconvinced that merge join is the best join type for the previous example, we could try", "which shows that the planner thinks that hash join would be nearly 50% more expensive than merge join for this case. Of course, the next question is whether it's right about that. We can investigate that using EXPLAIN ANALYZE , as discussed below .", "As seen in this example, when the query is an INSERT , UPDATE , DELETE , or MERGE command, the actual work of applying the table changes is done by a top-level Insert, Update, Delete, or Merge plan node. The plan nodes underneath this node perform the work of locating the old rows and/or computing the new data. So above, we see the same sort of bitmap table scan we've seen already, and its output is fed to an Update node that stores the updated rows. It's worth noting that although the data-modifying node can take a considerable amount of run time (here, it's consuming the lion's share of the time), the planner does not currently add anything to the cost estimates to account for that work. That's because the work to be done is the same for every correct query plan, so it doesn't affect planning decisions.", "When an UPDATE , DELETE , or MERGE command affects a partitioned table or inheritance hierarchy, the output might look like this:", "In this example the Update node needs to consider three child tables, but not the originally-mentioned partitioned table (since that never stores any data). So there are three input scanning subplans, one per table. For clarity, the Update node is annotated to show the specific target tables that will be updated, in the same order as the corresponding subplans."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, onek t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n                                        QUERY PLAN\n------------------------------------------------------------------------------------------\n Merge Join  (cost=0.56..233.49 rows=10 width=488)\n   Merge Cond: (t1.unique2 = t2.unique2)\n   ->  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..643.28 rows=100 width=244)\n         Filter: (unique1 < 100)\n   ->  Index Scan using onek_unique2 on onek t2  (cost=0.28..166.28 rows=1000 width=244)", "source": {"url": "/docs/17/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 17.11 \u00b7 using-explain", "sha256": "8e3422c77496cc53bfc225ccadda3e82eb23c8c362b8eb95e7fb02cdb315ea78"}, "paragraphs": ["Example copied from the PostgreSQL 17.11 manual; it was not executed for this collection.", "Another possible type of join is a merge join, illustrated here:"]}, {"code": "BEGIN;\n\nEXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;\n\n                                                           QUERY PLAN\n--------------------------------------------------------------------------------------------------------------------------------\n Update on tenk1  (cost=5.06..225.23 rows=0 width=0) (actual time=1.634..1.635 rows=0 loops=1)\n   ->  Bitmap Heap Scan on tenk1  (cost=5.06..225.23 rows=100 width=10) (actual time=0.065..0.141 rows=100 loops=1)\n         Recheck Cond: (unique1 < 100)\n         Heap Blocks: exact=90\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.031..0.031 rows=100 loops=1)\n               Index Cond: (unique1 < 100)\n Planning Time: 0.151 ms\n Execution Time: 1.856 ms\n\nROLLBACK;", "source": {"url": "/docs/17/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 17.11 \u00b7 using-explain", "sha256": "8e3422c77496cc53bfc225ccadda3e82eb23c8c362b8eb95e7fb02cdb315ea78"}, "paragraphs": ["Example copied from the PostgreSQL 17.11 manual; it was not executed for this collection.", "Keep in mind that because EXPLAIN ANALYZE actually runs the query, any side-effects will happen as usual, even though whatever results the query might output are discarded in favor of printing the EXPLAIN data. If you want to analyze a data-modifying query without changing your tables, you can roll the command back afterwards, for example:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["NOTES The ModifyTable node receives input from its outerPlan, which is the data to insert for INSERT cases, the changed columns' new values plus row-locating info for UPDATE and MERGE cases, or just the row-locating info for DELETE cases.", "The relation to modify can be an ordinary table, a foreign table, or a view. If it's a view, either it has sufficient INSTEAD OF triggers or this node executes only MERGE ... DO NOTHING. If the original MERGE targeted a view not in one of those two categories, earlier processing already pointed the ModifyTable result relation to an underlying relation of that other view. This node does process ri_WithCheckOptions, which may have expressions from those other, automatically updatable views.", "MERGE runs a join between the source relation and the target table. If any WHEN NOT MATCHED [BY TARGET] clauses are present, then the join is an outer join that might output tuples without a matching target tuple. In this case, any unmatched target tuples will have NULL row-locating info, and only INSERT can be run. But for matched target tuples, the row-locating info is used to determine the tuple to UPDATE or DELETE. When all clauses are WHEN MATCHED or WHEN NOT MATCHED BY SOURCE, all tuples produced by the join will include a matching target tuple, so all tuples contain row-locating info.", "If the query specifies RETURNING, then the ModifyTable returns a RETURNING tuple after completing each row insert, update, or delete. It must be called again to continue the operation. Without RETURNING, we just loop within the node until all the work is done, then return NULL. This avoids useless call/return overhead.", "Context struct for a ModifyTable operation, containing basic execution state and some output variables populated by ExecUpdateAct() and ExecDeleteAct() to report the result of their actions to callers."]}, {"code": "case T_ModifyTable:\n\t\t\tsname = \"ModifyTable\";\n\t\t\tswitch (((ModifyTable *) plan)->operation)\n\t\t\t{\n\t\t\t\tcase CMD_INSERT:\n\t\t\t\t\tpname = operation = \"Insert\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_UPDATE:\n\t\t\t\t\tpname = operation = \"Update\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_DELETE:\n\t\t\t\t\tpname = operation = \"Delete\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_MERGE:\n\t\t\t\t\tpname = operation = \"Merge\";\n\t\t\t\t\tbreak;\n\t\t\t\tdefault:\n\t\t\t\t\tpname = \"???\";\n\t\t\t\t\tbreak;\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Performs the data-modification operation selected by the plan and produces RETURNING rows when requested."], "evidence_kind": "source and documentation", "explain_names": ["Insert", "Update", "Delete", "Merge"], "partial_modes": [], "comparison_data": {"node_tag": "T_ModifyTable", "strategies": [], "text_names": ["Insert", "Update", "Delete", "Merge"], "initializer": "ExecInitModifyTable", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "cf664334c8d3479ccec4439895ce74d4602368021778f85473730f30aa4ff4db", "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/nodeModifyTable.c"}, "parallel_callbacks": []}, "18": {"facts": [{"label": "Core node tag", "value": "T_ModifyTable"}, {"label": "Structured EXPLAIN Node Type", "value": "ModifyTable"}, {"label": "Inputs", "value": "Modification input plan or plans"}, {"label": "Output", "value": "RETURNING tuples when requested"}, {"label": "Executor initializer", "value": "ExecInitModifyTable"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "0fc3cb180b3443f216142954e43d8ed5f84713d75094ce8cd2785ad8f604bd68", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": ["If the FDW supports batching, and batching is requested, accumulate rows and insert them in batches. Otherwise use the per-row inserts.", "Initialize the batch slots. We don't know how many slots will be needed, so we initialize them as the batch grows, and we keep them across batches. To mitigate an inefficiency in how resource owner handles objects with many references (as with many slots all referencing the same tuple descriptor) we copy the appropriate tuple descriptor for each slot."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Insert", "identity": "ModifyTable"}, {"label": "Update", "identity": "ModifyTable"}, {"label": "Delete", "identity": "ModifyTable"}, {"label": "Merge", "identity": "ModifyTable"}], "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"}], "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": 1385, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1385", "sha256": "34c86d6070224a0e981efef51f79101d6d505e5874f1684ace183034bab14bb4", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 176, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:176", "sha256": "f8a06a3f539077249b20664b2812433db6d7bd12b2c0ca633525db43d06f112a", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "0fc3cb180b3443f216142954e43d8ed5f84713d75094ce8cd2785ad8f604bd68", "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"}, {"url": "/docs/18/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}, {"url": "/docs/18/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}], "node_tag": "T_ModifyTable", "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: Insert, Update, Delete, Merge.", "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": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "If the FDW supports batching, and batching is requested, accumulate rows and insert them in batches. Otherwise use the per-row inserts.", "Initialize the batch slots. We don't know how many slots will be needed, so we initialize them as the batch grows, and we keep them across batches. To mitigate an inefficiency in how resource owner handles objects with many references (as with many slots all referencing the same tuple descriptor) we copy the appropriate tuple descriptor for each slot."]}, {"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: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["Merge join requires its input data to be sorted on the join keys. In this example each input is sorted by using an index scan to visit the rows in the correct order; but a sequential scan and sort could also be used. (Sequential-scan-and-sort frequently beats an index scan for sorting many rows, because of the nonsequential disk access required by the index scan.)", "One way to look at variant plans is to force the planner to disregard whatever strategy it thought was the cheapest, using the enable/disable flags described in Section 19.7.1 . (This is a crude tool, but useful. See also Section 14.3 .) For example, if we're unconvinced that merge join is the best join type for the previous example, we could try", "which shows that the planner thinks that hash join would be nearly 50% more expensive than merge join for this case. Of course, the next question is whether it's right about that. We can investigate that using EXPLAIN ANALYZE , as discussed below .", "As seen in this example, when the query is an INSERT , UPDATE , DELETE , or MERGE command, the actual work of applying the table changes is done by a top-level Insert, Update, Delete, or Merge plan node. The plan nodes underneath this node perform the work of locating the old rows and/or computing the new data. So above, we see the same sort of bitmap table scan we've seen already, and its output is fed to an Update node that stores the updated rows. It's worth noting that although the data-modifying node can take a considerable amount of run time (here, it's consuming the lion's share of the time), the planner does not currently add anything to the cost estimates to account for that work. That's because the work to be done is the same for every correct query plan, so it doesn't affect planning decisions.", "When an UPDATE , DELETE , or MERGE command affects a partitioned table or inheritance hierarchy, the output might look like this:", "In this example the Update node needs to consider three child tables, but not the originally-mentioned partitioned table (since that never stores any data). So there are three input scanning subplans, one per table. For clarity, the Update node is annotated to show the specific target tables that will be updated, in the same order as the corresponding subplans."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, onek t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n                                        QUERY PLAN\n------------------------------------------------------------------------------------------\n Merge Join  (cost=0.56..233.49 rows=10 width=488)\n   Merge Cond: (t1.unique2 = t2.unique2)\n   ->  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..643.28 rows=100 width=244)\n         Filter: (unique1 < 100)\n   ->  Index Scan using onek_unique2 on onek t2  (cost=0.28..166.28 rows=1000 width=244)", "source": {"url": "/docs/18/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}, "paragraphs": ["Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.", "Another possible type of join is a merge join, illustrated here:"]}, {"code": "BEGIN;\n\nEXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;\n\n                                                           QUERY PLAN\n--------------------------------------------------------------------------------------------------------------------------------\n Update on tenk1  (cost=5.06..225.23 rows=0 width=0) (actual time=1.634..1.635 rows=0.00 loops=1)\n   ->  Bitmap Heap Scan on tenk1  (cost=5.06..225.23 rows=100 width=10) (actual time=0.065..0.141 rows=100.00 loops=1)\n         Recheck Cond: (unique1 < 100)\n         Heap Blocks: exact=90\n         Buffers: shared hit=4 read=2\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.031..0.031 rows=100.00 loops=1)\n               Index Cond: (unique1 < 100)\n               Index Searches: 1\n               Buffers: shared read=2\n Planning Time: 0.151 ms\n Execution Time: 1.856 ms\n\nROLLBACK;", "source": {"url": "/docs/18/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}, "paragraphs": ["Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.", "Keep in mind that because EXPLAIN ANALYZE actually runs the query, any side-effects will happen as usual, even though whatever results the query might output are discarded in favor of printing the EXPLAIN data. If you want to analyze a data-modifying query without changing your tables, you can roll the command back afterwards, for example:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["NOTES The ModifyTable node receives input from its outerPlan, which is the data to insert for INSERT cases, the changed columns' new values plus row-locating info for UPDATE and MERGE cases, or just the row-locating info for DELETE cases.", "The relation to modify can be an ordinary table, a foreign table, or a view. If it's a view, either it has sufficient INSTEAD OF triggers or this node executes only MERGE ... DO NOTHING. If the original MERGE targeted a view not in one of those two categories, earlier processing already pointed the ModifyTable result relation to an underlying relation of that other view. This node does process ri_WithCheckOptions, which may have expressions from those other, automatically updatable views.", "MERGE runs a join between the source relation and the target table. If any WHEN NOT MATCHED [BY TARGET] clauses are present, then the join is an outer join that might output tuples without a matching target tuple. In this case, any unmatched target tuples will have NULL row-locating info, and only INSERT can be run. But for matched target tuples, the row-locating info is used to determine the tuple to UPDATE or DELETE. When all clauses are WHEN MATCHED or WHEN NOT MATCHED BY SOURCE, all tuples produced by the join will include a matching target tuple, so all tuples contain row-locating info.", "If the query specifies RETURNING, then the ModifyTable returns a RETURNING tuple after completing each row insert, update, or delete. It must be called again to continue the operation. Without RETURNING, we just loop within the node until all the work is done, then return NULL. This avoids useless call/return overhead.", "Context struct for a ModifyTable operation, containing basic execution state and some output variables populated by ExecUpdateAct() and ExecDeleteAct() to report the result of their actions to callers."]}, {"code": "case T_ModifyTable:\n\t\t\tsname = \"ModifyTable\";\n\t\t\tswitch (((ModifyTable *) plan)->operation)\n\t\t\t{\n\t\t\t\tcase CMD_INSERT:\n\t\t\t\t\tpname = operation = \"Insert\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_UPDATE:\n\t\t\t\t\tpname = operation = \"Update\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_DELETE:\n\t\t\t\t\tpname = operation = \"Delete\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_MERGE:\n\t\t\t\t\tpname = operation = \"Merge\";\n\t\t\t\t\tbreak;\n\t\t\t\tdefault:\n\t\t\t\t\tpname = \"???\";\n\t\t\t\t\tbreak;\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Performs the data-modification operation selected by the plan and produces RETURNING rows when requested."], "evidence_kind": "source and documentation", "explain_names": ["Insert", "Update", "Delete", "Merge"], "partial_modes": [], "comparison_data": {"node_tag": "T_ModifyTable", "strategies": [], "text_names": ["Insert", "Update", "Delete", "Merge"], "initializer": "ExecInitModifyTable", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "cf664334c8d3479ccec4439895ce74d4602368021778f85473730f30aa4ff4db", "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/nodeModifyTable.c"}, "parallel_callbacks": []}, "19": {"facts": [{"label": "Core node tag", "value": "T_ModifyTable"}, {"label": "Structured EXPLAIN Node Type", "value": "ModifyTable"}, {"label": "Inputs", "value": "Modification input plan or plans"}, {"label": "Output", "value": "RETURNING tuples when requested"}, {"label": "Executor initializer", "value": "ExecInitModifyTable"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "4f0fd312e0e56979e5ce0180b4dfd1f78d15aabb76f5e524d3068c6459b988f8", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": ["If the FDW supports batching, and batching is requested, accumulate rows and insert them in batches. Otherwise use the per-row inserts.", "Initialize the batch slots. We don't know how many slots will be needed, so we initialize them as the batch grows, and we keep them across batches. To mitigate an inefficiency in how resource owner handles objects with many references (as with many slots all referencing the same tuple descriptor) we copy the appropriate tuple descriptor for each slot."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Insert", "identity": "ModifyTable"}, {"label": "Update", "identity": "ModifyTable"}, {"label": "Delete", "identity": "ModifyTable"}, {"label": "Merge", "identity": "ModifyTable"}], "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"}], "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": 1397, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1397", "sha256": "8b115b1c194a4b54ae630209a741e293b1df49a9052f10b2de9ca092a48998e3", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "line": 176, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:176", "sha256": "5e39b2037bed672da55104229ecc32da5abde44c26bcad01479edcfa044d09ed", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "4f0fd312e0e56979e5ce0180b4dfd1f78d15aabb76f5e524d3068c6459b988f8", "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"}, {"url": "/docs/19/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 19beta4 \u00b7 using-explain", "sha256": "52f111fbd213e2200146319d01a9dcc2ea90001617d2a28edc52f7954c2dd70b"}, {"url": "/docs/19/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 19beta4 \u00b7 using-explain", "sha256": "52f111fbd213e2200146319d01a9dcc2ea90001617d2a28edc52f7954c2dd70b"}], "node_tag": "T_ModifyTable", "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: Insert, Update, Delete, Merge.", "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": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "If the FDW supports batching, and batching is requested, accumulate rows and insert them in batches. Otherwise use the per-row inserts.", "Initialize the batch slots. We don't know how many slots will be needed, so we initialize them as the batch grows, and we keep them across batches. To mitigate an inefficiency in how resource owner handles objects with many references (as with many slots all referencing the same tuple descriptor) we copy the appropriate tuple descriptor for each slot."]}, {"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: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["Merge join requires its input data to be sorted on the join keys. In this example each input is sorted by using an index scan to visit the rows in the correct order; but a sequential scan and sort could also be used. (Sequential-scan-and-sort frequently beats an index scan for sorting many rows, because of the nonsequential disk access required by the index scan.)", "One way to look at variant plans is to force the planner to disregard whatever strategy it thought was the cheapest, using the enable/disable flags described in Section 19.7.1 . (This is a crude tool, but useful. See also Section 14.3 .) For example, if we're unconvinced that merge join is the best join type for the previous example, we could try", "which shows that the planner thinks that hash join would be nearly 50% more expensive than merge join for this case. Of course, the next question is whether it's right about that. We can investigate that using EXPLAIN ANALYZE , as discussed below .", "As seen in this example, when the query is an INSERT , UPDATE , DELETE , or MERGE command, the actual work of applying the table changes is done by a top-level Insert, Update, Delete, or Merge plan node. The plan nodes underneath this node perform the work of locating the old rows and/or computing the new data. So above, we see the same sort of bitmap table scan we've seen already, and its output is fed to an Update node that stores the updated rows. It's worth noting that although the data-modifying node can take a considerable amount of run time (here, it's consuming the lion's share of the time), the planner does not currently add anything to the cost estimates to account for that work. That's because the work to be done is the same for every correct query plan, so it doesn't affect planning decisions.", "When an UPDATE , DELETE , or MERGE command affects a partitioned table or inheritance hierarchy, the output might look like this:", "In this example the Update node needs to consider three child tables, but not the originally-mentioned partitioned table (since that never stores any data). So there are three input scanning subplans, one per table. For clarity, the Update node is annotated to show the specific target tables that will be updated, in the same order as the corresponding subplans."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, onek t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n                                        QUERY PLAN\n------------------------------------------------------------------------------------------\n Merge Join  (cost=0.56..233.49 rows=10 width=488)\n   Merge Cond: (t1.unique2 = t2.unique2)\n   ->  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..643.28 rows=100 width=244)\n         Filter: (unique1 < 100)\n   ->  Index Scan using onek_unique2 on onek t2  (cost=0.28..166.28 rows=1000 width=244)", "source": {"url": "/docs/19/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 19beta4 \u00b7 using-explain", "sha256": "52f111fbd213e2200146319d01a9dcc2ea90001617d2a28edc52f7954c2dd70b"}, "paragraphs": ["Example copied from the PostgreSQL 19beta4 manual; it was not executed for this collection.", "Another possible type of join is a merge join, illustrated here:"]}, {"code": "BEGIN;\n\nEXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;\n\n                                                           QUERY PLAN\n--------------------------------------------------------------------------------------------------------------------------------\n Update on tenk1  (cost=5.06..225.23 rows=0 width=0) (actual time=1.634..1.635 rows=0.00 loops=1)\n   ->  Bitmap Heap Scan on tenk1  (cost=5.06..225.23 rows=100 width=10) (actual time=0.065..0.141 rows=100.00 loops=1)\n         Recheck Cond: (unique1 < 100)\n         Heap Blocks: exact=90\n         Buffers: shared hit=4 read=2\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.031..0.031 rows=100.00 loops=1)\n               Index Cond: (unique1 < 100)\n               Index Searches: 1\n               Buffers: shared read=2\n Planning Time: 0.151 ms\n Execution Time: 1.856 ms\n\nROLLBACK;", "source": {"url": "/docs/19/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 19beta4 \u00b7 using-explain", "sha256": "52f111fbd213e2200146319d01a9dcc2ea90001617d2a28edc52f7954c2dd70b"}, "paragraphs": ["Example copied from the PostgreSQL 19beta4 manual; it was not executed for this collection.", "Keep in mind that because EXPLAIN ANALYZE actually runs the query, any side-effects will happen as usual, even though whatever results the query might output are discarded in favor of printing the EXPLAIN data. If you want to analyze a data-modifying query without changing your tables, you can roll the command back afterwards, for example:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["NOTES The ModifyTable node receives input from its outerPlan, which is the data to insert for INSERT cases, the changed columns' new values plus row-locating info for UPDATE and MERGE cases, or just the row-locating info for DELETE cases.", "The relation to modify can be an ordinary table, a foreign table, or a view. If it's a view, either it has sufficient INSTEAD OF triggers or this node executes only MERGE ... DO NOTHING. If the original MERGE targeted a view not in one of those two categories, earlier processing already pointed the ModifyTable result relation to an underlying relation of that other view. This node does process ri_WithCheckOptions, which may have expressions from those other, automatically updatable views.", "MERGE runs a join between the source relation and the target table. If any WHEN NOT MATCHED [BY TARGET] clauses are present, then the join is an outer join that might output tuples without a matching target tuple. In this case, any unmatched target tuples will have NULL row-locating info, and only INSERT can be run. But for matched target tuples, the row-locating info is used to determine the tuple to UPDATE or DELETE. When all clauses are WHEN MATCHED or WHEN NOT MATCHED BY SOURCE, all tuples produced by the join will include a matching target tuple, so all tuples contain row-locating info.", "If the query specifies RETURNING, then the ModifyTable returns a RETURNING tuple after completing each row insert, update, or delete. It must be called again to continue the operation. Without RETURNING, we just loop within the node until all the work is done, then return NULL. This avoids useless call/return overhead.", "Context struct for a ModifyTable operation, containing basic execution state and some output variables populated by ExecUpdateAct() and ExecDeleteAct() to report the result of their actions to callers."]}, {"code": "case T_ModifyTable:\n\t\t\tsname = \"ModifyTable\";\n\t\t\tswitch (((ModifyTable *) plan)->operation)\n\t\t\t{\n\t\t\t\tcase CMD_INSERT:\n\t\t\t\t\tpname = operation = \"Insert\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_UPDATE:\n\t\t\t\t\tpname = operation = \"Update\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_DELETE:\n\t\t\t\t\tpname = operation = \"Delete\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_MERGE:\n\t\t\t\t\tpname = operation = \"Merge\";\n\t\t\t\t\tbreak;\n\t\t\t\tdefault:\n\t\t\t\t\tpname = \"???\";\n\t\t\t\t\tbreak;\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Performs the data-modification operation selected by the plan and produces RETURNING rows when requested."], "evidence_kind": "source and documentation", "explain_names": ["Insert", "Update", "Delete", "Merge"], "partial_modes": [], "comparison_data": {"node_tag": "T_ModifyTable", "strategies": [], "text_names": ["Insert", "Update", "Delete", "Merge"], "initializer": "ExecInitModifyTable", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "cf664334c8d3479ccec4439895ce74d4602368021778f85473730f30aa4ff4db", "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/nodeModifyTable.c"}, "parallel_callbacks": []}, "20": {"facts": [{"label": "Core node tag", "value": "T_ModifyTable"}, {"label": "Structured EXPLAIN Node Type", "value": "ModifyTable"}, {"label": "Inputs", "value": "Modification input plan or plans"}, {"label": "Output", "value": "RETURNING tuples when requested"}, {"label": "Executor initializer", "value": "ExecInitModifyTable"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "a8a3423f161af2ac3996e82d6fb2654b7c16ef217ec01fea86f4495e66736687", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": ["If the FDW supports batching, and batching is requested, accumulate rows and insert them in batches. Otherwise use the per-row inserts.", "Initialize the batch slots. We don't know how many slots will be needed, so we initialize them as the batch grows, and we keep them across batches. To mitigate an inefficiency in how resource owner handles objects with many references (as with many slots all referencing the same tuple descriptor) we copy the appropriate tuple descriptor for each slot."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Insert", "identity": "ModifyTable"}, {"label": "Update", "identity": "ModifyTable"}, {"label": "Delete", "identity": "ModifyTable"}, {"label": "Merge", "identity": "ModifyTable"}], "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"}], "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": 1397, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1397", "sha256": "13402758013520451539427b5993db06d463ca11c4e2d4cc5444e82367688077", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "line": 176, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:176", "sha256": "5e39b2037bed672da55104229ecc32da5abde44c26bcad01479edcfa044d09ed", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "a8a3423f161af2ac3996e82d6fb2654b7c16ef217ec01fea86f4495e66736687", "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"}, {"url": "/docs/devel/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 20devel \u00b7 using-explain", "sha256": "99cfea3035876ea63f88b75ba8c964b51a32a70606544e09f769c0b60a234b31"}, {"url": "/docs/devel/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 20devel \u00b7 using-explain", "sha256": "99cfea3035876ea63f88b75ba8c964b51a32a70606544e09f769c0b60a234b31"}], "node_tag": "T_ModifyTable", "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: Insert, Update, Delete, Merge.", "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": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "If the FDW supports batching, and batching is requested, accumulate rows and insert them in batches. Otherwise use the per-row inserts.", "Initialize the batch slots. We don't know how many slots will be needed, so we initialize them as the batch grows, and we keep them across batches. To mitigate an inefficiency in how resource owner handles objects with many references (as with many slots all referencing the same tuple descriptor) we copy the appropriate tuple descriptor for each slot."]}, {"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: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["Merge join requires its input data to be sorted on the join keys. In this example each input is sorted by using an index scan to visit the rows in the correct order; but a sequential scan and sort could also be used. (Sequential-scan-and-sort frequently beats an index scan for sorting many rows, because of the nonsequential disk access required by the index scan.)", "One way to look at variant plans is to force the planner to disregard whatever strategy it thought was the cheapest, using the enable/disable flags described in Section 19.7.1 . (This is a crude tool, but useful. See also Section 14.3 .) For example, if we're unconvinced that merge join is the best join type for the previous example, we could try", "which shows that the planner thinks that hash join would be nearly 50% more expensive than merge join for this case. Of course, the next question is whether it's right about that. We can investigate that using EXPLAIN ANALYZE , as discussed below .", "As seen in this example, when the query is an INSERT , UPDATE , DELETE , or MERGE command, the actual work of applying the table changes is done by a top-level Insert, Update, Delete, or Merge plan node. The plan nodes underneath this node perform the work of locating the old rows and/or computing the new data. So above, we see the same sort of bitmap table scan we've seen already, and its output is fed to an Update node that stores the updated rows. It's worth noting that although the data-modifying node can take a considerable amount of run time (here, it's consuming the lion's share of the time), the planner does not currently add anything to the cost estimates to account for that work. That's because the work to be done is the same for every correct query plan, so it doesn't affect planning decisions.", "When an UPDATE , DELETE , or MERGE command affects a partitioned table or inheritance hierarchy, the output might look like this:", "In this example the Update node needs to consider three child tables, but not the originally-mentioned partitioned table (since that never stores any data). So there are three input scanning subplans, one per table. For clarity, the Update node is annotated to show the specific target tables that will be updated, in the same order as the corresponding subplans."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, onek t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n                                        QUERY PLAN\n------------------------------------------------------------------------------------------\n Merge Join  (cost=0.56..233.49 rows=10 width=488)\n   Merge Cond: (t1.unique2 = t2.unique2)\n   ->  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..643.28 rows=100 width=244)\n         Filter: (unique1 < 100)\n   ->  Index Scan using onek_unique2 on onek t2  (cost=0.28..166.28 rows=1000 width=244)", "source": {"url": "/docs/devel/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 20devel \u00b7 using-explain", "sha256": "99cfea3035876ea63f88b75ba8c964b51a32a70606544e09f769c0b60a234b31"}, "paragraphs": ["Example copied from the PostgreSQL 20devel manual; it was not executed for this collection.", "Another possible type of join is a merge join, illustrated here:"]}, {"code": "BEGIN;\n\nEXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;\n\n                                                           QUERY PLAN\n--------------------------------------------------------------------------------------------------------------------------------\n Update on tenk1  (cost=5.06..225.23 rows=0 width=0) (actual time=1.634..1.635 rows=0.00 loops=1)\n   ->  Bitmap Heap Scan on tenk1  (cost=5.06..225.23 rows=100 width=10) (actual time=0.065..0.141 rows=100.00 loops=1)\n         Recheck Cond: (unique1 < 100)\n         Heap Blocks: exact=90\n         Buffers: shared hit=4 read=2\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.031..0.031 rows=100.00 loops=1)\n               Index Cond: (unique1 < 100)\n               Index Searches: 1\n               Buffers: shared read=2\n Planning Time: 0.151 ms\n Execution Time: 1.856 ms\n\nROLLBACK;", "source": {"url": "/docs/devel/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 20devel \u00b7 using-explain", "sha256": "99cfea3035876ea63f88b75ba8c964b51a32a70606544e09f769c0b60a234b31"}, "paragraphs": ["Example copied from the PostgreSQL 20devel manual; it was not executed for this collection.", "Keep in mind that because EXPLAIN ANALYZE actually runs the query, any side-effects will happen as usual, even though whatever results the query might output are discarded in favor of printing the EXPLAIN data. If you want to analyze a data-modifying query without changing your tables, you can roll the command back afterwards, for example:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["NOTES The ModifyTable node receives input from its outerPlan, which is the data to insert for INSERT cases, the changed columns' new values plus row-locating info for UPDATE and MERGE cases, or just the row-locating info for DELETE cases.", "The relation to modify can be an ordinary table, a foreign table, or a view. If it's a view, either it has sufficient INSTEAD OF triggers or this node executes only MERGE ... DO NOTHING. If the original MERGE targeted a view not in one of those two categories, earlier processing already pointed the ModifyTable result relation to an underlying relation of that other view. This node does process ri_WithCheckOptions, which may have expressions from those other, automatically updatable views.", "MERGE runs a join between the source relation and the target table. If any WHEN NOT MATCHED [BY TARGET] clauses are present, then the join is an outer join that might output tuples without a matching target tuple. In this case, any unmatched target tuples will have NULL row-locating info, and only INSERT can be run. But for matched target tuples, the row-locating info is used to determine the tuple to UPDATE or DELETE. When all clauses are WHEN MATCHED or WHEN NOT MATCHED BY SOURCE, all tuples produced by the join will include a matching target tuple, so all tuples contain row-locating info.", "If the query specifies RETURNING, then the ModifyTable returns a RETURNING tuple after completing each row insert, update, or delete. It must be called again to continue the operation. Without RETURNING, we just loop within the node until all the work is done, then return NULL. This avoids useless call/return overhead.", "Context struct for a ModifyTable operation, containing basic execution state and some output variables populated by ExecUpdateAct() and ExecDeleteAct() to report the result of their actions to callers."]}, {"code": "case T_ModifyTable:\n\t\t\tsname = \"ModifyTable\";\n\t\t\tswitch (((ModifyTable *) plan)->operation)\n\t\t\t{\n\t\t\t\tcase CMD_INSERT:\n\t\t\t\t\tpname = operation = \"Insert\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_UPDATE:\n\t\t\t\t\tpname = operation = \"Update\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_DELETE:\n\t\t\t\t\tpname = operation = \"Delete\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_MERGE:\n\t\t\t\t\tpname = operation = \"Merge\";\n\t\t\t\t\tbreak;\n\t\t\t\tdefault:\n\t\t\t\t\tpname = \"???\";\n\t\t\t\t\tbreak;\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Performs the data-modification operation selected by the plan and produces RETURNING rows when requested."], "evidence_kind": "source and documentation", "explain_names": ["Insert", "Update", "Delete", "Merge"], "partial_modes": [], "comparison_data": {"node_tag": "T_ModifyTable", "strategies": [], "text_names": ["Insert", "Update", "Delete", "Merge"], "initializer": "ExecInitModifyTable", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "cf664334c8d3479ccec4439895ce74d4602368021778f85473730f30aa4ff4db", "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/nodeModifyTable.c"}, "parallel_callbacks": []}}}, "snapshot": {"facts": [{"label": "Core node tag", "value": "T_ModifyTable"}, {"label": "Structured EXPLAIN Node Type", "value": "ModifyTable"}, {"label": "Inputs", "value": "Modification input plan or plans"}, {"label": "Output", "value": "RETURNING tuples when requested"}, {"label": "Executor initializer", "value": "ExecInitModifyTable"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "0fc3cb180b3443f216142954e43d8ed5f84713d75094ce8cd2785ad8f604bd68", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": ["If the FDW supports batching, and batching is requested, accumulate rows and insert them in batches. Otherwise use the per-row inserts.", "Initialize the batch slots. We don't know how many slots will be needed, so we initialize them as the batch grows, and we keep them across batches. To mitigate an inefficiency in how resource owner handles objects with many references (as with many slots all referencing the same tuple descriptor) we copy the appropriate tuple descriptor for each slot."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Insert", "identity": "ModifyTable"}, {"label": "Update", "identity": "ModifyTable"}, {"label": "Delete", "identity": "ModifyTable"}, {"label": "Merge", "identity": "ModifyTable"}], "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"}], "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": 1385, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1385", "sha256": "34c86d6070224a0e981efef51f79101d6d505e5874f1684ace183034bab14bb4", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 176, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:176", "sha256": "f8a06a3f539077249b20664b2812433db6d7bd12b2c0ca633525db43d06f112a", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeModifyTable.c", "label": "src/backend/executor/nodeModifyTable.c", "sha256": "0fc3cb180b3443f216142954e43d8ed5f84713d75094ce8cd2785ad8f604bd68", "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"}, {"url": "/docs/18/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}, {"url": "/docs/18/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}], "node_tag": "T_ModifyTable", "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: Insert, Update, Delete, Merge.", "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": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "If the FDW supports batching, and batching is requested, accumulate rows and insert them in batches. Otherwise use the per-row inserts.", "Initialize the batch slots. We don't know how many slots will be needed, so we initialize them as the batch grows, and we keep them across batches. To mitigate an inefficiency in how resource owner handles objects with many references (as with many slots all referencing the same tuple descriptor) we copy the appropriate tuple descriptor for each slot."]}, {"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: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["Merge join requires its input data to be sorted on the join keys. In this example each input is sorted by using an index scan to visit the rows in the correct order; but a sequential scan and sort could also be used. (Sequential-scan-and-sort frequently beats an index scan for sorting many rows, because of the nonsequential disk access required by the index scan.)", "One way to look at variant plans is to force the planner to disregard whatever strategy it thought was the cheapest, using the enable/disable flags described in Section 19.7.1 . (This is a crude tool, but useful. See also Section 14.3 .) For example, if we're unconvinced that merge join is the best join type for the previous example, we could try", "which shows that the planner thinks that hash join would be nearly 50% more expensive than merge join for this case. Of course, the next question is whether it's right about that. We can investigate that using EXPLAIN ANALYZE , as discussed below .", "As seen in this example, when the query is an INSERT , UPDATE , DELETE , or MERGE command, the actual work of applying the table changes is done by a top-level Insert, Update, Delete, or Merge plan node. The plan nodes underneath this node perform the work of locating the old rows and/or computing the new data. So above, we see the same sort of bitmap table scan we've seen already, and its output is fed to an Update node that stores the updated rows. It's worth noting that although the data-modifying node can take a considerable amount of run time (here, it's consuming the lion's share of the time), the planner does not currently add anything to the cost estimates to account for that work. That's because the work to be done is the same for every correct query plan, so it doesn't affect planning decisions.", "When an UPDATE , DELETE , or MERGE command affects a partitioned table or inheritance hierarchy, the output might look like this:", "In this example the Update node needs to consider three child tables, but not the originally-mentioned partitioned table (since that never stores any data). So there are three input scanning subplans, one per table. For clarity, the Update node is annotated to show the specific target tables that will be updated, in the same order as the corresponding subplans."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, onek t2\nWHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;\n\n                                        QUERY PLAN\n------------------------------------------------------------------------------------------\n Merge Join  (cost=0.56..233.49 rows=10 width=488)\n   Merge Cond: (t1.unique2 = t2.unique2)\n   ->  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..643.28 rows=100 width=244)\n         Filter: (unique1 < 100)\n   ->  Index Scan using onek_unique2 on onek t2  (cost=0.28..166.28 rows=1000 width=244)", "source": {"url": "/docs/18/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}, "paragraphs": ["Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.", "Another possible type of join is a merge join, illustrated here:"]}, {"code": "BEGIN;\n\nEXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;\n\n                                                           QUERY PLAN\n--------------------------------------------------------------------------------------------------------------------------------\n Update on tenk1  (cost=5.06..225.23 rows=0 width=0) (actual time=1.634..1.635 rows=0.00 loops=1)\n   ->  Bitmap Heap Scan on tenk1  (cost=5.06..225.23 rows=100 width=10) (actual time=0.065..0.141 rows=100.00 loops=1)\n         Recheck Cond: (unique1 < 100)\n         Heap Blocks: exact=90\n         Buffers: shared hit=4 read=2\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.031..0.031 rows=100.00 loops=1)\n               Index Cond: (unique1 < 100)\n               Index Searches: 1\n               Buffers: shared read=2\n Planning Time: 0.151 ms\n Execution Time: 1.856 ms\n\nROLLBACK;", "source": {"url": "/docs/18/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}, "paragraphs": ["Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.", "Keep in mind that because EXPLAIN ANALYZE actually runs the query, any side-effects will happen as usual, even though whatever results the query might output are discarded in favor of printing the EXPLAIN data. If you want to analyze a data-modifying query without changing your tables, you can roll the command back afterwards, for example:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["NOTES The ModifyTable node receives input from its outerPlan, which is the data to insert for INSERT cases, the changed columns' new values plus row-locating info for UPDATE and MERGE cases, or just the row-locating info for DELETE cases.", "The relation to modify can be an ordinary table, a foreign table, or a view. If it's a view, either it has sufficient INSTEAD OF triggers or this node executes only MERGE ... DO NOTHING. If the original MERGE targeted a view not in one of those two categories, earlier processing already pointed the ModifyTable result relation to an underlying relation of that other view. This node does process ri_WithCheckOptions, which may have expressions from those other, automatically updatable views.", "MERGE runs a join between the source relation and the target table. If any WHEN NOT MATCHED [BY TARGET] clauses are present, then the join is an outer join that might output tuples without a matching target tuple. In this case, any unmatched target tuples will have NULL row-locating info, and only INSERT can be run. But for matched target tuples, the row-locating info is used to determine the tuple to UPDATE or DELETE. When all clauses are WHEN MATCHED or WHEN NOT MATCHED BY SOURCE, all tuples produced by the join will include a matching target tuple, so all tuples contain row-locating info.", "If the query specifies RETURNING, then the ModifyTable returns a RETURNING tuple after completing each row insert, update, or delete. It must be called again to continue the operation. Without RETURNING, we just loop within the node until all the work is done, then return NULL. This avoids useless call/return overhead.", "Context struct for a ModifyTable operation, containing basic execution state and some output variables populated by ExecUpdateAct() and ExecDeleteAct() to report the result of their actions to callers."]}, {"code": "case T_ModifyTable:\n\t\t\tsname = \"ModifyTable\";\n\t\t\tswitch (((ModifyTable *) plan)->operation)\n\t\t\t{\n\t\t\t\tcase CMD_INSERT:\n\t\t\t\t\tpname = operation = \"Insert\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_UPDATE:\n\t\t\t\t\tpname = operation = \"Update\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_DELETE:\n\t\t\t\t\tpname = operation = \"Delete\";\n\t\t\t\t\tbreak;\n\t\t\t\tcase CMD_MERGE:\n\t\t\t\t\tpname = operation = \"Merge\";\n\t\t\t\t\tbreak;\n\t\t\t\tdefault:\n\t\t\t\t\tpname = \"???\";\n\t\t\t\t\tbreak;\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Performs the data-modification operation selected by the plan and produces RETURNING rows when requested."], "evidence_kind": "source and documentation", "explain_names": ["Insert", "Update", "Delete", "Merge"], "partial_modes": [], "comparison_data": {"node_tag": "T_ModifyTable", "strategies": [], "text_names": ["Insert", "Update", "Delete", "Merge"], "initializer": "ExecInitModifyTable", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "cf664334c8d3479ccec4439895ce74d4602368021778f85473730f30aa4ff4db", "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/nodeModifyTable.c"}, "parallel_callbacks": []}, "comparison": {"left": "17", "right": "18", "status": "unchanged", "diff": ""}}