PostgreSQL 原始碼解讀（64）- 查詢語句#49（make_one_rel函式#14-...
本節大體介紹了動態規劃演算法實現(standard_join_search)中的join_search_one_level->make_join_rel函式，該函式建立兩個rels連線所生成的RelOptInfo,並在RelOptInfo中新增訪問路徑資訊。
一、資料結構
RelOptInfo
typedef enum RelOptKind
{
RELOPT_BASEREL,//基本關係(如基表/子查詢等)
RELOPT_JOINREL,//連線產生的關係,要注意的是透過連線等方式產生的結果亦可以視為關係
RELOPT_OTHER_MEMBER_REL,
RELOPT_OTHER_JOINREL,
RELOPT_UPPER_REL,//上層的關係
RELOPT_OTHER_UPPER_REL,
RELOPT_DEADREL
} RelOptKind;
/*
* Is the given relation a simple relation i.e a base or "other" member
* relation?
*/
#define IS_SIMPLE_REL(rel) \
((rel)->reloptkind == RELOPT_BASEREL || \
(rel)->reloptkind == RELOPT_OTHER_MEMBER_REL)
/* Is the given relation a join relation? */
#define IS_JOIN_REL(rel) \
((rel)->reloptkind == RELOPT_JOINREL || \
(rel)->reloptkind == RELOPT_OTHER_JOINREL)
/* Is the given relation an upper relation? */
#define IS_UPPER_REL(rel) \
((rel)->reloptkind == RELOPT_UPPER_REL || \
(rel)->reloptkind == RELOPT_OTHER_UPPER_REL)
/* Is the given relation an "other" relation? */
#define IS_OTHER_REL(rel) \
((rel)->reloptkind == RELOPT_OTHER_MEMBER_REL || \
(rel)->reloptkind == RELOPT_OTHER_JOINREL || \
(rel)->reloptkind == RELOPT_OTHER_UPPER_REL)
typedef struct RelOptInfo
{
NodeTag type;//節點標識
RelOptKind reloptkind;//RelOpt型別
/* all relations included in this RelOptInfo */
Relids relids; /*Relids(rtindex)集合 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; /*掃描該Relation時預設的結果 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; /*使用lateral語法,需依賴的Relids rels directly laterally referenced */
Relids lateral_relids; /* minimum parameterization of rel */
/* information about a base rel (not set for join rels!) */
//reloptkind=RELOPT_BASEREL時使用的資料結構
Index relid; /* Relation ID */
Oid reltablespace; /* 表空間 containing tablespace */
RTEKind rtekind; /* 基表?子查詢?還是函式等等?RELATION, SUBQUERY, FUNCTION, etc */
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; /* 關係依賴的Vars/PHVs LATERAL Vars and PHVs referenced by rel */
Relids lateral_referencers; /*依賴該關係的Relids rels that reference me laterally */
List *indexlist; /* 該關係的IndexOptInfo連結串列 list of IndexOptInfo */
List *statlist; /* 統計資訊連結串列 list of StatisticExtInfo */
BlockNumber pages; /* 塊數 size estimates derived from pg_class */
double tuples; /* 元組數 */
double allvisfrac; /* ? */
PlannerInfo *subroot; /* 如為子查詢,儲存子查詢的root if subquery */
List *subplan_params; /* 如為子查詢,儲存子查詢的引數 if subquery */
int rel_parallel_workers; /* 並行執行,需要多少個workers? wanted number of parallel workers */
/* Information about foreign tables and foreign joins */
//FWD相關資訊
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 */
//已知的,可保證唯一的Relids連結串列
List *unique_for_rels; /* known unique for these other relid
* set(s) */
List *non_unique_for_rels; /* 已知的,不唯一的Relids連結串列 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 partitionwise joins: */
bool consider_partitionwise_join; /* 分割槽? consider partitionwise
* join paths? (if
* partitioned rel) */
Relids top_parent_relids; /* Relids of topmost parents (if "other"
* rel) */
/* used for partitioned relations */
//分割槽表使用
PartitionScheme part_scheme; /* 分割槽的schema Partitioning scheme. */
int nparts; /* 分割槽數 number of partitions */
struct PartitionBoundInfoData *boundinfo; /* 分割槽邊界資訊 Partition bounds */
List *partition_qual; /* 分割槽約束 partition constraint */
struct RelOptInfo **part_rels; /* 分割槽的RelOptInfo陣列 Array of RelOptInfos of partitions,
* stored in the same order of bounds */
List **partexprs; /* 非空分割槽鍵表示式 Non-nullable partition key expressions. */
List **nullable_partexprs; /* 可為空的分割槽鍵表示式 Nullable partition key expressions. */
List *partitioned_child_rels; /* RT Indexes連結串列 List of RT indexes. */
} RelOptInfo;
二、原始碼解讀
join_search_one_level->...(如make_rels_by_clause_joins)->make_join_rel函式建立兩個rels連線所生成的RelOptInfo,並建立訪問路徑新增到RelOptInfo的pathlist連結串列中。這裡重點介紹make_join_rel函式中的build_join_rel函式,populate_joinrel_with_paths函式下一小節再行介紹.
//---------------------------------------------------- make_join_rel
/*
* make_join_rel
* Find or create a join RelOptInfo that represents the join of
* the two given rels, and add to it path information for paths
* created with the two rels as outer and inner rel.
* (The join rel may already contain paths generated from other
* pairs of rels that add up to the same set of base rels.)
* 建立兩個rels連線所生成的RelOptInfo,並新增訪問路徑資訊.
* (新產生的rel可能已經包含從相同的兩個rels對所生成的的路徑)
*
* NB: will return NULL if attempted join is not valid. This can happen
* when working with outer joins, or with IN or EXISTS clauses that have been
* turned into joins.
* 注意:如果嘗試連線失敗,則返回NULL.這可能出現在處理外連線或者已轉變為連線的IN/EXISTS子句上
*/
RelOptInfo *
make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
{
Relids joinrelids;
SpecialJoinInfo *sjinfo;
bool reversed;
SpecialJoinInfo sjinfo_data;
RelOptInfo *joinrel;
List *restrictlist;
/* We should never try to join two overlapping sets of rels. */
Assert(!bms_overlap(rel1->relids, rel2->relids));//兩者無交接
/* Construct Relids set that identifies the joinrel. */
joinrelids = bms_union(rel1->relids, rel2->relids);//兩個rel涉及的rels
/* Check validity and determine join type. */
if (!join_is_legal(root, rel1, rel2, joinrelids,
&sjinfo, &reversed))//是否非法
{
/* invalid join path */
bms_free(joinrelids);
return NULL;//返回
}
/* Swap rels if needed to match the join info. */
if (reversed)//位置是否調換
{
RelOptInfo *trel = rel1;
rel1 = rel2;
rel2 = trel;
}
/*
* If it's a plain inner join, then we won't have found anything in
* join_info_list. Make up a SpecialJoinInfo so that selectivity
* estimation functions will know what's being joined.
* 普通的內連線,不需要使用join_info_list,
* 構造一個SpecialJoinInfo以便告知選擇率估算函式已連線
*/
if (sjinfo == NULL)
{
sjinfo = &sjinfo_data;
sjinfo->type = T_SpecialJoinInfo;
sjinfo->min_lefthand = rel1->relids;
sjinfo->min_righthand = rel2->relids;
sjinfo->syn_lefthand = rel1->relids;
sjinfo->syn_righthand = rel2->relids;
sjinfo->jointype = JOIN_INNER;
/* we don't bother trying to make the remaining fields valid */
sjinfo->lhs_strict = false;
sjinfo->delay_upper_joins = false;
sjinfo->semi_can_btree = false;
sjinfo->semi_can_hash = false;
sjinfo->semi_operators = NIL;
sjinfo->semi_rhs_exprs = NIL;
}
/*
* Find or build the join RelOptInfo, and compute the restrictlist that
* goes with this particular joining.
* 建立連線生成的新關係RelOptInfo,併為此連線生成限制條件連結串列
*/
joinrel = build_join_rel(root, joinrelids, rel1, rel2, sjinfo,
&restrictlist);
/*
* If we've already proven this join is empty, we needn't consider any
* more paths for it.
*/
if (is_dummy_rel(joinrel))
{
bms_free(joinrelids);
return joinrel;
}
/* Add paths to the join relation. */
//為連線生成的新關係構造訪問路徑
populate_joinrel_with_paths(root, rel1, rel2, joinrel, sjinfo,
restrictlist);
bms_free(joinrelids);//釋放資源
return joinrel;//返回joinrel
}
//-------------------------------------------------------------------- build_join_rel
/*
* build_join_rel
* Returns relation entry corresponding to the union of two given rels,
* creating a new relation entry if none already exists.
* 給定兩個rels,建立並返回對應這兩個rels連線生成的新的Relation
*
* 'joinrelids' is the Relids set that uniquely identifies the join
* 'outer_rel' and 'inner_rel' are relation nodes for the relations to be
* joined
* 'sjinfo': join context info
* 'restrictlist_ptr': result variable. If not NULL, *restrictlist_ptr
* receives the list of RestrictInfo nodes that apply to this
* particular pair of joinable relations.
* joinrelids-與此連線相關的所有relids
* outer_rel和inner_rel-構成連線的外表(驅動表)和內表
* sjinfo-連線上下文資訊
* restrictlist_ptr-儲存結果的變數,如為非NULL值,該指標指向RestrictInfo(約束條件)節點連結串列
*
* restrictlist_ptr makes the routine's API a little grotty, but it saves
* duplicated calculation of the restrictlist...
*/
RelOptInfo *
build_join_rel(PlannerInfo *root,
Relids joinrelids,
RelOptInfo *outer_rel,
RelOptInfo *inner_rel,
SpecialJoinInfo *sjinfo,
List **restrictlist_ptr)
{
RelOptInfo *joinrel;
List *restrictlist;
/* This function should be used only for join between parents. */
Assert(!IS_OTHER_REL(outer_rel) && !IS_OTHER_REL(inner_rel));
/*
* See if we already have a joinrel for this set of base rels.
* 這些基礎rels所構成的連線是否已存在?
*/
joinrel = find_join_rel(root, joinrelids);
if (joinrel)//已存在
{
/*
* Yes, so we only need to figure the restrictlist for this particular
* pair of component relations.
*/
if (restrictlist_ptr)
*restrictlist_ptr = build_joinrel_restrictlist(root,
joinrel,
outer_rel,
inner_rel);//如已存在約束條件,則找出相應的資訊即可
return joinrel;//返回
}
/*
* Nope, so make one.
* 沒有,則構造之
*/
joinrel = makeNode(RelOptInfo);
joinrel->reloptkind = RELOPT_JOINREL;
joinrel->relids = bms_copy(joinrelids);
joinrel->rows = 0;
/* cheap startup cost is interesting iff not all tuples to be retrieved */
joinrel->consider_startup = (root->tuple_fraction > 0);
joinrel->consider_param_startup = false;
joinrel->consider_parallel = false;
joinrel->reltarget = create_empty_pathtarget();
joinrel->pathlist = NIL;
joinrel->ppilist = NIL;
joinrel->partial_pathlist = NIL;
joinrel->cheapest_startup_path = NULL;
joinrel->cheapest_total_path = NULL;
joinrel->cheapest_unique_path = NULL;
joinrel->cheapest_parameterized_paths = NIL;
/* init direct_lateral_relids from children; we'll finish it up below */
joinrel->direct_lateral_relids =
bms_union(outer_rel->direct_lateral_relids,
inner_rel->direct_lateral_relids);
joinrel->lateral_relids = min_join_parameterization(root, joinrel->relids,
outer_rel, inner_rel);
joinrel->relid = 0; /* indicates not a baserel */
joinrel->rtekind = RTE_JOIN;//RTE_JOIN
joinrel->min_attr = 0;
joinrel->max_attr = 0;
joinrel->attr_needed = NULL;
joinrel->attr_widths = NULL;
joinrel->lateral_vars = NIL;
joinrel->lateral_referencers = NULL;
joinrel->indexlist = NIL;
joinrel->statlist = NIL;
joinrel->pages = 0;
joinrel->tuples = 0;
joinrel->allvisfrac = 0;
joinrel->subroot = NULL;
joinrel->subplan_params = NIL;
joinrel->rel_parallel_workers = -1;
joinrel->serverid = InvalidOid;
joinrel->userid = InvalidOid;
joinrel->useridiscurrent = false;
joinrel->fdwroutine = NULL;
joinrel->fdw_private = NULL;
joinrel->unique_for_rels = NIL;
joinrel->non_unique_for_rels = NIL;
joinrel->baserestrictinfo = NIL;
joinrel->baserestrictcost.startup = 0;
joinrel->baserestrictcost.per_tuple = 0;
joinrel->baserestrict_min_security = UINT_MAX;
joinrel->joininfo = NIL;
joinrel->has_eclass_joins = false;
joinrel->consider_partitionwise_join = false; /* might get changed later */
joinrel->top_parent_relids = NULL;
joinrel->part_scheme = NULL;
joinrel->nparts = 0;
joinrel->boundinfo = NULL;
joinrel->partition_qual = NIL;
joinrel->part_rels = NULL;
joinrel->partexprs = NULL;
joinrel->nullable_partexprs = NULL;
joinrel->partitioned_child_rels = NIL;
/* 設定FDW的相關資訊,Compute information relevant to the foreign relations. */
set_foreign_rel_properties(joinrel, outer_rel, inner_rel);
/*
* Create a new tlist containing just the vars that need to be output from
* this join (ie, are needed for higher joinclauses or final output).
*
* 建立一個新的投影列連結串列,只包含該連線的輸出
* NOTE: the tlist order for a join rel will depend on which pair of outer
* and inner rels we first try to build it from. But the contents should
* be the same regardless.
*/
build_joinrel_tlist(root, joinrel, outer_rel);//連線外表
build_joinrel_tlist(root, joinrel, inner_rel);//連線內表
add_placeholders_to_joinrel(root, joinrel, outer_rel, inner_rel);//新增PHV
/*
* add_placeholders_to_joinrel also took care of adding the ph_lateral
* sets of any PlaceHolderVars computed here to direct_lateral_relids, so
* now we can finish computing that. This is much like the computation of
* the transitively-closed lateral_relids in min_join_parameterization,
* except that here we *do* have to consider the added PHVs.
* add_placeholders_to_joinrel函式將這裡產生的PlaceHolderVars的ph_lateral集合
* 新增到direct_lateral_relids中以完成最終的處理。
* 這非常類似於min_join_parameterization的transtivelyclosed lateral al_relid的計算，
* 只是這裡需要考慮新增的phv。
*/
joinrel->direct_lateral_relids =
bms_del_members(joinrel->direct_lateral_relids, joinrel->relids);
if (bms_is_empty(joinrel->direct_lateral_relids))
joinrel->direct_lateral_relids = NULL;
/*
* Construct restrict and join clause lists for the new joinrel. (The
* caller might or might not need the restrictlist, but I need it anyway
* for set_joinrel_size_estimates().)
* 為新產生的joinrel構造約束和連線條件連結串列
*/
restrictlist = build_joinrel_restrictlist(root, joinrel,
outer_rel, inner_rel);//構建限制條件連結串列
if (restrictlist_ptr)
*restrictlist_ptr = restrictlist;
build_joinrel_joinlist(joinrel, outer_rel, inner_rel);//構建連線條件連結串列
/*
* This is also the right place to check whether the joinrel has any
* pending EquivalenceClass joins.
* 判斷是否存在等價類EC
*/
joinrel->has_eclass_joins = has_relevant_eclass_joinclause(root, joinrel);
/* S儲存分割槽資訊,tore the partition information. */
build_joinrel_partition_info(joinrel, outer_rel, inner_rel, restrictlist,
sjinfo->jointype);
/*
* 估算joinrel的大小,Set estimates of the joinrel's size.
*/
set_joinrel_size_estimates(root, joinrel, outer_rel, inner_rel,
sjinfo, restrictlist);
/*
* Set the consider_parallel flag if this joinrel could potentially be
* scanned within a parallel worker. If this flag is false for either
* inner_rel or outer_rel, then it must be false for the joinrel also.
* Even if both are true, there might be parallel-restricted expressions
* in the targetlist or quals.
* 設定consider_parallel標記,如joinrel可以並行掃描的話
*
* Note that if there are more than two rels in this relation, they could
* be divided between inner_rel and outer_rel in any arbitrary way. We
* assume this doesn't matter, because we should hit all the same baserels
* and joinclauses while building up to this joinrel no matter which we
* take; therefore, we should make the same decision here however we get
* here.
*/
if (inner_rel->consider_parallel && outer_rel->consider_parallel &&
is_parallel_safe(root, (Node *) restrictlist) &&
is_parallel_safe(root, (Node *) joinrel->reltarget->exprs))
joinrel->consider_parallel = true;
/* Add the joinrel to the PlannerInfo. */
add_join_rel(root, joinrel);//新增到最佳化器資訊中
/*
* Also, if dynamic-programming join search is active, add the new joinrel
* to the appropriate sublist. Note: you might think the Assert on number
* of members should be for equality, but some of the level 1 rels might
* have been joinrels already, so we can only assert <=.
* 新增到合適的連結串列中root->join_rel_levep[j]
*/
if (root->join_rel_level)
{
Assert(root->join_cur_level > 0);
Assert(root->join_cur_level <= bms_num_members(joinrel->relids));
root->join_rel_level[root->join_cur_level] =
lappend(root->join_rel_level[root->join_cur_level], joinrel);//加入到連結串列中
}
return joinrel;
}
//----------------------------------------------- find_join_rel
/*
* find_join_rel
* Returns relation entry corresponding to 'relids' (a set of RT indexes),
* or NULL if none exists. This is for join relations.
* 返回對應relids(RT indexes的集合)的RelOptInfo,如無則返回NULL.
*/
RelOptInfo *
find_join_rel(PlannerInfo *root, Relids relids)
{
/*
* Switch to using hash lookup when list grows "too long". The threshold
* is arbitrary and is known only here.
* 如連結串列過長,則改用hash查詢
*/
if (!root->join_rel_hash && list_length(root->join_rel_list) > 32)
build_join_rel_hash(root);
/*
* Use either hashtable lookup or linear search, as appropriate.
* 使用hash表查詢或者線性搜尋
*
* Note: the seemingly redundant hashkey variable is used to avoid taking
* the address of relids; unless the compiler is exceedingly smart, doing
* so would force relids out of a register and thus probably slow down the
* list-search case.
*/
if (root->join_rel_hash)//hash
{
Relids hashkey = relids;
JoinHashEntry *hentry;
hentry = (JoinHashEntry *) hash_search(root->join_rel_hash,
&hashkey,
HASH_FIND,
NULL);
if (hentry)
return hentry->join_rel;
}
else//線性
{
ListCell *l;
foreach(l, root->join_rel_list)
{
RelOptInfo *rel = (RelOptInfo *) lfirst(l);
if (bms_equal(rel->relids, relids))
return rel;
}
}
return NULL;
}
//----------------------------------------------- build_joinrel_restrictlist
/*
* build_joinrel_restrictlist
* build_joinrel_joinlist
* These routines build lists of restriction and join clauses for a
* join relation from the joininfo lists of the relations it joins.
* 從關係的joininfo連結串列中建立限制條件和連線條件連結串列
*
* These routines are separate because the restriction list must be
* built afresh for each pair of input sub-relations we consider, whereas
* the join list need only be computed once for any join RelOptInfo.
* The join list is fully determined by the set of rels making up the
* joinrel, so we should get the same results (up to ordering) from any
* candidate pair of sub-relations. But the restriction list is whatever
* is not handled in the sub-relations, so it depends on which
* sub-relations are considered.
* 這些處理過程是獨立的，因為限制條件連結串列必須為所考慮的每一對輸入子關係重新構建，
* 而連線條件連結串列只需要為任何連線RelOptInfo計算一次即可。
* 連線連結串列完全由組成joinrel的一組rels決定，
* 因此從任何子關係的候選對中都應該得到相同的結果(直到排序過程)。
* 但是限制條件連結串列是子關係中沒有處理的內容，所以它取決於考慮的子關係。
*
* If a join clause from an input relation refers to base rels still not
* present in the joinrel, then it is still a join clause for the joinrel;
* we put it into the joininfo list for the joinrel. Otherwise,
* the clause is now a restrict clause for the joined relation, and we
* return it to the caller of build_joinrel_restrictlist() to be stored in
* join paths made from this pair of sub-relations. (It will not need to
* be considered further up the join tree.)
* 如果構成連線關係中的連線條件子句指向的base rels不在joinrel中,
* 那麼它仍然是joinrel的連線條件子句;這些資訊會放到joinrel的joininfo連結串列中。
* 否則，如果條件子句現在是連線關係的限制子句，
* 那麼將它返回給build_joinrel_restrictlist()的呼叫方，將其儲存在由這對子關係構成的連線路徑中。
* (它不需要被認為位於連線樹的更上層。)
*
* In many case we will find the same RestrictInfos in both input
* relations' joinlists, so be careful to eliminate duplicates.
* Pointer equality should be a sufficient test for dups, since all
* the various joinlist entries ultimately refer to RestrictInfos
* pushed into them by distribute_restrictinfo_to_rels().
* 在許多情況下，在兩個關係的連線列表中可以發現相同的RestrictInfos，因此要小心排除重複。
* 指標相等的判斷應該是對重複值的充分測試，因為所有的joinlist條目最終
* 都指向distribute_restrictinfo_to_rels()推入的RestrictInfos。
*
* 'joinrel' is a join relation node,連線新產生的關係
* 'outer_rel' and 'inner_rel' are a pair of relations that can be joined
* to form joinrel.連線的外表和內表
*
* build_joinrel_restrictlist() returns a list of relevant restrictinfos,
* whereas build_joinrel_joinlist() stores its results in the joinrel's
* joininfo list. One or the other must accept each given clause!
* build_joinrel_restrictlist()返回相關限制條件的連結串列,
* 而build_joinrel_joinlist()把結果儲存在joinrel的joininfo連結串列中
*
* NB: Formerly, we made deep(!) copies of each input RestrictInfo to pass
* up to the join relation. I believe this is no longer necessary, because
* RestrictInfo nodes are no longer context-dependent. Instead, just include
* the original nodes in the lists made for the join relation.
*/
static List *
build_joinrel_restrictlist(PlannerInfo *root,
RelOptInfo *joinrel,
RelOptInfo *outer_rel,
RelOptInfo *inner_rel)
{
List *result;
/*
* Collect all the clauses that syntactically belong at this level,
* eliminating any duplicates (important since we will see many of the
* same clauses arriving from both input relations).
* 收集語法上屬於該級別的所有限制條件子句，消除任何重複(這很重要，因為存在來自兩個關係的相同子句)。
*/
result = subbuild_joinrel_restrictlist(joinrel, outer_rel->joininfo, NIL);
result = subbuild_joinrel_restrictlist(joinrel, inner_rel->joininfo, result);
/*
* Add on any clauses derived from EquivalenceClasses. These cannot be
* redundant with the clauses in the joininfo lists, so don't bother
* checking.
* 新增來自EC的條件子句.
*/
result = list_concat(result,
generate_join_implied_equalities(root,
joinrel->relids,
outer_rel->relids,
inner_rel));
return result;
}
static void
build_joinrel_joinlist(RelOptInfo *joinrel,
RelOptInfo *outer_rel,
RelOptInfo *inner_rel)
{
List *result;
/*
* Collect all the clauses that syntactically belong above this level,
* eliminating any duplicates (important since we will see many of the
* same clauses arriving from both input relations).
* 收集語法上屬於該級別的所有連線條件子句，消除任何重複(這很重要，因為存在來自兩個關係的相同子句)。
*/
result = subbuild_joinrel_joinlist(joinrel, outer_rel->joininfo, NIL);
result = subbuild_joinrel_joinlist(joinrel, inner_rel->joininfo, result);
joinrel->joininfo = result;
}
三、跟蹤分析
測試表和資料繼續沿用上一節建立的表和資料，使用的SQL語句如下:
testdb=# explain verbose select a.*,b.c1,c.c2,d.c2,e.c1,f.c2
from a inner join b on a.c1=b.c1,c,d,e inner join f on e.c1 = f.c1 and e.c1 < 100
where a.c1=f.c1 and b.c1=c.c1 and c.c1 = d.c1 and d.c1 = e.c1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Nested Loop (cost=101.17..2218.24 rows=2 width=42)
Output: a.c1, a.c2, b.c1, c.c2, d.c2, e.c1, f.c2
Join Filter: (a.c1 = b.c1)
-> Hash Join (cost=3.25..196.75 rows=100 width=22)
Output: a.c1, a.c2, c.c2, c.c1
Hash Cond: (c.c1 = a.c1)
-> Seq Scan on public.c (cost=0.00..155.00 rows=10000 width=12)
Output: c.c1, c.c2
-> Hash (cost=2.00..2.00 rows=100 width=10)
Output: a.c1, a.c2
-> Seq Scan on public.a (cost=0.00..2.00 rows=100 width=10)
Output: a.c1, a.c2
-> Materialize (cost=97.92..2014.00 rows=5 width=32)
Output: b.c1, d.c2, d.c1, e.c1, f.c2, f.c1
-> Hash Join (cost=97.92..2013.97 rows=5 width=32)
Output: b.c1, d.c2, d.c1, e.c1, f.c2, f.c1
Hash Cond: (f.c1 = b.c1)
-> Seq Scan on public.f (cost=0.00..1541.00 rows=100000 width=13)
Output: f.c1, f.c2
-> Hash (cost=97.86..97.86 rows=5 width=19)
Output: b.c1, d.c2, d.c1, e.c1
-> Hash Join (cost=78.10..97.86 rows=5 width=19)
Output: b.c1, d.c2, d.c1, e.c1
Hash Cond: (b.c1 = e.c1)
-> Seq Scan on public.b (cost=0.00..16.00 rows=1000 width=4)
Output: b.c1, b.c2
-> Hash (cost=78.04..78.04 rows=5 width=15)
Output: d.c2, d.c1, e.c1
-> Hash Join (cost=73.24..78.04 rows=5 width=15)
Output: d.c2, d.c1, e.c1
Hash Cond: (d.c1 = e.c1)
-> Seq Scan on public.d (cost=0.00..4.00 rows=200 width=11)
Output: d.c1, d.c2
-> Hash (cost=72.00..72.00 rows=99 width=4)
Output: e.c1
-> Seq Scan on public.e (cost=0.00..72.00 rows=99 width=4)
Output: e.c1
Filter: (e.c1 < 100)
(38 rows)
最佳化器選擇了2 rels + 4 rels的連線模式,跟蹤重點考察bushy plans的執行情況.
啟動gdb,設定斷點,只考察level=6的情況
(gdb) b join_search_one_level
Breakpoint 2 at 0x7b0289: file joinrels.c, line 67.
(gdb) c
Continuing.
...
(gdb) c
Continuing.
Breakpoint 2, join_search_one_level (root=0x241ca38, level=6) at joinrels.c:67
67 List **joinrels = root->join_rel_level;
完成5(rels)+1(rels)的呼叫
(gdb) b joinrels.c:142
Breakpoint 3 at 0x7b03c4: file joinrels.c, line 142.
(gdb) c
Continuing.
Breakpoint 3, join_search_one_level (root=0x241ca38, level=6) at joinrels.c:142
142 for (k = 2;; k++)
檢視root->join_rel_level[6]
(gdb) p *root->join_rel_level[6]
$1 = {type = T_List, length = 1, head = 0x24c8468, tail = 0x24c8468}
檢視該連結串列中的RelOptInfo
(gdb) set $roi=(RelOptInfo *)root->join_rel_level[6]->head->data.ptr_value
(gdb) p *$roi
$3 = {type = T_RelOptInfo, reloptkind = RELOPT_JOINREL, relids = 0x1eb8330, rows = 2, consider_startup = false,
consider_param_startup = false, consider_parallel = true, reltarget = 0x1f25ac8, pathlist = 0x1f25f80, ppilist = 0x0,
partial_pathlist = 0x0, cheapest_startup_path = 0x0, cheapest_total_path = 0x0, cheapest_unique_path = 0x0,
cheapest_parameterized_paths = 0x0, direct_lateral_relids = 0x0, lateral_relids = 0x0, relid = 0, reltablespace = 0,
rtekind = RTE_JOIN, min_attr = 0, max_attr = 0, attr_needed = 0x0, attr_widths = 0x0, lateral_vars = 0x0,
lateral_referencers = 0x0, indexlist = 0x0, statlist = 0x0, pages = 0, tuples = 0, allvisfrac = 0, subroot = 0x0,
subplan_params = 0x0, rel_parallel_workers = -1, serverid = 0, userid = 0, useridiscurrent = false, fdwroutine = 0x0,
fdw_private = 0x0, unique_for_rels = 0x0, non_unique_for_rels = 0x0, baserestrictinfo = 0x0, baserestrictcost = {
startup = 0, per_tuple = 0}, baserestrict_min_security = 4294967295, joininfo = 0x0, has_eclass_joins = false,
top_parent_relids = 0x0, part_scheme = 0x0, nparts = 0, boundinfo = 0x0, partition_qual = 0x0, part_rels = 0x0,
partexprs = 0x0, nullable_partexprs = 0x0, partitioned_child_rels = 0x0}
檢視該RelOptInfo的pathlist
(gdb) p *$roi->pathlist
$4 = {type = T_List, length = 1, head = 0x1f25f60, tail = 0x1f25f60}
(gdb) p *(Node *)$roi->pathlist->head->data.ptr_value
$5 = {type = T_NestPath}
(gdb) set $np=(NestPath *)$roi->pathlist->head->data.ptr_value
(gdb) p *(NestPath *)$np
$5 = {path = {type = T_NestPath, pathtype = T_NestLoop, parent = 0x1f258b8, pathtarget = 0x1f25ac8, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 2, startup_cost = 290.57499999999999,
total_cost = 2216.1374999999998, pathkeys = 0x0}, jointype = JOIN_INNER, inner_unique = false,
outerjoinpath = 0x1f07c00, innerjoinpath = 0x1f27c40, joinrestrictinfo = 0x1f27e60}
檢視該連線的外表和內部訪問路徑
(gdb) p *$np->outerjoinpath
$6 = {type = T_Path, pathtype = T_SeqScan, parent = 0x1e228e8, pathtarget = 0x1f04bc0, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 100, startup_cost = 0, total_cost = 2,
pathkeys = 0x0}
(gdb) p *$np->innerjoinpath
$7 = {type = T_MaterialPath, pathtype = T_Material, parent = 0x1ebb538, pathtarget = 0x1ebb748, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 5, startup_cost = 290.57499999999999,
total_cost = 2206.6500000000001, pathkeys = 0x0}
下面開始嘗試bushy plans,即（2/4 rels+ 4/2 rels)或(3 rels + 3 rels)模式,重點考察ac + bdef這種組合
(gdb) b joinrels.c:156
Breakpoint 3 at 0x7557df: file joinrels.c, line 156.
(gdb) c
Continuing.
Breakpoint 3, join_search_one_level (root=0x1e214b8, level=6) at joinrels.c:164
164 if (old_rel->joininfo == NIL && !old_rel->has_eclass_joins &&
(gdb) p *old_rel->relids->words
$13 = 18
進入make_join_rel函式
173 for_each_cell(r2, other_rels)
(gdb)
175 RelOptInfo *new_rel = (RelOptInfo *) lfirst(r2);
(gdb)
177 if (!bms_overlap(old_rel->relids, new_rel->relids))
(gdb)
184 if (have_relevant_joinclause(root, old_rel, new_rel) ||
(gdb)
187 (void) make_join_rel(root, old_rel, new_rel);
(gdb) step
make_join_rel (root=0x1e214b8, rel1=0x1f079f0, rel2=0x1e96520) at joinrels.c:681
681 joinrelids = bms_union(rel1->relids, rel2->relids);
進入build_join_rel函式,相應的RelOptInfo已存在,返回
(gdb)
728 joinrel = build_join_rel(root, joinrelids, rel1, rel2, sjinfo,
(gdb) step
build_join_rel (root=0x1e214b8, joinrelids=0x1e401d8, outer_rel=0x1f079f0, inner_rel=0x1e96520, sjinfo=0x7fff247e18a0,
restrictlist_ptr=0x7fff247e1898) at relnode.c:498
498 joinrel = find_join_rel(root, joinrelids);
500 if (joinrel)
(gdb) n
506 if (restrictlist_ptr)
(gdb)
507 *restrictlist_ptr = build_joinrel_restrictlist(root,
(gdb)
511 return joinrel;
執行populate_joinrel_with_paths,該函式執行後再次檢視外表和內部訪問路徑,變成了HashPath + MaterialPath的組合,具體的變化,下一節再行介紹.
...
(gdb)
742 populate_joinrel_with_paths(root, rel1, rel2, joinrel, sjinfo,
(gdb) n
745 bms_free(joinrelids);
(gdb) set $roi=(RelOptInfo *)root->join_rel_level[6]->head->data.ptr_value
(gdb) set $np=(NestPath *)$roi->pathlist->head->data.ptr_value
(gdb) p *$np->outerjoinpath
$30 = {type = T_HashPath, pathtype = T_HashJoin, parent = 0x1f079f0, pathtarget = 0x1e41128, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 100, startup_cost = 3.25, total_cost = 196.75,
pathkeys = 0x0}
(gdb) p *$np->innerjoinpath
$31 = {type = T_MaterialPath, pathtype = T_Material, parent = 0x1e96520, pathtarget = 0x1e96730, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 5, startup_cost = 97.962499999999991,
total_cost = 2014.0375000000001, pathkeys = 0x0}
DONE!
四、參考資料
allpaths.c
cost.h
costsize.c
PG Document:Query Planning
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6906/viewspace-2374836/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PostgreSQL 原始碼解讀（49）- 查詢語句#34（make_one_rel函式#1-概覽）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 原始碼解讀（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原始碼函式
- PostgreSQL 原始碼解讀（71）- 查詢語句#56（make_one_rel函式#21-...SQL原始碼函式
- PostgreSQL 原始碼解讀（72）- 查詢語句#57（make_one_rel函式#22-...SQL原始碼函式
- PostgreSQL 原始碼解讀（52）- 查詢語句#37（make_one_rel函式#2-估...SQL原始碼函式
- PostgreSQL 原始碼解讀（53）- 查詢語句#38（make_one_rel函式#3-順...SQL原始碼函式
- PostgreSQL 原始碼解讀（54）- 查詢語句#39（make_one_rel函式#4-生...SQL原始碼函式
- PostgreSQL 原始碼解讀（55）- 查詢語句#40（make_one_rel函式#5-索...SQL原始碼函式
- PostgreSQL 原始碼解讀（56）- 查詢語句#41（make_one_rel函式#6-索...SQL原始碼函式
- PostgreSQL 原始碼解讀（57）- 查詢語句#42（make_one_rel函式#7-索...SQL原始碼函式
- PostgreSQL 原始碼解讀（58）- 查詢語句#43（make_one_rel函式#8-B...SQL原始碼函式
- PostgreSQL 原始碼解讀（59）- 查詢語句#44（make_one_rel函式#9-B...SQL原始碼函式
- PostgreSQL 原始碼解讀（83）- 查詢語句#68（PortalStart函式）SQL原始碼函式
- PostgreSQL 原始碼解讀（79）- 查詢語句#64（create_plan函式#3-Se...SQL原始碼函式
- PostgreSQL 原始碼解讀（90）- 查詢語句#75（ExecHashJoin函式#1）SQL原始碼函式
- PostgreSQL 原始碼解讀（91）- 查詢語句#76（ExecHashJoin函式#2）SQL原始碼函式
- PostgreSQL 原始碼解讀（93）- 查詢語句#77（ExecHashJoin函式#3）SQL原始碼函式
- PostgreSQL 原始碼解讀（52）- 查詢語句#37（make_one_rel函式#2-估算關係大小）SQL原始碼函式
- PostgreSQL 原始碼解讀（95）- 查詢語句#78（ExecHashJoin函式#4-H...SQL原始碼函式
- PostgreSQL 原始碼解讀（97）- 查詢語句#79（ExecHashJoin函式#5-H...SQL原始碼函式
- PostgreSQL 原始碼解讀（88）- 查詢語句#73（SeqNext函式#1）SQL原始碼函式
- PostgreSQL 原始碼解讀（89）- 查詢語句#74（SeqNext函式#2）SQL原始碼函式
- 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原始碼函式