{"Entry":{"collection":"plan","key":"modifytable","name":"ModifyTable","aliases":["Delete","Insert","Merge","ModifyTable","T_ModifyTable","Update"],"metadata":{"aliases":["Delete","Insert","Merge","ModifyTable","T_ModifyTable","Update"],"category":"Modification","content_hash":"68bbce37f45404930fee9ad5ae3daa94be8681a1f255e3a0e86f0d3d67324dcf","derived_content_hash":"21192c85fdc7f1551e8558430a1aa540667b5a289a2cb17124943fcd5d3511bf","imported_at":"2026-09-30T00:40:44.164051+08:00","name":"ModifyTable","name_zh":"ModifyTable","slug":"modifytable","summary":"Performs the data-modification operation selected by the plan and produces RETURNING rows when requested."}},"Definition":{"Collection":"plan","Key":"modifytable","SourceDatabase":"center","Version":"18","SourceTable":"plan_node","SourceKey":"modifytable","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","Facts":{"comparison_data":{"initializer":"ExecInitModifyTable","memory_mechanism":"unclassified","node_tag":"T_ModifyTable","parallel_callbacks":[],"partial_modes":[],"strategies":[],"text_names":["Insert","Update","Delete","Merge"]},"comparison_hash":"cf664334c8d3479ccec4439895ce74d4602368021778f85473730f30aa4ff4db","description":["Performs the data-modification operation selected by the plan and produces RETURNING rows when requested."],"description_zh":"执行计划指定的数据修改，并在请求时生成 RETURNING 行。","description_zh_html":"\u003cp\u003e执行计划指定的数据修改，并在请求时生成 RETURNING 行。\u003c/p\u003e\u003cp\u003e核心节点标识：\u003ccode\u003eT_ModifyTable\u003c/code\u003e；EXPLAIN 名称：\u003ccode\u003eInsert, Update, Delete, Merge\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e本版本源码中的并行执行或观测回调：该节点实现中未提取到此类回调。回调的存在不表示该节点必然支持共享并行扫描；在并行工作进程中执行也不等于具有并行感知能力。\u003c/p\u003e\u003ch3\u003e同版本中文手册节选\u003c/h3\u003e\u003cp\u003e\n    查询计划的结构是一棵由\u003cem class=\"firstterm\"\u003e计划节点\u003c/em\u003e组成的树。树的最底层是扫描节点，它们从表中返回原始行。不同的表访问方法对应不同类型的扫描节点，例如顺序扫描、索引扫描和位图索引扫描。也有一些并非表的行来源，例如 \u003ccode class=\"literal\"\u003eVALUES\u003c/code\u003e 子句和 \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e 中的返回集合函数，它们也各自有对应的扫描节点类型。如果查询需要对原始行执行连接、聚合、排序或其他操作，那么扫描节点之上还会出现额外的节点来完成这些操作。同样，这些操作通常不止一种实现方式，因此这里也会出现不同的节点类型。\u003ccode class=\"command\"\u003eEXPLAIN\u003c/code\u003e 会为计划树中的每个节点输出一行，显示基本节点类型以及规划器对该计划节点执行代价的估计值。还可能出现相对节点摘要行缩进的附加行，用来显示该节点的更多属性。第一行，也就是最顶层节点的摘要行，给出了整个计划的估计总执行代价；规划器力图最小化的正是这个数字。\n   \u003c/p\u003e\u003cp\u003e\n    下面是一个简单的示例，仅用于展示输出的样子：\n\n\u003c/p\u003e\u003cp\u003e\n    由于这个查询没有 \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e 子句，它必须扫描表中的所有行，因此规划器选择了一个简单的顺序扫描计划。圆括号中的数字从左到右依次表示：\n\n    \u003c/p\u003e\u003cp\u003e\n    这些代价使用由规划器代价参数决定的任意单位来衡量（见\u003ca class=\"xref\" href=\"/zh/docs/18/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS\"\u003e第 19.7.2 节\u003c/a\u003e）。传统上通常以磁盘页读取作为代价单位；也就是说，惯例上将 \u003ca class=\"xref\" href=\"/zh/docs/18/runtime-config-query.html#GUC-SEQ-PAGE-COST\"\u003eseq_page_cost\u003c/a\u003e 设为 \u003ccode class=\"literal\"\u003e1.0\u003c/code\u003e，其他代价参数都相对它来设定。本节中的示例都使用默认代价参数。\n   \u003c/p\u003e\u003cp\u003e\u003ca href=\"/zh/docs/18/using-explain.html#USING-EXPLAIN-BASICS\"\u003e查看完整中文手册与上下文\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e本页开头的中文说明为 PG.CENTER 对编辑说明的翻译；手册节选保留对应版本中文文档原文。源码信息及手册示例不代表重新进行过运行测试。\u003c/p\u003e","evidence_kind":"source and documentation","explain_names":["Insert","Update","Delete","Merge"],"explain_prefixes":["Parallel","Async"],"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"}],"facts_zh":[{"label":"核心节点标签","value":"T_ModifyTable"},{"label":"结构化 EXPLAIN 节点类型","value":"ModifyTable"},{"label":"输入","value":"一个或多个数据修改输入计划"},{"label":"输出","value":"请求时输出 RETURNING 元组"},{"label":"执行器初始化函数","value":"ExecInitModifyTable"},{"label":"内存机制","value":"unclassified"}],"memory":{"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.","evidence":[{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/executor/nodeModifyTable.c","path":"src/backend/executor/nodeModifyTable.c","sha256":"0fc3cb180b3443f216142954e43d8ed5f84713d75094ce8cd2785ad8f604bd68","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"}],"mechanism":"unclassified","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."]},"node_tag":"T_ModifyTable","parallel_callbacks":[],"partial_modes":[],"related":[{"label":"EXPLAIN","url":"/wiki/sql/explain/?v=18"},{"label":"Using EXPLAIN","url":"/docs/18/using-explain.html"},{"label":"Parallel plans","url":"/docs/18/parallel-plans.html"}],"release":{"channel":"stable","label":"18.6","major":"18","ref":"PostgreSQL 18.6 source archive","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_snapshot_utc":"","source_url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},"runtime_verified":false,"sections":[{"paragraphs":["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.","Text names recorded by this source: 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":"EXPLAIN names and attributes"},{"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":"Memory and temporary storage"},{"paragraphs":["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.","Callbacks in this build: none extracted from this node implementation."],"title":"Parallel execution and instrumentation"},{"paragraphs":["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":"Same-version manual discussion"},{"blocks":[{"code":"EXPLAIN SELECT *\nFROM tenk1 t1, onek t2\nWHERE t1.unique1 \u003c 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   -\u003e  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..643.28 rows=100 width=244)\n         Filter: (unique1 \u003c 100)\n   -\u003e  Index Scan using onek_unique2 on onek t2  (cost=0.28..166.28 rows=1000 width=244)","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:"],"source":{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-BASICS"}},{"code":"BEGIN;\n\nEXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 \u003c 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   -\u003e  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 \u003c 100)\n         Heap Blocks: exact=90\n         Buffers: shared hit=4 read=2\n         -\u003e  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 \u003c 100)\n               Index Searches: 1\n               Buffers: shared read=2\n Planning Time: 0.151 ms\n Execution Time: 1.856 ms\n\nROLLBACK;","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:"],"source":{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-ANALYZE"}}],"title":"Examples from this manual build"},{"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."],"title":"Executor implementation notes"},{"code":"case T_ModifyTable:\n\t\t\tsname = \"ModifyTable\";\n\t\t\tswitch (((ModifyTable *) plan)-\u003eoperation)\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"}],"sections_zh":[{"paragraphs":["结构化格式使用上述 Node Type。文本格式名称还可能包含操作、策略、连接类型、扫描方向或聚合阶段属性。","此源码记录的文本名称：Insert, Update, Delete, Merge.","并行感知与并行安全是不同的计划属性。在并行工作进程内运行的节点不一定是并行感知节点。"],"title":"EXPLAIN 名称与属性"},{"paragraphs":["本次抽取不为此节点设定统一的内存上限或落盘策略。请查看同一构建的实现、相关表达式或提供方。","如果 FDW 支持批处理且请求了批处理，则累积行并分批插入，否则逐行插入。","初始化批次槽。由于不知道需要多少槽，随着批次增长逐步初始化，并在不同批次间保留。为缓解资源所有者处理大量引用对象时的低效情况，例如许多槽引用同一元组描述符，为每个槽复制相应的元组描述符。"],"title":"内存与临时存储"},{"paragraphs":["以下源码回调可以协调执行或收集工作进程的测量数据。回调存在不代表该节点普遍支持共享并行扫描或共享状态。","此构建的回调：none extracted from this node implementation."],"title":"并行执行与运行信息采集"},{"paragraphs":["归并连接要求输入数据按连接键排序。在这个例子中，两个输入都通过索引扫描按正确顺序访问行而完成排序；不过也可以采用顺序扫描再排序的方式。（对于需要排序很多行的情况，顺序扫描加排序往往会胜过索引扫描，因为索引扫描需要非顺序磁盘访问。）","观察备选计划的一种方法，是利用 第 19.7.1 节 中描述的启用/禁用标志，强迫规划器忽略它认为最便宜的策略。（这是个粗糙但有用的工具。另见 第 14.3 节 。）例如，如果我们并不确信前一个示例中归并连接真的是最佳连接类型，可以试试：","这表明，规划器认为在这个场景下，哈希连接的代价几乎比归并连接高出 50%。当然，接下来的问题就是它是否判断正确。我们可以像 下文 所述，使用 EXPLAIN ANALYZE 来研究。","如本例所示，查询为 INSERT、UPDATE、DELETE 或 MERGE 命令时，实际应用表变更的工作由顶层 Insert、Update、Delete 或 Merge 计划节点完成。其下的计划节点负责定位旧行和／或计算新数据。因此，上例仍采用前面见过的位图表扫描，其输出传给 Update 节点，由后者存储更新后的行。虽然数据修改节点可能消耗相当多的运行时间（本例中占了绝大部分），但规划器目前并未将这部分工作计入代价估算。这是因为任何正确查询计划需要完成的这部分工作都相同，因此不会影响规划决策。","当 UPDATE 、 DELETE 或 MERGE 命令影响分区表或继承层次时，输出可能像这样：","在这个示例中，Update 节点需要考虑三个子表，而不包括最初提到的那个分区表，因为它本身并不存储任何数据。因此这里有三个输入扫描子计划，每个表对应一个。为了便于理解，Update 节点带有注释，显示将要更新的具体目标表，其顺序与对应子计划的顺序一致。"],"title":"同版本手册说明"},{"blocks":[{"code":"EXPLAIN SELECT *\nFROM tenk1 t1, onek t2\nWHERE t1.unique1 \u003c 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   -\u003e  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..643.28 rows=100 width=244)\n         Filter: (unique1 \u003c 100)\n   -\u003e  Index Scan using onek_unique2 on onek t2  (cost=0.28..166.28 rows=1000 width=244)","paragraphs":["示例摘自 PostgreSQL 18.6 手册；本百科未实际执行此示例。","另一种可能的连接方式是归并连接，如下所示："],"source":{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-BASICS"}},{"code":"BEGIN;\n\nEXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 \u003c 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   -\u003e  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 \u003c 100)\n         Heap Blocks: exact=90\n         Buffers: shared hit=4 read=2\n         -\u003e  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 \u003c 100)\n               Index Searches: 1\n               Buffers: shared read=2\n Planning Time: 0.151 ms\n Execution Time: 1.856 ms\n\nROLLBACK;","paragraphs":["示例摘自 PostgreSQL 18.6 手册；本百科未实际执行此示例。","请记住，EXPLAIN ANALYZE 会实际执行查询，因此所有副作用都会照常发生，只是查询本应输出的结果被丢弃，改为打印 EXPLAIN 数据。如果希望分析修改数据的查询而不改变表，可以随后回滚命令，例如："],"source":{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-ANALYZE"}}],"title":"本版手册中的示例"},{"paragraphs":["说明：ModifyTable 从 outerPlan 接收输入：INSERT 时为待插入数据；UPDATE 和 MERGE 时为修改列的新值及行定位信息；DELETE 时仅为行定位信息。","待修改关系可以是普通表、外部表或视图。如果是视图，要么具有足够的 INSTEAD OF 触发器，要么此节点只执行 MERGE ... DO NOTHING。若原始 MERGE 指向不属于这两类的视图，之前的处理已把 ModifyTable 的结果关系改为该视图的底层关系。此节点会处理 ri_WithCheckOptions，其中可能包含那些可自动更新视图的表达式。","MERGE 对源关系和目标表执行连接。若包含 WHEN NOT MATCHED [BY TARGET] 子句，则使用可能输出没有匹配目标元组的外连接。此时，未匹配目标元组的行定位信息为 NULL，只能执行 INSERT。对于匹配的目标元组，使用行定位信息确定要 UPDATE 或 DELETE 的元组。若所有子句均为 WHEN MATCHED 或 WHEN NOT MATCHED BY SOURCE，连接输出的所有元组都包含匹配的目标元组，因此都有行定位信息。","如果查询指定了 RETURNING，ModifyTable 每完成一行的插入、更新或删除后就返回一个 RETURNING 元组，需要再次调用才能继续执行。如果没有 RETURNING，则在节点内部循环，直到全部工作完成，再返回 NULL，从而避免无用的调用／返回开销。","ModifyTable 操作的上下文结构体，包含基本执行状态，以及由 ExecUpdateAct() 和 ExecDeleteAct() 填充、用于向调用者报告执行结果的若干输出变量。"],"title":"执行器实现说明"},{"code":"case T_ModifyTable:\n\t\t\tsname = \"ModifyTable\";\n\t\t\tswitch (((ModifyTable *) plan)-\u003eoperation)\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 标识"}],"source_inventory":{"executor":"src/backend/executor/execProcnode.c","explain":"src/backend/commands/explain.c","implementation":"src/backend/executor/nodeModifyTable.c"},"sources":[{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/commands/explain.c:1385","line":1385,"path":"src/backend/commands/explain.c","sha256":"34c86d6070224a0e981efef51f79101d6d505e5874f1684ace183034bab14bb4","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/executor/execProcnode.c:176","line":176,"path":"src/backend/executor/execProcnode.c","sha256":"f8a06a3f539077249b20664b2812433db6d7bd12b2c0ca633525db43d06f112a","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/executor/nodeModifyTable.c","path":"src/backend/executor/nodeModifyTable.c","sha256":"0fc3cb180b3443f216142954e43d8ed5f84713d75094ce8cd2785ad8f604bd68","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/include/nodes/plannodes.h","path":"src/include/nodes/plannodes.h","sha256":"52422b327a8049fbbb20d8b96008a0fc0a6fafa60f7eff3c695d5b2e83830120","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-BASICS"},{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-ANALYZE"}],"strategies":[],"tables":[{"columns":[{"key":"label","label":"Text-format label"},{"key":"identity","label":"Structured node identity"}],"key":"explain-labels","rows":[{"identity":"ModifyTable","label":"Insert"},{"identity":"ModifyTable","label":"Update"},{"identity":"ModifyTable","label":"Delete"},{"identity":"ModifyTable","label":"Merge"}],"title":"EXPLAIN labels in this source build"}],"tables_zh":[{"columns":[{"key":"label","label":"文本格式标签"},{"key":"identity","label":"结构化节点标识"}],"key":"explain-labels","rows":[{"identity":"ModifyTable","label":"Insert"},{"identity":"ModifyTable","label":"Update"},{"identity":"ModifyTable","label":"Delete"},{"identity":"ModifyTable","label":"Merge"}],"title":"本构建中的 EXPLAIN 标签"}],"zh_from":{"authored_translation":"authored-translation-of-reviewed-guidance","body_sha256":"8f2993bb8ee82647fcf02e8708620ee636cf68b34a69062ba7c162e26c9e27d3","kind":"same-version-chinese-manual-with-authored-translation-of-reviewed-guidance","manual":{"anchor":"USING-EXPLAIN-BASICS","build_id":107,"extraction":"concept-introduction","fragment_sha256":"bc79b6f9c70f2b56968e08ea26d2c9ec3d395e425aca0594e227203daa0ad29b","locale":"zh-Hans","page_key":"using-explain.html","source_hash":"d2f749744b3ac5aa2eb0154f295e8163903cd20fcc130c74c6172cdf2ae3a9df","source_revision":"unknown:rendered-translation-baseline","translated_from_revision":null,"translation_revision":"git:b1b500221dbe5437cfca004d6b04d3f6e422765d;html-sha256:08e987a66af27b98444a8057c256314eafa13611a3fb9e888214fc436824244b","url":"/zh/docs/18/using-explain.html#USING-EXPLAIN-BASICS","version":"18"},"runtime_rerun":false,"source_facts_revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}},"ManualEvidence":{"release":{"channel":"stable","label":"18.6","major":"18","ref":"PostgreSQL 18.6 source archive","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_snapshot_utc":"","source_url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},"sources":[{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/commands/explain.c:1385","line":1385,"path":"src/backend/commands/explain.c","sha256":"34c86d6070224a0e981efef51f79101d6d505e5874f1684ace183034bab14bb4","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/executor/execProcnode.c:176","line":176,"path":"src/backend/executor/execProcnode.c","sha256":"f8a06a3f539077249b20664b2812433db6d7bd12b2c0ca633525db43d06f112a","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/backend/executor/nodeModifyTable.c","path":"src/backend/executor/nodeModifyTable.c","sha256":"0fc3cb180b3443f216142954e43d8ed5f84713d75094ce8cd2785ad8f604bd68","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"src/include/nodes/plannodes.h","path":"src/include/nodes/plannodes.h","sha256":"52422b327a8049fbbb20d8b96008a0fc0a6fafa60f7eff3c695d5b2e83830120","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-BASICS"},{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-ANALYZE"}]},"MeasuredEvidence":{"runtime_verified":false}},"Text":{"Collection":"plan","Key":"modifytable","SourceDatabase":"center","Version":"18","Locale":"en","Title":"ModifyTable","Summary":"Performs the data-modification operation selected by the plan and produces RETURNING rows when requested.","BodyHTML":"\u003cp\u003ePerforms the data-modification operation selected by the plan and produces RETURNING rows when requested.\u003c/p\u003e","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","ContentHash":"c38ec8f15450571f4dc2d25178a2ce28e5b4d875001b14ca4ed21e93a0cd5d31","Payload":{"description":["Performs the data-modification operation selected by the plan and produces RETURNING rows when requested."],"related":[{"label":"EXPLAIN","url":"/wiki/sql/explain/?v=18"},{"label":"Using EXPLAIN","url":"/docs/18/using-explain.html"},{"label":"Parallel plans","url":"/docs/18/parallel-plans.html"}],"sections":[{"paragraphs":["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.","Text names recorded by this source: 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":"EXPLAIN names and attributes"},{"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":"Memory and temporary storage"},{"paragraphs":["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.","Callbacks in this build: none extracted from this node implementation."],"title":"Parallel execution and instrumentation"},{"paragraphs":["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":"Same-version manual discussion"},{"blocks":[{"code":"EXPLAIN SELECT *\nFROM tenk1 t1, onek t2\nWHERE t1.unique1 \u003c 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   -\u003e  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..643.28 rows=100 width=244)\n         Filter: (unique1 \u003c 100)\n   -\u003e  Index Scan using onek_unique2 on onek t2  (cost=0.28..166.28 rows=1000 width=244)","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:"],"source":{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-BASICS"}},{"code":"BEGIN;\n\nEXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 \u003c 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   -\u003e  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 \u003c 100)\n         Heap Blocks: exact=90\n         Buffers: shared hit=4 read=2\n         -\u003e  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 \u003c 100)\n               Index Searches: 1\n               Buffers: shared read=2\n Planning Time: 0.151 ms\n Execution Time: 1.856 ms\n\nROLLBACK;","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:"],"source":{"label":"PostgreSQL 18.6 · using-explain","path":"using-explain.html","sha256":"60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed","url":"/docs/18/using-explain.html#USING-EXPLAIN-ANALYZE"}}],"title":"Examples from this manual build"},{"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."],"title":"Executor implementation notes"},{"code":"case T_ModifyTable:\n\t\t\tsname = \"ModifyTable\";\n\t\t\tswitch (((ModifyTable *) plan)-\u003eoperation)\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"}],"tables":[{"columns":[{"key":"label","label":"Text-format label"},{"key":"identity","label":"Structured node identity"}],"key":"explain-labels","rows":[{"identity":"ModifyTable","label":"Insert"},{"identity":"ModifyTable","label":"Update"},{"identity":"ModifyTable","label":"Delete"},{"identity":"ModifyTable","label":"Merge"}],"title":"EXPLAIN labels in this source build"}]}},"RequestedLocale":"en","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}