本篇内容介绍了“PostgreSQL查询优化对表达式预处理中连接Var溯源的过程是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

处理逻辑在主函数subquery_planner中通过调用flatten_join_alias_vars函数实现，该函数位于src/backend/optimizer/util/var.c文件中。

连接Var溯源，意思是把连接产生的中间结果（中间结果也是Relation关系的一种）的投影替换为实际存在的关系的列（在PG中通过Var表示）。
如下面的SQL语句：

selecta.*,b.grbh,b.jefromt_dwxxa,lateral(selectt1.dwbh,t1.grbh,t2.jefromt_grxxt1innerjoint_jfxxt2ont1.dwbh=a.dwbhandt1.grbh=t2.grbh)b;

b与a连接运算,查询树Query中的投影b.grbh和b.je这两列如需依赖关系b(子查询产生的中间结果),则需要把中间关系的投影列替换为实际的Relation的投影列,即t_grxx和t_jfxx的数据列.

PG源码中的注释：

/**IfthequeryhasanyjoinRTEs,replacejoinaliasvariableswith*base-relationvariables.Wemustdothisfirst,sinceanyexpressions*wemayextractfromthejoinaliasvarslistshavenotbeenpreprocessed.*Forexample,ifwedidthisaftersublinkprocessing,sublinksexpanded*outfromjoinaliaseswouldnotgetprocessed.Butwecanskipthisin*non-lateralRTEfunctions,VALUESlists,andTABLESAMPLEclauses,since*theycan'tcontainanyVarsofthecurrentquerylevel.*/if(root->hasJoinRTEs&&!(kind==EXPRKIND_RTFUNC||kind==EXPRKIND_VALUES||kind==EXPRKIND_TABLESAMPLE||kind==EXPRKIND_TABLEFUNC))expr=flatten_join_alias_vars(root,expr);二、源码解读

flatten_join_alias_vars

/**flatten_join_alias_vars*ReplaceVarsthatreferenceJOINoutputswithreferencestotheoriginal*relationvariablesinstead.Thisallowsqualsinvolvingsuchvarstobe*pusheddown.Whole-rowVarsthatreferenceJOINrelationsareexpanded*intoRowExprconstructsthatnametheindividualoutputVars.This*isnecessarysincewewillnotscantheJOINasabaserelation,which*istheonlywaythattheexecutorcandirectlyhandlewhole-rowVars.**Thisalsoadjustsrelidsetsfoundinsomeexpressionnodetypesto*substitutethecontainedbaserelsforanyjoinrelid.**IfaJOINcontainssub-selectsthathavebeenflattened,itsjoinalias*entriesmightnowbearbitraryexpressions,notjustVars.Thisaffects*thisfunctioninoneimportantway:wemightfindourselvesinserting*SubLinkexpressionsintosubqueries,andwemustmakesurethattheir*Query.hasSubLinksfieldsgetsettotrueifso.Ifthereareany*SubLinksinthejoinaliaslists,theouterQueryshouldalreadyhave*hasSubLinks=true,sothisisonlyrelevanttoun-flattenedsubqueries.**NOTE:thisisusedonnot-yet-plannedexpressions.Wedonotexpectit*tobeapplieddirectlytothewholeQuery,soifweseeaQuerytostart*with,wedowanttoincrementsublevels_up(thisoccursforLATERAL*subqueries).*/Node*flatten_join_alias_vars(PlannerInfo*root,Node*node){flatten_join_alias_vars_contextcontext;context.root=root;context.sublevels_up=0;/*flagwhetherjoinaliasescouldpossiblycontainSubLinks*/context.possible_sublink=root->parse->hasSubLinks;/*ifhasSubLinksisalreadytrue,noneedtoworkhard*/context.inserted_sublink=root->parse->hasSubLinks;//调用flatten_join_alias_vars_mutator处理Varsreturnflatten_join_alias_vars_mutator(node,&context);}staticNode*flatten_join_alias_vars_mutator(Node*node,flatten_join_alias_vars_context*context){if(node==NULL)returnNULL;if(IsA(node,Var))//Var类型{Var*var=(Var*)node;RangeTblEntry*rte;Node*newvar;/*NochangeunlessVarbelongstoaJOINofthetargetlevel*/if(var->varlevelsup!=context->sublevels_up)returnnode;/*noneedtocopy,really*/rte=rt_fetch(var->varno,context->root->parse->rtable);if(rte->rtekind!=RTE_JOIN)returnnode;//在rte->rtekind==RTE_JOIN时才需要处理if(var->varattno==InvalidAttrNumber){/*Mustexpandwhole-rowreference*/RowExpr*rowexpr;List*fields=NIL;List*colnames=NIL;AttrNumberattnum;ListCell*lv;ListCell*ln;attnum=0;Assert(list_length(rte->joinaliasvars)==list_length(rte->eref->colnames));forboth(lv,rte->joinaliasvars,ln,rte->eref->colnames){newvar=(Node*)lfirst(lv);attnum++;/*Ignoredroppedcolumns*/if(newvar==NULL)continue;newvar=copyObject(newvar);/**Ifweareexpandinganaliascarrieddownfromanupper*query,mustadjustitsvarlevelsupfields.*/if(context->sublevels_up!=0)IncrementVarSublevelsUp(newvar,context->sublevels_up,0);/*PreserveoriginalVar'slocation,ifpossible*/if(IsA(newvar,Var))((Var*)newvar)->location=var->location;/*Recurseincasejoininputisitselfajoin*//*(alsotakescareofsettinginserted_sublinkifneeded)*/newvar=flatten_join_alias_vars_mutator(newvar,context);fields=lappend(fields,newvar);/*Weneedthenamesofnon-droppedcolumns,too*/colnames=lappend(colnames,copyObject((Node*)lfirst(ln)));}rowexpr=makeNode(RowExpr);rowexpr->args=fields;rowexpr->row_typeid=var->vartype;rowexpr->row_format=COERCE_IMPLICIT_CAST;rowexpr->colnames=colnames;rowexpr->location=var->location;return(Node*)rowexpr;}/*Expandjoinaliasreference*///扩展joinaliasVarAssert(var->varattno>0);newvar=(Node*)list_nth(rte->joinaliasvars,var->varattno-1);Assert(newvar!=NULL);newvar=copyObject(newvar);/**Ifweareexpandinganaliascarrieddownfromanupperquery,must*adjustitsvarlevelsupfields.*/if(context->sublevels_up!=0)IncrementVarSublevelsUp(newvar,context->sublevels_up,0);/*PreserveoriginalVar'slocation,ifpossible*/if(IsA(newvar,Var))((Var*)newvar)->location=var->location;/*Recurseincasejoininputisitselfajoin*/newvar=flatten_join_alias_vars_mutator(newvar,context);/*Detectifweareaddingasublinktoquery*/if(context->possible_sublink&&!context->inserted_sublink)context->inserted_sublink=checkExprHasSubLink(newvar);returnnewvar;}if(IsA(node,PlaceHolderVar))//占位符{/*CopythePlaceHolderVarnodewithcorrectmutationofsubnodes*/PlaceHolderVar*phv;phv=(PlaceHolderVar*)expression_tree_mutator(node,flatten_join_alias_vars_mutator,(void*)context);/*nowfixPlaceHolderVar'srelidsets*/if(phv->phlevelsup==context->sublevels_up){phv->phrels=alias_relid_set(context->root,phv->phrels);}return(Node*)phv;}if(IsA(node,Query))//查询树{/*RecurseintoRTEsubqueryornot-yet-plannedsublinksubquery*/Query*newnode;boolsave_inserted_sublink;context->sublevels_up++;save_inserted_sublink=context->inserted_sublink;context->inserted_sublink=((Query*)node)->hasSubLinks;newnode=query_tree_mutator((Query*)node,flatten_join_alias_vars_mutator,(void*)context,QTW_IGNORE_JOINALIASES);newnode->hasSubLinks|=context->inserted_sublink;context->inserted_sublink=save_inserted_sublink;context->sublevels_up--;return(Node*)newnode;}/*Already-plannedtreenotsupported*/Assert(!IsA(node,SubPlan));/*Shouldn'tneedtohandletheseplannerauxiliarynodeshere*/Assert(!IsA(node,SpecialJoinInfo));Assert(!IsA(node,PlaceHolderInfo));Assert(!IsA(node,MinMaxAggInfo));//其他表达式returnexpression_tree_mutator(node,flatten_join_alias_vars_mutator,(void*)context);}

query_tree_mutator

/**query_tree_mutator---initiatemodificationofaQuery'sexpressions**Thisroutineexistsjusttoreducethenumberofplacesthatneedtoknow*wherealltheexpressionsubtreesofaQueryare.Noteitcanbeused*forstartingawalkattoplevelofaQueryregardlessofwhetherthe*mutatorintendstodescendintosubqueries.Itisalsousefulfor*descendingintosubquerieswithinamutator.**SomecallerswanttosuppressmutatingofcertainitemsintheQuery,*typicallybecausetheyneedtoprocessthemspecially,ordon'tactually*wanttorecurseintosubqueries.Thisissupportedbytheflagsargument,*whichisthebitwiseORofflagvaluestosuppressmutatingof*indicateditems.(Moreflagbitsmaybeaddedasneeded.)**NormallytheQuerynodeitselfiscopied,butsomecallerswantittobe*modifiedin-place;theymustpassQTW_DONT_COPY_QUERYinflags.All*modifiedsubstructureissafelycopiedinanycase.*/Query*query_tree_mutator(Query*query,Node*(*mutator)(),void*context,intflags)//遍历查询树{Assert(query!=NULL&&IsA(query,Query));if(!(flags&QTW_DONT_COPY_QUERY)){Query*newquery;FLATCOPY(newquery,query,Query);query=newquery;}MUTATE(query->targetList,query->targetList,List*);//投影列MUTATE(query->withCheckOptions,query->withCheckOptions,List*);MUTATE(query->onConflict,query->onConflict,OnConflictExpr*);MUTATE(query->returningList,query->returningList,List*);MUTATE(query->jointree,query->jointree,FromExpr*);MUTATE(query->setOperations,query->setOperations,Node*);MUTATE(query->havingQual,query->havingQual,Node*);MUTATE(query->limitOffset,query->limitOffset,Node*);MUTATE(query->limitCount,query->limitCount,Node*);if(!(flags&QTW_IGNORE_CTE_SUBQUERIES))MUTATE(query->cteList,query->cteList,List*);else/*elsecopyCTElistas-is*/query->cteList=copyObject(query->cteList);query->rtable=range_table_mutator(query->rtable,mutator,context,flags);//RTEreturnquery;}

range_table_mutator

/**range_table_mutatorisjustthepartofquery_tree_mutatorthatprocesses*aquery'srangetable.Thisissplitoutsinceitcanbeusefulon*itsown.*/List*range_table_mutator(List*rtable,Node*(*mutator)(),void*context,intflags){List*newrt=NIL;ListCell*rt;foreach(rt,rtable)//遍历RTE{RangeTblEntry*rte=(RangeTblEntry*)lfirst(rt);RangeTblEntry*newrte;FLATCOPY(newrte,rte,RangeTblEntry);switch(rte->rtekind){caseRTE_RELATION:MUTATE(newrte->tablesample,rte->tablesample,TableSampleClause*);/*wedon'tbothertocopyeref,aliases,etc;OK?*/break;caseRTE_CTE:caseRTE_NAMEDTUPLESTORE:/*nothingtodo*/break;caseRTE_SUBQUERY:if(!(flags&QTW_IGNORE_RT_SUBQUERIES)){CHECKFLATCOPY(newrte->subquery,rte->subquery,Query);MUTATE(newrte->subquery,newrte->subquery,Query*);//遍历处理子查询}else{/*else,copyRTsubqueriesas-is*/newrte->subquery=copyObject(rte->subquery);}break;caseRTE_JOIN://连接,遍历处理joinaliasvarsif(!(flags&QTW_IGNORE_JOINALIASES))MUTATE(newrte->joinaliasvars,rte->joinaliasvars,List*);else{/*else,copyjoinaliasesas-is*/newrte->joinaliasvars=copyObject(rte->joinaliasvars);}break;caseRTE_FUNCTION:MUTATE(newrte->functions,rte->functions,List*);break;caseRTE_TABLEFUNC:MUTATE(newrte->tablefunc,rte->tablefunc,TableFunc*);break;caseRTE_VALUES:MUTATE(newrte->values_lists,rte->values_lists,List*);break;}MUTATE(newrte->securityQuals,rte->securityQuals,List*);newrt=lappend(newrt,newrte);}returnnewrt;}三、跟踪分析

在PG11中,没有进入"Expand join alias reference"的实现逻辑,猜测在上拉子查询的时候已作优化.

“PostgreSQL查询优化对表达式预处理中连接Var溯源的过程是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！