本篇内容介绍了“PostgreSQL中哪个函数创建两个rels连接所生成的RelOptInfo”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

RelOptInfo

typedefenumRelOptKind{RELOPT_BASEREL,//基本关系(如基表/子查询等)RELOPT_JOINREL,//连接产生的关系,要注意的是通过连接等方式产生的结果亦可以视为关系RELOPT_OTHER_MEMBER_REL,RELOPT_OTHER_JOINREL,RELOPT_UPPER_REL,//上层的关系RELOPT_OTHER_UPPER_REL,RELOPT_DEADREL}RelOptKind;/**Isthegivenrelationasimplerelationi.eabaseor"other"member*relation?*/#defineIS_SIMPLE_REL(rel)\((rel)->reloptkind==RELOPT_BASEREL||\(rel)->reloptkind==RELOPT_OTHER_MEMBER_REL)/*Isthegivenrelationajoinrelation?*/#defineIS_JOIN_REL(rel)\((rel)->reloptkind==RELOPT_JOINREL||\(rel)->reloptkind==RELOPT_OTHER_JOINREL)/*Isthegivenrelationanupperrelation?*/#defineIS_UPPER_REL(rel)\((rel)->reloptkind==RELOPT_UPPER_REL||\(rel)->reloptkind==RELOPT_OTHER_UPPER_REL)/*Isthegivenrelationan"other"relation?*/#defineIS_OTHER_REL(rel)\((rel)->reloptkind==RELOPT_OTHER_MEMBER_REL||\(rel)->reloptkind==RELOPT_OTHER_JOINREL||\(rel)->reloptkind==RELOPT_OTHER_UPPER_REL)typedefstructRelOptInfo{NodeTagtype;//节点标识RelOptKindreloptkind;//RelOpt类型/*allrelationsincludedinthisRelOptInfo*/Relidsrelids;/*Relids(rtindex)集合setofbaserelids(rangetableindexes)*//*sizeestimatesgeneratedbyplanner*/doublerows;/*结果元组的估算数量estimatednumberofresulttuples*//*per-relationplannercontrolflags*/boolconsider_startup;/*是否考虑启动成本?是,需要保留启动成本低的路径keepcheap-startup-costpaths?*/boolconsider_param_startup;/*是否考虑参数化?的路径ditto,forparameterizedpaths?*/boolconsider_parallel;/*是否考虑并行处理路径considerparallelpaths?*//*defaultresulttargetlistforPathsscanningthisrelation*/structPathTarget*reltarget;/*扫描该Relation时默认的结果listofVars/Exprs,cost,width*//*materializationinformation*/List*pathlist;/*访问路径链表Pathstructures*/List*ppilist;/*路径链表中使用参数化路径进行ParamPathInfosusedinpathlist*/List*partial_pathlist;/*partialPaths*/structPath*cheapest_startup_path;//代价最低的启动路径structPath*cheapest_total_path;//代价最低的整体路径structPath*cheapest_unique_path;//代价最低的获取唯一值的路径List*cheapest_parameterized_paths;//代价最低的参数化?路径链表/*parameterizationinformationneededforbothbaserelsandjoinrels*//*(seealsolateral_varsandlateral_referencers)*/Relidsdirect_lateral_relids;/*使用lateral语法,需依赖的Relidsrelsdirectlylaterallyreferenced*/Relidslateral_relids;/*minimumparameterizationofrel*//*informationaboutabaserel(notsetforjoinrels!)*///reloptkind=RELOPT_BASEREL时使用的数据结构Indexrelid;/*RelationID*/Oidreltablespace;/*表空间containingtablespace*/RTEKindrtekind;/*基表?子查询?还是函数等等?RELATION,SUBQUERY,FUNCTION,etc*/AttrNumbermin_attr;/*最小的属性编号smallestattrnoofrel(often<0)*/AttrNumbermax_attr;/*最大的属性编号largestattrnoofrel*/Relids*attr_needed;/*数组arrayindexed[min_attr..max_attr]*/int32*attr_widths;/*属性宽度arrayindexed[min_attr..max_attr]*/List*lateral_vars;/*关系依赖的Vars/PHVsLATERALVarsandPHVsreferencedbyrel*/Relidslateral_referencers;/*依赖该关系的Relidsrelsthatreferencemelaterally*/List*indexlist;/*该关系的IndexOptInfo链表listofIndexOptInfo*/List*statlist;/*统计信息链表listofStatisticExtInfo*/BlockNumberpages;/*块数sizeestimatesderivedfrompg_class*/doubletuples;/*元组数*/doubleallvisfrac;/*?*/PlannerInfo*subroot;/*如为子查询,存储子查询的rootifsubquery*/List*subplan_params;/*如为子查询,存储子查询的参数ifsubquery*/intrel_parallel_workers;/*并行执行,需要多少个workers?wantednumberofparallelworkers*//*Informationaboutforeigntablesandforeignjoins*///FWD相关信息Oidserverid;/*identifiesserverforthetableorjoin*/Oiduserid;/*identifiesusertocheckaccessas*/booluseridiscurrent;/*joinisonlyvalidforcurrentuser*//*use"structFdwRoutine"toavoidincludingfdwapi.hhere*/structFdwRoutine*fdwroutine;void*fdw_private;/*cachespaceforrememberingifwehaveproventhisrelationunique*///已知的,可保证唯一的Relids链表List*unique_for_rels;/*knownuniquefortheseotherrelid*set(s)*/List*non_unique_for_rels;/*已知的,不唯一的Relids链表knownnotuniquefortheseset(s)*//*usedbyvariousscansandjoins:*/List*baserestrictinfo;/*如为基本关系,存储约束条件RestrictInfostructures(ifbaserel)*/QualCostbaserestrictcost;/*解析约束表达式的成本?costofevaluatingtheabove*/Indexbaserestrict_min_security;/*最低安全等级minsecurity_levelfoundin*baserestrictinfo*/List*joininfo;/*连接语句的约束条件信息RestrictInfostructuresforjoinclauses*involvingthisrel*/boolhas_eclass_joins;/*是否存在等价类连接?Tmeansjoininfoisincomplete*//*usedbypartitionwisejoins:*/boolconsider_partitionwise_join;/*分区?considerpartitionwise*joinpaths?(if*partitionedrel)*/Relidstop_parent_relids;/*Relidsoftopmostparents(if"other"*rel)*//*usedforpartitionedrelations*///分区表使用PartitionSchemepart_scheme;/*分区的schemaPartitioningscheme.*/intnparts;/*分区数numberofpartitions*/structPartitionBoundInfoData*boundinfo;/*分区边界信息Partitionbounds*/List*partition_qual;/*分区约束partitionconstraint*/structRelOptInfo**part_rels;/*分区的RelOptInfo数组ArrayofRelOptInfosofpartitions,*storedinthesameorderofbounds*/List**partexprs;/*非空分区键表达式Non-nullablepartitionkeyexpressions.*/List**nullable_partexprs;/*可为空的分区键表达式Nullablepartitionkeyexpressions.*/List*partitioned_child_rels;/*RTIndexes链表ListofRTindexes.*/}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*FindorcreateajoinRelOptInfothatrepresentsthejoinof*thetwogivenrels,andaddtoitpathinformationforpaths*createdwiththetworelsasouterandinnerrel.*(Thejoinrelmayalreadycontainpathsgeneratedfromother*pairsofrelsthatadduptothesamesetofbaserels.)*创建两个rels连接所生成的RelOptInfo,并添加访问路径信息.*(新产生的rel可能已经包含从相同的两个rels对所生成的的路径)**NB:willreturnNULLifattemptedjoinisnotvalid.Thiscanhappen*whenworkingwithouterjoins,orwithINorEXISTSclausesthathavebeen*turnedintojoins.*注意:如果尝试连接失败,则返回NULL.这可能出现在处理外连接或者已转变为连接的IN/EXISTS子句上*/RelOptInfo*make_join_rel(PlannerInfo*root,RelOptInfo*rel1,RelOptInfo*rel2){Relidsjoinrelids;SpecialJoinInfo*sjinfo;boolreversed;SpecialJoinInfosjinfo_data;RelOptInfo*joinrel;List*restrictlist;/*Weshouldnevertrytojointwooverlappingsetsofrels.*/Assert(!bms_overlap(rel1->relids,rel2->relids));//两者无交接/*ConstructRelidssetthatidentifiesthejoinrel.*/joinrelids=bms_union(rel1->relids,rel2->relids);//两个rel涉及的rels/*Checkvalidityanddeterminejointype.*/if(!join_is_legal(root,rel1,rel2,joinrelids,&sjinfo,&reversed))//是否非法{/*invalidjoinpath*/bms_free(joinrelids);returnNULL;//返回}/*Swaprelsifneededtomatchthejoininfo.*/if(reversed)//位置是否调换{RelOptInfo*trel=rel1;rel1=rel2;rel2=trel;}/**Ifit'saplaininnerjoin,thenwewon'thavefoundanythingin*join_info_list.MakeupaSpecialJoinInfosothatselectivity*estimationfunctionswillknowwhat'sbeingjoined.*普通的内连接,不需要使用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;/*wedon'tbothertryingtomaketheremainingfieldsvalid*/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;}/**FindorbuildthejoinRelOptInfo,andcomputetherestrictlistthat*goeswiththisparticularjoining.*创建连接生成的新关系RelOptInfo,并为此连接生成限制条件链表*/joinrel=build_join_rel(root,joinrelids,rel1,rel2,sjinfo,&restrictlist);/**Ifwe'vealreadyproventhisjoinisempty,weneedn'tconsiderany*morepathsforit.*/if(is_dummy_rel(joinrel)){bms_free(joinrelids);returnjoinrel;}/*Addpathstothejoinrelation.*///为连接生成的新关系构造访问路径populate_joinrel_with_paths(root,rel1,rel2,joinrel,sjinfo,restrictlist);bms_free(joinrelids);//释放资源returnjoinrel;//返回joinrel}//--------------------------------------------------------------------build_join_rel/**build_join_rel*Returnsrelationentrycorrespondingtotheunionoftwogivenrels,*creatinganewrelationentryifnonealreadyexists.*给定两个rels,创建并返回对应这两个rels连接生成的新的Relation**'joinrelids'istheRelidssetthatuniquelyidentifiesthejoin*'outer_rel'and'inner_rel'arerelationnodesfortherelationstobe*joined*'sjinfo':joincontextinfo*'restrictlist_ptr':resultvariable.IfnotNULL,*restrictlist_ptr*receivesthelistofRestrictInfonodesthatapplytothis*particularpairofjoinablerelations.*joinrelids-与此连接相关的所有relids*outer_rel和inner_rel-构成连接的外表(驱动表)和内表*sjinfo-连接上下文信息*restrictlist_ptr-存储结果的变量,如为非NULL值,该指针指向RestrictInfo(约束条件)节点链表**restrictlist_ptrmakestheroutine'sAPIalittlegrotty,butitsaves*duplicatedcalculationoftherestrictlist...*/RelOptInfo*build_join_rel(PlannerInfo*root,Relidsjoinrelids,RelOptInfo*outer_rel,RelOptInfo*inner_rel,SpecialJoinInfo*sjinfo,List**restrictlist_ptr){RelOptInfo*joinrel;List*restrictlist;/*Thisfunctionshouldbeusedonlyforjoinbetweenparents.*/Assert(!IS_OTHER_REL(outer_rel)&&!IS_OTHER_REL(inner_rel));/**Seeifwealreadyhaveajoinrelforthissetofbaserels.*这些基础rels所构成的连接是否已存在?*/joinrel=find_join_rel(root,joinrelids);if(joinrel)//已存在{/**Yes,soweonlyneedtofiguretherestrictlistforthisparticular*pairofcomponentrelations.*/if(restrictlist_ptr)*restrictlist_ptr=build_joinrel_restrictlist(root,joinrel,outer_rel,inner_rel);//如已存在约束条件,则找出相应的信息即可returnjoinrel;//返回}/**Nope,somakeone.*没有,则构造之*/joinrel=makeNode(RelOptInfo);joinrel->reloptkind=RELOPT_JOINREL;joinrel->relids=bms_copy(joinrelids);joinrel->rows=0;/*cheapstartupcostisinterestingiffnotalltuplestoberetrieved*/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;/*initdirect_lateral_relidsfromchildren;we'llfinishitupbelow*/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;/*indicatesnotabaserel*/joinrel->rtekind=RTE_JOIN;//RTE_JOINjoinrel->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;/*mightgetchangedlater*/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的相关信息,Computeinformationrelevanttotheforeignrelations.*/set_foreign_rel_properties(joinrel,outer_rel,inner_rel);/**Createanewtlistcontainingjustthevarsthatneedtobeoutputfrom*thisjoin(ie,areneededforhigherjoinclausesorfinaloutput).**创建一个新的投影列链表,只包含该连接的输出*NOTE:thetlistorderforajoinrelwilldependonwhichpairofouter*andinnerrelswefirsttrytobuilditfrom.Butthecontentsshould*bethesameregardless.*/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_joinrelalsotookcareofaddingtheph_lateral*setsofanyPlaceHolderVarscomputedheretodirect_lateral_relids,so*nowwecanfinishcomputingthat.Thisismuchlikethecomputationof*thetransitively-closedlateral_relidsinmin_join_parameterization,*exceptthatherewe*do*havetoconsidertheaddedPHVs.*add_placeholders_to_joinrel函数将这里产生的PlaceHolderVars的ph_lateral集合*添加到direct_lateral_relids中以完成最终的处理。*这非常类似于min_join_parameterization的transtivelyclosedlateralal_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;/**Constructrestrictandjoinclauselistsforthenewjoinrel.(The*callermightormightnotneedtherestrictlist,butIneeditanyway*forset_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);//构建连接条件链表/**Thisisalsotherightplacetocheckwhetherthejoinrelhasany*pendingEquivalenceClassjoins.*判断是否存在等价类EC*/joinrel->has_eclass_joins=has_relevant_eclass_joinclause(root,joinrel);/*S存储分区信息,torethepartitioninformation.*/build_joinrel_partition_info(joinrel,outer_rel,inner_rel,restrictlist,sjinfo->jointype);/**估算joinrel的大小,Setestimatesofthejoinrel'ssize.*/set_joinrel_size_estimates(root,joinrel,outer_rel,inner_rel,sjinfo,restrictlist);/**Settheconsider_parallelflagifthisjoinrelcouldpotentiallybe*scannedwithinaparallelworker.Ifthisflagisfalseforeither*inner_relorouter_rel,thenitmustbefalseforthejoinrelalso.*Evenifbotharetrue,theremightbeparallel-restrictedexpressions*inthetargetlistorquals.*设置consider_parallel标记,如joinrel可以并行扫描的话**Notethatiftherearemorethantworelsinthisrelation,theycould*bedividedbetweeninner_relandouter_relinanyarbitraryway.We*assumethisdoesn'tmatter,becauseweshouldhitallthesamebaserels*andjoinclauseswhilebuildinguptothisjoinrelnomatterwhichwe*take;therefore,weshouldmakethesamedecisionherehoweverweget*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;/*AddthejoinreltothePlannerInfo.*/add_join_rel(root,joinrel);//添加到优化器信息中/**Also,ifdynamic-programmingjoinsearchisactive,addthenewjoinrel*totheappropriatesublist.Note:youmightthinktheAssertonnumber*ofmembersshouldbeforequality,butsomeofthelevel1relsmight*havebeenjoinrelsalready,sowecanonlyassert<=.*添加到合适的链表中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);//加入到链表中}returnjoinrel;}//-----------------------------------------------find_join_rel/**find_join_rel*Returnsrelationentrycorrespondingto'relids'(asetofRTindexes),*orNULLifnoneexists.Thisisforjoinrelations.*返回对应relids(RTindexes的集合)的RelOptInfo,如无则返回NULL.*/RelOptInfo*find_join_rel(PlannerInfo*root,Relidsrelids){/**Switchtousinghashlookupwhenlistgrows"toolong".Thethreshold*isarbitraryandisknownonlyhere.*如链表过长,则改用hash查找*/if(!root->join_rel_hash&&list_length(root->join_rel_list)>32)build_join_rel_hash(root);/**Useeitherhashtablelookuporlinearsearch,asappropriate.*使用hash表查找或者线性搜索**Note:theseeminglyredundanthashkeyvariableisusedtoavoidtaking*theaddressofrelids;unlessthecompilerisexceedinglysmart,doing*sowouldforcerelidsoutofaregisterandthusprobablyslowdownthe*list-searchcase.*/if(root->join_rel_hash)//hash{Relidshashkey=relids;JoinHashEntry*hentry;hentry=(JoinHashEntry*)hash_search(root->join_rel_hash,&hashkey,HASH_FIND,NULL);if(hentry)returnhentry->join_rel;}else//线性{ListCell*l;foreach(l,root->join_rel_list){RelOptInfo*rel=(RelOptInfo*)lfirst(l);if(bms_equal(rel->relids,relids))returnrel;}}returnNULL;}//-----------------------------------------------build_joinrel_restrictlist/**build_joinrel_restrictlist*build_joinrel_joinlist*Theseroutinesbuildlistsofrestrictionandjoinclausesfora*joinrelationfromthejoininfolistsoftherelationsitjoins.*从关系的joininfo链表中建立限制条件和连接条件链表**Theseroutinesareseparatebecausetherestrictionlistmustbe*builtafreshforeachpairofinputsub-relationsweconsider,whereas*thejoinlistneedonlybecomputedonceforanyjoinRelOptInfo.*Thejoinlistisfullydeterminedbythesetofrelsmakingupthe*joinrel,soweshouldgetthesameresults(uptoordering)fromany*candidatepairofsub-relations.Buttherestrictionlistiswhatever*isnothandledinthesub-relations,soitdependsonwhich*sub-relationsareconsidered.*这些处理过程是独立的，因为限制条件链表必须为所考虑的每一对输入子关系重新构建，*而连接条件链表只需要为任何连接RelOptInfo计算一次即可。*连接链表完全由组成joinrel的一组rels决定，*因此从任何子关系的候选对中都应该得到相同的结果(直到排序过程)。*但是限制条件链表是子关系中没有处理的内容，所以它取决于考虑的子关系。**Ifajoinclausefromaninputrelationreferstobaserelsstillnot*presentinthejoinrel,thenitisstillajoinclauseforthejoinrel;*weputitintothejoininfolistforthejoinrel.Otherwise,*theclauseisnowarestrictclauseforthejoinedrelation,andwe*returnittothecallerofbuild_joinrel_restrictlist()tobestoredin*joinpathsmadefromthispairofsub-relations.(Itwillnotneedto*beconsideredfurtherupthejointree.)*如果构成连接关系中的连接条件子句指向的baserels不在joinrel中,*那么它仍然是joinrel的连接条件子句;这些信息会放到joinrel的joininfo链表中。*否则，如果条件子句现在是连接关系的限制子句，*那么将它返回给build_joinrel_restrictlist()的调用方，将其存储在由这对子关系构成的连接路径中。*(它不需要被认为位于连接树的更上层。)**InmanycasewewillfindthesameRestrictInfosinbothinput*relations'joinlists,sobecarefultoeliminateduplicates.*Pointerequalityshouldbeasufficienttestfordups,sinceall*thevariousjoinlistentriesultimatelyrefertoRestrictInfos*pushedintothembydistribute_restrictinfo_to_rels().*在许多情况下，在两个关系的连接列表中可以发现相同的RestrictInfos，因此要小心排除重复。*指针相等的判断应该是对重复值的充分测试，因为所有的joinlist条目最终*都指向distribute_restrictinfo_to_rels()推入的RestrictInfos。**'joinrel'isajoinrelationnode,连接新产生的关系*'outer_rel'and'inner_rel'areapairofrelationsthatcanbejoined*toformjoinrel.连接的外表和内表**build_joinrel_restrictlist()returnsalistofrelevantrestrictinfos,*whereasbuild_joinrel_joinlist()storesitsresultsinthejoinrel's*joininfolist.Oneortheothermustaccepteachgivenclause!*build_joinrel_restrictlist()返回相关限制条件的链表,*而build_joinrel_joinlist()把结果存储在joinrel的joininfo链表中**NB:Formerly,wemadedeep(!)copiesofeachinputRestrictInfotopass*uptothejoinrelation.Ibelievethisisnolongernecessary,because*RestrictInfonodesarenolongercontext-dependent.Instead,justinclude*theoriginalnodesinthelistsmadeforthejoinrelation.*/staticList*build_joinrel_restrictlist(PlannerInfo*root,RelOptInfo*joinrel,RelOptInfo*outer_rel,RelOptInfo*inner_rel){List*result;/**Collectalltheclausesthatsyntacticallybelongatthislevel,*eliminatinganyduplicates(importantsincewewillseemanyofthe*sameclausesarrivingfrombothinputrelations).*收集语法上属于该级别的所有限制条件子句，消除任何重复(这很重要，因为存在来自两个关系的相同子句)。*/result=subbuild_joinrel_restrictlist(joinrel,outer_rel->joininfo,NIL);result=subbuild_joinrel_restrictlist(joinrel,inner_rel->joininfo,result);/**AddonanyclausesderivedfromEquivalenceClasses.Thesecannotbe*redundantwiththeclausesinthejoininfolists,sodon'tbother*checking.*添加来自EC的条件子句.*/result=list_concat(result,generate_join_implied_equalities(root,joinrel->relids,outer_rel->relids,inner_rel));returnresult;}staticvoidbuild_joinrel_joinlist(RelOptInfo*joinrel,RelOptInfo*outer_rel,RelOptInfo*inner_rel){List*result;/**Collectalltheclausesthatsyntacticallybelongabovethislevel,*eliminatinganyduplicates(importantsincewewillseemanyofthe*sameclausesarrivingfrombothinputrelations).*收集语法上属于该级别的所有连接条件子句，消除任何重复(这很重要，因为存在来自两个关系的相同子句)。*/result=subbuild_joinrel_joinlist(joinrel,outer_rel->joininfo,NIL);result=subbuild_joinrel_joinlist(joinrel,inner_rel->joininfo,result);joinrel->joininfo=result;}三、跟踪分析

测试表和数据继续沿用上一节创建的表和数据，使用的SQL语句如下:

testdb=#explainverboseselecta.*,b.c1,c.c2,d.c2,e.c1,f.c2fromainnerjoinbona.c1=b.c1,c,d,einnerjoinfone.c1=f.c1ande.c1<100wherea.c1=f.c1andb.c1=c.c1andc.c1=d.c1andd.c1=e.c1;QUERYPLAN----------------------------------------------------------------------------------------------------------NestedLoop(cost=101.17..2218.24rows=2width=42)Output:a.c1,a.c2,b.c1,c.c2,d.c2,e.c1,f.c2JoinFilter:(a.c1=b.c1)->HashJoin(cost=3.25..196.75rows=100width=22)Output:a.c1,a.c2,c.c2,c.c1HashCond:(c.c1=a.c1)->SeqScanonpublic.c(cost=0.00..155.00rows=10000width=12)Output:c.c1,c.c2->Hash(cost=2.00..2.00rows=100width=10)Output:a.c1,a.c2->SeqScanonpublic.a(cost=0.00..2.00rows=100width=10)Output:a.c1,a.c2->Materialize(cost=97.92..2014.00rows=5width=32)Output:b.c1,d.c2,d.c1,e.c1,f.c2,f.c1->HashJoin(cost=97.92..2013.97rows=5width=32)Output:b.c1,d.c2,d.c1,e.c1,f.c2,f.c1HashCond:(f.c1=b.c1)->SeqScanonpublic.f(cost=0.00..1541.00rows=100000width=13)Output:f.c1,f.c2->Hash(cost=97.86..97.86rows=5width=19)Output:b.c1,d.c2,d.c1,e.c1->HashJoin(cost=78.10..97.86rows=5width=19)Output:b.c1,d.c2,d.c1,e.c1HashCond:(b.c1=e.c1)->SeqScanonpublic.b(cost=0.00..16.00rows=1000width=4)Output:b.c1,b.c2->Hash(cost=78.04..78.04rows=5width=15)Output:d.c2,d.c1,e.c1->HashJoin(cost=73.24..78.04rows=5width=15)Output:d.c2,d.c1,e.c1HashCond:(d.c1=e.c1)->SeqScanonpublic.d(cost=0.00..4.00rows=200width=11)Output:d.c1,d.c2->Hash(cost=72.00..72.00rows=99width=4)Output:e.c1->SeqScanonpublic.e(cost=0.00..72.00rows=99width=4)Output:e.c1Filter:(e.c1<100)(38rows)

优化器选择了2 rels + 4 rels的连接模式,跟踪重点考察bushy plans的执行情况.

启动gdb,设置断点,只考察level=6的情况

(gdb)bjoin_search_one_levelBreakpoint2at0x7b0289:filejoinrels.c,line67.(gdb)cContinuing....(gdb)cContinuing.Breakpoint2,join_search_one_level(root=0x241ca38,level=6)atjoinrels.c:6767List**joinrels=root->join_rel_level;

完成5(rels)+1(rels)的调用

(gdb)bjoinrels.c:142Breakpoint3at0x7b03c4:filejoinrels.c,line142.(gdb)cContinuing.Breakpoint3,join_search_one_level(root=0x241ca38,level=6)atjoinrels.c:142142for(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)bjoinrels.c:156Breakpoint3at0x7557df:filejoinrels.c,line156.(gdb)cContinuing.Breakpoint3,join_search_one_level(root=0x1e214b8,level=6)atjoinrels.c:164164if(old_rel->joininfo==NIL&&!old_rel->has_eclass_joins&&(gdb)p*old_rel->relids->words$13=18

进入make_join_rel函数

173for_each_cell(r2,other_rels)(gdb)175RelOptInfo*new_rel=(RelOptInfo*)lfirst(r2);(gdb)177if(!bms_overlap(old_rel->relids,new_rel->relids))(gdb)184if(have_relevant_joinclause(root,old_rel,new_rel)||(gdb)187(void)make_join_rel(root,old_rel,new_rel);(gdb)stepmake_join_rel(root=0x1e214b8,rel1=0x1f079f0,rel2=0x1e96520)atjoinrels.c:681681joinrelids=bms_union(rel1->relids,rel2->relids);

进入build_join_rel函数,相应的RelOptInfo已存在,返回

(gdb)728joinrel=build_join_rel(root,joinrelids,rel1,rel2,sjinfo,(gdb)stepbuild_join_rel(root=0x1e214b8,joinrelids=0x1e401d8,outer_rel=0x1f079f0,inner_rel=0x1e96520,sjinfo=0x7fff247e18a0,restrictlist_ptr=0x7fff247e1898)atrelnode.c:498498joinrel=find_join_rel(root,joinrelids);500if(joinrel)(gdb)n506if(restrictlist_ptr)(gdb)507*restrictlist_ptr=build_joinrel_restrictlist(root,(gdb)511returnjoinrel;

执行populate_joinrel_with_paths,该函数执行后再次查看外表和内部访问路径,变成了HashPath + MaterialPath的组合,具体的变化,下一节再行介绍.

...(gdb)742populate_joinrel_with_paths(root,rel1,rel2,joinrel,sjinfo,(gdb)n745bms_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}

“PostgreSQL中哪个函数创建两个rels连接所生成的RelOptInfo”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！