PostgreSQL 原始碼解讀（65）- 查詢語句#50（make_one_rel函式#15-...
本節大體介紹了動態規劃演算法實現(standard_join_search)中的join_search_one_level->make_join_rel->populate_joinrel_with_paths函式的主實現邏輯，該函式為連線新生成的joinrel構造訪問路徑。
一、資料結構
SpecialJoinInfo
/*
* "Special join" info.
*
* One-sided outer joins constrain the order of joining partially but not
* completely. We flatten such joins into the planner's top-level list of
* relations to join, but record information about each outer join in a
* SpecialJoinInfo struct. These structs are kept in the PlannerInfo node's
* join_info_list.
*
* Similarly, semijoins and antijoins created by flattening IN (subselect)
* and EXISTS(subselect) clauses create partial constraints on join order.
* These are likewise recorded in SpecialJoinInfo structs.
*
* We make SpecialJoinInfos for FULL JOINs even though there is no flexibility
* of planning for them, because this simplifies make_join_rel()'s API.
*
* min_lefthand and min_righthand are the sets of base relids that must be
* available on each side when performing the special join. lhs_strict is
* true if the special join's condition cannot succeed when the LHS variables
* are all NULL (this means that an outer join can commute with upper-level
* outer joins even if it appears in their RHS). We don't bother to set
* lhs_strict for FULL JOINs, however.
*
* It is not valid for either min_lefthand or min_righthand to be empty sets;
* if they were, this would break the logic that enforces join order.
*
* syn_lefthand and syn_righthand are the sets of base relids that are
* syntactically below this special join. (These are needed to help compute
* min_lefthand and min_righthand for higher joins.)
*
* delay_upper_joins is set true if we detect a pushed-down clause that has
* to be evaluated after this join is formed (because it references the RHS).
* Any outer joins that have such a clause and this join in their RHS cannot
* commute with this join, because that would leave noplace to check the
* pushed-down clause. (We don't track this for FULL JOINs, either.)
*
* For a semijoin, we also extract the join operators and their RHS arguments
* and set semi_operators, semi_rhs_exprs, semi_can_btree, and semi_can_hash.
* This is done in support of possibly unique-ifying the RHS, so we don't
* bother unless at least one of semi_can_btree and semi_can_hash can be set
* true. (You might expect that this information would be computed during
* join planning; but it's helpful to have it available during planning of
* parameterized table scans, so we store it in the SpecialJoinInfo structs.)
*
* jointype is never JOIN_RIGHT; a RIGHT JOIN is handled by switching
* the inputs to make it a LEFT JOIN. So the allowed values of jointype
* in a join_info_list member are only LEFT, FULL, SEMI, or ANTI.
*
* For purposes of join selectivity estimation, we create transient
* SpecialJoinInfo structures for regular inner joins; so it is possible
* to have jointype == JOIN_INNER in such a structure, even though this is
* not allowed within join_info_list. We also create transient
* SpecialJoinInfos with jointype == JOIN_INNER for outer joins, since for
* cost estimation purposes it is sometimes useful to know the join size under
* plain innerjoin semantics. Note that lhs_strict, delay_upper_joins, and
* of course the semi_xxx fields are not set meaningfully within such structs.
*/
typedef struct SpecialJoinInfo
{
NodeTag type;
Relids min_lefthand; /* base relids in minimum LHS for join */
Relids min_righthand; /* base relids in minimum RHS for join */
Relids syn_lefthand; /* base relids syntactically within LHS */
Relids syn_righthand; /* base relids syntactically within RHS */
JoinType jointype; /* always INNER, LEFT, FULL, SEMI, or ANTI */
bool lhs_strict; /* joinclause is strict for some LHS rel */
bool delay_upper_joins; /* can't commute with upper RHS */
/* Remaining fields are set only for JOIN_SEMI jointype: */
bool semi_can_btree; /* true if semi_operators are all btree */
bool semi_can_hash; /* true if semi_operators are all hash */
List *semi_operators; /* OIDs of equality join operators */
List *semi_rhs_exprs; /* righthand-side expressions of these ops */
} SpecialJoinInfo;
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->populate_joinrel_with_paths函式為新生成的連線joinrel(給定參與連線的relations)構造訪問路徑.
輸入引數中的sjinfo(SpecialJoinInfo結構體)提供了有關連線的詳細資訊，限制條件連結串列restrictlist(List)包含連線條件子句和適用於給定連線關係對的其他條件子句。
//-------------------------------------------------------------------- populate_joinrel_with_paths
/*
* populate_joinrel_with_paths
* Add paths to the given joinrel for given pair of joining relations. The
* SpecialJoinInfo provides details about the join and the restrictlist
* contains the join clauses and the other clauses applicable for given pair
* of the joining relations.
* 為新生成的連線joinrel(給定參與連線的relations)構造訪問路徑.
* SpecialJoinInfo提供了有關連線的詳細資訊，
* 限制條件連結串列包含連線條件子句和適用於給定連線關係對的其他條件子句。
*/
static void
populate_joinrel_with_paths(PlannerInfo *root, RelOptInfo *rel1,
RelOptInfo *rel2, RelOptInfo *joinrel,
SpecialJoinInfo *sjinfo, List *restrictlist)
{
/*
* Consider paths using each rel as both outer and inner. Depending on
* the join type, a provably empty outer or inner rel might mean the join
* is provably empty too; in which case throw away any previously computed
* paths and mark the join as dummy. (We do it this way since it's
* conceivable that dummy-ness of a multi-element join might only be
* noticeable for certain construction paths.)
* 考慮使用每個rel分別作為外表和內表的路徑。
* 根據連線型別的不同，一個可證明為空的外表或內表可能意味著連線結果也是為空;
* 在這種情況下，丟棄任何以前計算過的路徑，並將連線標記為虛(dummy)連線。
*
* Also, a provably constant-false join restriction typically means that
* we can skip evaluating one or both sides of the join. We do this by
* marking the appropriate rel as dummy. For outer joins, a
* constant-false restriction that is pushed down still means the whole
* join is dummy, while a non-pushed-down one means that no inner rows
* will join so we can treat the inner rel as dummy.
* 此外，可以證明的常量-false連線限制通常意味著我們可以跳過對連線的一個或兩個方面的表示式解析。
* 我們透過將適當的rel標記為虛(dummy)來完成這一操作。
* 對於外連線，被下推的常量-false限制仍然意味著整個連線是虛連線，
* 而非下推的限制意味著沒有內部行會連線，因此我們可以將內部rel視為虛擬的。
*
* We need only consider the jointypes that appear in join_info_list, plus
* JOIN_INNER.
* 只需要考慮出現在join_info_list和JOIN_INNER中的jointype。
*/
switch (sjinfo->jointype)
{
case JOIN_INNER:
if (is_dummy_rel(rel1) || is_dummy_rel(rel2) ||
restriction_is_constant_false(restrictlist, joinrel, false))
{
mark_dummy_rel(joinrel);//設定為虛連線
break;
}
add_paths_to_joinrel(root, joinrel, rel1, rel2,
JOIN_INNER, sjinfo,
restrictlist);//新增路徑,rel1為外表,rel2為內表
add_paths_to_joinrel(root, joinrel, rel2, rel1,
JOIN_INNER, sjinfo,
restrictlist);//新增路徑,rel2為外表,rel1為內表
break;
case JOIN_LEFT://同上
if (is_dummy_rel(rel1) ||
restriction_is_constant_false(restrictlist, joinrel, true))
{
mark_dummy_rel(joinrel);
break;
}
if (restriction_is_constant_false(restrictlist, joinrel, false) &&
bms_is_subset(rel2->relids, sjinfo->syn_righthand))
mark_dummy_rel(rel2);
add_paths_to_joinrel(root, joinrel, rel1, rel2,
JOIN_LEFT, sjinfo,
restrictlist);
add_paths_to_joinrel(root, joinrel, rel2, rel1,
JOIN_RIGHT, sjinfo,
restrictlist);
break;
case JOIN_FULL://同上
if ((is_dummy_rel(rel1) && is_dummy_rel(rel2)) ||
restriction_is_constant_false(restrictlist, joinrel, true))
{
mark_dummy_rel(joinrel);
break;
}
add_paths_to_joinrel(root, joinrel, rel1, rel2,
JOIN_FULL, sjinfo,
restrictlist);
add_paths_to_joinrel(root, joinrel, rel2, rel1,
JOIN_FULL, sjinfo,
restrictlist);
/*
* If there are join quals that aren't mergeable or hashable, we
* may not be able to build any valid plan. Complain here so that
* we can give a somewhat-useful error message. (Since we have no
* flexibility of planning for a full join, there's no chance of
* succeeding later with another pair of input rels.)
* 如果有無法合併或不能合併的join quals，我們可能無法建立任何有效的計劃。
* 在這裡報錯，這樣就可以給出一些有用的錯誤訊息。
* (由於無法靈活地為全連線新增計劃，所以以後再加入另一對rels就沒有成功的機會了。)
*/
if (joinrel->pathlist == NIL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("FULL JOIN is only supported with merge-joinable or hash-joinable join conditions")));
break;
case JOIN_SEMI://半連線
/*
* We might have a normal semijoin, or a case where we don't have
* enough rels to do the semijoin but can unique-ify the RHS and
* then do an innerjoin (see comments in join_is_legal). In the
* latter case we can't apply JOIN_SEMI joining.
* 可能有一個普通的半連線，或者我們沒有足夠的rels來做半連線，
* 但是可以透過唯一化RHS，然後做一個innerjoin(請參閱join_is_legal中的註釋)。
* 在後一種情況下，我們不能應用JOIN_SEMI join。
*/
if (bms_is_subset(sjinfo->min_lefthand, rel1->relids) &&
bms_is_subset(sjinfo->min_righthand, rel2->relids))
{
if (is_dummy_rel(rel1) || is_dummy_rel(rel2) ||
restriction_is_constant_false(restrictlist, joinrel, false))
{
mark_dummy_rel(joinrel);
break;
}
add_paths_to_joinrel(root, joinrel, rel1, rel2,
JOIN_SEMI, sjinfo,
restrictlist);
}
/*
* If we know how to unique-ify the RHS and one input rel is
* exactly the RHS (not a superset) we can consider unique-ifying
* it and then doing a regular join. (The create_unique_path
* check here is probably redundant with what join_is_legal did,
* but if so the check is cheap because it's cached. So test
* anyway to be sure.)
* 如果我們知道如何唯一化RHS，一個輸入rel恰好是RHS(不是超集)，
* 我們可以考慮唯一化它，然後進行常規連線。
* (這裡的create_unique_path檢查與join_is_legal的檢查可能是冗餘的，
* 但是如果是的話，這樣的檢查的成本就顯得很低，因為它可以快取。所以不管怎樣，還是要檢查一下。
*/
if (bms_equal(sjinfo->syn_righthand, rel2->relids) &&
create_unique_path(root, rel2, rel2->cheapest_total_path,
sjinfo) != NULL)
{
if (is_dummy_rel(rel1) || is_dummy_rel(rel2) ||
restriction_is_constant_false(restrictlist, joinrel, false))
{
mark_dummy_rel(joinrel);
break;
}
add_paths_to_joinrel(root, joinrel, rel1, rel2,
JOIN_UNIQUE_INNER, sjinfo,
restrictlist);
add_paths_to_joinrel(root, joinrel, rel2, rel1,
JOIN_UNIQUE_OUTER, sjinfo,
restrictlist);
}
break;
case JOIN_ANTI://反連線
if (is_dummy_rel(rel1) ||
restriction_is_constant_false(restrictlist, joinrel, true))
{
mark_dummy_rel(joinrel);
break;
}
if (restriction_is_constant_false(restrictlist, joinrel, false) &&
bms_is_subset(rel2->relids, sjinfo->syn_righthand))
mark_dummy_rel(rel2);
add_paths_to_joinrel(root, joinrel, rel1, rel2,
JOIN_ANTI, sjinfo,
restrictlist);
break;
default://非法的連線型別
/* other values not expected here */
elog(ERROR, "unrecognized join type: %d", (int) sjinfo->jointype);
break;
}
/* 嘗試partitionwise技術. Apply partitionwise join technique, if possible. */
try_partitionwise_join(root, rel1, rel2, joinrel, sjinfo, restrictlist);
}
//------------------------------------------------------------------- add_paths_to_joinrel
/*
* add_paths_to_joinrel
* Given a join relation and two component rels from which it can be made,
* consider all possible paths that use the two component rels as outer
* and inner rel respectively. Add these paths to the join rel's pathlist
* if they survive comparison with other paths (and remove any existing
* paths that are dominated by these paths).
* 給出組成連線的兩個組合rels,嘗試所有可能的路徑進行連線,比如分別設定為outer和inner表等.
* 如果連線的路徑在與其他路徑的比較中可以留存下來，
* 則將這些路徑新增到連線rel的路徑列表中(並刪除現有的由這些路徑控制的其他路徑)。
*
* Modifies the pathlist field of the joinrel node to contain the best
* paths found so far.
* 更新joinrel->pathlist連結串列已容納最優的訪問路徑.
*
* jointype is not necessarily the same as sjinfo->jointype; it might be
* "flipped around" if we are considering joining the rels in the opposite
* direction from what's indicated in sjinfo.
* jointype不需要與sjinfo->jointype一致,如果我們考慮加入與sjinfo所示相反方向的rels，它可能是“翻轉”的。
*
* Also, this routine and others in this module accept the special JoinTypes
* JOIN_UNIQUE_OUTER and JOIN_UNIQUE_INNER to indicate that we should
* unique-ify the outer or inner relation and then apply a regular inner
* join. These values are not allowed to propagate outside this module,
* however. Path cost estimation code may need to recognize that it's
* dealing with such a case --- the combination of nominal jointype INNER
* with sjinfo->jointype == JOIN_SEMI indicates that.
* 此外，這個處理過程和這個模組中的其他過程接受特殊的JoinTypes(JOIN_UNIQUE_OUTER和JOIN_UNIQUE_INNER)，
* 以表明應該對外部或內部關係進行唯一化，然後應用一個常規的內部連線。
* 但是，這些值不允許傳播到這個模組之外。
* 訪問路徑成本估算過程可能需要認識到，
* 它正在處理這樣的情況———名義上的INNER jointype與sjinfo->jointype == JOIN_SEMI的組合。
*/
void
add_paths_to_joinrel(PlannerInfo *root,
RelOptInfo *joinrel,
RelOptInfo *outerrel,
RelOptInfo *innerrel,
JoinType jointype,
SpecialJoinInfo *sjinfo,
List *restrictlist)
{
JoinPathExtraData extra;
bool mergejoin_allowed = true;
ListCell *lc;
Relids joinrelids;
/*
* PlannerInfo doesn't contain the SpecialJoinInfos created for joins
* between child relations, even if there is a SpecialJoinInfo node for
* the join between the topmost parents. So, while calculating Relids set
* representing the restriction, consider relids of topmost parent of
* partitions.
* PlannerInfo不包含為子關係之間的連線建立的SpecialJoinInfo，
* 即使最頂層的父關係之間有一個SpecialJoinInfo節點。
* 因此，在計算表示限制條件的Relids集合時，需考慮分割槽的最頂層父類的Relids。
*/
if (joinrel->reloptkind == RELOPT_OTHER_JOINREL)
joinrelids = joinrel->top_parent_relids;
else
joinrelids = joinrel->relids;
extra.restrictlist = restrictlist;
extra.mergeclause_list = NIL;
extra.sjinfo = sjinfo;
extra.param_source_rels = NULL;
/*
* See if the inner relation is provably unique for this outer rel.
* 判斷內表是否已被驗證為唯一.
*
* We have some special cases: for JOIN_SEMI and JOIN_ANTI, it doesn't
* matter since the executor can make the equivalent optimization anyway;
* we need not expend planner cycles on proofs. For JOIN_UNIQUE_INNER, we
* must be considering a semijoin whose inner side is not provably unique
* (else reduce_unique_semijoins would've simplified it), so there's no
* point in calling innerrel_is_unique. However, if the LHS covers all of
* the semijoin's min_lefthand, then it's appropriate to set inner_unique
* because the path produced by create_unique_path will be unique relative
* to the LHS. (If we have an LHS that's only part of the min_lefthand,
* that is *not* true.) For JOIN_UNIQUE_OUTER, pass JOIN_INNER to avoid
* letting that value escape this module.
* 存在一些特殊的情況:
* 1.對於JOIN_SEMI和JOIN_ANTI，這無關緊要，因為執行器無論如何都可以進行等價的最佳化;
* 這些不需要在證明上花費時間證明。
* 2.對於JOIN_UNIQUE_INNER，必須考慮一個內部不是唯一的半連線(否則reduce_unique_semijoin會簡化它)，
* 所以呼叫innerrel_is_unique沒有任何意義。
* 但是，如果LHS覆蓋了半連線的所有min_left，那麼就應該設定inner_unique，
* 因為create_unique_path生成的路徑相對於LHS是唯一的。
* (如果LHS只是min_left的一部分，那就不是真的)
* 對於JOIN_UNIQUE_OUTER，傳遞JOIN_INNER以避免讓該值轉義這個模組。
*/
switch (jointype)
{
case JOIN_SEMI:
case JOIN_ANTI:
extra.inner_unique = false; /* well, unproven */
break;
case JOIN_UNIQUE_INNER:
extra.inner_unique = bms_is_subset(sjinfo->min_lefthand,
outerrel->relids);
break;
case JOIN_UNIQUE_OUTER:
extra.inner_unique = innerrel_is_unique(root,
joinrel->relids,
outerrel->relids,
innerrel,
JOIN_INNER,
restrictlist,
false);
break;
default:
extra.inner_unique = innerrel_is_unique(root,
joinrel->relids,
outerrel->relids,
innerrel,
jointype,
restrictlist,
false);
break;
}
/*
* Find potential mergejoin clauses. We can skip this if we are not
* interested in doing a mergejoin. However, mergejoin may be our only
* way of implementing a full outer join, so override enable_mergejoin if
* it's a full join.
* 尋找潛在的mergejoin條件。如果不允許Merge Join,則跳過。
* 然而，mergejoin可能是實現完整外部連線的唯一方法，
* 因此，如果它是完全連線，則不理會enable_mergejoin引數。
*/
if (enable_mergejoin || jointype == JOIN_FULL)
extra.mergeclause_list = select_mergejoin_clauses(root,
joinrel,
outerrel,
innerrel,
restrictlist,
jointype,
&mergejoin_allowed);
/*
* If it's SEMI, ANTI, or inner_unique join, compute correction factors
* for cost estimation. These will be the same for all paths.
* 如果是半連線、反連線或inner_unique連線，則計算成本估算的相關因子,該值對所有路徑都是一樣的。
*/
if (jointype == JOIN_SEMI || jointype == JOIN_ANTI || extra.inner_unique)
compute_semi_anti_join_factors(root, joinrel, outerrel, innerrel,
jointype, sjinfo, restrictlist,
&extra.semifactors);
/*
* Decide whether it's sensible to generate parameterized paths for this
* joinrel, and if so, which relations such paths should require. There
* is usually no need to create a parameterized result path unless there
* is a join order restriction that prevents joining one of our input rels
* directly to the parameter source rel instead of joining to the other
* input rel. (But see allow_star_schema_join().) This restriction
* reduces the number of parameterized paths we have to deal with at
* higher join levels, without compromising the quality of the resulting
* plan. We express the restriction as a Relids set that must overlap the
* parameterization of any proposed join path.
* 確定為這個連線生成引數化路徑是否合理，如果是，這些路徑應該需要哪些關係。
* 通常不需要建立一個引數化的結果路徑，除非存在一個連線順序限制，
* 阻止將一個關係直接連線到引數源rel，而不是連線到另一個input rel(但請參閱allow_star_schema_join())。
* 這種限制減少了在更高的連線級別上必須處理的引數化路徑的數量，而不會影響最終計劃的質量。
* 把這個限制表示為一個Relids集合，它必須與任何建議的連線路徑的引數化重疊。
*/
foreach(lc, root->join_info_list)
{
SpecialJoinInfo *sjinfo2 = (SpecialJoinInfo *) lfirst(lc);
/*
* SJ is relevant to this join if we have some part of its RHS
* (possibly not all of it), and haven't yet joined to its LHS. (This
* test is pretty simplistic, but should be sufficient considering the
* join has already been proven legal.) If the SJ is relevant, it
* presents constraints for joining to anything not in its RHS.
* 與這個連線相關的SJ，如果有它的部分RHS(可能不是全部)，並且還沒有加入它的LHS。
* (這個驗證非常簡單，但是考慮到連線已經被證明是合法的，這個驗證就足夠了。)
* 如果SJ是相關的，那麼它就為連線到其RHS之外的任何內容提供了約束。
*/
if (bms_overlap(joinrelids, sjinfo2->min_righthand) &&
!bms_overlap(joinrelids, sjinfo2->min_lefthand))
extra.param_source_rels = bms_join(extra.param_source_rels,
bms_difference(root->all_baserels,
sjinfo2->min_righthand));
/* 全連線在語法上約束左右兩邊.full joins constrain both sides symmetrically */
if (sjinfo2->jointype == JOIN_FULL &&
bms_overlap(joinrelids, sjinfo2->min_lefthand) &&
!bms_overlap(joinrelids, sjinfo2->min_righthand))
extra.param_source_rels = bms_join(extra.param_source_rels,
bms_difference(root->all_baserels,
sjinfo2->min_lefthand));
}
/*
* However, when a LATERAL subquery is involved, there will simply not be
* any paths for the joinrel that aren't parameterized by whatever the
* subquery is parameterized by, unless its parameterization is resolved
* within the joinrel. So we might as well allow additional dependencies
* on whatever residual lateral dependencies the joinrel will have.
* 然而，當涉及到一個LATERAL子查詢時，除非在joinrel中解析其引數化，
* 否則joinrel的任何路徑都不會被子查詢引數化。
* 因此，也可以允許對joinrel將擁有的任何剩餘的LATERAL依賴進行額外依賴。
*/
extra.param_source_rels = bms_add_members(extra.param_source_rels,
joinrel->lateral_relids);
/*
* 1. Consider mergejoin paths where both relations must be explicitly
* sorted. Skip this if we can't mergejoin.
* 1. 嘗試merge join訪問路徑，其中兩個關係必須執行顯式的排序。
* 如果禁用merge join，則跳過。
*/
if (mergejoin_allowed)
sort_inner_and_outer(root, joinrel, outerrel, innerrel,
jointype, &extra);
/*
* 2. Consider paths where the outer relation need not be explicitly
* sorted. This includes both nestloops and mergejoins where the outer
* path is already ordered. Again, skip this if we can't mergejoin.
* (That's okay because we know that nestloop can't handle right/full
* joins at all, so it wouldn't work in the prohibited cases either.)
* 2. 考慮外部關係不需要顯式排序的路徑。
* 這包括nestloop和mergejoin，它們的外部路徑已經排序。
* 再一次的，如果禁用merge join，則跳過。
* (nestloop無法處理正確/完全連線，所以在禁止的情況下它也無法工作)
*/
if (mergejoin_allowed)
match_unsorted_outer(root, joinrel, outerrel, innerrel,
jointype, &extra);
#ifdef NOT_USED
/*
* 3. Consider paths where the inner relation need not be explicitly
* sorted. This includes mergejoins only (nestloops were already built in
* match_unsorted_outer).
* 3. 嘗試內部關係不需要顯式排序的路徑。這隻包括mergejoin(在match_unsorted_outer中已經構建了nestloop)。
* (已廢棄)
*
* Diked out as redundant 2/13/2000 -- tgl. There isn't any really
* significant difference between the inner and outer side of a mergejoin,
* so match_unsorted_inner creates no paths that aren't equivalent to
* those made by match_unsorted_outer when add_paths_to_joinrel() is
* invoked with the two rels given in the other order.
*/
if (mergejoin_allowed)
match_unsorted_inner(root, joinrel, outerrel, innerrel,
jointype, &extra);
#endif
/*
* 4. Consider paths where both outer and inner relations must be hashed
* before being joined. As above, disregard enable_hashjoin for full
* joins, because there may be no other alternative.
* 4. 考慮在連線之前必須對外部/內部關係進行雜湊處理的路徑。
* 如上所述，對於完全連線，忽略enable_hashjoin，因為可能沒有其他選擇。
*/
if (enable_hashjoin || jointype == JOIN_FULL)
hash_inner_and_outer(root, joinrel, outerrel, innerrel,
jointype, &extra);
/*
* 5. If inner and outer relations are foreign tables (or joins) belonging
* to the same server and assigned to the same user to check access
* permissions as, give the FDW a chance to push down joins.
* 如果內部和外部關係是屬於同一伺服器的外部表(或連線)，
* 並分配給同一使用者以檢查訪問許可權，則FDW有機會下推連線。
*/
if (joinrel->fdwroutine &&
joinrel->fdwroutine->GetForeignJoinPaths)
joinrel->fdwroutine->GetForeignJoinPaths(root, joinrel,
outerrel, innerrel,
jointype, &extra);
/*
* 6. Finally, give extensions a chance to manipulate the path list.
* 6. 最後,呼叫擴充套件鉤子函式.
*/
if (set_join_pathlist_hook)
set_join_pathlist_hook(root, joinrel, outerrel, innerrel,
jointype, &extra);
}
三、跟蹤分析
SQL語句如下:
testdb=# explain verbose select dw.*,grjf.grbh,grjf.xm,grjf.ny,grjf.je
from t_dwxx dw,lateral (select gr.grbh,gr.xm,jf.ny,jf.je
from t_grxx gr inner join t_jfxx jf
on gr.dwbh = dw.dwbh
and gr.grbh = jf.grbh) grjf
order by dw.dwbh;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Merge Join (cost=18841.64..21009.94 rows=99850 width=47)
Output: dw.dwmc, dw.dwbh, dw.dwdz, gr.grbh, gr.xm, jf.ny, jf.je
Merge Cond: ((dw.dwbh)::text = (gr.dwbh)::text)
-> Index Scan using t_dwxx_pkey on public.t_dwxx dw (cost=0.29..399.62 rows=10000 width=20)
Output: dw.dwmc, dw.dwbh, dw.dwdz
-> Materialize (cost=18836.82..19336.82 rows=100000 width=31)
Output: gr.grbh, gr.xm, gr.dwbh, jf.ny, jf.je
-> Sort (cost=18836.82..19086.82 rows=100000 width=31)
Output: gr.grbh, gr.xm, gr.dwbh, jf.ny, jf.je
Sort Key: gr.dwbh
-> Hash Join (cost=3465.00..8138.00 rows=100000 width=31)
Output: gr.grbh, gr.xm, gr.dwbh, jf.ny, jf.je
Hash Cond: ((jf.grbh)::text = (gr.grbh)::text)
-> Seq Scan on public.t_jfxx jf (cost=0.00..1637.00 rows=100000 width=20)
Output: jf.ny, jf.je, jf.grbh
-> Hash (cost=1726.00..1726.00 rows=100000 width=16)
Output: gr.grbh, gr.xm, gr.dwbh
-> Seq Scan on public.t_grxx gr (cost=0.00..1726.00 rows=100000 width=16)
Output: gr.grbh, gr.xm, gr.dwbh
(19 rows)
參與連線的有3張基表,分別是t_dwxx/t_grxx/t_jfxx,從執行計劃可見,由於存在order by dwbh排序子句,最佳化器"聰明"的選擇Merge Join.
啟動gdb,設定斷點,只考察level=3的情況(最終結果)
(gdb) b join_search_one_level
Breakpoint 1 at 0x755667: file joinrels.c, line 67.
(gdb) c
Continuing.
Breakpoint 1, join_search_one_level (root=0x1cae678, level=2) at joinrels.c:67
67 List **joinrels = root->join_rel_level;
(gdb) c
Continuing.
Breakpoint 1, join_search_one_level (root=0x1cae678, level=3) at joinrels.c:67
67 List **joinrels = root->join_rel_level;
(gdb)
跟蹤populate_joinrel_with_paths
(gdb) b populate_joinrel_with_paths
Breakpoint 2 at 0x75646d: file joinrels.c, line 780.
進入populate_joinrel_with_paths函式
(gdb) c
Continuing.
Breakpoint 2, populate_joinrel_with_paths (root=0x1cae678, rel1=0x1d10978, rel2=0x1d09610, joinrel=0x1d131b8,
sjinfo=0x7ffef59baf20, restrictlist=0x1d135e8) at joinrels.c:780
780 switch (sjinfo->jointype)
檢視輸入引數
1.root:simple_rte_array陣列,其中simple_rel_array_size = 6,存在6個Item,1->16734/t_dwxx,3->16742/t_grxx,4->16747/t_jfxx
2.rel1:1號和3號連線生成的Relation,即t_dwxx和t_grxx連線
3.rel2:4號RTE,即t_jfxx
4.joinrel:rel1和rel2透過build_join_rel函式生成的連線Relation
5.sjinfo:連線資訊,連線型別為內連線JOIN_INNER
6.restrictlist:約束條件連結串列,t_grxx.grbh=t_jfxx.grbh
(gdb) p *root
$3 = {type = T_PlannerInfo, parse = 0x1cd7830, glob = 0x1cb8d38, query_level = 1, parent_root = 0x0, plan_params = 0x0,
outer_params = 0x0, simple_rel_array = 0x1d07af8, simple_rel_array_size = 6, simple_rte_array = 0x1d07b48,
all_baserels = 0x1d0ada8, nullable_baserels = 0x0, join_rel_list = 0x1d10e48, join_rel_hash = 0x0,
join_rel_level = 0x1d10930, join_cur_level = 3, init_plans = 0x0, cte_plan_ids = 0x0, multiexpr_params = 0x0,
eq_classes = 0x1d0a6d8, canon_pathkeys = 0x1d0ad28, left_join_clauses = 0x0, right_join_clauses = 0x0,
full_join_clauses = 0x0, join_info_list = 0x0, append_rel_list = 0x0, rowMarks = 0x0, placeholder_list = 0x0,
fkey_list = 0x0, query_pathkeys = 0x1d0ad78, group_pathkeys = 0x0, window_pathkeys = 0x0, distinct_pathkeys = 0x0,
sort_pathkeys = 0x1d0ad78, part_schemes = 0x0, initial_rels = 0x1d108c0, upper_rels = {0x0, 0x0, 0x0, 0x0, 0x0, 0x0,
0x0}, upper_targets = {0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0}, processed_tlist = 0x1cbb608, grouping_map = 0x0,
minmax_aggs = 0x0, planner_cxt = 0x1bfa040, total_table_pages = 1427, tuple_fraction = 0, limit_tuples = -1,
qual_security_level = 0, inhTargetKind = INHKIND_NONE, hasJoinRTEs = true, hasLateralRTEs = false,
hasDeletedRTEs = false, hasHavingQual = false, hasPseudoConstantQuals = false, hasRecursion = false, wt_param_id = -1,
non_recursive_path = 0x0, curOuterRels = 0x0, curOuterParams = 0x0, join_search_private = 0x0, partColsUpdated = false}
(gdb) p *root->simple_rte_array[1]
$4 = {type = T_RangeTblEntry, rtekind = RTE_RELATION, relid = 16734, relkind = 114 'r', tablesample = 0x0, subquery = 0x0, ...
...
(gdb) p *rel1->relids
$10 = {nwords = 1, words = 0x1d10b8c}
(gdb) p *rel1->relids->words
$11 = 10
(gdb) p *rel2->relids->words
$13 = 16
(gdb) p *joinrel->relids->words
$15 = 26
(gdb) p *sjinfo
$16 = {type = T_SpecialJoinInfo, min_lefthand = 0x1d10b88, min_righthand = 0x1d09518, syn_lefthand = 0x1d10b88,
syn_righthand = 0x1d09518, jointype = JOIN_INNER, lhs_strict = false, delay_upper_joins = false, semi_can_btree = false,
semi_can_hash = false, semi_operators = 0x0, semi_rhs_exprs = 0x0}
...
(gdb) p *(Var *)((RelabelType *)$args->head->data.ptr_value)->arg
$34 = {xpr = {type = T_Var}, varno = 3, varattno = 2, vartype = 1043, vartypmod = 14, varcollid = 100, varlevelsup = 0,
varnoold = 3, varoattno = 2, location = 273} -->t_grxx.grbh
(gdb) p *(Var *)((RelabelType *)$args->head->next->data.ptr_value)->arg
$35 = {xpr = {type = T_Var}, varno = 4, varattno = 1, vartype = 1043, vartypmod = 14, varcollid = 100, varlevelsup = 0,
varnoold = 4, varoattno = 1, location = 283} -->t_jfxx.grbh
進入JOIN_INNER分支,呼叫函式add_paths_to_joinrel
(gdb)
789 add_paths_to_joinrel(root, joinrel, rel1, rel2,
進入add_paths_to_joinrel函式
(gdb) step
add_paths_to_joinrel (root=0x1cae678, joinrel=0x1d131b8, outerrel=0x1d10978, innerrel=0x1d09610, jointype=JOIN_INNER,
sjinfo=0x7ffef59baf20, restrictlist=0x1d135e8) at joinpath.c:126
126 bool mergejoin_allowed = true;
判斷內表是否已被驗證為唯一
162 switch (jointype)
(gdb)
182 extra.inner_unique = innerrel_is_unique(root,
(gdb)
189 break;
(gdb) p extra.inner_unique
$36 = false
尋找潛在的mergejoin條件。如果不允許Merge Join,則跳過
merge join的條件是t_grxx.grbh=t_jfxx.grbh
(gdb) n
198 if (enable_mergejoin || jointype == JOIN_FULL)
(gdb)
199 extra.mergeclause_list = select_mergejoin_clauses(root,
(gdb)
211 if (jointype == JOIN_SEMI || jointype == JOIN_ANTI || extra.inner_unique)
(gdb) p *(Var *)((RelabelType *)$args->head->data.ptr_value)->arg
$47 = {xpr = {type = T_Var}, varno = 3, varattno = 2, vartype = 1043, vartypmod = 14, varcollid = 100, varlevelsup = 0,
varnoold = 3, varoattno = 2, location = 273} -->t_grxx.grbh
(gdb) p *(Var *)((RelabelType *)$args->head->next->data.ptr_value)->arg
$48 = {xpr = {type = T_Var}, varno = 4, varattno = 1, vartype = 1043, vartypmod = 14, varcollid = 100, varlevelsup = 0,
varnoold = 4, varoattno = 1, location = 283} -->t_jfxx.grbh
確定為這個連線生成引數化路徑是否合理，如果是，這些路徑應該需要哪些關係(結果為:NULL)
(gdb)
261 extra.param_source_rels = bms_add_members(extra.param_source_rels,
(gdb)
268 if (mergejoin_allowed)
(gdb) p *extra.param_source_rels
Cannot access memory at address 0x0
嘗試merge join訪問路徑，其中兩個關係必須執行顯式的排序.
注:joinrel->pathlist在執行前為NULL,執行後生成了訪問路徑.
(gdb) p *joinrel->pathlist
Cannot access memory at address 0x0
(gdb) n
269 sort_inner_and_outer(root, joinrel, outerrel, innerrel,
(gdb)
279 if (mergejoin_allowed)
(gdb) p *joinrel->pathlist
$50 = {type = T_List, length = 1, head = 0x1d13850, tail = 0x1d13850}
其他實現邏輯類似,sort_inner_and_outer等函式的實現邏輯,後續再行詳細解讀.
最終結果是生成了2條訪問路徑,儲存在pathlist連結串列中.
324 if (set_join_pathlist_hook)
(gdb)
327 }
(gdb) p *joinrel->pathlist
$51 = {type = T_List, length = 2, head = 0x1d13850, tail = 0x1d13930}
(gdb) p *(Node *)joinrel->pathlist->head->data.ptr_value
$52 = {type = T_HashPath}
(gdb) p *(HashPath *)joinrel->pathlist->head->data.ptr_value
$53 = {jpath = {path = {type = T_HashPath, pathtype = T_HashJoin, parent = 0x1d131b8, pathtarget = 0x1d133c8,
param_info = 0x0, parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 99850,
startup_cost = 3762, total_cost = 10075.348750000001, pathkeys = 0x0}, jointype = JOIN_INNER, inner_unique = false,
outerjoinpath = 0x1d11f48, innerjoinpath = 0x1d0f548, joinrestrictinfo = 0x1d135e8}, path_hashclauses = 0x1d13aa0,
num_batches = 2, inner_rows_total = 100000}
(gdb) p *(Node *)joinrel->pathlist->head->next->data.ptr_value
$54 = {type = T_NestPath}
(gdb) p *(NestPath *)joinrel->pathlist->head->next->data.ptr_value
$55 = {path = {type = T_NestPath, pathtype = T_NestLoop, parent = 0x1d131b8, pathtarget = 0x1d133c8, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 99850, startup_cost = 39.801122856046675,
total_cost = 41318.966172885761, pathkeys = 0x1d0b818}, jointype = JOIN_INNER, inner_unique = false,
outerjoinpath = 0x1d119d8, innerjoinpath = 0x1d0f9d8, joinrestrictinfo = 0x0}
DONE!
四、參考資料
allpaths.c
cost.h
costsize.c
PG Document:Query Planning
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6906/viewspace-2374835/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PostgreSQL 原始碼解讀（70）- 查詢語句#55（make_one_rel函式#20-...SQL原始碼函式
- PostgreSQL 原始碼解讀（71）- 查詢語句#56（make_one_rel函式#21-...SQL原始碼函式
- PostgreSQL 原始碼解讀（67）- 查詢語句#52（make_one_rel函式#17-...SQL原始碼函式
- PostgreSQL 原始碼解讀（68）- 查詢語句#53（make_one_rel函式#18-...SQL原始碼函式
- PostgreSQL 原始碼解讀（69）- 查詢語句#54（make_one_rel函式#19-...SQL原始碼函式
- PostgreSQL 原始碼解讀（66）- 查詢語句#51（make_one_rel函式#16-...SQL原始碼函式
- PostgreSQL 原始碼解讀（72）- 查詢語句#57（make_one_rel函式#22-...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 原始碼解讀（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 原始碼解讀（80）- 查詢語句#65（create_plan函式#4-Jo...SQL原始碼函式
- PostgreSQL 原始碼解讀（49）- 查詢語句#34（make_one_rel函式#1-概覽）SQL原始碼函式
- PostgreSQL 原始碼解讀（83）- 查詢語句#68（PortalStart函式）SQL原始碼函式
- PostgreSQL 原始碼解讀（82）- 查詢語句#67（PortalXXX系列函式）SQL原始碼函式
- PostgreSQL 原始碼解讀（89）- 查詢語句#74（SeqNext函式#2）SQL原始碼函式
- PostgreSQL 原始碼解讀（90）- 查詢語句#75（ExecHashJoin函式#1）SQL原始碼函式
- PostgreSQL 原始碼解讀（91）- 查詢語句#76（ExecHashJoin函式#2）SQL原始碼函式
- PostgreSQL 原始碼解讀（88）- 查詢語句#73（SeqNext函式#1）SQL原始碼函式
- PostgreSQL 原始碼解讀（93）- 查詢語句#77（ExecHashJoin函式#3）SQL原始碼函式
- PostgreSQL 原始碼解讀（52）- 查詢語句#37（make_one_rel函式#2-估算關係大小）SQL原始碼函式
- PostgreSQL 原始碼解讀（73）- 查詢語句#58（grouping_planner函式...SQL原始碼函式
- PostgreSQL 原始碼解讀（50）- 查詢語句#35（Optimizer Review#1）SQL原始碼View
- PostgreSQL 原始碼解讀（95）- 查詢語句#78（ExecHashJoin函式#4-H...SQL原始碼函式
- PostgreSQL 原始碼解讀（97）- 查詢語句#79（ExecHashJoin函式#5-H...SQL原始碼函式
- PostgreSQL 原始碼解讀（43）- 查詢語句#28（query_planner函式#5）SQL原始碼函式
- PostgreSQL 原始碼解讀（45）- 查詢語句#30（query_planner函式#6）SQL原始碼函式
- PostgreSQL 原始碼解讀（46）- 查詢語句#31（query_planner函式#7）SQL原始碼函式
- PostgreSQL 原始碼解讀（47）- 查詢語句#32（query_planner函式#8）SQL原始碼函式