PostgreSQL数据库查询优化——计划树

比眉伴天荒 2022-10-16 09:55 213阅读 0赞

### PostgreSQL数据库查询优化——计划树 ###

*   *  PlannedStmt node
     *  Plan node
     *   *  子类节点

计划树由许多称为计划节点的元素组成，这些节点挂在PlannedStmt结构对应的计划树上，定义在plannodes.h中(src/include/nodes/plannodes.h)。

## PlannedStmt node ##

计划器的输出是一个以 PlannedStmt 节点为首的计划树。 PlannedStmt 保存着执行者所需的“一次性”信息。为了简化 API，我们还在 PlannedStmt 节点中包装了实用程序语句； 在这种情况下，commandType == CMD\_UTILITY，语句本身在utilityStmt 字段中，结构的其余部分大多是虚拟的。 （我们确实使用 canSetTag、stmt\_location、stmt\_len 和可能的 queryId。）

typedef struct PlannedStmt { 
    	NodeTag		type;
    	CmdType		commandType;	/* select|insert|update|delete|utility */
    	uint64		queryId;		/* query identifier (copied from Query) */
    	bool		hasReturning;	/* is it insert|update|delete RETURNING? */
    	bool		hasModifyingCTE;	/* has insert|update|delete in WITH? */
    	bool		canSetTag;		/* do I set the command result tag? */
    	bool		transientPlan;	/* redo plan when TransactionXmin changes? */
    	bool		dependsOnRole;	/* is plan specific to current role? */
    	bool		parallelModeNeeded; /* parallel mode required to execute? */
    	int			jitFlags;		/* which forms of JIT should be performed */
    	struct Plan *planTree;		/* tree of Plan nodes */
    	List	   *rtable;			/* list of RangeTblEntry nodes */
    	/* rtable indexes of target relations for INSERT/UPDATE/DELETE */
    	List	   *resultRelations;	/* integer list of RT indexes, or NIL */
    	/* rtable indexes of partitioned table roots that are UPDATE/DELETE * targets; needed for trigger firing. */
    	List	   *rootResultRelations;
    	List	   *subplans;		/* Plan trees for SubPlan expressions; note that some could be NULL */
    	Bitmapset  *rewindPlanIDs;	/* indices of subplans that require REWIND */
    	List	   *rowMarks;		/* a list of PlanRowMark's */
    	List	   *relationOids;	/* OIDs of relations the plan depends on */
    	List	   *invalItems;		/* other dependencies, as PlanInvalItems */
    	List	   *paramExecTypes; /* type OIDs for PARAM_EXEC Params */
    	Node	   *utilityStmt;	/* non-null if this is utility stmt */
    	/* statement location in source string (copied from Query) */
    	int			stmt_location;	/* start location, or -1 if unknown */
    	int			stmt_len;		/* length in bytes; 0 means "rest of string" */
    } PlannedStmt;

## Plan node ##

所有的Plan节点都包含Plan结构作为第一个成员，相当于基类，可以通过强制类型转换将其他Plan节点转换为Plan结构。主要关注所有Plan节点共有的通用结构：plan\_node\_id、targetlist、qual、lefttree、righttree、initPlan。

typedef struct Plan { 
    	NodeTag		type;
    	/* estimated execution costs for plan (see costsize.c for more info) */
    	Cost		startup_cost;	/* cost expended before fetching any tuples */
    	Cost		total_cost;		/* total cost (assuming all tuples fetched) */
    	/* planner's estimate of result size of this plan step */
    	double		plan_rows;		/* number of rows plan is expected to emit */
    	int			plan_width;		/* average row width in bytes */
    	/* information needed for parallel query */
    	bool		parallel_aware; /* engage parallel-aware logic? */
    	bool		parallel_safe;	/* OK to use as part of parallel plan? */
    	/* Common structural data for all Plan types. */
    	int			plan_node_id;	/* unique across entire final plan tree */
    	List	   *targetlist;		/* target list to be computed at this node */
    	List	   *qual;			/* implicitly-ANDed qual conditions */
    	struct Plan *lefttree;		/* input plan tree(s) */
    	struct Plan *righttree;
    	List	   *initPlan;		/* Init Plan nodes (un-correlated expr subselects) */
    	/* Information for management of parameter-change-driven rescanning * extParam includes the paramIDs of all external PARAM_EXEC params * affecting this plan node or its children. setParam params from the * node's initPlans are not included, but their extParams are. * allParam includes all the extParam paramIDs, plus the IDs of local * params that affect the node (i.e., the setParams of its initplans). * These are _all_ the PARAM_EXEC params that affect this node. */
    	Bitmapset  *extParam;
    	Bitmapset  *allParam;
    } Plan;

### 子类节点 ###

typedef struct Result { 
    	Plan		plan;
    	Node	   *resconstantqual;
    } Result;

如果没有外部计划，则评估无变量目标列表(variable-free targetlist)。 如果是外部计划，则从外部计划返回元组（在目标列表所示的投影级别level projection之后）。如果 resconstantqual 不为 NULL，则表示一次资格测试one-time qualification（即，不依赖于外部计划中的任何变量，因此只需要评估一次）。

typedef struct ProjectSet{ 
    	Plan		plan;
    } ProjectSet;

将包含集合返回函数的投影projection应用于外部计划的输出元组。Apply a projection that includes set-returning functions to the output tuples of the outer plan.

--------------------

`typedef struct ModifyTable`  
通过插入、更新或删除，将子计划生成的行应用到结果表。如果最初命名的目标表是分区表，那么nominalRelation 和rootRelation 都包含分区根的RT 索引(计划中没有另外提及)。 否则 rootRelation 为零。 但是，nominalRelation 将始终被设置，因为EXPLAIN应该声明的rel是 INSERT/UPDATE/DELETE 目标。请注意，假定 rowMarks 和 epqParam 对所有子计划都有效； 它们不能包含任何因子计划而异的信息。

--------------------

typedef struct Append { 
    	Plan		plan;
    	List	   *appendplans;
    	/* All 'appendplans' preceding this index are non-partial plans. All 'appendplans' from this index onwards are partial plans. */
    	int			first_partial_plan;
    	/* Info for run-time subplan pruning; NULL if we're not doing that */
    	struct PartitionPruneInfo *part_prune_info;
    } Append;

生成子计划结果的串联。Generate the concatenation of the results of sub-plans.

typedef struct MergeAppend { 
    	Plan		plan;
    	List	   *mergeplans;
    	/* these fields are just like the sort-key info in struct Sort: */
    	int			numCols;		/* number of sort-key columns */
    	AttrNumber *sortColIdx;		/* their indexes in the target list */
    	Oid		   *sortOperators;	/* OIDs of operators to sort them by */
    	Oid		   *collations;		/* OIDs of collations */
    	bool	   *nullsFirst;		/* NULLS FIRST/LAST directions */
    	/* Info for run-time subplan pruning; NULL if we're not doing that */
    	struct PartitionPruneInfo *part_prune_info;
    } MergeAppend;

合并预先排序的子计划的结果以保留排序。Merge the results of pre-sorted sub-plans to preserve the ordering.

--------------------

typedef struct RecursiveUnion

typedef struct BitmapAnd  
typedef struct BitmapOr

typedef struct Scan  
typedef Scan SeqScan  
typedef struct SampleScan  
typedef struct IndexScan  
typedef struct IndexOnlyScan  
typedef struct BitmapIndexScan  
typedef struct BitmapHeapScan  
typedef struct TidScan  
typedef struct SubqueryScan  
typedef struct FunctionScan  
typedef struct ValuesScan  
typedef struct TableFuncScan  
typedef struct CteScan  
typedef struct NamedTuplestoreScan  
typedef struct WorkTableScan  
typedef struct ForeignScan  
struct CustomScanMethods  
typedef struct CustomScan

typedef struct Join  
typedef struct NestLoop  
typedef struct NestLoopParam

typedef struct MergeJoin  
typedef struct HashJoin

--------------------

typedef struct Material{ 
    	Plan		plan;
    } Material;

--------------------

typedef struct Sort

typedef struct Group

typedef struct Agg  
typedef struct WindowAgg

typedef struct Unique

typedef struct Gather  
typedef struct GatherMerge

typedef struct Hash

typedef struct SetOp