本篇内容主要讲解“PostgreSQL查询优化中怎么上拉子链接”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“PostgreSQL查询优化中怎么上拉子链接”吧!

查询树

convert_EXISTS_sublink_to_join函数源码:

/**convert_EXISTS_sublink_to_join:trytoconvertanEXISTSSubLinktoajoin**TheAPIofthisfunctionisidenticaltoconvert_ANY_sublink_to_join's,*exceptthatwealsosupportthecasewherethecallerhasfoundNOTEXISTS,*soweneedanadditionalinputparameter"under_not".*逻辑与ANY一致,为了支持NOTEXISTS,多加了一个参数under_not*/JoinExpr*convert_EXISTS_sublink_to_join(PlannerInfo*root,SubLink*sublink,boolunder_not,Relidsavailable_rels){JoinExpr*result;//返回结果Query*parse=root->parse;//查询树Query*subselect=(Query*)sublink->subselect;//子查询Node*whereClause;//where语句intrtoffset;//intvarno;Relidsclause_varnos;Relidsupper_varnos;Assert(sublink->subLinkType==EXISTS_SUBLINK);/**Can'tflattenifitcontainsWITH.(Wecouldarrangetopullupthe*WITHintotheparentquery'scteList,butthatriskschangingthe*semantics,sinceaWITHoughttobeexecutedonceperassociatedquery*call.)Notethatconvert_ANY_sublink_to_joindoesn'thavetoreject*thiscase,sinceitjustproducesasubqueryRTEthatdoesn'thaveto*getflattenedintotheparentquery.*/if(subselect->cteList)//存在With子句,返回returnNULL;/**Copythesubquerysowecanmodifyitsafely(seecommentsin*make_subplan).*/subselect=copyObject(subselect);/**Seeifthesubquerycanbesimplifiedbasedontheknowledgethatit's*beingusedinEXISTS().Ifwearen'tabletogetridofits*targetlist,wehavetofail,becausethepullupoperationleavesus*withnoplacetoevaluatethetargetlist.*///能否handletargetList?不行,则退出//如果含有集合操作/聚合操作/Having子句等,子链接不能提升if(!simplify_EXISTS_query(root,subselect))returnNULL;/**Thesubquerymusthaveanonemptyjointree,elsewewon'thaveajoin.*/if(subselect->jointree->fromlist==NIL)//子查询没有查询主体,退出returnNULL;/**SeparateouttheWHEREclause.(Wecouldtheoreticallyalsoremove*top-levelplainJOIN/ONclauses,butit'sprobablynotworththe*trouble.)*/whereClause=subselect->jointree->quals;//子查询条件语句单独保存subselect->jointree->quals=NULL;//子查询的条件语句设置为NULL/**Therestofthesub-selectmustnotrefertoanyVarsoftheparent*query.(Varsofhigherlevelsshouldbeokay,though.)*/if(contain_vars_of_level((Node*)subselect,1))//去掉条件语句后,如仍依赖父查询的Vars,退出returnNULL;/**Ontheotherhand,theWHEREclausemustcontainsomeVarsofthe*parentquery,elseit'snotgonnabeajoin.*/if(!contain_vars_of_level(whereClause,1))//条件语句必须含有父查询的Vars,否则不构成连接,退出returnNULL;/**Wedon'triskoptimizingiftheWHEREclauseisvolatile,either.*/if(contain_volatile_functions(whereClause))//条件语句存在易变函数(如随机函数等)returnNULL;/**Preparetopullupthesub-selectintotoprangetable.**Werelyhereontheassumptionthattheouterqueryhasnoreferences*totheinner(necessarilytrue).Thereforethisisaloteasierthan*whatpull_up_subquerieshastogothrough.**Infact,it'seveneasierthanwhatconvert_ANY_sublink_to_joinhasto*do.Themachinationsofsimplify_EXISTS_queryensuredthatthereis*nothinginterestinginthesubqueryexceptanrtableandjointree,and*eventhejointreeFromExprnolongerhasquals.Sowecanjustappend*thertabletoourownandusetheFromExprinourjointree.Butfirst,*adjustalllevel-zerovarnosinthesubquerytoaccountforthertable*merger.*/rtoffset=list_length(parse->rtable);//获取rtable的长度(新RTE插入的偏移)//调整子查询中varno为0(指向rtable)的Vars,varno调整为父查询rtable的index//(详见依赖函数解析)OffsetVarNodes((Node*)subselect,rtoffset,0);OffsetVarNodes(whereClause,rtoffset,0);/**Upper-levelvarsinsubquerywillnowbeonelevelclosertotheir*parentthanbefore;inparticular,anythingthathadbeenlevel1*becomeslevelzero.*///子查询中与父查询相关的Vars,varlevelsup需要从Leve1变为Level0//在子查询中,这些Vars的varlevelsup为1,表示依赖于父查询(上一层)的Vars,提升后不存在此依赖,需改为0IncrementVarSublevelsUp((Node*)subselect,-1,1);IncrementVarSublevelsUp(whereClause,-1,1);/**NowthattheWHEREclauseisadjustedtomatchtheparentquery*environment,wecaneasilyidentifyallthelevel-zerorelsituses.*Theones<=rtoffsetbelongtotheupperquery;theones>rtoffsetdo*not.*/clause_varnos=pull_varnos(whereClause);upper_varnos=NULL;while((varno=bms_first_member(clause_varnos))>=0){if(varno<=rtoffset)upper_varnos=bms_add_member(upper_varnos,varno);}bms_free(clause_varnos);Assert(!bms_is_empty(upper_varnos));/**Nowthatwe'vegotthesetofupper-levelvarnos,wecanmakethelast*check:onlyavailable_relscanbereferenced.*/if(!bms_is_subset(upper_varnos,available_rels))returnNULL;/*Nowwecanattachthemodifiedsubqueryrtabletotheparent*///把子查询的rtable拼接到父查询的rtable中parse->rtable=list_concat(parse->rtable,subselect->rtable);//构造JoinExpr/**Andfinally,buildtheJoinExprnode.*/result=makeNode(JoinExpr);result->jointype=under_not?JOIN_ANTI:JOIN_SEMI;result->isNatural=false;result->larg=NULL;/*callermustfillthisin*//*flattenouttheFromExprnodeifit'suseless*/if(list_length(subselect->jointree->fromlist)==1)result->rarg=(Node*)linitial(subselect->jointree->fromlist);elseresult->rarg=(Node*)subselect->jointree;result->usingClause=NIL;result->quals=whereClause;result->alias=NULL;result->rtindex=0;/*wedon'tneedanRTEforit*/returnresult;}二、基础信息

相关数据结构
1、Var

/**Var-expressionnoderepresentingavariable(ie,atablecolumn)**Note:duringparsing/planning,varnoold/varoattnoarealwaysjustcopies*ofvarno/varattno.Atthetailendofplanning,Varnodesappearingin*upper-levelplannodesarereassignedtopointtotheoutputsoftheir*subplans;forexample,inajoinnodevarnobecomesINNER_VARorOUTER_VAR*andvarattnobecomestheindexoftheproperelementofthatsubplan's*targetlist.Similarly,INDEX_VARisusedtoidentifyVarsthatreference*anindexcolumnratherthanaheapcolumn.(InForeignScanandCustomScan*plannodes,INDEX_VARisabusedtosignifyreferencestocolumnsofa*customscantupletype.)Inallthesecases,varnoold/varoattnoholdthe*originalvalues.Thecodedoesn'treallyneedvarnoold/varoattno,butthey*areveryusefulfordebuggingandinterpretingcompletedplans,sowekeep*themaround.*/#defineINNER_VAR65000/*referencetoinnersubplan*/#defineOUTER_VAR65001/*referencetooutersubplan*/#defineINDEX_VAR65002/*referencetoindexcolumn*/#defineIS_SPECIAL_VARNO(varno)((varno)>=INNER_VAR)/*SymbolsfortheindexesofthespecialRTEentriesinrules*/#definePRS2_OLD_VARNO1#definePRS2_NEW_VARNO2typedefstructVar{Exprxpr;Indexvarno;/*indexofthisvar'srelationintherange*table,orINNER_VAR/OUTER_VAR/INDEX_VAR*/AttrNumbervarattno;/*attributenumberofthisvar,orzerofor*allattrs("whole-rowVar")*/Oidvartype;/*pg_typeOIDforthetypeofthisvar*/int32vartypmod;/*pg_attributetypmodvalue*/Oidvarcollid;/*OIDofcollation,orInvalidOidifnone*/Indexvarlevelsup;/*forsubqueryvariablesreferencingouter*relations;0inanormalvar,>0meansN*levelsup*/Indexvarnoold;/*originalvalueofvarno,fordebugging*/AttrNumbervaroattno;/*originalvalueofvarattno*/intlocation;/*tokenlocation,or-1ifunknown*/}Var;

XX_one_pos

/**Lookuptablestoavoidneedforbit-by-bitgroveling**rightmost_one_pos[x]givesthebitnumber(0-7)oftherightmostonebit*inanonzerobytevaluex.Theentryforx=0isneverused.**leftmost_one_pos[x]givesthebitnumber(0-7)oftheleftmostonebitina*nonzerobytevaluex.Theentryforx=0isneverused.**number_of_ones[x]givesthenumberofone-bits(0-8)inabytevaluex.**Wecouldmakethesetableslargerandreducethenumberofiterations*inthefunctionsthatusethem,butbytewiseshiftsandmasksare*especiallyfastonmanymachines,soworkingabyteatatimeseemsbest.*/staticconstuint8rightmost_one_pos[256]={0,0,1,0,2,0,1,0,3,0,1,0,2,0,1,0,4,0,1,0,2,0,1,0,3,0,1,0,2,0,1,0,5,0,1,0,2,0,1,0,3,0,1,0,2,0,1,0,4,0,1,0,2,0,1,0,3,0,1,0,2,0,1,0,6,0,1,0,2,0,1,0,3,0,1,0,2,0,1,0,4,0,1,0,2,0,1,0,3,0,1,0,2,0,1,0,5,0,1,0,2,0,1,0,3,0,1,0,2,0,1,0,4,0,1,0,2,0,1,0,3,0,1,0,2,0,1,0,7,0,1,0,2,0,1,0,3,0,1,0,2,0,1,0,4,0,1,0,2,0,1,0,3,0,1,0,2,0,1,0,5,0,1,0,2,0,1,0,3,0,1,0,2,0,1,0,4,0,1,0,2,0,1,0,3,0,1,0,2,0,1,0,6,0,1,0,2,0,1,0,3,0,1,0,2,0,1,0,4,0,1,0,2,0,1,0,3,0,1,0,2,0,1,0,5,0,1,0,2,0,1,0,3,0,1,0,2,0,1,0,4,0,1,0,2,0,1,0,3,0,1,0,2,0,1,0};staticconstuint8leftmost_one_pos[256]={0,0,1,1,2,2,2,2,3,3,3,3,3,3,3,3,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7};staticconstuint8number_of_ones[256]={0,1,1,2,1,2,2,3,1,2,2,3,2,3,3,4,1,2,2,3,2,3,3,4,2,3,3,4,3,4,4,5,1,2,2,3,2,3,3,4,2,3,3,4,3,4,4,5,2,3,3,4,3,4,4,5,3,4,4,5,4,5,5,6,1,2,2,3,2,3,3,4,2,3,3,4,3,4,4,5,2,3,3,4,3,4,4,5,3,4,4,5,4,5,5,6,2,3,3,4,3,4,4,5,3,4,4,5,4,5,5,6,3,4,4,5,4,5,5,6,4,5,5,6,5,6,6,7,1,2,2,3,2,3,3,4,2,3,3,4,3,4,4,5,2,3,3,4,3,4,4,5,3,4,4,5,4,5,5,6,2,3,3,4,3,4,4,5,3,4,4,5,4,5,5,6,3,4,4,5,4,5,5,6,4,5,5,6,5,6,6,7,2,3,3,4,3,4,4,5,3,4,4,5,4,5,5,6,3,4,4,5,4,5,5,6,4,5,5,6,5,6,6,7,3,4,4,5,4,5,5,6,4,5,5,6,5,6,6,7,4,5,5,6,5,6,6,7,5,6,6,7,6,7,7,8};

依赖的函数
simplify_EXISTS_query

/**simplify_EXISTS_query:removeanyuselessstuffinanEXISTS'ssubquery**TheonlythingthatmattersaboutanEXISTSqueryiswhetheritreturns*zeroormorethanzerorows.Therefore,wecanremovecertainSQLfeatures*thatwon'taffectthat.Theonlypartthatisreallylikelytomatterin*typicalusageissimplifyingthetargetlist:it'sacommonhabittowrite*"SELECT*FROM"eventhoughthereisnoneedtoevaluateanycolumns.**Note:bysuppressingthetargetlistwecouldcauseanobservablebehavioral*change,namelythatanyerrorsthatmightoccurinevaluatingthetlist*won'toccur,norwillotherside-effectsofvolatilefunctions.Thisseems*unlikelytobotheranyoneinpractice.**Returnstrueifwasabletodiscardthetargetlist,elsefalse.*/staticboolsimplify_EXISTS_query(PlannerInfo*root,Query*query){/**Wedon'ttrytosimplifyatallifthequeryusessetoperations,*aggregates,groupingsets,SRFs,modifyingCTEs,HAVING,OFFSET,orFOR*UPDATE/SHARE;noneoftheseseemlikelyinnormalusageandtheir*possibleeffectsarecomplex.(Note:wecouldignorean"OFFSET0"*clause,butthattraditionallyisusedasanoptimizationfence,sowe*don't.)*/if(query->commandType!=CMD_SELECT||query->setOperations||query->hasAggs||query->groupingSets||query->hasWindowFuncs||query->hasTargetSRFs||query->hasModifyingCTE||query->havingQual||query->limitOffset||query->rowMarks)returnfalse;/**LIMITwithaconstantpositive(orNULL)valuedoesn'taffectthe*semanticsofEXISTS,solet'signoresuchclauses.Thisisworthdoing*becausepeopleaccustomedtocertainotherDBMSesmaybeinthehabit*ofwritingEXISTS(SELECT...LIMIT1)asanoptimization.Ifthere'sa*LIMITwithanythingelseasargument,though,wecan'tsimplify.*/if(query->limitCount){/**TheLIMITclausehasnotyetbeenthrougheval_const_expressions,*sowehavetoapplythathere.Itmightseemlikethisisawaste*ofcycles,sincetheonlycaseplausiblyworthworryingaboutis*"LIMIT1"...butwhatwe'llactuallyseeis"LIMITint8(1::int4)",*sowehavetofoldconstantsorwe'renotgoingtorecognizeit.*/Node*node=eval_const_expressions(root,query->limitCount);Const*limit;/*Mightaswellupdatethequeryifwesimplifiedtheclause.*/query->limitCount=node;if(!IsA(node,Const))returnfalse;limit=(Const*)node;Assert(limit->consttype==INT8OID);if(!limit->constisnull&&DatumGetInt64(limit->constvalue)<=0)returnfalse;/*Whetherornotthetargetlistissafe,wecandroptheLIMIT.*/query->limitCount=NULL;}/**Otherwise,wecanthrowawaythetargetlist,aswellasanyGROUP,*WINDOW,DISTINCT,andORDERBYclauses;noneofthoseclauseswill*changeanonzero-rowsresulttozerorowsorviceversa.(Furthermore,*sinceourparsetreerepresentationoftheseclausesdependsonthe*targetlist,we'dbetterthrowthemawayifwedropthetargetlist.)*/query->targetList=NIL;query->groupClause=NIL;query->windowClause=NIL;query->distinctClause=NIL;query->sortClause=NIL;query->hasDistinctOn=false;returntrue;}

OffsetVarNodes

voidOffsetVarNodes(Node*node,intoffset,intsublevels_up){OffsetVarNodes_contextcontext;//上下文context.offset=offset;//保存传入的offsetcontext.sublevels_up=sublevels_up;/**MustbepreparedtostartwithaQueryorabareexpressiontree;if*it'saQuery,gostraighttoquery_tree_walkertomakesurethat*sublevels_updoesn'tgetincrementedprematurely.*/if(node&&IsA(node,Query)){Query*qry=(Query*)node;/**IfwearestartingataQuery,andsublevels_upiszero,thenwe*mustalsofixrangetableindexesintheQueryitself---namely*resultRelation,exclRelIndexandrowMarksentries.sublevels_up*cannotbezerowhenrecursingintoasubquery,sothere'snoneed*tohavethesamelogicinsideOffsetVarNodes_walker.*/if(sublevels_up==0){ListCell*l;if(qry->resultRelation)qry->resultRelation+=offset;if(qry->onConflict&&qry->onConflict->exclRelIndex)qry->onConflict->exclRelIndex+=offset;foreach(l,qry->rowMarks){RowMarkClause*rc=(RowMarkClause*)lfirst(l);rc->rti+=offset;}}query_tree_walker(qry,OffsetVarNodes_walker,(void*)&context,0);}elseOffsetVarNodes_walker(node,&context);}

IncrementVarSublevelsUp

voidIncrementVarSublevelsUp(Node*node,intdelta_sublevels_up,intmin_sublevels_up){IncrementVarSublevelsUp_contextcontext;context.delta_sublevels_up=delta_sublevels_up;context.min_sublevels_up=min_sublevels_up;/**MustbepreparedtostartwithaQueryorabareexpressiontree;if*it'saQuery,wedon'twanttoincrementsublevels_up.*/query_or_expression_tree_walker(node,IncrementVarSublevelsUp_walker,(void*)&context,QTW_EXAMINE_RTES);}

IncrementVarSublevelsUp

/**IncrementVarSublevelsUp-adjustVarnodeswhenpushingthemdownintree**FindallVarnodesinthegiventreehavingvarlevelsup>=min_sublevels_up,*andadddelta_sublevels_uptotheirvarlevelsupvalue.Thisisneededwhen*anexpressionthat'scorrectforsomenestinglevelisinsertedintoa*subquery.Ordinarilytheinitialcallhasmin_sublevels_up==0sothat*allVarsareaffected.Thepointofmin_sublevels_upisthatwecan*incrementitwhenwerecurseintoasublink,sothatlocalvariablesin*thatsublinkarenotaffected,onlyouterreferencestovarsthatbelong*totheexpression'soriginalquerylevelorparentsthereof.**Likewiseforothernodescontaininglevelsupfields,suchasAggref.**NOTE:althoughthishastheformofawalker,wecheatandmodifythe*Varnodesin-place.Thegivenexpressiontreeshouldhavebeencopied*earliertoensurethatnounwantedside-effectsoccur!*/typedefstruct{intdelta_sublevels_up;intmin_sublevels_up;}IncrementVarSublevelsUp_context;staticboolIncrementVarSublevelsUp_walker(Node*node,IncrementVarSublevelsUp_context*context){if(node==NULL)returnfalse;if(IsA(node,Var)){Var*var=(Var*)node;if(var->varlevelsup>=context->min_sublevels_up)var->varlevelsup+=context->delta_sublevels_up;returnfalse;/*donehere*/}if(IsA(node,CurrentOfExpr)){/*thisshouldnothappen*/if(context->min_sublevels_up==0)elog(ERROR,"cannotpushdownCurrentOfExpr");returnfalse;}if(IsA(node,Aggref)){Aggref*agg=(Aggref*)node;if(agg->agglevelsup>=context->min_sublevels_up)agg->agglevelsup+=context->delta_sublevels_up;/*fallthroughtorecurseintoargument*/}if(IsA(node,GroupingFunc)){GroupingFunc*grp=(GroupingFunc*)node;if(grp->agglevelsup>=context->min_sublevels_up)grp->agglevelsup+=context->delta_sublevels_up;/*fallthroughtorecurseintoargument*/}if(IsA(node,PlaceHolderVar)){PlaceHolderVar*phv=(PlaceHolderVar*)node;if(phv->phlevelsup>=context->min_sublevels_up)phv->phlevelsup+=context->delta_sublevels_up;/*fallthroughtorecurseintoargument*/}if(IsA(node,RangeTblEntry)){RangeTblEntry*rte=(RangeTblEntry*)node;if(rte->rtekind==RTE_CTE){if(rte->ctelevelsup>=context->min_sublevels_up)rte->ctelevelsup+=context->delta_sublevels_up;}returnfalse;/*allowrange_table_walkertocontinue*/}if(IsA(node,Query)){/*Recurseintosubselects*/boolresult;context->min_sublevels_up++;result=query_tree_walker((Query*)node,IncrementVarSublevelsUp_walker,(void*)context,QTW_EXAMINE_RTES);context->min_sublevels_up--;returnresult;}returnexpression_tree_walker(node,IncrementVarSublevelsUp_walker,(void*)context);}

pull_varnos

/**pull_varnos*Createasetofallthedistinctvarnospresentinaparsetree.*Onlyvarnosthatreferencelevel-zerortableentriesareconsidered.**NOTE:thisisusedonnot-yet-plannedexpressions.Itmaythereforefind*bareSubLinks,andifsoitneedstorecurseintothemtolookforuplevel*referencestothedesiredrtablelevel!Butwhenwefindacompleted*SubPlan,weonlyneedtolookattheparameterspassedtothesubplan.*/Relidspull_varnos(Node*node){pull_varnos_contextcontext;context.varnos=NULL;context.sublevels_up=0;/**MustbepreparedtostartwithaQueryorabareexpressiontree;if*it'saQuery,wedon'twanttoincrementsublevels_up.*/query_or_expression_tree_walker(node,pull_varnos_walker,(void*)&context,0);returncontext.varnos;}

contain_vars_of_level

/**contain_vars_of_level*RecursivelyscanaclausetodiscoverwhetheritcontainsanyVarnodes*ofthespecifiedquerylevel.**ReturnstrueifanysuchVarfound.**Willrecurseintosublinks.Also,maybeinvokeddirectlyonaQuery.*/boolcontain_vars_of_level(Node*node,intlevelsup){intsublevels_up=levelsup;returnquery_or_expression_tree_walker(node,contain_vars_of_level_walker,(void*)&sublevels_up,0);}

query_tree_walker

/**query_tree_walker---initiateawalkofaQuery'sexpressions**Thisroutineexistsjusttoreducethenumberofplacesthatneedtoknow*wherealltheexpressionsubtreesofaQueryare.Noteitcanbeused*forstartingawalkattoplevelofaQueryregardlessofwhetherthe*walkerintendstodescendintosubqueries.Itisalsousefulfor*descendingintosubquerieswithinawalker.**Somecallerswanttosuppressvisitationofcertainitemsinthesub-Query,*typicallybecausetheyneedtoprocessthemspecially,ordon'tactually*wanttorecurseintosubqueries.Thisissupportedbytheflagsargument,*whichisthebitwiseORofflagvaluestosuppressvisitationof*indicateditems.(Moreflagbitsmaybeaddedasneeded.)*/boolquery_tree_walker(Query*query,bool(*walker)(),void*context,intflags){Assert(query!=NULL&&IsA(query,Query));if(walker((Node*)query->targetList,context))returntrue;if(walker((Node*)query->withCheckOptions,context))returntrue;if(walker((Node*)query->onConflict,context))returntrue;if(walker((Node*)query->returningList,context))returntrue;if(walker((Node*)query->jointree,context))returntrue;if(walker(query->setOperations,context))returntrue;if(walker(query->havingQual,context))returntrue;if(walker(query->limitOffset,context))returntrue;if(walker(query->limitCount,context))returntrue;if(!(flags&QTW_IGNORE_CTE_SUBQUERIES)){if(walker((Node*)query->cteList,context))returntrue;}if(!(flags&QTW_IGNORE_RANGE_TABLE)){if(range_table_walker(query->rtable,walker,context,flags))returntrue;}returnfalse;}

OffsetVarNodes_walker

/**OffsetVarNodes-adjustVarswhenappendingonequery'sRTtoanother**FindallVarnodesinthegiventreewithvarlevelsup==sublevels_up,*andincrementtheirvarnofields(rangetableindexes)by'offset'.*Thevarnooldfieldsareadjustedsimilarly.Also,adjustothernodes*thatcontainrangetableindexes,suchasRangeTblRefandJoinExpr.**NOTE:althoughthishastheformofawalker,wecheatandmodifythe*nodesin-place.Thegivenexpressiontreeshouldhavebeencopied*earliertoensurethatnounwantedside-effectsoccur!*/typedefstruct{intoffset;intsublevels_up;}OffsetVarNodes_context;staticboolOffsetVarNodes_walker(Node*node,OffsetVarNodes_context*context){if(node==NULL)returnfalse;if(IsA(node,Var)){Var*var=(Var*)node;if(var->varlevelsup==context->sublevels_up){var->varno+=context->offset;var->varnoold+=context->offset;}returnfalse;}if(IsA(node,CurrentOfExpr)){CurrentOfExpr*cexpr=(CurrentOfExpr*)node;if(context->sublevels_up==0)cexpr->cvarno+=context->offset;returnfalse;}if(IsA(node,RangeTblRef)){RangeTblRef*rtr=(RangeTblRef*)node;if(context->sublevels_up==0)rtr->rtindex+=context->offset;/*thesubqueryitselfisvisitedseparately*/returnfalse;}if(IsA(node,JoinExpr)){JoinExpr*j=(JoinExpr*)node;if(j->rtindex&&context->sublevels_up==0)j->rtindex+=context->offset;/*fallthroughtoexaminechildren*/}if(IsA(node,PlaceHolderVar)){PlaceHolderVar*phv=(PlaceHolderVar*)node;if(phv->phlevelsup==context->sublevels_up){phv->phrels=offset_relid_set(phv->phrels,context->offset);}/*fallthroughtoexaminechildren*/}if(IsA(node,AppendRelInfo)){AppendRelInfo*appinfo=(AppendRelInfo*)node;if(context->sublevels_up==0){appinfo->parent_relid+=context->offset;appinfo->child_relid+=context->offset;}/*fallthroughtoexaminechildren*/}/*Shouldn'tneedtohandleotherplannerauxiliarynodeshere*/Assert(!IsA(node,PlanRowMark));Assert(!IsA(node,SpecialJoinInfo));Assert(!IsA(node,PlaceHolderInfo));Assert(!IsA(node,MinMaxAggInfo));if(IsA(node,Query)){/*Recurseintosubselects*/boolresult;context->sublevels_up++;result=query_tree_walker((Query*)node,OffsetVarNodes_walker,(void*)context,0);context->sublevels_up--;returnresult;}returnexpression_tree_walker(node,OffsetVarNodes_walker,(void*)context);}

query_or_expression_tree_walker

/**query_or_expression_tree_walker---hybridform**Thisroutinewillinvokequery_tree_walkerifcalledonaQuerynode,*elsewillinvokethewalkerdirectly.Thisisausefulwayofstarting*therecursionwhenthewalker'snormalchangeofstateisnotappropriate*fortheoutermostQuerynode.*/boolquery_or_expression_tree_walker(Node*node,bool(*walker)(),void*context,intflags){if(node&&IsA(node,Query))returnquery_tree_walker((Query*)node,walker,context,flags);elsereturnwalker(node,context);}

pull_varnos_walker

staticboolpull_varnos_walker(Node*node,pull_varnos_context*context){if(node==NULL)returnfalse;if(IsA(node,Var)){Var*var=(Var*)node;if(var->varlevelsup==context->sublevels_up)context->varnos=bms_add_member(context->varnos,var->varno);returnfalse;}if(IsA(node,CurrentOfExpr)){CurrentOfExpr*cexpr=(CurrentOfExpr*)node;if(context->sublevels_up==0)context->varnos=bms_add_member(context->varnos,cexpr->cvarno);returnfalse;}if(IsA(node,PlaceHolderVar)){/**APlaceHolderVaractsasavariableofitssyntacticscope,or*lowerthanthatifitreferencesonlyasubsetoftherelsinits*syntacticscope.Itmightalsocontainlateralreferences,butwe*shouldignoresuchreferenceswhencomputingthesetofvarnosin*anexpressiontree.Also,ifthePHVcontainsnovariableswithin*itssyntacticscope,itwillbeforcedtobeevaluatedexactlyat*thesyntacticscope,sotakethatastherelidset.*/PlaceHolderVar*phv=(PlaceHolderVar*)node;pull_varnos_contextsubcontext;subcontext.varnos=NULL;subcontext.sublevels_up=context->sublevels_up;(void)pull_varnos_walker((Node*)phv->phexpr,&subcontext);if(phv->phlevelsup==context->sublevels_up){subcontext.varnos=bms_int_members(subcontext.varnos,phv->phrels);if(bms_is_empty(subcontext.varnos))context->varnos=bms_add_members(context->varnos,phv->phrels);}context->varnos=bms_join(context->varnos,subcontext.varnos);returnfalse;}if(IsA(node,Query)){/*RecurseintoRTEsubqueryornot-yet-plannedsublinksubquery*/boolresult;context->sublevels_up++;result=query_tree_walker((Query*)node,pull_varnos_walker,(void*)context,0);context->sublevels_up--;returnresult;}returnexpression_tree_walker(node,pull_varnos_walker,(void*)context);}

contain_vars_of_level_walker

staticboolcontain_vars_of_level_walker(Node*node,int*sublevels_up){if(node==NULL)returnfalse;if(IsA(node,Var)){if(((Var*)node)->varlevelsup==*sublevels_up)returntrue;/*aborttreetraversalandreturntrue*/returnfalse;}if(IsA(node,CurrentOfExpr)){if(*sublevels_up==0)returntrue;returnfalse;}if(IsA(node,PlaceHolderVar)){if(((PlaceHolderVar*)node)->phlevelsup==*sublevels_up)returntrue;/*abortthetreetraversalandreturntrue*//*elsefallthroughtocheckthecontainedexpr*/}if(IsA(node,Query)){/*Recurseintosubselects*/boolresult;(*sublevels_up)++;result=query_tree_walker((Query*)node,contain_vars_of_level_walker,(void*)sublevels_up,0);(*sublevels_up)--;returnresult;}returnexpression_tree_walker(node,contain_vars_of_level_walker,(void*)sublevels_up);}

expression_tree_walker

/**Standardexpression-treewalkingsupport**Weusedtohavenear-duplicatecodeinmanydifferentroutinesthat*understoodhowtorecursethroughanexpressionnodetree.Thatwas*apaintomaintain,andwefrequentlyhadbugsduetosomeparticular*routineneglectingtosupportaparticularnodetype.Inmostcases,*theseroutinesonlyactuallycareaboutcertainnodetypes,anddon't*careaboutothertypesexceptinsofarastheyhavetorecursethrough*non-primitivenodetypes.Therefore,wenowprovidegenerictree-walking*logictoconsolidatetheredundant"boilerplate"code.Thereare*twoversions:expression_tree_walker()andexpression_tree_mutator().*//**expression_tree_walker()isdesignedtosupportroutinesthattraverse*atreeinaread-onlyfashion(althoughitwillalsoworkforroutines*thatmodifynodesin-placebutneveradd/delete/replacenodes).*Awalkerroutineshouldlooklikethis:**boolmy_walker(Node*node,my_struct*context)*{*if(node==NULL)*returnfalse;*//checkfornodesthatspecialworkisrequiredfor,eg:*if(IsA(node,Var))*{*...dospecialactionsforVarnodes*}*elseif(IsA(node,...))*{*...dospecialactionsforothernodetypes*}*//foranynodetypenotspeciallyprocessed,do:*returnexpression_tree_walker(node,my_walker,(void*)context);*}**The"context"argumentpointstoastructthatholdswhatevercontext*informationthewalkerroutineneeds---itcanbeusedtoreturndata*gatheredbythewalker,too.Thisargumentisnottouchedby*expression_tree_walker,butitispasseddowntorecursivesub-invocations*ofmy_walker.Thetreewalkisstartedfromasetuproutinethat*fillsintheappropriatecontextstruct,callsmy_walkerwiththetop-level*nodeofthetree,andthenexaminestheresults.**Thewalkerroutineshouldreturn"false"tocontinuethetreewalk,or*"true"toabortthewalkandimmediatelyreturn"true"tothetop-level*caller.Thiscanbeusedtoshort-circuitthetraversalifthewalker*hasfoundwhatitcamefor."false"isreturnedtothetop-levelcaller*iffnoinvocationofthewalkerreturned"true".**Thenodetypeshandledbyexpression_tree_walkerincludeallthose*normallyfoundintargetlistsandqualifierclausesduringtheplanning*stage.Inparticular,ithandlesListnodessinceacnf-ifiedqualclause*willhaveListstructureatthetoplevel,andithandlesTargetEntrynodes*sothatascanofatargetlistcanbehandledwithoutadditionalcode.*Also,RangeTblRef,FromExpr,JoinExpr,andSetOperationStmtnodesare*handled,sothatqueryjointreesandsetOperationtreescanbeprocessed*withoutadditionalcode.**expression_tree_walkerwillhandleSubLinknodesbyrecursingnormally*intothe"testexpr"subtree(whichisanexpressionbelongingtotheouter*plan).Itwillalsocallthewalkeronthesub-Querynode;however,when*expression_tree_walkeritselfiscalledonaQuerynode,itdoesnothing*andreturns"false".Theneteffectisthatunlessthewalkerdoes*somethingspecialataQuerynode,sub-selectswillnotbevisitedduring*anexpressiontreewalk.Thisisexactlythebehaviorwantedinmanycases*---andforthosewalkersthatdowanttorecurseintosub-selects,special*behavioristypicallyneededanywayattheentrytoasub-select(suchas*incrementingadepthcounter).Awalkerthatwantstoexaminesub-selects*shouldincludecodealongthelinesof:**if(IsA(node,Query))*{*adjustcontextforsubquery;*result=query_tree_walker((Query*)node,my_walker,context,*0);//adjustflagsasneeded*restorecontextifneeded;*returnresult;*}**query_tree_walkerisaconvenienceroutine(seebelow)thatcallsthe*walkeronalltheexpressionsubtreesofthegivenQuerynode.**expression_tree_walkerwillhandleSubPlannodesbyrecursingnormally*intothe"testexpr"andthe"args"list(whichareexpressionsbelongingto*theouterplan).Itwillnottouchthecompletedsubplan,however.Since*thereisnolinktotheoriginalQuery,itisnotpossibletorecurseinto*subselectsofanalready-plannedexpressiontree.ThisisOKforcurrent*uses,butmayneedtoberevisitedinfuture.*/boolexpression_tree_walker(Node*node,bool(*walker)(),void*context){ListCell*temp;/**Thewalkerhasalreadyvisitedthecurrentnode,andsoweneedonly*recurseintoanysub-nodesithas.**WeassumethatthewalkerisnotinterestedinListnodesperse,so*whenweexpectaListwejustrecursedirectlytoselfwithout*botheringtocallthewalker.*/if(node==NULL)returnfalse;/*Guardagainststackoverflowduetooverlycomplexexpressions*/check_stack_depth();switch(nodeTag(node)){caseT_Var:caseT_Const:caseT_Param:caseT_CaseTestExpr:caseT_SQLValueFunction:caseT_CoerceToDomainValue:caseT_SetToDefault:caseT_CurrentOfExpr:caseT_NextValueExpr:caseT_RangeTblRef:caseT_SortGroupClause:/*primitivenodetypeswithnoexpressionsubnodes*/break;caseT_WithCheckOption:returnwalker(((WithCheckOption*)node)->qual,context);caseT_Aggref:{Aggref*expr=(Aggref*)node;/*recursedirectlyonList*/if(expression_tree_walker((Node*)expr->aggdirectargs,walker,context))returntrue;if(expression_tree_walker((Node*)expr->args,walker,context))returntrue;if(expression_tree_walker((Node*)expr->aggorder,walker,context))returntrue;if(expression_tree_walker((Node*)expr->aggdistinct,walker,context))returntrue;if(walker((Node*)expr->aggfilter,context))returntrue;}break;caseT_GroupingFunc:{GroupingFunc*grouping=(GroupingFunc*)node;if(expression_tree_walker((Node*)grouping->args,walker,context))returntrue;}break;caseT_WindowFunc:{WindowFunc*expr=(WindowFunc*)node;/*recursedirectlyonList*/if(expression_tree_walker((Node*)expr->args,walker,context))returntrue;if(walker((Node*)expr->aggfilter,context))returntrue;}break;caseT_ArrayRef:{ArrayRef*aref=(ArrayRef*)node;/*recursedirectlyforupper/lowerarrayindexlists*/if(expression_tree_walker((Node*)aref->refupperindexpr,walker,context))returntrue;if(expression_tree_walker((Node*)aref->reflowerindexpr,walker,context))returntrue;/*walkermustseetherefexprandrefassgnexpr,however*/if(walker(aref->refexpr,context))returntrue;if(walker(aref->refassgnexpr,context))returntrue;}break;caseT_FuncExpr:{FuncExpr*expr=(FuncExpr*)node;if(expression_tree_walker((Node*)expr->args,walker,context))returntrue;}break;caseT_NamedArgExpr:returnwalker(((NamedArgExpr*)node)->arg,context);caseT_OpExpr:caseT_DistinctExpr:/*struct-equivalenttoOpExpr*/caseT_NullIfExpr:/*struct-equivalenttoOpExpr*/{OpExpr*expr=(OpExpr*)node;if(expression_tree_walker((Node*)expr->args,walker,context))returntrue;}break;caseT_ScalarArrayOpExpr:{ScalarArrayOpExpr*expr=(ScalarArrayOpExpr*)node;if(expression_tree_walker((Node*)expr->args,walker,context))returntrue;}break;caseT_BoolExpr:{BoolExpr*expr=(BoolExpr*)node;if(expression_tree_walker((Node*)expr->args,walker,context))returntrue;}break;caseT_SubLink:{SubLink*sublink=(SubLink*)node;if(walker(sublink->testexpr,context))returntrue;/**Alsoinvokethewalkeronthesublink'sQuerynode,soit*canrecurseintothesub-queryifitwantsto.*/returnwalker(sublink->subselect,context);}break;caseT_SubPlan:{SubPlan*subplan=(SubPlan*)node;/*recurseintothetestexpr,butnotintothePlan*/if(walker(subplan->testexpr,context))returntrue;/*alsoexamineargslist*/if(expression_tree_walker((Node*)subplan->args,walker,context))returntrue;}break;caseT_AlternativeSubPlan:returnwalker(((AlternativeSubPlan*)node)->subplans,context);caseT_FieldSelect:returnwalker(((FieldSelect*)node)->arg,context);caseT_FieldStore:{FieldStore*fstore=(FieldStore*)node;if(walker(fstore->arg,context))returntrue;if(walker(fstore->newvals,context))returntrue;}break;caseT_RelabelType:returnwalker(((RelabelType*)node)->arg,context);caseT_CoerceViaIO:returnwalker(((CoerceViaIO*)node)->arg,context);caseT_ArrayCoerceExpr:{ArrayCoerceExpr*acoerce=(ArrayCoerceExpr*)node;if(walker(acoerce->arg,context))returntrue;if(walker(acoerce->elemexpr,context))returntrue;}break;caseT_ConvertRowtypeExpr:returnwalker(((ConvertRowtypeExpr*)node)->arg,context);caseT_CollateExpr:returnwalker(((CollateExpr*)node)->arg,context);caseT_CaseExpr:{CaseExpr*caseexpr=(CaseExpr*)node;if(walker(caseexpr->arg,context))returntrue;/*weassumewalkerdoesn'tcareaboutCaseWhens,either*/foreach(temp,caseexpr->args){CaseWhen*when=lfirst_node(CaseWhen,temp);if(walker(when->expr,context))returntrue;if(walker(when->result,context))returntrue;}if(walker(caseexpr->defresult,context))returntrue;}break;caseT_ArrayExpr:returnwalker(((ArrayExpr*)node)->elements,context);caseT_RowExpr:/*Assumecolnamesisn'tinteresting*/returnwalker(((RowExpr*)node)->args,context);caseT_RowCompareExpr:{RowCompareExpr*rcexpr=(RowCompareExpr*)node;if(walker(rcexpr->largs,context))returntrue;if(walker(rcexpr->rargs,context))returntrue;}break;caseT_CoalesceExpr:returnwalker(((CoalesceExpr*)node)->args,context);caseT_MinMaxExpr:returnwalker(((MinMaxExpr*)node)->args,context);caseT_XmlExpr:{XmlExpr*xexpr=(XmlExpr*)node;if(walker(xexpr->named_args,context))returntrue;/*weassumewalkerdoesn'tcareaboutarg_names*/if(walker(xexpr->args,context))returntrue;}break;caseT_NullTest:returnwalker(((NullTest*)node)->arg,context);caseT_BooleanTest:returnwalker(((BooleanTest*)node)->arg,context);caseT_CoerceToDomain:returnwalker(((CoerceToDomain*)node)->arg,context);caseT_TargetEntry:returnwalker(((TargetEntry*)node)->expr,context);caseT_Query:/*Donothingwithasub-Query,perdiscussionabove*/break;caseT_WindowClause:{WindowClause*wc=(WindowClause*)node;if(walker(wc->partitionClause,context))returntrue;if(walker(wc->orderClause,context))returntrue;if(walker(wc->startOffset,context))returntrue;if(walker(wc->endOffset,context))returntrue;}break;caseT_CommonTableExpr:{CommonTableExpr*cte=(CommonTableExpr*)node;/**InvokethewalkerontheCTE'sQuerynode,soitcan*recurseintothesub-queryifitwantsto.*/returnwalker(cte->ctequery,context);}break;caseT_List:foreach(temp,(List*)node){if(walker((Node*)lfirst(temp),context))returntrue;}break;caseT_FromExpr:{FromExpr*from=(FromExpr*)node;if(walker(from->fromlist,context))returntrue;if(walker(from->quals,context))returntrue;}break;caseT_OnConflictExpr:{OnConflictExpr*onconflict=(OnConflictExpr*)node;if(walker((Node*)onconflict->arbiterElems,context))returntrue;if(walker(onconflict->arbiterWhere,context))returntrue;if(walker(onconflict->onConflictSet,context))returntrue;if(walker(onconflict->onConflictWhere,context))returntrue;if(walker(onconflict->exclRelTlist,context))returntrue;}break;caseT_PartitionPruneStepOp:{PartitionPruneStepOp*opstep=(PartitionPruneStepOp*)node;if(walker((Node*)opstep->exprs,context))returntrue;}break;caseT_PartitionPruneStepCombine:/*noexpressionsubnodes*/break;caseT_JoinExpr:{JoinExpr*join=(JoinExpr*)node;if(walker(join->larg,context))returntrue;if(walker(join->rarg,context))returntrue;if(walker(join->quals,context))returntrue;/**aliasclause,usinglistaredeemeduninteresting.*/}break;caseT_SetOperationStmt:{SetOperationStmt*setop=(SetOperationStmt*)node;if(walker(setop->larg,context))returntrue;if(walker(setop->rarg,context))returntrue;/*groupClausesaredeemeduninteresting*/}break;caseT_PlaceHolderVar:returnwalker(((PlaceHolderVar*)node)->phexpr,context);caseT_InferenceElem:returnwalker(((InferenceElem*)node)->expr,context);caseT_AppendRelInfo:{AppendRelInfo*appinfo=(AppendRelInfo*)node;if(expression_tree_walker((Node*)appinfo->translated_vars,walker,context))returntrue;}break;caseT_PlaceHolderInfo:returnwalker(((PlaceHolderInfo*)node)->ph_var,context);caseT_RangeTblFunction:returnwalker(((RangeTblFunction*)node)->funcexpr,context);caseT_TableSampleClause:{TableSampleClause*tsc=(TableSampleClause*)node;if(expression_tree_walker((Node*)tsc->args,walker,context))returntrue;if(walker((Node*)tsc->repeatable,context))returntrue;}break;caseT_TableFunc:{TableFunc*tf=(TableFunc*)node;if(walker(tf->ns_uris,context))returntrue;if(walker(tf->docexpr,context))returntrue;if(walker(tf->rowexpr,context))returntrue;if(walker(tf->colexprs,context))returntrue;if(walker(tf->coldefexprs,context))returntrue;}break;default:elog(ERROR,"unrecognizednodetype:%d",(int)nodeTag(node));break;}returnfalse;}三、跟踪分析

测试脚本:

select*fromt_dwxxawhereexists(selectb.dwbhfromt_grxxbwherea.dwbh=b.dwbh);

gdb分析:

(gdb)bconvert_EXISTS_sublink_to_joinBreakpoint1at0x77a4fe:filesubselect.c,line1426.(gdb)cContinuing.Breakpoint1,convert_EXISTS_sublink_to_join(root=0x22de828,sublink=0x22292a0,under_not=false,available_rels=0x22defa8)atsubselect.c:14261426Query*parse=root->parse;(gdb)#1.root见上一节#2.sublink,子链接,见本节查询树结构#3.under_not,false,表示EXISTS#4.available_rels,可用的rels(gdb)p*available_rels$1={nwords=1,words=0x22defac}(gdb)pavailable_rels->words[0]$3=2(gdb)...1511rtoffset=list_length(parse->rtable);(gdb)1512OffsetVarNodes((Node*)subselect,rtoffset,0);(gdb)prtoffset$6=1(gdb)stepOffsetVarNodes(node=0x22dee48,offset=1,sublevels_up=0)atrewriteManip.c:428428context.offset=offset;...#调整subselect->jointree->fromlist->head(类型为RTR)的rtindex,原为1,调整为2(gdb)p*node$20={type=T_RangeTblRef}(gdb)p*(RangeTblRef*)node$21={type=T_RangeTblRef,rtindex=1}(gdb)n368rtr->rtindex+=context->offset;(gdb)370returnfalse;(gdb)p*(RangeTblRef*)node$22={type=T_RangeTblRef,rtindex=2}(gdb)1513OffsetVarNodes(whereClause,rtoffset,0);(gdb)1520IncrementVarSublevelsUp((Node*)subselect,-1,1);(gdb)pwhereClause$23=(Node*)0x22def58(gdb)p*whereClause$24={type=T_OpExpr}(gdb)set$arg1=(RelabelType*)((OpExpr*)whereClause)->args->head->data.ptr_value(gdb)p*$arg1->arg$32={type=T_Var}#第1个参数是t_dwxx.dwbh(gdb)p*(Var*)$arg1->arg$33={xpr={type=T_Var},varno=1,varattno=2,vartype=1043,vartypmod=14,varcollid=100,varlevelsup=1,varnoold=1,varoattno=2,location=72}(gdb)set$arg2=(RelabelType*)((OpExpr*)whereClause)->args->tail->data.ptr_value#第2个参数是t_grxx.dwbh#varno/varnoold从原来的值1调整为2(gdb)p*(Var*)$arg2->arg$34={xpr={type=T_Var},varno=2,varattno=1,vartype=1043,vartypmod=14,varcollid=100,varlevelsup=0,varnoold=2,varoattno=1,location=81}(gdb)(gdb)n1521IncrementVarSublevelsUp(whereClause,-1,1);(gdb)1529clause_varnos=pull_varnos(whereClause);#调整varlevelsup为0(gdb)p*(Var*)$arg1->arg$36={xpr={type=T_Var},varno=1,varattno=2,vartype=1043,vartypmod=14,varcollid=100,varlevelsup=0,varnoold=1,varoattno=2,location=72}(gdb)p*(Var*)$arg2->arg$37={xpr={type=T_Var},varno=2,varattno=1,vartype=1043,vartypmod=14,varcollid=100,varlevelsup=0,varnoold=2,varoattno=1,location=81}...#构造返回值(gdb)1552result=makeNode(JoinExpr);1566returnresult;(gdb)1567}(gdb)pull_up_sublinks_qual_recurse(root=0x22de828,node=0x22292a0,jtlink1=0x7ffdcabbc918,available_rels1=0x22defa8,jtlink2=0x0,available_rels2=0x0)atprepjointree.c:404404j->larg=*jtlink1;#上拉成为半连接SEMIJOIN(gdb)p*(JoinExpr*)jtnode$65={type=T_JoinExpr,jointype=JOIN_SEMI,isNatural=false,larg=0x22e7118,rarg=0x22e7438,usingClause=0x0,quals=0x22def58,alias=0x0,rtindex=0}#DONE!

到此，相信大家对“PostgreSQL查询优化中怎么上拉子链接”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！