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

query_planner代码片段:

//.../**Examinethetargetlistandjointree,addingentriestobaserel*targetlistsforallreferencedVars,andgeneratingPlaceHolderInfo*entriesforallreferencedPlaceHolderVars.Restrictandjoinclauses*areaddedtoappropriatelistsbelongingtothementionedrelations.We*alsobuildEquivalenceClassesforprovablyequivalentexpressions.The*SpecialJoinInfolistisalsobuilttoholdinformationaboutjoinorder*restrictions.Finally,weformatargetjoinlistformake_one_rel()to*workfrom.*/build_base_rel_tlists(root,tlist);//构建"baserels"的投影列find_placeholders_in_jointree(root);//处理jointree中的PHIfind_lateral_references(root);//处理jointree中Lateral依赖joinlist=deconstruct_jointree(root);//分解jointree/**Reconsideranypostponedouter-joinqualsnowthatwehavebuiltup*equivalenceclasses.(Thiscouldresultinfurtheradditionsor*mergingsofclasses.)*/reconsider_outer_join_clauses(root);//已创建等价类,那么需要重新考虑被下推后处理的外连接表达式/**Ifweformedanyequivalenceclasses,generateadditionalrestriction*clausesasappropriate.(Impliedjoinclausesareformedon-the-fly*later.)*/generate_base_implied_equalities(root);//等价类构建后,生成因此外加的约束语句//...一、重要的数据结构

RelOptInfo
与上节一样,RelOptInfo结构体贯彻逻辑优化和物理优化过程的始终,需不时Review.

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;二、源码解读

reconsider_outer_join_clauses函数
该函数遍历优化器信息(PlannerInfo)中的外连接子句(left_join_clauses),把条件分发到合适的地方,其中限制条件(Where子句中的条件)分发到RelOptInfo->baserestrictinfo中,连接条件(连接语句中的条件ON XX)分发到joininfo中

/**reconsider_outer_join_clauses*Re-examineanyouter-joinclausesthatweresetasideby*distribute_qual_to_rels(),andseeifwecanderiveany*EquivalenceClassesfromthem.Then,iftheywerenotmade*redundant,pushthemoutintotheregularjoin-clauselists.**WhenwehavemergejoinableclausesA=Bthatareouter-joinclauses,*wecan'tblindlycombinethemwithotherclausesA=CtodeduceB=C,*sinceinfactthe"equality"A=Bwon'tnecessarilyholdabovethe*outerjoin(oneofthevariablesmightbeNULLinstead).Nonetheless*therearecaseswherewecanaddqualclausesusingtransitivity.**Onecasethatwelookforhereisanouter-joinclauseOUTERVAR=INNERVAR*forwhichthereisalsoanequivalenceclauseOUTERVAR=CONSTANT.*ItissafeandusefultopushaclauseINNERVAR=CONSTANTintothe*evaluationoftheinner(nullable)relation,becauseanyinnerrowsnot*meetingthisconditionwillnotcontributetotheouter-joinresultanyway.*(Anyouterrowstheycouldjointowillbeeliminatedbythepushed-down*equivalenceclause.)**Notethattheaboveruledoesnotworkforfullouterjoins;norisit*veryinterestingtoconsidercaseswherethegeneratedequivalenceclause*wouldinvolverelationsoutsidetheouterjoin,sincesuchclausescouldn't*bepushedintotheinnerside'sscananyway.Sotherestrictionto*outervar=pseudoconstantisnotreallygivingupanything.**Forfull-joincases,wecanonlydosomethingusefulifit'saFULLJOIN*USINGandamergedcolumnhasanequivalenceMERGEDVAR=CONSTANT.*Bythetimeitgetshere,themergedcolumnwilllooklike*COALESCE(LEFTVAR,RIGHTVAR)*andwewillhaveafull-joinclauseLEFTVAR=RIGHTVARthatwecanmatch*theCOALESCEexpressionto.InthissituationwecanpushLEFTVAR=CONSTANT*andRIGHTVAR=CONSTANTintotheinputrelations,sinceanyrowsnot*meetingtheseconditionscannotcontributetothejoinresult.**Again,thereisn'tanytractiontobegainedbytryingtodealwith*clausescomparingamergedvartoanon-pseudoconstant.Sowecanmake*useoftheEquivalenceClassestosearchformatchingvariablesthatwere*equivalencedtoconstants.Theinterestingouter-joinclauseswere*accumulatedforusbydistribute_qual_to_rels.**Whenwefindoneofthesecases,weimplementthechangeswewantby*generatinganewequivalenceclauseINNERVAR=CONSTANT(orLEFTVAR,etc)*andpushingitintotheEquivalenceClassstructures.Thisisbecausewe*mayalreadyknowthatINNERVARisequivalencedtosomeothervar(s),and*we'dliketheconstanttopropagatetothemtoo.Notethatitwouldbe*unsafetomergeanyexistingECforINNERVARwiththeOUTERVAR'sEC---*thatcouldresultinpropagatingconstantrestrictionsfrom*INNERVARtoOUTERVAR,whichwouldbeverywrong.**It'spossiblethattheINNERVARisalsoanOUTERVARforsomeother*outer-joinclause,inwhichcasetheprocesscanberepeated.Sowerepeat*loopingoverthelistsofclausesuntilnofurtherdeductionscanbemade.*Wheneverwedomakeadeduction,weremovethegeneratingclausefromthe*lists,sincewedon'twanttomakethesamedeductiontwice.**Ifwedon'tfindanymatchforaset-asideouterjoinclause,wemust*throwitbackintotheregularjoinclauseprocessingbypassingitto*distribute_restrictinfo_to_rels().Ifwedogenerateaderivedclause,*however,theouter-joinclauseisredundant.Westillthrowitback,*becauseotherwisethejoinwillbeseenasaclauselessjoinandavoided*duringjoinordersearching;butwemarkitasredundanttokeepfrom*messingupthejoinrel'ssizeestimate.(Thisbehaviormeansthatthe*APIforthisroutineisuselesslycomplex:wecouldhavejustputall*theclausesintotheregularprocessinginitially.Wekeepitbecause*somedaywemightwanttodosomethingelse,suchasinserting"dummy"*joinclausesinsteadofrealones.)**Outerjoinclausesthataremarkedouterjoin_delayedarespecial:this*conditionmeansthatoneorbothVARsmightgotonullduetoalower*outerjoin.Wecanstillpushaconstantthroughtheclause,butonly*ifitsoperatorisstrict;andwe*haveto*throwtheclausebackinto*regularjoinclauseprocessing.Bykeepingthestrictjoinclause,*weensurethatanynull-extendedrowsthataremistakenlygenerateddue*tosuppressingrowsnotmatchingtheconstantwillberejectedatthe*upperouterjoin.(Thisdoesn'tworkforfull-joinclauses.)*/voidreconsider_outer_join_clauses(PlannerInfo*root){boolfound;ListCell*cell;ListCell*prev;ListCell*next;/*Outerlooprepeatsuntilwefindnomoredeductions*/do{found=false;/*ProcesstheLEFTJOINclauses*/prev=NULL;for(cell=list_head(root->left_join_clauses);cell;cell=next)//遍历left_join_clauses{RestrictInfo*rinfo=(RestrictInfo*)lfirst(cell);next=lnext(cell);if(reconsider_outer_join_clause(root,rinfo,true)){found=true;/*removeitfromthelist*/root->left_join_clauses=list_delete_cell(root->left_join_clauses,cell,prev);//如可以,则去掉连接条件(移到约束条件中)/*wethrowitbackanyway(seenotesabove)*//*butthethrown-backclausehasnoextraselectivity*/rinfo->norm_selec=2.0;rinfo->outer_selec=1.0;distribute_restrictinfo_to_rels(root,rinfo);//分发到RelOptInfo中}elseprev=cell;}/*ProcesstheRIGHTJOINclauses*/prev=NULL;for(cell=list_head(root->right_join_clauses);cell;cell=next)//处理右连接{RestrictInfo*rinfo=(RestrictInfo*)lfirst(cell);next=lnext(cell);if(reconsider_outer_join_clause(root,rinfo,false)){found=true;/*removeitfromthelist*/root->right_join_clauses=list_delete_cell(root->right_join_clauses,cell,prev);/*wethrowitbackanyway(seenotesabove)*//*butthethrown-backclausehasnoextraselectivity*/rinfo->norm_selec=2.0;rinfo->outer_selec=1.0;distribute_restrictinfo_to_rels(root,rinfo);}elseprev=cell;}/*ProcesstheFULLJOINclauses*/prev=NULL;for(cell=list_head(root->full_join_clauses);cell;cell=next)//全连接{RestrictInfo*rinfo=(RestrictInfo*)lfirst(cell);next=lnext(cell);if(reconsider_full_join_clause(root,rinfo)){found=true;/*removeitfromthelist*/root->full_join_clauses=list_delete_cell(root->full_join_clauses,cell,prev);/*wethrowitbackanyway(seenotesabove)*//*butthethrown-backclausehasnoextraselectivity*/rinfo->norm_selec=2.0;rinfo->outer_selec=1.0;distribute_restrictinfo_to_rels(root,rinfo);}elseprev=cell;}}while(found);//处理连接条件链表中余下的条件/*Now,anyremainingclauseshavetobethrownback*/foreach(cell,root->left_join_clauses){RestrictInfo*rinfo=(RestrictInfo*)lfirst(cell);distribute_restrictinfo_to_rels(root,rinfo);}foreach(cell,root->right_join_clauses){RestrictInfo*rinfo=(RestrictInfo*)lfirst(cell);distribute_restrictinfo_to_rels(root,rinfo);}foreach(cell,root->full_join_clauses){RestrictInfo*rinfo=(RestrictInfo*)lfirst(cell);distribute_restrictinfo_to_rels(root,rinfo);}}/**reconsider_outer_join_clausesforasingleLEFT/RIGHTJOINclause**Returnstrueifwewereabletopropagateaconstantthroughtheclause.*/staticboolreconsider_outer_join_clause(PlannerInfo*root,RestrictInfo*rinfo,boolouter_on_left){Expr*outervar,*innervar;Oidopno,collation,left_type,right_type,inner_datatype;Relidsinner_relids,inner_nullable_relids;ListCell*lc1;Assert(is_opclause(rinfo->clause));opno=((OpExpr*)rinfo->clause)->opno;collation=((OpExpr*)rinfo->clause)->inputcollid;/*Ifclauseisouterjoin_delayed,operatormustbestrict*/if(rinfo->outerjoin_delayed&&!op_strict(opno))returnfalse;/*Extractneededinfofromtheclause*/op_input_types(opno,&left_type,&right_type);if(outer_on_left){outervar=(Expr*)get_leftop(rinfo->clause);innervar=(Expr*)get_rightop(rinfo->clause);inner_datatype=right_type;inner_relids=rinfo->right_relids;}else{outervar=(Expr*)get_rightop(rinfo->clause);innervar=(Expr*)get_leftop(rinfo->clause);inner_datatype=left_type;inner_relids=rinfo->left_relids;}inner_nullable_relids=bms_intersect(inner_relids,rinfo->nullable_relids);/*ScanEquivalenceClassesforamatchtooutervar*/foreach(lc1,root->eq_classes)//遍历等价类{EquivalenceClass*cur_ec=(EquivalenceClass*)lfirst(lc1);boolmatch;ListCell*lc2;/*IgnoreECunlessitcontainspseudoconstants*/if(!cur_ec->ec_has_const)continue;/*NevermatchtoavolatileEC*/if(cur_ec->ec_has_volatile)continue;/*Ithastomatchtheouter-joinclauseastosemantics,too*/if(collation!=cur_ec->ec_collation)continue;if(!equal(rinfo->mergeopfamilies,cur_ec->ec_opfamilies))continue;/*Doesitcontainamatchtooutervar?*/match=false;foreach(lc2,cur_ec->ec_members){EquivalenceMember*cur_em=(EquivalenceMember*)lfirst(lc2);Assert(!cur_em->em_is_child);/*nochildrenyet*/if(equal(outervar,cur_em->em_expr)){match=true;break;}}if(!match)continue;/*nomatch,soignorethisEC*//**Yesitdoes!TrytogenerateaclauseINNERVAR=CONSTANTforeach*CONSTANTintheEC.Notethatwemustsucceedwithatleastone*constantbeforewecandecidetothrowawaytheouter-joinclause.*/match=false;foreach(lc2,cur_ec->ec_members){EquivalenceMember*cur_em=(EquivalenceMember*)lfirst(lc2);Oideq_op;RestrictInfo*newrinfo;if(!cur_em->em_is_const)continue;/*ignorenon-constmembers*/eq_op=select_equality_operator(cur_ec,inner_datatype,cur_em->em_datatype);if(!OidIsValid(eq_op))continue;/*can'tgenerateequality*/newrinfo=build_implied_join_equality(eq_op,cur_ec->ec_collation,innervar,cur_em->em_expr,bms_copy(inner_relids),bms_copy(inner_nullable_relids),cur_ec->ec_min_security);if(process_equivalence(root,&newrinfo,true))match=true;}/**IfwewereabletoequateINNERVARtoanyconstant,reportsuccess.*Otherwise,falloutofthesearchloop,sinceweknowtheOUTERVAR*appearsinatmostoneEC.*/if(match)returntrue;elsebreak;}returnfalse;/*failedtomakeanydeduction*/}

generate_base_implied_equalities函数
该函数遍历所有的等价类,找出一个隐含的条件然后分发到RelOptInfo中,这样做的目的是为了在连接(join)前过滤元组,减少参与运算的元组数量.

/**generate_base_implied_equalities*Generateanyrestrictionclausesthatwecandeducefromequivalence*classes.**WhenanECcontainspseudoconstants,ourstrategyistogenerate*"member=const1"clauseswhereconst1isthefirstconstantmember,for*everyothermember(includingotherconstants).Ifweareabletodothis*thenwedon'tneedany"var=var"comparisonsbecausewe'vesuccessfully*constrainedallthevarsattheirpointsofcreation.Ifwefailto*generateanyoftheseclausesduetolackofcross-typeoperators,wefall*backtothe"ec_broken"strategydescribedbelow.(XXXifthereare*multipleconstantsofdifferenttypes,it'spossiblethatwemightsucceed*informingalltherequiredclausesifwestartedfromadifferentconst*member;butthisseemsasufficientlyhokeycornercasetonotbeworth*spendinglotsofcycleson.)**ForECsthatcontainnopseudoconstants,wegeneratederivedclauses*"member1=member2"foreachpairofmembersbelongingtothesamebase*relation(actually,iftherearemorethantwoforthesamebaserelation,*weonlyneedenoughclausestolinkeachtoeachother).Thisprovides*thebasecasefortherecursion:eachrowemittedbyabaserelationscan*willconstrainallcomputablemembersoftheECtobeequal.Aseach*joinpathisformed,we'lladdadditionalderivedclauseson-the-fly*tomaintainthisinvariant(seegenerate_join_implied_equalities).**IftheopfamiliesusedbytheECdonotprovidecompletesetsofcross-type*equalityoperators,itispossiblethatwewillfailtogenerateaclause*thatmustbegeneratedtomaintaintheinvariant.(Anexample:given*"WHEREa.x=b.yANDb.y=a.z",theschemebreaksdownifwecannot*generate"a.x=a.z"asarestrictionclauseforA.)Inthiscasewemark*theEC"ec_broken"andfallbacktoregurgitatingitsoriginalsource*RestrictInfosatappropriatetimes.Wedonottrytoretractanyderived*clausesalreadygeneratedfromthebrokenEC,sotheresultingplancould*bepoorduetobadselectivityestimatescausedbyredundantclauses.But*thecorrectsolutiontothatistofixtheopfamilies...**Equalityclausesderivedbythisfunctionarepassedoffto*process_implied_equality(inplan/initsplan.c)tobeinsertedintothe*restrictinfodatastructures.Notethatthismustbecalledafterinitial*scanningofthequalsandbeforePathconstructionbegins.**WemakenoattempttoavoidgeneratingduplicateRestrictInfoshere:we*don'tsearchec_sourcesformatches,norputthecreatedRestrictInfos*intoec_derives.Doingsowouldrequiresomeslightlyuglychangesin*initsplan.c'sAPI,andthere'snorealadvantage,becausetheclauses*generatedherecan'tduplicateanythingwewillgenerateforjoinsanyway.*/voidgenerate_base_implied_equalities(PlannerInfo*root){ListCell*lc;Indexrti;foreach(lc,root->eq_classes)//遍历等价类{EquivalenceClass*ec=(EquivalenceClass*)lfirst(lc);Assert(ec->ec_merged==NULL);/*elseshouldn'tbeinlist*/Assert(!ec->ec_broken);/*notyetanyway...*//*Single-memberECswon'tgenerateanydeductions*/if(list_length(ec->ec_members)<=1)//小于1个成员,无需处理类continue;if(ec->ec_has_const)//有常量generate_base_implied_equalities_const(root,ec);else//无常量generate_base_implied_equalities_no_const(root,ec);/*Recoverifwefailedtogeneraterequiredderivedclauses*/if(ec->ec_broken)//处理失败个案generate_base_implied_equalities_broken(root,ec);}/**Thisisalsoahandyplacetomarkbaserels(whichshouldallexistby*now)withflagsshowingwhethertheyhavependingeclassjoins.*/for(rti=1;rti<root->simple_rel_array_size;rti++)//设置标记{RelOptInfo*brel=root->simple_rel_array[rti];if(brel==NULL)continue;brel->has_eclass_joins=has_relevant_eclass_joinclause(root,brel);}}/**generate_base_implied_equalitieswhenECcontainspseudoconstant(s)*/staticvoidgenerate_base_implied_equalities_const(PlannerInfo*root,EquivalenceClass*ec){EquivalenceMember*const_em=NULL;ListCell*lc;/**Inthetrivialcasewherewejusthadone"var=const"clause,push*theoriginalclausebackintothemainplannermachinery.Thereis*nothingtobegainedbydoingitdifferently,andwesavetheeffortto*re-buildandre-analyzeanequalityclausethatwillbeexactly*equivalenttotheoldone.*/if(list_length(ec->ec_members)==2&&list_length(ec->ec_sources)==1){RestrictInfo*restrictinfo=(RestrictInfo*)linitial(ec->ec_sources);if(bms_membership(restrictinfo->required_relids)!=BMS_MULTIPLE){distribute_restrictinfo_to_rels(root,restrictinfo);return;}}/**Findtheconstantmembertouse.Wepreferanactualconstantto*pseudo-constants(suchasParams),becausetheconstraintexclusion*machinerymightbeabletoexcluderelationsonthebasisofgenerated*"var=const"equalities,but"var=param"won'tworkforthat.*/foreach(lc,ec->ec_members)//获取常量Member{EquivalenceMember*cur_em=(EquivalenceMember*)lfirst(lc);if(cur_em->em_is_const){const_em=cur_em;if(IsA(cur_em->em_expr,Const))break;}}Assert(const_em!=NULL);/*Generateaderivedequalityagainsteachothermember*/foreach(lc,ec->ec_members){EquivalenceMember*cur_em=(EquivalenceMember*)lfirst(lc);Oideq_op;Assert(!cur_em->em_is_child);/*nochildrenyet*/if(cur_em==const_em)continue;eq_op=select_equality_operator(ec,cur_em->em_datatype,const_em->em_datatype);if(!OidIsValid(eq_op)){/*failed...*/ec->ec_broken=true;break;}process_implied_equality(root,eq_op,ec->ec_collation,cur_em->em_expr,const_em->em_expr,bms_copy(ec->ec_relids),bms_union(cur_em->em_nullable_relids,const_em->em_nullable_relids),ec->ec_min_security,ec->ec_below_outer_join,cur_em->em_is_const);//下推条件}}/**generate_base_implied_equalitieswhenECcontainsnopseudoconstants*/staticvoidgenerate_base_implied_equalities_no_const(PlannerInfo*root,EquivalenceClass*ec){EquivalenceMember**prev_ems;ListCell*lc;/**WescantheECmembersonceandtrackthelast-seenmemberforeach*baserelation.Whenweseeanothermemberofthesamebaserelation,*wegenerate"prev_mem=cur_mem".Thisresultsintheminimumnumber*ofderivedclauses,butit'spossiblethatitwillfailwhena*differentorderingwouldsucceed.XXXFIXME:useaUNION-FIND*algorithmsimilartothewaywebuildmergedECs.(Usealist-of-lists*foreachrel.)*/prev_ems=(EquivalenceMember**)palloc0(root->simple_rel_array_size*sizeof(EquivalenceMember*));foreach(lc,ec->ec_members){EquivalenceMember*cur_em=(EquivalenceMember*)lfirst(lc);intrelid;Assert(!cur_em->em_is_child);/*nochildrenyet*/if(!bms_get_singleton_member(cur_em->em_relids,&relid))continue;Assert(relid<root->simple_rel_array_size);if(prev_ems[relid]!=NULL){EquivalenceMember*prev_em=prev_ems[relid];Oideq_op;eq_op=select_equality_operator(ec,prev_em->em_datatype,cur_em->em_datatype);if(!OidIsValid(eq_op)){/*failed...*/ec->ec_broken=true;break;}process_implied_equality(root,eq_op,ec->ec_collation,prev_em->em_expr,cur_em->em_expr,bms_copy(ec->ec_relids),bms_union(prev_em->em_nullable_relids,cur_em->em_nullable_relids),ec->ec_min_security,ec->ec_below_outer_join,false);}prev_ems[relid]=cur_em;}pfree(prev_ems);/**WealsohavetomakesurethatalltheVarsusedinthememberclauses*willbeavailableatanyjoinnodewemighttrytoreferencethemat.*ForthemomentweforcealltheVarstobeavailableatalljoinnodes*forthiseclass.Perhapsthiscouldbeimprovedbydoingsome*pre-analysisofwhichmembersweprefertojoin,butit'snoworsethan*whathappenedinthepre-8.3code.*/foreach(lc,ec->ec_members){EquivalenceMember*cur_em=(EquivalenceMember*)lfirst(lc);List*vars=pull_var_clause((Node*)cur_em->em_expr,PVC_RECURSE_AGGREGATES|PVC_RECURSE_WINDOWFUNCS|PVC_INCLUDE_PLACEHOLDERS);add_vars_to_targetlist(root,vars,ec->ec_relids,false);list_free(vars);}}/**generate_base_implied_equalitiescleanupafterfailure**Whatwemustdohereispushanyzero-orone-relationsourceRestrictInfos*oftheECbackintothemainrestrictinfodatastructures.Multi-relation*clauseswillberegurgitatedlaterbygenerate_join_implied_equalities().*(Wedoitthiswaytomaintaincontinuitywiththecasethatec_broken*becomessetonlyafterwe'vegoneupajoinlevelortwo.)However,for*anECthatcontainsconstants,wecanadoptasimplerstrategyandjust*throwbackallthesourceRestrictInfosimmediately;thatworksbecause*weknowthatsuchanECcan'tbecomebrokenlater.(Thisrulejustifies*ignoringec_has_constECsingenerate_join_implied_equalities,evenwhen*theyarebroken.)*/staticvoidgenerate_base_implied_equalities_broken(PlannerInfo*root,EquivalenceClass*ec){ListCell*lc;foreach(lc,ec->ec_sources){RestrictInfo*restrictinfo=(RestrictInfo*)lfirst(lc);if(ec->ec_has_const||bms_membership(restrictinfo->required_relids)!=BMS_MULTIPLE)distribute_restrictinfo_to_rels(root,restrictinfo);}}/**process_implied_equality*Createarestrictinfoitemthatsays"item1opitem2",andpushit*intotheappropriatelists.(Inpracticeopnoisalwaysabtree*equalityoperator.)**"qualscope"isthenominalsyntacticleveltoimputetotherestrictinfo.*Thismustcontainatleastalltherelsusedintheexpressions,butit*isusedonlytosetthequalapplicationlevelwhenbothexprsare*variable-free.Otherwisethequalisappliedatthelowestjoinlevel*thatprovidesallitsvariables.**"nullable_relids"isthesetofrelidsusedintheexpressionsthatare*potentiallynullablebelowtheexpressions.(Thishastobesuppliedby*callerbecausethisfunctionisusedafterdeconstruct_jointree,sowe*don'thaveknowledgeofwheretheclauseitemscamefrom.)**"security_level"isthesecurityleveltoassigntothenewrestrictinfo.**"both_const"indicateswhetherbothitemsareknownpseudo-constant;*inthiscaseitisworthapplyingeval_const_expressions()incasewe*canproduceconstantTRUEorconstantFALSE.(Otherwiseit'snot,*becausetheexpressionswentthrougheval_const_expressionsalready.)**Note:thisfunctionwillcopyitem1anditem2,butitiscaller's*responsibilitytomakesurethattheRelidsparametersarefreshcopies*notsharedwithotheruses.**ThisiscurrentlyusedonlywhenanEquivalenceClassisfoundto*containpseudoconstants.Seepath/pathkeys.cformoredetails.*/voidprocess_implied_equality(PlannerInfo*root,Oidopno,Oidcollation,Expr*item1,Expr*item2,Relidsqualscope,Relidsnullable_relids,Indexsecurity_level,boolbelow_outer_join,boolboth_const){Expr*clause;/**Buildthenewclause.Copytoensureitsharesnosubstructurewith*original(thisisnecessaryincasetherearesubselectsinthere...)*/clause=make_opclause(opno,BOOLOID,/*opresulttype*/false,/*opretset*/copyObject(item1),copyObject(item2),InvalidOid,collation);//构造条件表达式/*Ifbothconstant,trytoreducetoabooleanconstant.*/if(both_const)//{clause=(Expr*)eval_const_expressions(root,(Node*)clause);/*IfweproducedconstTRUE,justdroptheclause*/if(clause&&IsA(clause,Const)){Const*cclause=(Const*)clause;Assert(cclause->consttype==BOOLOID);if(!cclause->constisnull&&DatumGetBool(cclause->constvalue))return;}}/**Pushthenewclauseintoalltheappropriaterestrictinfolists.*/distribute_qual_to_rels(root,(Node*)clause,true,below_outer_join,JOIN_INNER,security_level,qualscope,NULL,NULL,nullable_relids,NULL);//分发条件至RelOptInfo}三、跟踪分析

测试脚本:

testdb=#explainverboseselectt1.dwbh,t2.grbhtestdb-#fromt_dwxxt1leftjoint_grxxt2ont1.dwbh=t2.dwbhandt2.dwbh='1001'testdb-#orderbyt2.dwbh;QUERYPLAN-----------------------------------------------------------------------------------Sort(cost=19.16..19.56rows=160width=114)Output:t1.dwbh,t2.grbh,t2.dwbhSortKey:t2.dwbh->HashLeftJoin(cost=1.09..13.30rows=160width=114)Output:t1.dwbh,t2.grbh,t2.dwbhHashCond:((t1.dwbh)::text=(t2.dwbh)::text)->SeqScanonpublic.t_dwxxt1(cost=0.00..11.60rows=160width=38)Output:t1.dwmc,t1.dwbh,t1.dwdz->Hash(cost=1.07..1.07rows=1width=76)Output:t2.grbh,t2.dwbh->SeqScanonpublic.t_grxxt2(cost=0.00..1.07rows=1width=76)Output:t2.grbh,t2.dwbhFilter:((t2.dwbh)::text='1001'::text)(13rows)

跟踪分析,启动gdb

(gdb)bplanmain.c:161Breakpoint1at0x76958b:fileplanmain.c,line161.(gdb)cContinuing.Breakpoint1,query_planner(root=0x2c92a88,tlist=0x2c5f048,qp_callback=0x76e906<standard_qp_callback>,qp_extra=0x7fffed6e9c10)atplanmain.c:163warning:Sourcefileismorerecentthanexecutable.163reconsider_outer_join_clauses(root);

调用前检查root(PlannerInfo)->simple_rel_array数组的内存结构,可以看到baserestrictinfo和joininfo均为NULL

(gdb)p*root->simple_rel_array[1]$2={type=T_RelOptInfo,reloptkind=RELOPT_BASEREL,relids=0x2c5fdd0,rows=0,consider_startup=false,consider_param_startup=false,consider_parallel=false,reltarget=0x2c5fde8,pathlist=0x0,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=1,reltablespace=0,rtekind=RTE_RELATION,min_attr=-7,max_attr=3,attr_needed=0x2c5fe38,attr_widths=0x2c5fec8,lateral_vars=0x0,lateral_referencers=0x0,indexlist=0x2c60160,statlist=0x0,pages=10,tuples=160,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*root->simple_rel_array[2]$3={type=T_RelOptInfo,reloptkind=RELOPT_BASEREL,relids=0x2c60860,rows=0,consider_startup=false,consider_param_startup=false,consider_parallel=false,reltarget=0x2c60878,pathlist=0x0,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=2,reltablespace=0,rtekind=RTE_RELATION,min_attr=-7,max_attr=5,attr_needed=0x2c608c8,attr_widths=0x2c60958,lateral_vars=0x0,lateral_referencers=0x0,indexlist=0x0,statlist=0x0,pages=1,tuples=6,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)

调用reconsider_outer_join_clauses,注意joininfo,填入了相应的数据

(gdb)p*root->simple_rel_array[1]$4={type=T_RelOptInfo,reloptkind=RELOPT_BASEREL,relids=0x2c5fdd0,rows=0,consider_startup=false,consider_param_startup=false,consider_parallel=false,reltarget=0x2c5fde8,pathlist=0x0,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=1,reltablespace=0,rtekind=RTE_RELATION,min_attr=-7,max_attr=3,attr_needed=0x2c5fe38,attr_widths=0x2c5fec8,lateral_vars=0x0,lateral_referencers=0x0,indexlist=0x2c60160,statlist=0x0,pages=10,tuples=160,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=0x2c61780,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*root->simple_rel_array[2]$5={type=T_RelOptInfo,reloptkind=RELOPT_BASEREL,relids=0x2c60860,rows=0,consider_startup=false,consider_param_startup=false,consider_parallel=false,reltarget=0x2c60878,pathlist=0x0,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=2,reltablespace=0,rtekind=RTE_RELATION,min_attr=-7,max_attr=5,attr_needed=0x2c608c8,attr_widths=0x2c60958,lateral_vars=0x0,lateral_referencers=0x0,indexlist=0x0,statlist=0x0,pages=1,tuples=6,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=0x2c617d0,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}

调用generate_base_implied_equalities,注意root->simple_rel_array[2]->baserestrictinfo,条件已下推至限制条件(原为连接条件)

(gdb)p*root->simple_rel_array[2]$7={type=T_RelOptInfo,reloptkind=RELOPT_BASEREL,relids=0x2c60860,rows=0,consider_startup=false,consider_param_startup=false,consider_parallel=false,reltarget=0x2c60878,pathlist=0x0,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=2,reltablespace=0,rtekind=RTE_RELATION,min_attr=-7,max_attr=5,attr_needed=0x2c608c8,attr_widths=0x2c60958,lateral_vars=0x0,lateral_referencers=0x0,indexlist=0x0,statlist=0x0,pages=1,tuples=6,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=0x2c61820,baserestrictcost={startup=0,per_tuple=0},baserestrict_min_security=0,joininfo=0x2c617d0,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}

“PostgreSQL中函数reconsider_outer_join_clauses的主要实现逻辑是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！