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

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

计划树由许多称为计划节点的元素组成，这些节点挂在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 BitmapOrtypedef 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 CustomScantypedef struct Join
 typedef struct NestLoop
 typedef struct NestLoopParamtypedef struct MergeJoin
 typedef struct HashJoin

---

typedef struct Material{
  Plan    plan;
} Material;

---

typedef struct Sort
typedef struct Group
typedef struct Agg
 typedef struct WindowAggtypedef struct Unique
typedef struct Gather
 typedef struct GatherMergetypedef struct Hash
typedef struct SetOp
typedef struct LockRows
typedef struct Limit