PostgreSQL 原始碼解讀（62）- 查詢語句#47（make_one_rel函式#12-...
本節大體介紹了make_one_rel函式中的make_rel_from_joinlist函式，該函式根據連線關係連結串列（joinlist）構建連線路徑。
一、原始碼解讀
make_rel_from_joinlist函式根據連線關係連結串列（joinlist）透過外部演算法(鉤子函式)/遺傳演算法/動態規劃演算法構建連線路徑,其中joinlist連結串列在主函式中已透過呼叫deconstruct_jointree函式生成.
動態規劃演算法的實現standard_join_search函式以及遺傳演算法在後續章節再行介紹.
/*
* make_rel_from_joinlist
* Build access paths using a "joinlist" to guide the join path search.
* 依據deconstruct_jointree函式構造的joinlist生成連線路徑.
* joinlist詳細的資料結構參照deconstruct_jointree函式註釋
*
* See comments for deconstruct_jointree() for definition of the joinlist
* data structure.
*/
static RelOptInfo *
make_rel_from_joinlist(PlannerInfo *root, List *joinlist)
{
int levels_needed;
List *initial_rels;
ListCell *jl;
/*
* Count the number of child joinlist nodes. This is the depth of the
* dynamic-programming algorithm we must employ to consider all ways of
* joining the child nodes.
* 計算joinlist連結串列中節點的個數。
* 確定使用的演算法(動態規劃演算法 vs 遺傳演算法),如個數<閾值,則考慮所有連線的方式。
*/
levels_needed = list_length(joinlist);
if (levels_needed <= 0)
return NULL; /* nothing to do? */
/*
* Construct a list of rels corresponding to the child joinlist nodes.
* This may contain both base rels and rels constructed according to
* sub-joinlists.
* 構造與joinlist中元素相對應的rels連結串列。
* 這可能包括base rels和透過子連線構造的base rels。
*/
initial_rels = NIL;
foreach(jl, joinlist)//遍歷連結串列
{
Node *jlnode = (Node *) lfirst(jl);
RelOptInfo *thisrel;
if (IsA(jlnode, RangeTblRef))//RTR
{
int varno = ((RangeTblRef *) jlnode)->rtindex;
thisrel = find_base_rel(root, varno);//根據編號找到相應的RelOptInfo
}
else if (IsA(jlnode, List))//連結串列
{
/* Recurse to handle subproblem */
thisrel = make_rel_from_joinlist(root, (List *) jlnode);//遞迴呼叫,形成新的base rel
}
else//其他型別,出錯
{
elog(ERROR, "unrecognized joinlist node type: %d",
(int) nodeTag(jlnode));
thisrel = NULL; /* keep compiler quiet */
}
initial_rels = lappend(initial_rels, thisrel);//新增到base rel連結串列中
}
if (levels_needed == 1)//連線連結串列只有1個元素
{
/*
* Single joinlist node, so we're done.
*/
return (RelOptInfo *) linitial(initial_rels);//直接返回
}
else//>1個元素
{
/*
* Consider the different orders in which we could join the rels,
* using a plugin, GEQO, or the regular join search code.
* 考慮不同的連線順序->使用外部演算法/GEQO遺傳演算法/動態規劃演算法。
*
* We put the initial_rels list into a PlannerInfo field because
* has_legal_joinclause() needs to look at it (ugly :-().
*
*/
root->initial_rels = initial_rels;
if (join_search_hook)//呼叫鉤子函式
return (*join_search_hook) (root, levels_needed, initial_rels);
else if (enable_geqo && levels_needed >= geqo_threshold)
return geqo(root, levels_needed, initial_rels);//遺傳演算法
else
return standard_join_search(root, levels_needed, initial_rels);//動態規劃演算法
}
}
//----------------------------------------------------------------------- standard_join_search
/*
* standard_join_search
* Find possible joinpaths for a query by successively finding ways
* to join component relations into join relations.
* 透過動態規劃演算法為查詢語句構造連線路徑.
*
* 'levels_needed' is the number of iterations needed, ie, the number of
* independent jointree items in the query. This is > 1.
* levels_needed-連線連結串列中的節點個數,>1
*
* 'initial_rels' is a list of RelOptInfo nodes for each independent
* jointree item. These are the components to be joined together.
* Note that levels_needed == list_length(initial_rels).
* initial_rels-與連線樹每個元素相對應的RelOptInfo節點
*
* Returns the final level of join relations, i.e., the relation that is
* the result of joining all the original relations together.
* At least one implementation path must be provided for this relation and
* all required sub-relations.
* 返回連線的最終關係(最頂層的Relation):將所有原始關係連線在一起的最終結果。
* 最佳化器為該關係及其所必需的子關係提供至少一個的實現路徑。
*
* To support loadable plugins that modify planner behavior by changing the
* join searching algorithm, we provide a hook variable that lets a plugin
* replace or supplement this function. Any such hook must return the same
* final join relation as the standard code would, but it might have a
* different set of implementation paths attached, and only the sub-joinrels
* needed for these paths need have been instantiated.
* 為了支援自定義函式，PG提供了一個鉤子變數，允許外部外掛替換或填充這個函式。
* 任何這樣的鉤子都必須返回與PG標準函式相同的最終連線關係，
* 但是它可能附加了一組不同的實現路徑，並且只例項化了這些路徑所需的子連線。
*
* Note to plugin authors: the functions invoked during standard_join_search()
* modify root->join_rel_list and root->join_rel_hash. If you want to do more
* than one join-order search, you'll probably need to save and restore the
* original states of those data structures. See geqo_eval() for an example.
*/
RelOptInfo *
standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels)
{
int lev;
RelOptInfo *rel;
/*
* This function cannot be invoked recursively within any one planning
* problem, so join_rel_level[] can't be in use already.
*/
Assert(root->join_rel_level == NULL);//驗證
/*
* We employ a simple "dynamic programming" algorithm: we first find all
* ways to build joins of two jointree items, then all ways to build joins
* of three items (from two-item joins and single items), then four-item
* joins, and so on until we have considered all ways to join all the
* items into one rel.
* PG實現了一種簡單的動態規劃演算法:首先為連線樹中的兩個Relation建立可能的連線路徑
* 然後為三個Relation建立所有可能的連線路徑,以此類推直至已為所有的Relation建立了
* 連線路徑,從而得到最終的關係(final_rel)
*
* root->join_rel_level[j] is a list of all the j-item rels. Initially we
* set root->join_rel_level[1] to represent all the single-jointree-item
* relations.
* 設定root->join_rel_level陣列,[j]是所有j-item rels的連結串列(即1個item的放在[1]中)
*/
root->join_rel_level = (List **) palloc0((levels_needed + 1) * sizeof(List *));
root->join_rel_level[1] = initial_rels;//1個item對應的rel連結串列
for (lev = 2; lev <= levels_needed; lev++)//構造2->N個item對應的rel連結串列
{
ListCell *lc;
/*
* Determine all possible pairs of relations to be joined at this
* level, and build paths for making each one from every available
* pair of lower-level relations.
* 確定在此級別上要連線的所有可能的關係對，並構建訪問路徑，
* 以從每一對可用的較低階關係中往上建立關係。
*/
join_search_one_level(root, lev);
/*
* Run generate_partitionwise_join_paths() and generate_gather_paths()
* for each just-processed joinrel. We could not do this earlier
* because both regular and partial paths can get added to a
* particular joinrel at multiple times within join_search_one_level.
* 迴圈呼叫generate_partitionwise_join_paths()和generate_collect _paths()函式:
* 引數為上一步驟生成的連結串列中的每個元素。
* 由於常規路徑和部分路徑都可以在join_search_one_level中多次新增joinrel,因此在此處呼叫。
*
* After that, we're done creating paths for the joinrel, so run
* set_cheapest().
* 在此之後,PG已為joinrel(連線生成的新關係)建立了訪問路徑,因此可以呼叫函式set_cheapest
*
*/
foreach(lc, root->join_rel_level[lev])//遍歷連結串列
{
rel = (RelOptInfo *) lfirst(lc);//新生成的關係
/* Create paths for partitionwise joins. */
generate_partitionwise_join_paths(root, rel);//建立partitionwise路徑
/*
* Except for the topmost scan/join rel, consider gathering
* partial paths. We'll do the same for the topmost scan/join rel
* once we know the final targetlist (see grouping_planner).
*/
if (lev < levels_needed)
generate_gather_paths(root, rel, false);//並行執行需考慮gathering
/* Find and save the cheapest paths for this rel */
set_cheapest(rel);//從形成該joinrel的所有路徑中找到成本最低的
#ifdef OPTIMIZER_DEBUG
debug_print_rel(root, rel);//DEBUG資訊
#endif
}
}
/*
* We should have a single rel at the final level.
* 連線的最終結果,只有一個RelOptInfo
*/
if (root->join_rel_level[levels_needed] == NIL)
elog(ERROR, "failed to build any %d-way joins", levels_needed);
Assert(list_length(root->join_rel_level[levels_needed]) == 1);
rel = (RelOptInfo *) linitial(root->join_rel_level[levels_needed]);//獲取最終結果
root->join_rel_level = NULL;//重置
return rel;//返回
}
//----------------------------------------------------------------------- geqo
/*
* geqo
* solution of the query optimization problem
* similar to a constrained Traveling Salesman Problem (TSP)
* 遺傳演算法:可參考TSP的求解演算法.
* TSP-旅行推銷員問題（最短路徑問題）:
* 給定一系列城市和每對城市之間的距離，求解訪問每一座城市一次並回到起始城市的最短迴路。
*/
RelOptInfo *
geqo(PlannerInfo *root, int number_of_rels, List *initial_rels)
{
GeqoPrivateData private;
int generation;
Chromosome *momma;
Chromosome *daddy;
Chromosome *kid;
Pool *pool;
int pool_size,
number_generations;
#ifdef GEQO_DEBUG
int status_interval;
#endif
Gene *best_tour;
RelOptInfo *best_rel;
#if defined(ERX)
Edge *edge_table; /* list of edges */
int edge_failures = 0;
#endif
#if defined(CX) || defined(PX) || defined(OX1) || defined(OX2)
City *city_table; /* list of cities */
#endif
#if defined(CX)
int cycle_diffs = 0;
int mutations = 0;
#endif
/* set up private information */
root->join_search_private = (void *) &private;
private.initial_rels = initial_rels;
/* initialize private number generator */
geqo_set_seed(root, Geqo_seed);
/* set GA parameters */
pool_size = gimme_pool_size(number_of_rels);
number_generations = gimme_number_generations(pool_size);
#ifdef GEQO_DEBUG
status_interval = 10;
#endif
/* allocate genetic pool memory */
pool = alloc_pool(root, pool_size, number_of_rels);
/* random initialization of the pool */
random_init_pool(root, pool);
/* sort the pool according to cheapest path as fitness */
sort_pool(root, pool); /* we have to do it only one time, since all
* kids replace the worst individuals in
* future (-> geqo_pool.c:spread_chromo ) */
#ifdef GEQO_DEBUG
elog(DEBUG1, "GEQO selected %d pool entries, best %.2f, worst %.2f",
pool_size,
pool->data[0].worth,
pool->data[pool_size - 1].worth);
#endif
/* allocate chromosome momma and daddy memory */
momma = alloc_chromo(root, pool->string_length);
daddy = alloc_chromo(root, pool->string_length);
#if defined (ERX)
#ifdef GEQO_DEBUG
elog(DEBUG2, "using edge recombination crossover [ERX]");
#endif
/* allocate edge table memory */
edge_table = alloc_edge_table(root, pool->string_length);
#elif defined(PMX)
#ifdef GEQO_DEBUG
elog(DEBUG2, "using partially matched crossover [PMX]");
#endif
/* allocate chromosome kid memory */
kid = alloc_chromo(root, pool->string_length);
#elif defined(CX)
#ifdef GEQO_DEBUG
elog(DEBUG2, "using cycle crossover [CX]");
#endif
/* allocate city table memory */
kid = alloc_chromo(root, pool->string_length);
city_table = alloc_city_table(root, pool->string_length);
#elif defined(PX)
#ifdef GEQO_DEBUG
elog(DEBUG2, "using position crossover [PX]");
#endif
/* allocate city table memory */
kid = alloc_chromo(root, pool->string_length);
city_table = alloc_city_table(root, pool->string_length);
#elif defined(OX1)
#ifdef GEQO_DEBUG
elog(DEBUG2, "using order crossover [OX1]");
#endif
/* allocate city table memory */
kid = alloc_chromo(root, pool->string_length);
city_table = alloc_city_table(root, pool->string_length);
#elif defined(OX2)
#ifdef GEQO_DEBUG
elog(DEBUG2, "using order crossover [OX2]");
#endif
/* allocate city table memory */
kid = alloc_chromo(root, pool->string_length);
city_table = alloc_city_table(root, pool->string_length);
#endif
/* my pain main part: */
/* iterative optimization */
for (generation = 0; generation < number_generations; generation++)
{
/* SELECTION: using linear bias function */
geqo_selection(root, momma, daddy, pool, Geqo_selection_bias);
#if defined (ERX)
/* EDGE RECOMBINATION CROSSOVER */
gimme_edge_table(root, momma->string, daddy->string, pool->string_length, edge_table);
kid = momma;
/* are there any edge failures ? */
edge_failures += gimme_tour(root, edge_table, kid->string, pool->string_length);
#elif defined(PMX)
/* PARTIALLY MATCHED CROSSOVER */
pmx(root, momma->string, daddy->string, kid->string, pool->string_length);
#elif defined(CX)
/* CYCLE CROSSOVER */
cycle_diffs = cx(root, momma->string, daddy->string, kid->string, pool->string_length, city_table);
/* mutate the child */
if (cycle_diffs == 0)
{
mutations++;
geqo_mutation(root, kid->string, pool->string_length);
}
#elif defined(PX)
/* POSITION CROSSOVER */
px(root, momma->string, daddy->string, kid->string, pool->string_length, city_table);
#elif defined(OX1)
/* ORDER CROSSOVER */
ox1(root, momma->string, daddy->string, kid->string, pool->string_length, city_table);
#elif defined(OX2)
/* ORDER CROSSOVER */
ox2(root, momma->string, daddy->string, kid->string, pool->string_length, city_table);
#endif
/* EVALUATE FITNESS */
kid->worth = geqo_eval(root, kid->string, pool->string_length);
/* push the kid into the wilderness of life according to its worth */
spread_chromo(root, kid, pool);
#ifdef GEQO_DEBUG
if (status_interval && !(generation % status_interval))
print_gen(stdout, pool, generation);
#endif
}
#if defined(ERX) && defined(GEQO_DEBUG)
if (edge_failures != 0)
elog(LOG, "[GEQO] failures: %d, average: %d",
edge_failures, (int) number_generations / edge_failures);
else
elog(LOG, "[GEQO] no edge failures detected");
#endif
#if defined(CX) && defined(GEQO_DEBUG)
if (mutations != 0)
elog(LOG, "[GEQO] mutations: %d, generations: %d",
mutations, number_generations);
else
elog(LOG, "[GEQO] no mutations processed");
#endif
#ifdef GEQO_DEBUG
print_pool(stdout, pool, 0, pool_size - 1);
#endif
#ifdef GEQO_DEBUG
elog(DEBUG1, "GEQO best is %.2f after %d generations",
pool->data[0].worth, number_generations);
#endif
/*
* got the cheapest query tree processed by geqo; first element of the
* population indicates the best query tree
*/
best_tour = (Gene *) pool->data[0].string;
best_rel = gimme_tree(root, best_tour, pool->string_length);
if (best_rel == NULL)
elog(ERROR, "geqo failed to make a valid plan");
/* DBG: show the query plan */
#ifdef NOT_USED
print_plan(best_plan, root);
#endif
/* ... free memory stuff */
free_chromo(root, momma);
free_chromo(root, daddy);
#if defined (ERX)
free_edge_table(root, edge_table);
#elif defined(PMX)
free_chromo(root, kid);
#elif defined(CX)
free_chromo(root, kid);
free_city_table(root, city_table);
#elif defined(PX)
free_chromo(root, kid);
free_city_table(root, city_table);
#elif defined(OX1)
free_chromo(root, kid);
free_city_table(root, city_table);
#elif defined(OX2)
free_chromo(root, kid);
free_city_table(root, city_table);
#endif
free_pool(root, pool);
/* ... clear root pointer to our private storage */
root->join_search_private = NULL;
return best_rel;
}
二、跟蹤分析
測試指令碼以及執行計劃如下:
testdb=# explain verbose select a.*,b.grbh,b.je
testdb-# from t_dwxx a,
testdb-# lateral (select t1.dwbh,t1.grbh,t2.je
testdb(# from t_grxx t1
testdb(# inner join t_jfxx t2 on t1.dwbh = a.dwbh and t1.grbh = t2.grbh) b
testdb-# where a.dwbh = '1001'
testdb-# order by b.dwbh;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.87..111.89 rows=10 width=37)
Output: a.dwmc, a.dwbh, a.dwdz, t1.grbh, t2.je, t1.dwbh
-> Nested Loop (cost=0.58..28.69 rows=10 width=29)
Output: a.dwmc, a.dwbh, a.dwdz, t1.grbh, t1.dwbh
-> Index Scan using t_dwxx_pkey on public.t_dwxx a (cost=0.29..8.30 rows=1 width=20)
Output: a.dwmc, a.dwbh, a.dwdz
Index Cond: ((a.dwbh)::text = '1001'::text)
-> Index Scan using idx_t_grxx_dwbh on public.t_grxx t1 (cost=0.29..20.29 rows=10 width=9)
Output: t1.dwbh, t1.grbh, t1.xm, t1.xb, t1.nl
Index Cond: ((t1.dwbh)::text = '1001'::text)
-> Index Scan using idx_t_jfxx_grbh on public.t_jfxx t2 (cost=0.29..8.31 rows=1 width=13)
Output: t2.grbh, t2.ny, t2.je
Index Cond: ((t2.grbh)::text = (t1.grbh)::text)
啟動gdb跟蹤
(gdb) b make_rel_from_joinlist
Breakpoint 1 at 0x73f0d3: file allpaths.c, line 2617.
(gdb) c
Continuing.
Breakpoint 1, make_rel_from_joinlist (root=0x176c750, joinlist=0x179e480) at allpaths.c:2617
2617 levels_needed = list_length(joinlist);
進入make_rel_from_joinlist函式,檢視joinlist,連結串列中的Node為RangeTblRef,rindex分別是1/3/4
(gdb) p *joinlist
$1 = {type = T_List, length = 3, head = 0x17a0448, tail = 0x17a0408}
(gdb) p *(Node *)joinlist->head->data.ptr_value
$2 = {type = T_RangeTblRef}
(gdb) p *(RangeTblRef *)joinlist->head->data.ptr_value
$3 = {type = T_RangeTblRef, rtindex = 1}
(gdb) p *(RangeTblRef *)joinlist->head->next->data.ptr_value
$4 = {type = T_RangeTblRef, rtindex = 3}
(gdb) p *(RangeTblRef *)joinlist->head->next->next->data.ptr_value
$5 = {type = T_RangeTblRef, rtindex = 4}
連結串列中的Node個數為3,levels_needed=3
(gdb) n
2619 if (levels_needed <= 0)
(gdb) p levels_needed
$6 = 3
遍歷連結串列,構造RelOptInfo,新增到initial_rels中
(gdb)
2628 foreach(jl, joinlist)
...
(gdb)
2637 thisrel = find_base_rel(root, varno);
(gdb)
2651 initial_rels = lappend(initial_rels, thisrel);
完成遍歷後,開始構造連線路徑.
遺傳演算法的rels閾值為12(透過GUC引數配置)
2672 if (join_search_hook)
(gdb)
2674 else if (enable_geqo && levels_needed >= geqo_threshold)
(gdb)
2677 return standard_join_search(root, levels_needed, initial_rels);
(gdb) p geqo_threshold
$7 = 12
進入函式standard_join_search
(gdb) step
standard_join_search (root=0x176c750, levels_needed=3, initial_rels=0x17a6308) at allpaths.c:2733
2733 root->join_rel_level = (List **) palloc0((levels_needed + 1) * sizeof(List *));
開始構造2->N個item對應的rel連結串列
...
(gdb)
2746 join_search_one_level(root, lev);
(gdb) n
2757 foreach(lc, root->join_rel_level[lev])
呼叫函式join_search_one_level,檢視root->join_rel_level[j]
(gdb) p *root->join_rel_level[2]
$10 = {type = T_List, length = 2, head = 0x17a67a8, tail = 0x17a6ec0}
檢視連結串列中的RelOptInfo
(gdb) p *(RelOptInfo *)root->join_rel_level[2]->head->data.ptr_value
$12 = {type = T_RelOptInfo, reloptkind = RELOPT_JOINREL, relids = 0x17a65d0, rows = 10, consider_startup = false,
consider_param_startup = false, consider_parallel = true, reltarget = 0x17a65e8, pathlist = 0x17a68a8, 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 = true,
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}
(gdb) p *(RelOptInfo *)root->join_rel_level[2]->head->next->data.ptr_value
$13 = {type = T_RelOptInfo, reloptkind = RELOPT_JOINREL, relids = 0x17a68d8, rows = 10, consider_startup = false,
consider_param_startup = false, consider_parallel = true, reltarget = 0x17a6cd0, pathlist = 0x17a7720, 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 = true,
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中的relids
透過relids可知,第一個RelOptInfo是1/3號rel連線生成的Relation,第二個RelOptInfo是3/4號rel連線生成的Relation
(gdb) set $roi1=(RelOptInfo *)root->join_rel_level[2]->head->data.ptr_value
(gdb) set $roi2=(RelOptInfo *)root->join_rel_level[2]->head->next->data.ptr_value
(gdb) p *$roi1->relids
$16 = {nwords = 1, words = 0x17a65d4}
(gdb) p *$roi1->relids->words
$17 = 10 -->2 + 8 --> 1/3 號rel
(gdb) p *$roi2->relids->words
$18 = 24 -->8 + 16 --> 3/4號rel
檢視第一個RelOptInfo中的pathlist,該連結串列有2個Node,型別均為T_NestPath(巢狀連線),總成本分別是28.69和4308.57
(gdb) p *$roi1->pathlist
$19 = {type = T_List, length = 2, head = 0x17a6888, tail = 0x17a6a10}
(gdb) p *(Node *)$roi1->pathlist->head->data.ptr_value
$20 = {type = T_NestPath}
(gdb) p *(NestPath *)$roi1->pathlist->head->data.ptr_value
$21 = {path = {type = T_NestPath, pathtype = T_NestLoop, parent = 0x17a63c0, pathtarget = 0x17a65e8, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 10, startup_cost = 0.57750000000000001,
total_cost = 28.688484322533327, pathkeys = 0x0}, jointype = JOIN_INNER, inner_unique = false,
outerjoinpath = 0x17a2638, innerjoinpath = 0x17a2908, joinrestrictinfo = 0x0}
(gdb) p *(Node *)$roi1->pathlist->head->next->data.ptr_value
$22 = {type = T_NestPath}
(gdb) p *(NestPath *)$roi1->pathlist->head->next->data.ptr_value
$23 = {path = {type = T_NestPath, pathtype = T_NestLoop, parent = 0x17a63c0, pathtarget = 0x17a65e8, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 10, startup_cost = 0.57750000000000001,
total_cost = 4308.5748727883229, pathkeys = 0x17a3650}, jointype = JOIN_INNER, inner_unique = false,
outerjoinpath = 0x17a3190, innerjoinpath = 0x17a68f0, joinrestrictinfo = 0x0}
檢視第二個RelOptInfo中的pathlist,只有1個Node,型別為T_NestPath(巢狀連線),總成本為103.49
(gdb) p *$roi2->pathlist
$24 = {type = T_List, length = 1, head = 0x17a7700, tail = 0x17a7700}
(gdb) p *(Node *)$roi2->pathlist->head->data.ptr_value
$27 = {type = T_NestPath}
(gdb) p *(NestPath *)$roi2->pathlist->head->data.ptr_value
$28 = {path = {type = T_NestPath, pathtype = T_NestLoop, parent = 0x17a6ac0, pathtarget = 0x17a6cd0, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 10, startup_cost = 0.58499999999999996,
total_cost = 103.48598432253331, pathkeys = 0x0}, jointype = JOIN_INNER, inner_unique = false,
outerjoinpath = 0x17a2908, innerjoinpath = 0x17a5470, joinrestrictinfo = 0x0}
透過set_cheapest函式設定成本最低的訪問路徑,結果儲存在cheapest_startup_path和cheapest_total_path中
(gdb)
2773 set_cheapest(rel);
(gdb)
2757 foreach(lc, root->join_rel_level[lev])
...
(gdb) p *$roi1
$35 = ..., cheapest_startup_path = 0x17a67f8, cheapest_total_path = 0x17a67f8, ...
(gdb) p *$roi2
$36 =..., cheapest_startup_path = 0x17a7750, cheapest_total_path = 0x17a7750, ...
繼續迴圈,這時候lev=3
(gdb) n
2737 for (lev = 2; lev <= levels_needed; lev++)
(gdb) n
2746 join_search_one_level(root, lev);
(gdb) p lev
$38 = 3
得到3張表連線的final_rel
(gdb) p *root->join_rel_level[3]
$41 = {type = T_List, length = 1, head = 0x17a8090, tail = 0x17a8090}
(gdb) p *(RelOptInfo *)root->join_rel_level[3]->head->data.ptr_value
$42 = {type = T_RelOptInfo, reloptkind = RELOPT_JOINREL, relids = 0x17a74d8, rows = 10, consider_startup = false,
consider_param_startup = false, consider_parallel = true, reltarget = 0x17a7e40, pathlist = 0x17a8258, 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}
檢視pathlist,只有1個元素,型別為NestPath,該訪問路徑成本為111.89
(gdb) set $roi=(RelOptInfo *)root->join_rel_level[3]->head->data.ptr_value
(gdb) p *$roi->pathlist
$44 = {type = T_List, length = 1, head = 0x17a8238, tail = 0x17a8238}
(gdb) p *(Node *)$roi->pathlist->head->data.ptr_value
$45 = {type = T_NestPath}
(gdb) p *(NestPath *)$roi->pathlist->head->data.ptr_value
$46 = {path = {type = T_NestPath, pathtype = T_NestLoop, parent = 0x17a7c30, pathtarget = 0x17a7e40, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 10, startup_cost = 0.87,
total_cost = 111.88848432253332, pathkeys = 0x0}, jointype = JOIN_INNER, inner_unique = false,
outerjoinpath = 0x17a67f8, innerjoinpath = 0x17a5470, joinrestrictinfo = 0x0}
獲得最終結果
...
2792 return rel;
(gdb) p *rel
$47 = {type = T_RelOptInfo, reloptkind = RELOPT_JOINREL, relids = 0x17a74d8, rows = 10, consider_startup = false,
consider_param_startup = false, consider_parallel = true, reltarget = 0x17a7e40, pathlist = 0x17a8258, ppilist = 0x0,
partial_pathlist = 0x0, cheapest_startup_path = 0x17a8318, cheapest_total_path = 0x17a8318, cheapest_unique_path = 0x0,
cheapest_parameterized_paths = 0x17a89b0, 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}
(gdb) p *rel->cheapest_total_path
$48 = {type = T_NestPath, pathtype = T_NestLoop, parent = 0x17a7c30, pathtarget = 0x17a7e40, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 10, startup_cost = 0.87,
total_cost = 111.88848432253332, pathkeys = 0x0}
DONE!
三、參考資料
allpaths.c
cost.h
costsize.c
PG Document:Query Planning
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6906/viewspace-2374839/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PostgreSQL 原始碼解讀（47）- 查詢語句#32（query_planner函式#8）SQL原始碼函式
- 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 原始碼解讀（65）- 查詢語句#50（make_one_rel函式#15-...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 原始碼解讀（49）- 查詢語句#34（make_one_rel函式#1-概覽）SQL原始碼函式
- PostgreSQL 原始碼解讀（77）- 查詢語句#62（create_plan函式#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 原始碼解讀（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 原始碼解讀（48）- 查詢語句#33（query_planner函式#9）SQL原始碼函式