【PostgreSQL内核学习（七）—— 查询规划（生成路径）】

声明：本文的部分内容参考了他人的文章。在编写过程中，我们尊重他人的知识产权和学术成果，力求遵循合理使用原则，并在适用的情况下注明引用来源。
本文主要参考了《PostgresSQL数据库内核分析》一书

生成路径

  对于SQL中的计划命令的处理，无非就是获取一个（或者一系列)元组，然后将这个元组返回给用户或者以其为基础进行插入、更新、删除操作。因此对于一个执行计划来说，最重要的部分就是告诉查询执行模块如何取到要操作的元组。执行计划要操作的元组可以来自于一个基本表或者由一系列基本表连接而成的“连接表”，当然一个基本表也可以看成是由它自身构成的连接表。这些基本表连接成连接表的关系可以从逻辑上表示成一个二叉树结构（连接树)，由于表之间不同的连接方式和顺序，同一组基本表形成连接表的连接树会有多个，每一棵连接树在PostgreSQL中都称为一条路径。因此，路径在查询的规划和执行过程中表示了从一组基本表生成最终连接表的方式。而查询规划的工作就是从一系列等效的路径中选取效率最高的路径，并形成执行计划。
  生成路径的工作是由函数query_planner来完成的，query_planner 函数是 PostgreSQL 中生成简化查询计划的核心函数。该函数通过优化查询的 join 操作，但不涉及更高级的特性（例如分组、排序等），来为基本查询生成路径（simplified plan）。此函数的返回值是查询的顶层 join 连接（join relation）的 RelOptInfo。
  该函数的执行过程如下：

1. 首先，判断查询的 join 树是否为空。如果 join 树为空，则表明查询非常简单，例如 “SELECT 2+2;” 或 “INSERT … VALUES();”，则直接构建一个空的 join 关系（build_empty_join_rel）表示没有任何基本关系（baserel），然后创建一个 Result 路径，生成查询计划。这里还会检查是否允许并行执行，如果允许则检查查询的限制条件是否适合并行执行。
2. 初始化 PlannerInfo 结构体中的相关列表和数组。
3. 为查询中的所有基本关系（base relation）构建 RelOptInfo 结构体，并为所有附加关系（appendrel）的成员关系（other rels）间接构建 RelOptInfo 结构体。这样，对于查询涉及的所有“简单”（非 join）关系，我们都有了 RelOptInfo。
4. 构建基本关系的目标列表（target list）和 join 树中的限制和连接条件，并为占位符变量（PlaceHolderVar）生成 PlaceHolderInfo 条目。同时，构建表达式的等价类（Equivalence Classes）以描述可以等价变换的表达式。还构建了 SpecialJoinInfo 列表，用于存储关于连接顺序的限制信息。最后，构建了一个目标 joinlist，用于进一步生成计划。
5. 对于查询中所有占位符变量，确保所需的 Vars 被标记为相关联的连接层级（needed join level）。
6. 去除无用的外连接，因为可能会导致不必要的计算。
7. 将含有唯一内部关系的半连接转换为普通内连接，减少计算复杂度。
8. 将“占位符”表达式（placeholders）分发到需要的基本关系中。
9. 构建横向引用（lateral reference）集合，用于处理 lateral join。
10. 匹配外键（foreign keys）与等价类和连接条件。在构建完等价类之后进行，以便忽略涉及已移除关系的外键。
11. 提取 join OR 条件中的单关系限制 OR 条件。
12. 为所有实际表计算了大小估计，并计算 total_table_pages。
13. 最终开始执行主要的查询规划工作，通过 make_one_rel 函数生成 join 关系的 RelOptInfo。
14. 检查是否至少得到一个可用的路径，并返回查询顶层 join 关系的 RelOptInfo。

  该函数的处理流程如图5-26所示。

  query_planner函数的源码如下：（路径：src/backend/optimizer/plan/planmain.c）

该函数的参数说明如下：

• root：PlannerInfo 结构体，用于描述要优化的查询。
• tlist：目标列表，即查询要输出的列。
• qp_callback：查询路径键回调函数，用于计算查询的路径键（查询结果的排序顺序）。
• qp_extra：可选的额外数据，传递给查询路径键回调函数。

/*
 * query_planner
 *	  Generate a path (that is, a simplified plan) for a basic query,
 *	  which may involve joins but not any fancier features.
 *
 * Since query_planner does not handle the toplevel processing (grouping,
 * sorting, etc) it cannot select the best path by itself.  Instead, it
 * returns the RelOptInfo for the top level of joining, and the caller
 * (grouping_planner) can choose among the surviving paths for the rel.
 *
 * root describes the query to plan
 * tlist is the target list the query should produce
 *		(this is NOT necessarily root->parse->targetList!)
 * qp_callback is a function to compute query_pathkeys once it's safe to do so
 * qp_extra is optional extra data to pass to qp_callback
 *
 * Note: the PlannerInfo node also includes a query_pathkeys field, which
 * tells query_planner the sort order that is desired in the final output
 * plan.  This value is *not* available at call time, but is computed by
 * qp_callback once we have completed merging the query's equivalence classes.
 * (We cannot construct canonical pathkeys until that's done.)
 */
RelOptInfo *
query_planner(PlannerInfo *root, List *tlist,
			  query_pathkeys_callback qp_callback, void *qp_extra)
{
	Query	   *parse = root->parse;
	List	   *joinlist;
	RelOptInfo *final_rel;
	Index		rti;
	double		total_pages;

	/*
	 * If the query has an empty join tree, then it's something easy like
	 * "SELECT 2+2;" or "INSERT ... VALUES()".  Fall through quickly.
	 */
	if (parse->jointree->fromlist == NIL)
	{
		/* We need a dummy joinrel to describe the empty set of baserels */
		final_rel = build_empty_join_rel(root);

		/*
		 * If query allows parallelism in general, check whether the quals are
		 * parallel-restricted.  (We need not check final_rel->reltarget
		 * because it's empty at this point.  Anything parallel-restricted in
		 * the query tlist will be dealt with later.)
		 */
		if (root->glob->parallelModeOK)
			final_rel->consider_parallel =
				is_parallel_safe(root, parse->jointree->quals);

		/* The only path for it is a trivial Result path */
		add_path(final_rel, (Path *)
				 create_result_path(root, final_rel,
									final_rel->reltarget,
									(List *) parse->jointree->quals));

		/* Select cheapest path (pretty easy in this case...) */
		set_cheapest(final_rel);

		/*
		 * We still are required to call qp_callback, in case it's something
		 * like "SELECT 2+2 ORDER BY 1".
		 */
		root->canon_pathkeys = NIL;
		(*qp_callback) (root, qp_extra);

		return final_rel;
	}

	/*
	 * Init planner lists to empty.
	 *
	 * NOTE: append_rel_list was set up by subquery_planner, so do not touch
	 * here.
	 */
	root->join_rel_list = NIL;
	root->join_rel_hash = NULL;
	root->join_rel_level = NULL;
	root->join_cur_level = 0;
	root->canon_pathkeys = NIL;
	root->left_join_clauses = NIL;
	root->right_join_clauses = NIL;
	root->full_join_clauses = NIL;
	root->join_info_list = NIL;
	root->placeholder_list = NIL;
	root->fkey_list = NIL;
	root->initial_rels = NIL;

	/*
	 * Make a flattened version of the rangetable for faster access (this is
	 * OK because the rangetable won't change any more), and set up an empty
	 * array for indexing base relations.
	 */
	setup_simple_rel_arrays(root);

	/*
	 * Construct RelOptInfo nodes for all base relations in query, and
	 * indirectly for all appendrel member relations ("other rels").  This
	 * will give us a RelOptInfo for every "simple" (non-join) rel involved in
	 * the query.
	 *
	 * Note: the reason we find the rels by searching the jointree and
	 * appendrel list, rather than just scanning the rangetable, is that the
	 * rangetable may contain RTEs for rels not actively part of the query,
	 * for example views.  We don't want to make RelOptInfos for them.
	 */
	add_base_rels_to_query(root, (Node *) parse->jointree);

	/*
	 * Examine the targetlist and join tree, adding entries to baserel
	 * targetlists for all referenced Vars, and generating PlaceHolderInfo
	 * entries for all referenced PlaceHolderVars.  Restrict and join clauses
	 * are added to appropriate lists belonging to the mentioned relations. We
	 * also build EquivalenceClasses for provably equivalent expressions. The
	 * SpecialJoinInfo list is also built to hold information about join order
	 * restrictions.  Finally, we form a target joinlist for make_one_rel() to
	 * work from.
	 */
	build_base_rel_tlists(root, tlist);

	find_placeholders_in_jointree(root);

	find_lateral_references(root);

	joinlist = deconstruct_jointree(root);

	/*
	 * Reconsider any postponed outer-join quals now that we have built up
	 * equivalence classes.  (This could result in further additions or
	 * mergings of classes.)
	 */
	reconsider_outer_join_clauses(root);

	/*
	 * If we formed any equivalence classes, generate additional restriction
	 * clauses as appropriate.  (Implied join clauses are formed on-the-fly
	 * later.)
	 */
	generate_base_implied_equalities(root);

	/*
	 * We have completed merging equivalence sets, so it's now possible to
	 * generate pathkeys in canonical form; so compute query_pathkeys and
	 * other pathkeys fields in PlannerInfo.
	 */
	(*qp_callback) (root, qp_extra);

	/*
	 * Examine any "placeholder" expressions generated during subquery pullup.
	 * Make sure that the Vars they need are marked as needed at the relevant
	 * join level.  This must be done before join removal because it might
	 * cause Vars or placeholders to be needed above a join when they weren't
	 * so marked before.
	 */
	fix_placeholder_input_needed_levels(root);

	/*
	 * Remove any useless outer joins.  Ideally this would be done during
	 * jointree preprocessing, but the necessary information isn't available
	 * until we've built baserel data structures and classified qual clauses.
	 */
	joinlist = remove_useless_joins(root, joinlist);

	/*
	 * Also, reduce any semijoins with unique inner rels to plain inner joins.
	 * Likewise, this can't be done until now for lack of needed info.
	 */
	reduce_unique_semijoins(root);

	/*
	 * Now distribute "placeholders" to base rels as needed.  This has to be
	 * done after join removal because removal could change whether a
	 * placeholder is evaluable at a base rel.
	 */
	add_placeholders_to_base_rels(root);

	/*
	 * Construct the lateral reference sets now that we have finalized
	 * PlaceHolderVar eval levels.
	 */
	create_lateral_join_info(root);

	/*
	 * Match foreign keys to equivalence classes and join quals.  This must be
	 * done after finalizing equivalence classes, and it's useful to wait till
	 * after join removal so that we can skip processing foreign keys
	 * involving removed relations.
	 */
	match_foreign_keys_to_quals(root);

	/*
	 * Look for join OR clauses that we can extract single-relation
	 * restriction OR clauses from.
	 */
	extract_restriction_or_clauses(root);

	/*
	 * We should now have size estimates for every actual table involved in
	 * the query, and we also know which if any have been deleted from the
	 * query by join removal; so we can compute total_table_pages.
	 *
	 * Note that appendrels are not double-counted here, even though we don't
	 * bother to distinguish RelOptInfos for appendrel parents, because the
	 * parents will still have size zero.
	 *
	 * XXX if a table is self-joined, we will count it once per appearance,
	 * which perhaps is the wrong thing ... but that's not completely clear,
	 * and detecting self-joins here is difficult, so ignore it for now.
	 */
	total_pages = 0;
	for (rti = 1; rti < root->simple_rel_array_size; rti++)
	{
		RelOptInfo *brel = root->simple_rel_array[rti];

		if (brel == NULL)
			continue;

		Assert(brel->relid == rti); /* sanity check on array */

		if (IS_SIMPLE_REL(brel))
			total_pages += (double) brel->pages;
	}
	root->total_table_pages = total_pages;

	/*
	 * Ready to do the primary planning.
	 */
	final_rel = make_one_rel(root, joinlist);

	/* Check that we got at least one usable path */
	if (!final_rel || !final_rel->cheapest_total_path ||
		final_rel->cheapest_total_path->param_info != NULL)
		elog(ERROR, "failed to construct the join relation");

	return final_rel;
}

  由源代码可以看到，生成的路径是 RelOptInfo 结构体，其中包含了计划中要执行的操作和访问基本关系的路径信息。
  RelOptInfo结构（数据结构5.15）是贯穿整个路径生成过程的一个数据结构，生成路径的最终结果始终存放在其中，生成和选择路径所需的许多数据也存放在其中。路径生成和选择涉及的所有操作几乎都是针对这个结构进行的，因此搞清楚这个结构对于理解整个路径生成过程非常重要。
  先来看看书中所描述的 RelOptInfo 结构吧。


  RelOptInfo结构源码如下：（路径：src/include/nodes/relation.h）

typedef struct RelOptInfo
{
	NodeTag		type;

	RelOptKind	reloptkind;

	/* all relations included in this RelOptInfo */
	Relids		relids;			/* set of base relids (rangetable indexes) */

	/* size estimates generated by planner */
	double		rows;			/* estimated number of result tuples */

	/* per-relation planner control flags */
	bool		consider_startup;	/* keep cheap-startup-cost paths? */
	bool		consider_param_startup; /* ditto, for parameterized paths? */
	bool		consider_parallel;	/* consider parallel paths? */

	/* default result targetlist for Paths scanning this relation */
	struct PathTarget *reltarget;	/* list of Vars/Exprs, cost, width */

	/* materialization information */
	List	   *pathlist;		/* Path structures */
	List	   *ppilist;		/* ParamPathInfos used in pathlist */
	List	   *partial_pathlist;	/* partial Paths */
	struct Path *cheapest_startup_path;
	struct Path *cheapest_total_path;
	struct Path *cheapest_unique_path;
	List	   *cheapest_parameterized_paths;

	/* parameterization information needed for both base rels and join rels */
	/* (see also lateral_vars and lateral_referencers) */
	Relids		direct_lateral_relids;	/* rels directly laterally referenced */
	Relids		lateral_relids; /* minimum parameterization of rel */

	/* information about a base rel (not set for join rels!) */
	Index		relid;
	Oid			reltablespace;	/* containing tablespace */
	RTEKind		rtekind;		/* RELATION, SUBQUERY, or FUNCTION */
	AttrNumber	min_attr;		/* smallest attrno of rel (often <0) */
	AttrNumber	max_attr;		/* largest attrno of rel */
	Relids	   *attr_needed;	/* array indexed [min_attr .. max_attr] */
	int32	   *attr_widths;	/* array indexed [min_attr .. max_attr] */
	List	   *lateral_vars;	/* LATERAL Vars and PHVs referenced by rel */
	Relids		lateral_referencers;	/* rels that reference me laterally */
	List	   *indexlist;		/* list of IndexOptInfo */
	List	   *statlist;		/* list of StatisticExtInfo */
	BlockNumber pages;			/* size estimates derived from pg_class */
	double		tuples;
	double		allvisfrac;
	PlannerInfo *subroot;		/* if subquery */
	List	   *subplan_params; /* if subquery */
	int			rel_parallel_workers;	/* wanted number of parallel workers */

	/* Information about foreign tables and foreign joins */
	Oid			serverid;		/* identifies server for the table or join */
	Oid			userid;			/* identifies user to check access as */
	bool		useridiscurrent;	/* join is only valid for current user */
	/* use "struct FdwRoutine" to avoid including fdwapi.h here */
	struct FdwRoutine *fdwroutine;
	void	   *fdw_private;

	/* cache space for remembering if we have proven this relation unique */
	List	   *unique_for_rels;	/* known unique for these other relid
									 * set(s) */
	List	   *non_unique_for_rels;	/* known not unique for these set(s) */

	/* used by various scans and joins: */
	List	   *baserestrictinfo;	/* RestrictInfo structures (if base rel) */
	QualCost	baserestrictcost;	/* cost of evaluating the above */
	Index		baserestrict_min_security;	/* min security_level found in
											 * baserestrictinfo */
	List	   *joininfo;		/* RestrictInfo structures for join clauses
								 * involving this rel */
	bool		has_eclass_joins;	/* T means joininfo is incomplete */

	/* used by "other" relations */
	Relids		top_parent_relids;	/* Relids of topmost parents */
} RelOptInfo;

  RelOptInfo 结构体的存在意义是为了存储一个关系的优化信息，这样在查询优化过程中，可以方便地查找和管理每个关系的相关信息。RelOptInfo 结构体中的字段可以用于存储基本关系的属性、约束条件、可选的执行路径等信息，以便优化器可以根据这些信息选择最优的执行计划。同时，它还能记录关系之间的连接关系和关联条件，帮助优化器进行联接路径的选择和优化。
  在 RelOptInfo 中，pathlist 记录了生成该 RelOptInfo 在某方面较优的路径。每个节点都是一个 Path 结构的指针，用于描述扫描表的不同方法（例如顺序扫描、索引扫描）以及元组排序的不同结果。Path 结构是路径的一个超类，可以根据 pathtype 字段表示的路径类型转换成具体的路径节点，如 T_IndexScan 对应 IndexPath 数据结构。pathlist 中的每个 Path 节点对应的具体路径节点中存放了构成该路径的具体信息。
  Path 结构体：表示一个查询中的一个执行路径。执行路径是优化器生成的可执行计划的一部分，它描述了如何从关系中获取数据的具体方式，如扫描、聚合、排序等。每个 RelOptInfo 结构体都包含了多个 Path 结构体，这些 Path 结构体代表了不同的执行路径选择，优化器会通过比较这些路径的代价来选择最优的执行计划。
  还是先看看书中对Path结构体的描述吧

  Path结构体源码如下：（路径：src/include/nodes/relation.h）

/*
 * Type "Path" is used as-is for sequential-scan paths, as well as some other
 * simple plan types that we don't need any extra information in the path for.
 * For other path types it is the first component of a larger struct.
 *
 * "pathtype" is the NodeTag of the Plan node we could build from this Path.
 * It is partially redundant with the Path's NodeTag, but allows us to use
 * the same Path type for multiple Plan types when there is no need to
 * distinguish the Plan type during path processing.
 *
 * "parent" identifies the relation this Path scans, and "pathtarget"
 * describes the precise set of output columns the Path would compute.
 * In simple cases all Paths for a given rel share the same targetlist,
 * which we represent by having path->pathtarget equal to parent->reltarget.
 *
 * "param_info", if not NULL, links to a ParamPathInfo that identifies outer
 * relation(s) that provide parameter values to each scan of this path.
 * That means this path can only be joined to those rels by means of nestloop
 * joins with this path on the inside.  Also note that a parameterized path
 * is responsible for testing all "movable" joinclauses involving this rel
 * and the specified outer rel(s).
 *
 * "rows" is the same as parent->rows in simple paths, but in parameterized
 * paths and UniquePaths it can be less than parent->rows, reflecting the
 * fact that we've filtered by extra join conditions or removed duplicates.
 *
 * "pathkeys" is a List of PathKey nodes (see above), describing the sort
 * ordering of the path's output rows.
 */
typedef struct Path
{
	NodeTag		type;

	NodeTag		pathtype;		/* tag identifying scan/join method */

	RelOptInfo *parent;			/* the relation this path can build */
	PathTarget *pathtarget;		/* list of Vars/Exprs, cost, width */

	ParamPathInfo *param_info;	/* parameterization info, or NULL if none */

	bool		parallel_aware; /* engage parallel-aware logic? */
	bool		parallel_safe;	/* OK to use as part of parallel plan? */
	int			parallel_workers;	/* desired # of workers; 0 = not parallel */

	/* estimated size/costs for path (see costsize.c for more info) */
	double		rows;			/* estimated number of result tuples */
	Cost		startup_cost;	/* cost expended before fetching any tuples */
	Cost		total_cost;		/* total cost (assuming all tuples fetched) */

	List	   *pathkeys;		/* sort ordering of path's output */
	/* pathkeys is a List of PathKey nodes; see above */
} Path;

  路径是用一个树结构表示的。叶子节点是对基本关系的扫描路径，内部节点是连接路径的节点。例如，图5-27中的树结构是图中SQL语句生成的路径之一。

  在 Path 生成过程中，在某一方面代价较优的路径（启动代价、总代价等)都存放在RelOptInfo结构的pathlist链表中，如图5-28所示。

  通过gdb调试可以查看Path结构体内容：

我们来解读以下这段结构体信息：
  这段代码描述了一个路径（Path）的结构信息，是查询规划器（Planner）生成的一个节点，用于表示查询执行过程中的某种执行方案。下面逐个字段解释其含义：

• type = T_Path: 表示该节点的类型是 Path，即路径节点。
• pathtype = T_SeqScan: 表示该路径节点的类型是 T_SeqScan，即顺序扫描的路径，这意味着在执行该路径时，系统将按照表中的物理存储顺序逐行扫描。
• parent = 0x55ebb49db058: 表示该路径节点所属的父节点的指针，即该路径节点是哪个上级节点的一个子节点。在这里，parent 的值是一个内存地址（指针），指向上级节点的位置。
• pathtarget = 0x55ebb49dbab0: 表示该路径节点要生成的目标（TargetEntry）的指针。pathtarget 指向一个目标条目，描述了查询执行后返回的结果的信息，如表达式、别名等。
• param_info = 0x0: 表示该路径节点的参数信息，如果该路径节点没有需要传递的参数，则为 NULL。
• parallel_aware = 0 ‘\000’: 表示该路径是否支持并行执行。parallel_aware 为 0 表示不支持，并行执行。
• parallel_safe = 1 ‘\001’: 表示该路径是否是并行安全的。parallel_safe 为 1 表示是，并行安全的路径可以被并行执行。
• parallel_workers = 0: 表示在执行该路径时使用的并行工作者数量。
• rows = 6: 表示该路径执行后返回的估计行数。
• startup_cost = 0: 表示该路径的启动代价，即开始执行该路径所需要的额外开销。
• total_cost = 25.875: 表示该路径的总代价，即执行该路径所需要的总开销。
• pathkeys = 0x0: 表示该路径执行后返回的结果的排序方式，如果为 NULL，表示结果没有特定的排序要求。

  其中调试信息如下：