Postgresql 中文操作指南

52.6. Executor #

executor 获取由规划器/优化器创建的规划,并递归处理它以提取所需的行集。这本质上是一个按需拉取管道机制。每次调用计划节点时,它都必须提供另一行,或报告它已完成传递行。

The executor takes the plan created by the planner/optimizer and recursively processes it to extract the required set of rows. This is essentially a demand-pull pipeline mechanism. Each time a plan node is called, it must deliver one more row, or report that it is done delivering rows.

为了提供一个具体示例,假设顶级节点是 MergeJoin 节点。在执行任何合并之前,必须获取两行(从每个子规划中获取一行)。因此,执行程序会递归调用自身来处理子规划(它从附加到 lefttree 的子规划开始)。新的顶级节点(左子规划的顶级节点)是,假设,一个 Sort 节点,并且再次需要递归来获取输入行。Sort 的子节点可以是 SeqScan 节点,表示实际读取一个表。执行此节点将导致执行程序从表中获取一行并将其返回到调用节点。Sort 节点将重复调用其子节点以获取要排序的所有行。当输入耗尽(子节点返回 NULL 而不是一行表示)时,Sort 代码执行排序,最后能够返回其第一行输出,即按排序顺序排列的第一行。它将保留其余行,以便它能按排序顺序在响应后面的需求时传递它们。

To provide a concrete example, assume that the top node is a MergeJoin node. Before any merge can be done two rows have to be fetched (one from each subplan). So the executor recursively calls itself to process the subplans (it starts with the subplan attached to lefttree). The new top node (the top node of the left subplan) is, let’s say, a Sort node and again recursion is needed to obtain an input row. The child node of the Sort might be a SeqScan node, representing actual reading of a table. Execution of this node causes the executor to fetch a row from the table and return it up to the calling node. The Sort node will repeatedly call its child to obtain all the rows to be sorted. When the input is exhausted (as indicated by the child node returning a NULL instead of a row), the Sort code performs the sort, and finally is able to return its first output row, namely the first one in sorted order. It keeps the remaining rows stored so that it can deliver them in sorted order in response to later demands.

MergeJoin 节点以相似的方式从其右子规划中获取第一行。然后,它比较这两行以查看它们是否可以连接;如果可以,它会向其调用者返回一个连接行。在下一次调用中,或者如果它无法连接当前输入对,它会转到另一张表中的下一行(取决于比较结果),并再次检查匹配项。最终,一个子规划或另一个子规划将耗尽,并且 MergeJoin 节点将返回 NULL 以指示无法再形成更多连接行。

The MergeJoin node similarly demands the first row from its right subplan. Then it compares the two rows to see if they can be joined; if so, it returns a join row to its caller. On the next call, or immediately if it cannot join the current pair of inputs, it advances to the next row of one table or the other (depending on how the comparison came out), and again checks for a match. Eventually, one subplan or the other is exhausted, and the MergeJoin node returns NULL to indicate that no more join rows can be formed.

复杂查询可能涉及许多级别的计划节点,但常规方法是相同的:每当调用时,每个节点都会计算并返回其下一行输出。每个节点还负责应用规划器分配给它的任何选择或投影表达式。

Complex queries can involve many levels of plan nodes, but the general approach is the same: each node computes and returns its next output row each time it is called. Each node is also responsible for applying any selection or projection expressions that were assigned to it by the planner.

执行程序机制用于评估所有五种基本 SQL 查询类型:SELECTINSERTUPDATEDELETEMERGE。对于 SELECT,顶级执行程序代码仅需要将查询计划树返回的每一行发送到客户端。INSERT …​ SELECTUPDATEDELETEMERGE 有效地为 SELECT_s under a special top-level plan node called _ModifyTable

The executor mechanism is used to evaluate all five basic SQL query types: SELECT, INSERT, UPDATE, DELETE, and MERGE. For SELECT, the top-level executor code only needs to send each row returned by the query plan tree off to the client. INSERT …​ SELECT, UPDATE, DELETE, and MERGE are effectively SELECT_s under a special top-level plan node called _ModifyTable.

INSERT …​ SELECT 将行馈送至 ModifyTable 以插入。对于 UPDATE,规划器安排每行计算包含所有更新的列值以及目标原始行的 TID(元组 ID 或行 ID);此数据被馈送至 ModifyTable 节点,该节点使用该信息创建一个新的更新行并标记旧行已删除。对于 DELETE,该计划实际返回的唯一列是 TID,并且 ModifyTable 节点仅使用 TID 访问每个目标行并将其标记为已删除。对于 MERGE,规划器将源关系与目标关系连接,并包括任何 WHEN 子句要求的所有列值,加上目标行的 TID;此数据被馈送至 ModifyTable 节点,该节点使用该信息来确定要执行的 WHEN 子句,然后根据需要插入、更新或删除目标行。

INSERT …​ SELECT feeds the rows up to ModifyTable for insertion. For UPDATE, the planner arranges that each computed row includes all the updated column values, plus the TID (tuple ID, or row ID) of the original target row; this data is fed up to the ModifyTable node, which uses the information to create a new updated row and mark the old row deleted. For DELETE, the only column that is actually returned by the plan is the TID, and the ModifyTable node simply uses the TID to visit each target row and mark it deleted. For MERGE, the planner joins the source and target relations, and includes all column values required by any of the WHEN clauses, plus the TID of the target row; this data is fed up to the ModifyTable node, which uses the information to work out which WHEN clause to execute, and then inserts, updates or deletes the target row, as required.

一个简单的 INSERT …​ VALUES 命令创建一个简单的计划树,该树包含一个 Result 节点,该节点仅计算一行结果,将其馈送至 ModifyTable 来执行插入。

A simple INSERT …​ VALUES command creates a trivial plan tree consisting of a single Result node, which computes just one result row, feeding that up to ModifyTable to perform the insertion.