PostgreSQL 原始碼解讀（39）- 查詢語句#24（query_planner函式#2）
上一小節介紹了函式query_planner對簡單語句(SELECT 2+2;)的處理邏輯,本節介紹了函式query_planner函式除此之外的其他主處理邏輯。
一、重要的資料結構
在query_planner中,對root(PlannerInfo)結構進行初始化和處理,為後續的計劃作準備.
PlannerInfo
/*----------
* PlannerInfo
* Per-query information for planning/optimization
*
* This struct is conventionally called "root" in all the planner routines.
* It holds links to all of the planner's working state, in addition to the
* original Query. Note that at present the planner extensively modifies
* the passed-in Query data structure; someday that should stop.
*----------
*/
struct AppendRelInfo;
typedef struct PlannerInfo
{
NodeTag type;//Node標識
Query *parse; /* 查詢樹,the Query being planned */
PlannerGlobal *glob; /* 當前的planner全域性資訊,global info for current planner run */
Index query_level; /* 查詢層次,1標識最高層,1 at the outermost Query */
struct PlannerInfo *parent_root; /* 如為子計劃,則這裡儲存父計劃器指標,NULL標識最高層,NULL at outermost Query */
/*
* plan_params contains the expressions that this query level needs to
* make available to a lower query level that is currently being planned.
* outer_params contains the paramIds of PARAM_EXEC Params that outer
* query levels will make available to this query level.
*/
List *plan_params; /* list of PlannerParamItems, see below */
Bitmapset *outer_params;
/*
* simple_rel_array holds pointers to "base rels" and "other rels" (see
* comments for RelOptInfo for more info). It is indexed by rangetable
* index (so entry 0 is always wasted). Entries can be NULL when an RTE
* does not correspond to a base relation, such as a join RTE or an
* unreferenced view RTE; or if the RelOptInfo hasn't been made yet.
*/
/* RelOptInfo陣列,儲存"base rels",比如基表/子查詢等.該陣列與RTE的順序一一對應,而且是從1開始,因此[0]無用 */
struct RelOptInfo **simple_rel_array; /* All 1-rel RelOptInfos */
int simple_rel_array_size; /* 陣列大小,allocated size of array */
/*
* simple_rte_array is the same length as simple_rel_array and holds
* pointers to the associated rangetable entries. This lets us avoid
* rt_fetch(), which can be a bit slow once large inheritance sets have
* been expanded.
*/
RangeTblEntry **simple_rte_array; /* RTE陣列,rangetable as an array */
/*
* append_rel_array is the same length as the above arrays, and holds
* pointers to the corresponding AppendRelInfo entry indexed by
* child_relid, or NULL if none. The array itself is not allocated if
* append_rel_list is empty.
*/
struct AppendRelInfo **append_rel_array;//先前已介紹,在處理集合操作如UNION ALL時使用
/*
* all_baserels is a Relids set of all base relids (but not "other"
* relids) in the query; that is, the Relids identifier of the final join
* we need to form. This is computed in make_one_rel, just before we
* start making Paths.
*/
Relids all_baserels;//"base rels"
/*
* nullable_baserels is a Relids set of base relids that are nullable by
* some outer join in the jointree; these are rels that are potentially
* nullable below the WHERE clause, SELECT targetlist, etc. This is
* computed in deconstruct_jointree.
*/
Relids nullable_baserels;//Nullable-side端的"base rels"
/*
* join_rel_list is a list of all join-relation RelOptInfos we have
* considered in this planning run. For small problems we just scan the
* list to do lookups, but when there are many join relations we build a
* hash table for faster lookups. The hash table is present and valid
* when join_rel_hash is not NULL. Note that we still maintain the list
* even when using the hash table for lookups; this simplifies life for
* GEQO.
*/
List *join_rel_list; /* 參與連線的Relation的RelOptInfo連結串列,list of join-relation RelOptInfos */
struct HTAB *join_rel_hash; /* 可加快連結串列訪問的hash表,optional hashtable for join relations */
/*
* When doing a dynamic-programming-style join search, join_rel_level[k]
* is a list of all join-relation RelOptInfos of level k, and
* join_cur_level is the current level. New join-relation RelOptInfos are
* automatically added to the join_rel_level[join_cur_level] list.
* join_rel_level is NULL if not in use.
*/
List **join_rel_level; /* RelOptInfo指標連結串列陣列,k層的join儲存在[k]中,lists of join-relation RelOptInfos */
int join_cur_level; /* 當前的join層次,index of list being extended */
List *init_plans; /* 查詢的初始化計劃連結串列,init SubPlans for query */
List *cte_plan_ids; /* CTE子計劃ID連結串列,per-CTE-item list of subplan IDs */
List *multiexpr_params; /* List of Lists of Params for MULTIEXPR
* subquery outputs */
List *eq_classes; /* 活動的等價類連結串列,list of active EquivalenceClasses */
List *canon_pathkeys; /* 規範化PathKey連結串列,list of "canonical" PathKeys */
List *left_join_clauses; /* 外連線約束條件連結串列(左),list of RestrictInfos for mergejoinable
* outer join clauses w/nonnullable var on
* left */
List *right_join_clauses; /* 外連線約束條件連結串列(右),list of RestrictInfos for mergejoinable
* outer join clauses w/nonnullable var on
* right */
List *full_join_clauses; /* 全連線約束條件連結串列,list of RestrictInfos for mergejoinable
* full join clauses */
List *join_info_list; /* 特殊連線資訊連結串列,list of SpecialJoinInfos */
List *append_rel_list; /* AppendRelInfo連結串列,list of AppendRelInfos */
List *rowMarks; /* list of PlanRowMarks */
List *placeholder_list; /* PHI連結串列,list of PlaceHolderInfos */
List *fkey_list; /* 外來鍵資訊連結串列,list of ForeignKeyOptInfos */
List *query_pathkeys; /* uery_planner()要求的PathKeys,desired pathkeys for query_planner() */
List *group_pathkeys; /* groupClause pathkeys, if any */
List *window_pathkeys; /* pathkeys of bottom window, if any */
List *distinct_pathkeys; /* distinctClause pathkeys, if any */
List *sort_pathkeys; /* sortClause pathkeys, if any */
List *part_schemes; /* 已規範化的分割槽Schema,Canonicalised partition schemes used in the
* query. */
List *initial_rels; /* 嘗試連線的RelOptInfo連結串列,RelOptInfos we are now trying to join */
/* Use fetch_upper_rel() to get any particular upper rel */
List *upper_rels[UPPERREL_FINAL + 1]; /* 上層的RelOptInfo連結串列, upper-rel RelOptInfos */
/* Result tlists chosen by grouping_planner for upper-stage processing */
struct PathTarget *upper_targets[UPPERREL_FINAL + 1];//
/*
* grouping_planner passes back its final processed targetlist here, for
* use in relabeling the topmost tlist of the finished Plan.
*/
List *processed_tlist;//最後需處理的投影列
/* Fields filled during create_plan() for use in setrefs.c */
AttrNumber *grouping_map; /* for GroupingFunc fixup */
List *minmax_aggs; /* List of MinMaxAggInfos */
MemoryContext planner_cxt; /* 記憶體上下文,context holding PlannerInfo */
double total_table_pages; /* 所有的pages,# of pages in all tables of query */
double tuple_fraction; /* query_planner輸入引數:元組處理比例,tuple_fraction passed to query_planner */
double limit_tuples; /* query_planner輸入引數:limit_tuples passed to query_planner */
Index qual_security_level; /* 表示式的最新安全等級,minimum security_level for quals */
/* Note: qual_security_level is zero if there are no securityQuals */
InheritanceKind inhTargetKind; /* indicates if the target relation is an
* inheritance child or partition or a
* partitioned table */
bool hasJoinRTEs; /* 存在RTE_JOIN的RTE,true if any RTEs are RTE_JOIN kind */
bool hasLateralRTEs; /* 存在標記為LATERAL的RTE,true if any RTEs are marked LATERAL */
bool hasDeletedRTEs; /* 存在已在jointree刪除的RTE,true if any RTE was deleted from jointree */
bool hasHavingQual; /* 存在Having子句,true if havingQual was non-null */
bool hasPseudoConstantQuals; /* true if any RestrictInfo has
* pseudoconstant = true */
bool hasRecursion; /* 遞迴語句,true if planning a recursive WITH item */
/* These fields are used only when hasRecursion is true: */
int wt_param_id; /* PARAM_EXEC ID for the work table */
struct Path *non_recursive_path; /* a path for non-recursive term */
/* These fields are workspace for createplan.c */
Relids curOuterRels; /* outer rels above current node */
List *curOuterParams; /* not-yet-assigned NestLoopParams */
/* optional private data for join_search_hook, e.g., GEQO */
void *join_search_private;
/* Does this query modify any partition key columns? */
bool partColsUpdated;
} PlannerInfo;
二、原始碼解讀
本節介紹query_planner的主流程以及setup_simple_rel_arrays和setup_append_rel_array兩個子函式的實現邏輯.
query_planner
/*
* 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.
*
* query_planner不會處理頂層的處理過程(如最後的分組/排序等操作),因此,不能選擇最優的訪問路徑
* 該函式會返回RelOptInfo給最高層的連線,grouping_planner可以在剩下的路徑中進行選擇
*
* 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
*
* root是計劃資訊/tlist是投影列
* qp_callback是計算query_pathkeys的函式/qp_extra是傳遞給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;//RTE的index
double total_pages;//總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)//簡單SQL,無FROM/WHERE語句
{
/* 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;//初始化PlannerInfo
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);//初始化PlannerInfo->simple_rel/rte_array&size
/*
* Populate append_rel_array with each AppendRelInfo to allow direct
* lookups by child relid.
*/
setup_append_rel_array(root);//初始化PlannerInfo->append_rel_array(透過append_rel_list)
/*
* 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);//構建RelOptInfo節點
/*
* 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);//構建"base rels"的投影列
find_placeholders_in_jointree(root);//處理jointree中的PHI
find_lateral_references(root);//處理jointree中Lateral依賴
joinlist = deconstruct_jointree(root);//重構jointree
/*
* 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);//檢查在子查詢上拉時生成的PH表示式,確保Vars是OK的
/*
* 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);//在"base rels"中新增PH
/*
* Construct the lateral reference sets now that we have finalized
* PlaceHolderVar eval levels.
*/
create_lateral_join_info(root);//建立Lateral連線資訊
/*
* 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);//在OR語句中抽取約束條件
/*
* 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++)//計算總pages
{
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;//返回結果
}
setup_simple_rel_arrays
初始化setup_simple_rel_arrays(注意:[0]無用)和setup_simple_rel_arrays
/*
* setup_simple_rel_arrays
* Prepare the arrays we use for quickly accessing base relations.
*/
void
setup_simple_rel_arrays(PlannerInfo *root)
{
Index rti;
ListCell *lc;
/* Arrays are accessed using RT indexes (1..N) */
root->simple_rel_array_size = list_length(root->parse->rtable) + 1;
/* simple_rel_array is initialized to all NULLs */
root->simple_rel_array = (RelOptInfo **)
palloc0(root->simple_rel_array_size * sizeof(RelOptInfo *));
/* simple_rte_array is an array equivalent of the rtable list */
root->simple_rte_array = (RangeTblEntry **)
palloc0(root->simple_rel_array_size * sizeof(RangeTblEntry *));
rti = 1;
foreach(lc, root->parse->rtable)
{
RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc);
root->simple_rte_array[rti++] = rte;
}
}
setup_append_rel_array
原始碼比較簡單,讀取append_rel_list中的資訊初始化append_rel_array
/*
* setup_append_rel_array
* Populate the append_rel_array to allow direct lookups of
* AppendRelInfos by child relid.
*
* The array remains unallocated if there are no AppendRelInfos.
*/
void
setup_append_rel_array(PlannerInfo *root)
{
ListCell *lc;
int size = list_length(root->parse->rtable) + 1;
if (root->append_rel_list == NIL)
{
root->append_rel_array = NULL;
return;
}
root->append_rel_array = (AppendRelInfo **)
palloc0(size * sizeof(AppendRelInfo *));
foreach(lc, root->append_rel_list)
{
AppendRelInfo *appinfo = lfirst_node(AppendRelInfo, lc);
int child_relid = appinfo->child_relid;
/* Sanity check */
Assert(child_relid < size);
if (root->append_rel_array[child_relid])
elog(ERROR, "child relation already exists");
root->append_rel_array[child_relid] = appinfo;
}
}
三、參考資料
planmain.c
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6906/viewspace-2374872/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PostgreSQL 原始碼解讀（46）- 查詢語句#31（query_planner函式#7）SQL原始碼函式
- PostgreSQL 原始碼解讀（47）- 查詢語句#32（query_planner函式#8）SQL原始碼函式
- PostgreSQL 原始碼解讀（48）- 查詢語句#33（query_planner函式#9）SQL原始碼函式
- PostgreSQL 原始碼解讀（41）- 查詢語句#26（query_planner函式#4）SQL原始碼函式
- PostgreSQL 原始碼解讀（40）- 查詢語句#25（query_planner函式#3）SQL原始碼函式
- PostgreSQL 原始碼解讀（43）- 查詢語句#28（query_planner函式#5）SQL原始碼函式
- PostgreSQL 原始碼解讀（45）- 查詢語句#30（query_planner函式#6）SQL原始碼函式
- PostgreSQL 原始碼解讀（38）- 查詢語句#23（query_planner函式#1）SQL原始碼函式
- PostgreSQL 原始碼解讀（91）- 查詢語句#76（ExecHashJoin函式#2）SQL原始碼函式
- PostgreSQL 原始碼解讀（24）- 查詢語句#9（查詢重寫）SQL原始碼
- PostgreSQL 原始碼解讀（83）- 查詢語句#68（PortalStart函式）SQL原始碼函式
- PostgreSQL 原始碼解讀（54）- 查詢語句#39（make_one_rel函式#4-生...SQL原始碼函式
- PostgreSQL 原始碼解讀（89）- 查詢語句#74（SeqNext函式#2）SQL原始碼函式
- PostgreSQL 原始碼解讀（90）- 查詢語句#75（ExecHashJoin函式#1）SQL原始碼函式
- PostgreSQL 原始碼解讀（93）- 查詢語句#77（ExecHashJoin函式#3）SQL原始碼函式
- PostgreSQL 原始碼解讀（78）- 查詢語句#63（create_plan函式#2-cr...SQL原始碼函式
- PostgreSQL 原始碼解讀（95）- 查詢語句#78（ExecHashJoin函式#4-H...SQL原始碼函式
- PostgreSQL 原始碼解讀（97）- 查詢語句#79（ExecHashJoin函式#5-H...SQL原始碼函式
- PostgreSQL 原始碼解讀（88）- 查詢語句#73（SeqNext函式#1）SQL原始碼函式
- PostgreSQL 原始碼解讀（73）- 查詢語句#58（grouping_planner函式...SQL原始碼函式
- PostgreSQL 原始碼解讀（52）- 查詢語句#37（make_one_rel函式#2-估...SQL原始碼函式
- PostgreSQL 原始碼解讀（17）- 查詢語句#2（查詢優化基礎）SQL原始碼優化
- PostgreSQL 原始碼解讀（82）- 查詢語句#67（PortalXXX系列函式）SQL原始碼函式
- PostgreSQL 原始碼解讀（32）- 查詢語句#17（查詢優化-表示式預處理#2）SQL原始碼優化
- PostgreSQL 原始碼解讀（51）- 查詢語句#36（Optimizer Review#2）SQL原始碼View
- PostgreSQL 原始碼解讀（79）- 查詢語句#64（create_plan函式#3-Se...SQL原始碼函式
- PostgreSQL 原始碼解讀（80）- 查詢語句#65（create_plan函式#4-Jo...SQL原始碼函式
- PostgreSQL 原始碼解讀（65）- 查詢語句#50（make_one_rel函式#15-...SQL原始碼函式
- PostgreSQL 原始碼解讀（62）- 查詢語句#47（make_one_rel函式#12-...SQL原始碼函式
- PostgreSQL 原始碼解讀（63）- 查詢語句#48（make_one_rel函式#13-...SQL原始碼函式
- PostgreSQL 原始碼解讀（64）- 查詢語句#49（make_one_rel函式#14-...SQL原始碼函式
- PostgreSQL 原始碼解讀（60）- 查詢語句#45（make_one_rel函式#10-...SQL原始碼函式
- PostgreSQL 原始碼解讀（61）- 查詢語句#46（make_one_rel函式#11-...SQL原始碼函式
- PostgreSQL 原始碼解讀（69）- 查詢語句#54（make_one_rel函式#19-...SQL原始碼函式
- PostgreSQL 原始碼解讀（70）- 查詢語句#55（make_one_rel函式#20-...SQL原始碼函式
- PostgreSQL 原始碼解讀（66）- 查詢語句#51（make_one_rel函式#16-...SQL原始碼函式
- PostgreSQL 原始碼解讀（67）- 查詢語句#52（make_one_rel函式#17-...SQL原始碼函式
- PostgreSQL 原始碼解讀（68）- 查詢語句#53（make_one_rel函式#18-...SQL原始碼函式