这篇文章主要介绍“PostgreSQL中make_rel_from_joinlist函数分析”，在日常操作中，相信很多人在PostgreSQL中make_rel_from_joinlist函数分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL中make_rel_from_joinlist函数分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

make_rel_from_joinlist函数根据连接关系链表（joinlist）通过外部算法(钩子函数)/遗传算法/动态规划算法构建连接路径,其中joinlist链表在主函数中已通过调用deconstruct_jointree函数生成.
动态规划算法的实现standard_join_search函数以及遗传算法在后续章节再行介绍.

/**make_rel_from_joinlist*Buildaccesspathsusinga"joinlist"toguidethejoinpathsearch.*依据deconstruct_jointree函数构造的joinlist生成连接路径.*joinlist详细的数据结构参照deconstruct_jointree函数注释**Seecommentsfordeconstruct_jointree()fordefinitionofthejoinlist*datastructure.*/staticRelOptInfo*make_rel_from_joinlist(PlannerInfo*root,List*joinlist){intlevels_needed;List*initial_rels;ListCell*jl;/**Countthenumberofchildjoinlistnodes.Thisisthedepthofthe*dynamic-programmingalgorithmwemustemploytoconsiderallwaysof*joiningthechildnodes.*计算joinlist链表中节点的个数。*确定使用的算法(动态规划算法vs遗传算法),如个数<阈值,则考虑所有连接的方式。*/levels_needed=list_length(joinlist);if(levels_needed<=0)returnNULL;/*nothingtodo?*//**Constructalistofrelscorrespondingtothechildjoinlistnodes.*Thismaycontainbothbaserelsandrelsconstructedaccordingto*sub-joinlists.*构造与joinlist中元素相对应的rels链表。*这可能包括baserels和通过子连接构造的baserels。*/initial_rels=NIL;foreach(jl,joinlist)//遍历链表{Node*jlnode=(Node*)lfirst(jl);RelOptInfo*thisrel;if(IsA(jlnode,RangeTblRef))//RTR{intvarno=((RangeTblRef*)jlnode)->rtindex;thisrel=find_base_rel(root,varno);//根据编号找到相应的RelOptInfo}elseif(IsA(jlnode,List))//链表{/*Recursetohandlesubproblem*/thisrel=make_rel_from_joinlist(root,(List*)jlnode);//递归调用,形成新的baserel}else//其他类型,出错{elog(ERROR,"unrecognizedjoinlistnodetype:%d",(int)nodeTag(jlnode));thisrel=NULL;/*keepcompilerquiet*/}initial_rels=lappend(initial_rels,thisrel);//添加到baserel链表中}if(levels_needed==1)//连接链表只有1个元素{/**Singlejoinlistnode,sowe'redone.*/return(RelOptInfo*)linitial(initial_rels);//直接返回}else//>1个元素{/**Considerthedifferentordersinwhichwecouldjointherels,*usingaplugin,GEQO,ortheregularjoinsearchcode.*考虑不同的连接顺序->使用外部算法/GEQO遗传算法/动态规划算法。**Weputtheinitial_relslistintoaPlannerInfofieldbecause*has_legal_joinclause()needstolookatit(ugly:-().**/root->initial_rels=initial_rels;if(join_search_hook)//调用钩子函数return(*join_search_hook)(root,levels_needed,initial_rels);elseif(enable_geqo&&levels_needed>=geqo_threshold)returngeqo(root,levels_needed,initial_rels);//遗传算法elsereturnstandard_join_search(root,levels_needed,initial_rels);//动态规划算法}}//-----------------------------------------------------------------------standard_join_search/**standard_join_search*Findpossiblejoinpathsforaquerybysuccessivelyfindingways*tojoincomponentrelationsintojoinrelations.*通过动态规划算法为查询语句构造连接路径.**'levels_needed'isthenumberofiterationsneeded,ie,thenumberof*independentjointreeitemsinthequery.Thisis>1.*levels_needed-连接链表中的节点个数,>1**'initial_rels'isalistofRelOptInfonodesforeachindependent*jointreeitem.Thesearethecomponentstobejoinedtogether.*Notethatlevels_needed==list_length(initial_rels).*initial_rels-与连接树每个元素相对应的RelOptInfo节点**Returnsthefinallevelofjoinrelations,i.e.,therelationthatis*theresultofjoiningalltheoriginalrelationstogether.*Atleastoneimplementationpathmustbeprovidedforthisrelationand*allrequiredsub-relations.*返回连接的最终关系(最顶层的Relation):将所有原始关系连接在一起的最终结果。*优化器为该关系及其所必需的子关系提供至少一个的实现路径。**Tosupportloadablepluginsthatmodifyplannerbehaviorbychangingthe*joinsearchingalgorithm,weprovideahookvariablethatletsaplugin*replaceorsupplementthisfunction.Anysuchhookmustreturnthesame*finaljoinrelationasthestandardcodewould,butitmighthavea*differentsetofimplementationpathsattached,andonlythesub-joinrels*neededforthesepathsneedhavebeeninstantiated.*为了支持自定义函数，PG提供了一个钩子变量，允许外部插件替换或填充这个函数。*任何这样的钩子都必须返回与PG标准函数相同的最终连接关系，*但是它可能附加了一组不同的实现路径，并且只实例化了这些路径所需的子连接。**Notetopluginauthors:thefunctionsinvokedduringstandard_join_search()*modifyroot->join_rel_listandroot->join_rel_hash.Ifyouwanttodomore*thanonejoin-ordersearch,you'llprobablyneedtosaveandrestorethe*originalstatesofthosedatastructures.Seegeqo_eval()foranexample.*/RelOptInfo*standard_join_search(PlannerInfo*root,intlevels_needed,List*initial_rels){intlev;RelOptInfo*rel;/**Thisfunctioncannotbeinvokedrecursivelywithinanyoneplanning*problem,sojoin_rel_level[]can'tbeinusealready.*/Assert(root->join_rel_level==NULL);//验证/**Weemployasimple"dynamicprogramming"algorithm:wefirstfindall*waystobuildjoinsoftwojointreeitems,thenallwaystobuildjoins*ofthreeitems(fromtwo-itemjoinsandsingleitems),thenfour-item*joins,andsoonuntilwehaveconsideredallwaystojoinallthe*itemsintoonerel.*PG实现了一种简单的动态规划算法:首先为连接树中的两个Relation建立可能的连接路径*然后为三个Relation建立所有可能的连接路径,以此类推直至已为所有的Relation建立了*连接路径,从而得到最终的关系(final_rel)**root->join_rel_level[j]isalistofallthej-itemrels.Initiallywe*setroot->join_rel_level[1]torepresentallthesingle-jointree-item*relations.*设置root->join_rel_level数组,[j]是所有j-itemrels的链表(即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;/**Determineallpossiblepairsofrelationstobejoinedatthis*level,andbuildpathsformakingeachonefromeveryavailable*pairoflower-levelrelations.*确定在此级别上要连接的所有可能的关系对，并构建访问路径，*以从每一对可用的较低级关系中往上创建关系。*/join_search_one_level(root,lev);/**Rungenerate_partitionwise_join_paths()andgenerate_gather_paths()*foreachjust-processedjoinrel.Wecouldnotdothisearlier*becausebothregularandpartialpathscangetaddedtoa*particularjoinrelatmultipletimeswithinjoin_search_one_level.*循环调用generate_partitionwise_join_paths()和generate_collect_paths()函数:*参数为上一步骤生成的链表中的每个元素。*由于常规路径和部分路径都可以在join_search_one_level中多次添加joinrel,因此在此处调用。**Afterthat,we'redonecreatingpathsforthejoinrel,sorun*set_cheapest().*在此之后,PG已为joinrel(连接生成的新关系)创建了访问路径,因此可以调用函数set_cheapest**/foreach(lc,root->join_rel_level[lev])//遍历链表{rel=(RelOptInfo*)lfirst(lc);//新生成的关系/*Createpathsforpartitionwisejoins.*/generate_partitionwise_join_paths(root,rel);//创建partitionwise路径/**Exceptforthetopmostscan/joinrel,considergathering*partialpaths.We'lldothesameforthetopmostscan/joinrel*onceweknowthefinaltargetlist(seegrouping_planner).*/if(lev<levels_needed)generate_gather_paths(root,rel,false);//并行执行需考虑gathering/*Findandsavethecheapestpathsforthisrel*/set_cheapest(rel);//从形成该joinrel的所有路径中找到成本最低的#ifdefOPTIMIZER_DEBUGdebug_print_rel(root,rel);//DEBUG信息#endif}}/**Weshouldhaveasinglerelatthefinallevel.*连接的最终结果,只有一个RelOptInfo*/if(root->join_rel_level[levels_needed]==NIL)elog(ERROR,"failedtobuildany%d-wayjoins",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;//重置returnrel;//返回}//-----------------------------------------------------------------------geqo/**geqo*solutionofthequeryoptimizationproblem*similartoaconstrainedTravelingSalesmanProblem(TSP)*遗传算法:可参考TSP的求解算法.*TSP-旅行推销员问题（最短路径问题）:*给定一系列城市和每对城市之间的距离，求解访问每一座城市一次并回到起始城市的最短回路。*/RelOptInfo*geqo(PlannerInfo*root,intnumber_of_rels,List*initial_rels){GeqoPrivateDataprivate;intgeneration;Chromosome*momma;Chromosome*daddy;Chromosome*kid;Pool*pool;intpool_size,number_generations;#ifdefGEQO_DEBUGintstatus_interval;#endifGene*best_tour;RelOptInfo*best_rel;#ifdefined(ERX)Edge*edge_table;/*listofedges*/intedge_failures=0;#endif#ifdefined(CX)||defined(PX)||defined(OX1)||defined(OX2)City*city_table;/*listofcities*/#endif#ifdefined(CX)intcycle_diffs=0;intmutations=0;#endif/*setupprivateinformation*/root->join_search_private=(void*)&private;private.initial_rels=initial_rels;/*initializeprivatenumbergenerator*/geqo_set_seed(root,Geqo_seed);/*setGAparameters*/pool_size=gimme_pool_size(number_of_rels);number_generations=gimme_number_generations(pool_size);#ifdefGEQO_DEBUGstatus_interval=10;#endif/*allocategeneticpoolmemory*/pool=alloc_pool(root,pool_size,number_of_rels);/*randominitializationofthepool*/random_init_pool(root,pool);/*sortthepoolaccordingtocheapestpathasfitness*/sort_pool(root,pool);/*wehavetodoitonlyonetime,sinceall*kidsreplacetheworstindividualsin*future(->geqo_pool.c:spread_chromo)*/#ifdefGEQO_DEBUGelog(DEBUG1,"GEQOselected%dpoolentries,best%.2f,worst%.2f",pool_size,pool->data[0].worth,pool->data[pool_size-1].worth);#endif/*allocatechromosomemommaanddaddymemory*/momma=alloc_chromo(root,pool->string_length);daddy=alloc_chromo(root,pool->string_length);#ifdefined(ERX)#ifdefGEQO_DEBUGelog(DEBUG2,"usingedgerecombinationcrossover[ERX]");#endif/*allocateedgetablememory*/edge_table=alloc_edge_table(root,pool->string_length);#elifdefined(PMX)#ifdefGEQO_DEBUGelog(DEBUG2,"usingpartiallymatchedcrossover[PMX]");#endif/*allocatechromosomekidmemory*/kid=alloc_chromo(root,pool->string_length);#elifdefined(CX)#ifdefGEQO_DEBUGelog(DEBUG2,"usingcyclecrossover[CX]");#endif/*allocatecitytablememory*/kid=alloc_chromo(root,pool->string_length);city_table=alloc_city_table(root,pool->string_length);#elifdefined(PX)#ifdefGEQO_DEBUGelog(DEBUG2,"usingpositioncrossover[PX]");#endif/*allocatecitytablememory*/kid=alloc_chromo(root,pool->string_length);city_table=alloc_city_table(root,pool->string_length);#elifdefined(OX1)#ifdefGEQO_DEBUGelog(DEBUG2,"usingordercrossover[OX1]");#endif/*allocatecitytablememory*/kid=alloc_chromo(root,pool->string_length);city_table=alloc_city_table(root,pool->string_length);#elifdefined(OX2)#ifdefGEQO_DEBUGelog(DEBUG2,"usingordercrossover[OX2]");#endif/*allocatecitytablememory*/kid=alloc_chromo(root,pool->string_length);city_table=alloc_city_table(root,pool->string_length);#endif/*mypainmainpart:*//*iterativeoptimization*/for(generation=0;generation<number_generations;generation++){/*SELECTION:usinglinearbiasfunction*/geqo_selection(root,momma,daddy,pool,Geqo_selection_bias);#ifdefined(ERX)/*EDGERECOMBINATIONCROSSOVER*/gimme_edge_table(root,momma->string,daddy->string,pool->string_length,edge_table);kid=momma;/*arethereanyedgefailures?*/edge_failures+=gimme_tour(root,edge_table,kid->string,pool->string_length);#elifdefined(PMX)/*PARTIALLYMATCHEDCROSSOVER*/pmx(root,momma->string,daddy->string,kid->string,pool->string_length);#elifdefined(CX)/*CYCLECROSSOVER*/cycle_diffs=cx(root,momma->string,daddy->string,kid->string,pool->string_length,city_table);/*mutatethechild*/if(cycle_diffs==0){mutations++;geqo_mutation(root,kid->string,pool->string_length);}#elifdefined(PX)/*POSITIONCROSSOVER*/px(root,momma->string,daddy->string,kid->string,pool->string_length,city_table);#elifdefined(OX1)/*ORDERCROSSOVER*/ox1(root,momma->string,daddy->string,kid->string,pool->string_length,city_table);#elifdefined(OX2)/*ORDERCROSSOVER*/ox2(root,momma->string,daddy->string,kid->string,pool->string_length,city_table);#endif/*EVALUATEFITNESS*/kid->worth=geqo_eval(root,kid->string,pool->string_length);/*pushthekidintothewildernessoflifeaccordingtoitsworth*/spread_chromo(root,kid,pool);#ifdefGEQO_DEBUGif(status_interval&&!(generation%status_interval))print_gen(stdout,pool,generation);#endif}#ifdefined(ERX)&&defined(GEQO_DEBUG)if(edge_failures!=0)elog(LOG,"[GEQO]failures:%d,average:%d",edge_failures,(int)number_generations/edge_failures);elseelog(LOG,"[GEQO]noedgefailuresdetected");#endif#ifdefined(CX)&&defined(GEQO_DEBUG)if(mutations!=0)elog(LOG,"[GEQO]mutations:%d,generations:%d",mutations,number_generations);elseelog(LOG,"[GEQO]nomutationsprocessed");#endif#ifdefGEQO_DEBUGprint_pool(stdout,pool,0,pool_size-1);#endif#ifdefGEQO_DEBUGelog(DEBUG1,"GEQObestis%.2fafter%dgenerations",pool->data[0].worth,number_generations);#endif/**gotthecheapestquerytreeprocessedbygeqo;firstelementofthe*populationindicatesthebestquerytree*/best_tour=(Gene*)pool->data[0].string;best_rel=gimme_tree(root,best_tour,pool->string_length);if(best_rel==NULL)elog(ERROR,"geqofailedtomakeavalidplan");/*DBG:showthequeryplan*/#ifdefNOT_USEDprint_plan(best_plan,root);#endif/*...freememorystuff*/free_chromo(root,momma);free_chromo(root,daddy);#ifdefined(ERX)free_edge_table(root,edge_table);#elifdefined(PMX)free_chromo(root,kid);#elifdefined(CX)free_chromo(root,kid);free_city_table(root,city_table);#elifdefined(PX)free_chromo(root,kid);free_city_table(root,city_table);#elifdefined(OX1)free_chromo(root,kid);free_city_table(root,city_table);#elifdefined(OX2)free_chromo(root,kid);free_city_table(root,city_table);#endiffree_pool(root,pool);/*...clearrootpointertoourprivatestorage*/root->join_search_private=NULL;returnbest_rel;}二、跟踪分析

测试脚本以及执行计划如下:

testdb=#explainverboseselecta.*,b.grbh,b.jetestdb-#fromt_dwxxa,testdb-#lateral(selectt1.dwbh,t1.grbh,t2.jetestdb(#fromt_grxxt1testdb(#innerjoint_jfxxt2ont1.dwbh=a.dwbhandt1.grbh=t2.grbh)btestdb-#wherea.dwbh='1001'testdb-#orderbyb.dwbh;QUERYPLAN------------------------------------------------------------------------------------------------------NestedLoop(cost=0.87..111.89rows=10width=37)Output:a.dwmc,a.dwbh,a.dwdz,t1.grbh,t2.je,t1.dwbh->NestedLoop(cost=0.58..28.69rows=10width=29)Output:a.dwmc,a.dwbh,a.dwdz,t1.grbh,t1.dwbh->IndexScanusingt_dwxx_pkeyonpublic.t_dwxxa(cost=0.29..8.30rows=1width=20)Output:a.dwmc,a.dwbh,a.dwdzIndexCond:((a.dwbh)::text='1001'::text)->IndexScanusingidx_t_grxx_dwbhonpublic.t_grxxt1(cost=0.29..20.29rows=10width=9)Output:t1.dwbh,t1.grbh,t1.xm,t1.xb,t1.nlIndexCond:((t1.dwbh)::text='1001'::text)->IndexScanusingidx_t_jfxx_grbhonpublic.t_jfxxt2(cost=0.29..8.31rows=1width=13)Output:t2.grbh,t2.ny,t2.jeIndexCond:((t2.grbh)::text=(t1.grbh)::text)

启动gdb跟踪

(gdb)bmake_rel_from_joinlistBreakpoint1at0x73f0d3:fileallpaths.c,line2617.(gdb)cContinuing.Breakpoint1,make_rel_from_joinlist(root=0x176c750,joinlist=0x179e480)atallpaths.c:26172617levels_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)n2619if(levels_needed<=0)(gdb)plevels_needed$6=3

遍历链表,构造RelOptInfo,添加到initial_rels中

(gdb)2628foreach(jl,joinlist)...(gdb)2637thisrel=find_base_rel(root,varno);(gdb)2651initial_rels=lappend(initial_rels,thisrel);

完成遍历后,开始构造连接路径.
遗传算法的rels阈值为12(通过GUC参数配置)

2672if(join_search_hook)(gdb)2674elseif(enable_geqo&&levels_needed>=geqo_threshold)(gdb)2677returnstandard_join_search(root,levels_needed,initial_rels);(gdb)pgeqo_threshold$7=12

进入函数standard_join_search

(gdb)stepstandard_join_search(root=0x176c750,levels_needed=3,initial_rels=0x17a6308)atallpaths.c:27332733root->join_rel_level=(List**)palloc0((levels_needed+1)*sizeof(List*));

开始构造2->N个item对应的rel链表

...(gdb)2746join_search_one_level(root,lev);(gdb)n2757foreach(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)2773set_cheapest(rel);(gdb)2757foreach(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)n2737for(lev=2;lev<=levels_needed;lev++)(gdb)n2746join_search_one_level(root,lev);(gdb)plev$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}

获得最终结果

...2792returnrel;(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}

到此，关于“PostgreSQL中make_rel_from_joinlist函数分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！