Read with me. PostgreSQL source Code (eight)--executor (Query execution module--can optimize the execution of the statement)

2. Can optimize the execution of the statement

The common feature of the optimized statements is that they are processed by the query compiler to generate an inquiry plan tree, which is handled by the executor (Executor). The module provides three interfaces externally: Executorstart, Executorrun, and Executorend, whose input is the data structure Querydesc that contains the inquiry plan tree, and the output is the relevant execution information or the result data. If you want to execute a plan tree, you only need to construct a querydesc that contains this plan tree, and call Executorstart, Executorrun, executorend 3 procedures in turn to complete the corresponding process. From my previous article with me read the PostgreSQL source (vi)--executor (Query execution module-Query execution strategy) can be seen that the executor of the three interface functions are called in the relevant functions of the portal, respectively, responsible for the initialization, execution and cleanup of the actuator, The portal is also used in the same way in processing, so that the resource allocation and recovery work independently of the execution process, can simplify the implementation process, is a good way to manage resources.

The executor's handling of the inquiry plan tree is eventually converted to the processing of each node on the plan tree. Each node represents a physical algebra (physical Algebra) operation, and PostgreSQL initializes, processes, and cleans it in turn. The processing of the nodes is designed as Demand-driven mode, and the parent node uses the data provided by the child nodes as input and returns processing results to its upper nodes. When the actual execution, starting from the root node processing, the execution of each node will automatically call the execution of the child nodes according to the requirements of the process to obtain input data (generally tuples), so that the layer of recursive execution, to achieve the entire planning tree traversal execution process. Initialization and cleanup also adopt the same design pattern, this design pattern makes the code structure of node processing concise and unified, the semantics is clear, and the implementation method is simple and effective.

Next, the actuator part of the various principles, the implementation of a further introduction.

2.1 Processing Mode

The planning node in PostgreSQL is defined as having a 0~2 input and an output, which is to be able to correspond to a two-tree structure in the implementation. So you know: all the planning nodes are organized into a two-fork tree structure.

Each plan node corresponds to a node in the tree, and the output of the lower node is entered as the upper node. The data (tuples) flows from the underlying node to the upper node until the root node, and the output of the root node is the result of the entire poll.

For example, there is a query:

select a.q,b.w,c.e from a join b join c order by a.q limit 1;

Then the planning node might look like this (in fact, you explain a query to see the direct relationship of each node):

limit
          ^
          |
         sort
          ^
          |
         join
          ^^
         /       join   scan
     ^^
    /  scan    scan

In the implementation of PostgreSQL, the upper function initializes, executes, and cleans up the nodes by Execinitnode, Execprocnode, Execendnode two interface functions, and these three functions invoke the corresponding initialization according to the actual type of the node being processed. Execute, Clean function (visible at the end of this article).

Thus, the nodes on the survey plan tree form the pipelines of the physical tuples to the execution results, so the execution of the inquiry plan tree can be seen as the process of pulling tuples through the pipeline. PostgreSQL takes a one-tuple execution pattern, and each node is executed once to return only one tuple to the upper-level node. Therefore, the execution of the entire inquiry plan tree is also a one-tuple pattern. This model has a number of advantages:

• Decreases the delay of the returned tuple.

• For some operations, such as cursors, limit clauses, and so on, there is no need to get all the tuples at once, saving overhead.

• Reduces the code complexity of the cache results during implementation and the overhead of temporary storage during execution.

2.2 Data structure of the planning node

From the previous introduction we have seen that the survey plan tree is composed of various planning nodes, then how to store and represent the various nodes in PostgreSQL? The data structure representation of the hash type node is given.

The data structures of all plan node nodes begin with a field of type plan, which is somewhat like the inheritance of a class: Consider plan as a parent class, and the other plan node nodes are its direct or indirect subclasses. A thick object-oriented taste.

As shown, the join node is a subclass of plan, inheriting left and right subtree pointers (Lefttree, Rightlree), node type (type), selection expression (qual), and projection linked list (Targediso, etc.) from plan. and has its own extension field connection type (JoinType) and connection conditions (joinqual); The Hashjoin node is a subclass of the join node, with its own extended field hashclauses.

The PostgreSQL system divides all the planning nodes into four categories by function:

Control node
Scan node
Join node
Materialization node 

The public parent scan and join are defined for the scan, connection node type, respectively. The hash connection belongs to the connection node, so the hash connection inherits from the join node. The public parent class of the connection node type defines the type of connection and the condition of the connection. As a hash connection node, the hash function is required, so the Hashjoin node extension defines the Hashclauses field to store the relevant information, including the attributes that need to be hashed and the hash function used.

The many sub-class nodes of plan form the entire survey plan tree through the Lefttree and Righttree fields, and the root node pointer is stored in the PLANNEDSTMT type data structure, which contains the type of statement (CommandType), the query plan tree root node ( Plantrce), the consulting scope table (rtable), the result relational table (resultrelation) plannedstmt structure is placed in Querydesc, the basic definition of the QUERYDESC structure as shown.

As input to the actuator, Querydesc includes the survey plan tree (plannedstmt field), the function statement-related execution plan (utilitystmt field), the Executor Global State (estate field), and the Scheduled node execution status (planstate field). As can be seen, the actuator global State Estate holds the scope table (es_range_table) in which the inquiry is involved, the memory context in which the estate resides (ES_QUERY_CXT, which is also the memory context maintained during execution), The Global tuple table (es_tupletable) that is used to pass tuples between nodes, and the memory context (Es_per_tuple_exprcontext) that is reclaimed for each tuple that gets one.

When the actuator is initialized, Executorstart constructs the executor Global State (estate) and the scheduled node execution state (planstate) according to the survey plan tree. During the execution of the inquiry plan tree, the executor uses planstate to record the execution state and data of the plan node and passes the result tuple between the nodes using the Es_tupletable field in the global state record. The cleanup function of the executor Executorend will reclaim the global state of the executor and the scheduled node execution state.

The correspondence between the data structure and the plan node in PostgreSQL for scheduling node execution state record is given. PostgreSQL defines a state node for each of the scheduling nodes. All state nodes are inherited from the Planstate node, which contains the secondary plan node pointer (plan), the executor global State structure pointer (states), the projection operation related information (TargetList), the selection operation related condition (qual), and the left and right child state node pointers ( Lefttree, Righttree).

State nodes are organized into a state node tree that is similar to the survey plan tree by Lefttree and righttree pointers, and each state node holds a pointer to its corresponding plan node (the Plan field in the Planstate type). The public parent class Joinstale, which inherits from Planstate, extends the connection type (JoinType) and the Join condition (joinqual) properties, as shown in the node state of the connection node (Plan nodes and node execution states). Hashjoinstate inherits from Joinstate and extends more properties, including hash function related content (hashclauses, hj_hashtable, Hj_outerhashkeys, Hj_innerhashkeys, HJ _hashoperators), the left Dial hand node returns a tuple pointer (hj_outertupleslot), the right child node returns a tuple pointer (hj_hashtupleslot), and so on.

So far, the main data structures involved in the execution of actuators have been introduced. The input to the actuator is Querydesc, which contains the PLANNEDSTMT structure that stores the pointer to the tree root node of the poll plan. When the executor executes, the global state record estate structure is constructed first, the corresponding state node (planstate) is constructed for each planning node (plan), and the relevant structure is used to store the execution state in execution, and the related data structure is released when the execution is complete.

2.3 Operation of Actuators

In PostgreSQL, three interface functions are provided for invoking actuators, Executorstart, Executorrun, and Executorend, respectively. When you need to use an executor to process the survey plan, you only need to call the three functions in turn to complete the actuator
The line process.

As shown in the function call relationship at the runtime of the executor, Executorstart performs the necessary initialization of the executor by calling Standard_executorstart, which includes the construction of the estate structure and the initialization of the Inquiry plan tree ( The corresponding planstate tree is constructed, which is done by the Initplan function). The functionality of the Executorrun is implemented by Standard_executorrun, which is invoked during the execution of Executeplan to complete the execution of the inquiry plan. Executorend is done by the Standard_executorend function, which frees the allocated resources by invoking the Execendplan processing execution state tree node, and finally releases the Executor global State estate complete the entire execution process.

• (1) Initializing the inquiry plan tree

The initialization of the polling plan tree in the executor starts with its root node and initializes its child nodes recursively. The initialization process for a scheduled node typically goes through several basic steps as shown, which outputs the plansute structure pointer corresponding to the plan node after the initialization of the Plan node, and the planstate structure of the plan node is organized into a plan node execution state tree According to the structure of the inquiry plan tree. The main task of initializing a plan node is to construct the corresponding Planstale structure and assign values to the related fields based on the information defined in the Plan node.

The initialization of the plan node is done by the function Execinitnode, which is entered as the Plan node to initialize and returns the planstate structure pointer corresponding to the plan node. In Execinitnode, the corresponding processing is invoked by judging the type of the plan node, each of which has a dedicated initialization function, and is named in the form of "Execinit node type". For example, the initialization function for the Nestloop node is execinitnestloop. During the initialization of a scheduled node, if the node also has child nodes below it, the child node's initialization function is called recursively to initialize the subnode. The Execinitnode function invokes the initialization function (Execinitnestloop) of the type node based on the type of the Plan node (t_nestloop). Because the Nestloop node has two child nodes, Execinitnestloop calls Execinitnode to initialize its left child nodes first. The planstate structure pointer returned is stored in the Lefttree field of the nestloopstate structure constructed for nestloop, and the right child node is initialized in the same way. The returned planstate structure pointer is stored in the Righttree field of the nestloopstate. Similarly, if the left and right child nodes also have lower nodes, the initialization process is recursively recursive in exactly the same way until the leaf node of the polling plan tree is reached. In the initialization process, the tree is also returned to the upper nodes, and is linked in the planstate structure of the upper node, and finally constructs the complete planstate tree.

• (2) Query plan execution

The actual execution of the survey plan is done by the function Executeplan, the body part of the function is a large loop, each cycle through the Execprocnode function from the Plan node state tree to obtain a tuple, and then the tuple is processed accordingly (adding and deleting), and then return the results of processing. The loop process ends when Execprocnode no longer takes a valid tuple from the Plan node state tree.

The execution of Execproonode is similar to Execinitnode: fetching data from the root node of the Plan node state tree, the upper node will recursively invoke Execprocnode to get input data from the underlying node in order to be able to complete its own processing (typically tuples), Then, according to the input data to the upper node corresponding processing, the final selection of the conditions of operation and projection operations, and to the upper node to return the result tuple pointer. Like Execinitnode, Execprocnode is also a selection function that invokes the corresponding handler function based on the type of node being processed. For example, for a node of type Nestloop, its handler function is Execnestloop. The Execnestloop function also calls Execprocnode for the input data for the two child nodes of the Nestloop type. If its child nodes also have lower nodes, the Execprocnode is called recursively in the same way until the leaf node is reached. Each node is Execprocnode processed and returns a result tuple, which is processed as the input of the upper node to form the result tuple of the upper node, and eventually the root node returns the result tuple.

Whenever a result tuple is obtained from the Plan node state tree through Execprocnode, the Executeplan function invokes the corresponding function for final processing based on the operation type of the entire statement. For simple queries that do not scan the table (for example, select 1), the result node is called, and the "poll" results are directly output through the Execresult function. For queries that need to scan the table (for example, select XX from TableXX), the system will directly return the results after scanning the nodes, and for adding and deleting the query, the case is special, there is a dedicated modifytable node to deal with it: the main call Execinsert, Execdelete, execupdate These three functions for processing. For an INSERT statement, you first need to call execconstraints to constrain the tuple that is about to be inserted, and if the requirement is met, Execinsert calls the function Heap_insert to store the tuple in the storage system. For deletions and updates, the Heap_delete and heap_update are called by Execdelete and Execupdate respectively.

For other special cases, there are special nodes to deal with, which is called the control node in PostgreSQL. Readers can view the Execprocnode function for details. (This part of the code 9.5.4 and the 8.x version is significantly different)

• (3) Actuator Cleaning

After the executor has processed all the available tuples, the executor cleans up the function executorend responsible for the aftercare work. The function calls Execendplan to clean up the execution state tree of the scheduling node. The cleanup of the execution state tree for the scheduling node is similar to that of the execution state tree: Recursive invocation of execendnode from the root node cleans up the execution state node of each scheduled node. Similarly, Execendnode is just a selection function, and there are corresponding Cheongju functions for different types of nodes. For example, the cleanup function for the Nestloop node is execendnestloop. As shown, the task of the cleanup process is primarily to reclaim the resources allocated during initialization, the memory of the projection and selection structure, the result tuple storage, and so on, Executorend will also call Freeexecutorstate to clean up the global state of the executor after the scheduled node execution state tree has been cleaned up.

Note: The text in this article a large number of reference to the Peng teacher "PostgreSQL database kernel Analysis" a book, this acknowledgement.

Let's start by talking about the various planning nodes involved in query execution.

Read with me. PostgreSQL source Code (eight)--executor (Query execution module--can optimize the execution of the statement)