这篇文章主要为大家展示了“PostgreSQL中如何生成子链接执行计划、使用Param替换上层Vars以及转换表达式为隐式AND格式”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“PostgreSQL中如何生成子链接执行计划、使用Param替换上层Vars以及转换表达式为隐式AND格式”这篇文章吧。

主函数preprocess_expression先前章节也介绍过，在此函数中调用了生成子链接执行计划、使用Param替换上层Vars以及转换表达式为隐式AND格式（implicit-AND format）等相关子函数。
preprocess_expression

/**preprocess_expression*Dosubquery_planner'spreprocessingworkforanexpression,*whichcanbeatargetlist,aWHEREclause(includingJOIN/ON*conditions),aHAVINGclause,orafewotherthings.*/staticNode*preprocess_expression(PlannerInfo*root,Node*expr,intkind){//.../*ExpandSubLinkstoSubPlans*/if(root->parse->hasSubLinks)//扩展子链接为子计划expr=SS_process_sublinks(root,expr,(kind==EXPRKIND_QUAL));/**XXXdonotinsertanythinghereunlessyouhavegrokkedthecommentsin*SS_replace_correlation_vars...*//*ReplaceuplevelvarswithParamnodes(thisISpossibleinVALUES)*/if(root->query_level>1)expr=SS_replace_correlation_vars(root,expr);//使用Param节点替换上层的Vars/**Ifit'saqualorhavingQual,convertittoimplicit-ANDformat.(We*don'twanttodothisbeforeeval_const_expressions,sincethelatter*wouldbeunabletosimplifyatop-levelANDcorrectly.Also,*SS_process_sublinksexpectsexplicit-ANDformat.)*/if(kind==EXPRKIND_QUAL)//转换为隐式AND格式expr=(Node*)make_ands_implicit((Expr*)expr);returnexpr;}二、生成子链接执行计划

先前的章节已介绍了上拉子链接的相关处理过程,对于不能上拉的子链接,PG会生成子执行计划.对于会生成常量的子链接,则会把生成的常量记录在Param中,在需要的时候由父查询使用.
例1:以下的子链接,PG会生成子计划,并把子链接的结果物化(Materialize)提升整体性能.

testdb=#explainverboseselect*fromt_dwxxwheredwbh>all(selectb.dwbhfromt_grxxb);QUERYPLAN---------------------------------------------------------------------------------SeqScanonpublic.t_dwxx(cost=0.00..1498.00rows=80width=474)Output:t_dwxx.dwmc,t_dwxx.dwbh,t_dwxx.dwdzFilter:(SubPlan1)SubPlan1->Materialize(cost=0.00..17.35rows=490width=38)Output:b.dwbh->SeqScanonpublic.t_grxxb(cost=0.00..14.90rows=490width=38)Output:b.dwbh(8rows)

例2:以下的子链接,PG会把生成的常量记录在Param中(注意生成的参数:$0)

testdb=#explainverboseselect*fromt_dwxxawhereexists(selectmax(b.dwbh)fromt_grxxb);QUERYPLAN---------------------------------------------------------------------------------Result(cost=16.14..27.73rows=160width=474)Output:a.dwmc,a.dwbh,a.dwdzOne-TimeFilter:$0InitPlan1(returns$0)->Aggregate(cost=16.12..16.14rows=1width=32)Output:max((b.dwbh)::text)->SeqScanonpublic.t_grxxb(cost=0.00..14.90rows=490width=38)Output:b.dwbh,b.grbh,b.xm,b.nl->SeqScanonpublic.t_dwxxa(cost=16.14..27.73rows=160width=474)Output:a.dwmc,a.dwbh,a.dwdz(10rows)

源代码如下:
SS_process_sublinks

/**ExpandSubLinkstoSubPlansinthegivenexpression.**TheisQualargumenttellswhetherornotthisexpressionisaWHERE/HAVING*qualifierexpression.Ifitis,anysublinksappearingattoplevelneed*notdistinguishFALSEfromUNKNOWNreturnvalues.*/Node*SS_process_sublinks(PlannerInfo*root,Node*expr,boolisQual){process_sublinks_contextcontext;context.root=root;context.isTopQual=isQual;returnprocess_sublinks_mutator(expr,&context);//调用XX_mutator函数遍历并处理}staticNode*process_sublinks_mutator(Node*node,process_sublinks_context*context){process_sublinks_contextlocContext;locContext.root=context->root;if(node==NULL)returnNULL;if(IsA(node,SubLink))//子链接{SubLink*sublink=(SubLink*)node;Node*testexpr;/**First,recursivelyprocessthelefthand-sideexpressions,ifany.*They'renottop-levelanymore.*/locContext.isTopQual=false;testexpr=process_sublinks_mutator(sublink->testexpr,&locContext);/**NowbuildtheSubPlannodeandmaketheexprtoreturn.*/returnmake_subplan(context->root,(Query*)sublink->subselect,sublink->subLinkType,sublink->subLinkId,testexpr,context->isTopQual);//生成子执行计划,与整体的执行计划类似}/**Don'trecurseintotheargumentsofanouterPHVoraggregatehere.Any*SubLinksintheargumentshavetobedealtwithattheouterquery*level;they'llbehandledwhenbuild_subplancollectsthePHVorAggref*intotheargumentstobepasseddowntothecurrentsubplan.*/if(IsA(node,PlaceHolderVar)){if(((PlaceHolderVar*)node)->phlevelsup>0)returnnode;}elseif(IsA(node,Aggref)){if(((Aggref*)node)->agglevelsup>0)returnnode;}/**WeshouldneverseeaSubPlanexpressionintheinput(sincethisis*theveryroutinethatcreates'emtobeginwith).Weshouldn'tfind*ourselvesinvokeddirectlyonaQuery,either.*/Assert(!IsA(node,SubPlan));Assert(!IsA(node,AlternativeSubPlan));Assert(!IsA(node,Query));/**Becausemake_subplan()couldreturnanANDorORclause,wehaveto*takestepstopreserveAND/ORflatnessofaqual.Weassumetheinput*hasbeenAND/ORflattenedandsoweneednorecursionhere.**(Duetothecodinghere,wewillnotgetcalledontheListsubnodesof*anAND;andtheinputis*not*yetinimplicit-ANDformat.Sonocheck*isneededforabareList.)**Anywherewithinthetop-levelAND/ORclausestructure,wecantell*make_subplan()thatNULLandFALSEareinterchangeable.SoisTopQual*propagatesdowninbothcases.(Notethatthisisunlikethemeaning*of"toplevelqual"usedinmostotherplacesinPostgres.)*/if(and_clause(node))//AND语句{List*newargs=NIL;ListCell*l;/*Stillatqualtop-level*/locContext.isTopQual=context->isTopQual;foreach(l,((BoolExpr*)node)->args){Node*newarg;newarg=process_sublinks_mutator(lfirst(l),&locContext);if(and_clause(newarg))newargs=list_concat(newargs,((BoolExpr*)newarg)->args);elsenewargs=lappend(newargs,newarg);}return(Node*)make_andclause(newargs);}if(or_clause(node))//OR语句{List*newargs=NIL;ListCell*l;/*Stillatqualtop-level*/locContext.isTopQual=context->isTopQual;foreach(l,((BoolExpr*)node)->args){Node*newarg;newarg=process_sublinks_mutator(lfirst(l),&locContext);if(or_clause(newarg))newargs=list_concat(newargs,((BoolExpr*)newarg)->args);elsenewargs=lappend(newargs,newarg);}return(Node*)make_orclause(newargs);}/**IfwerecursedownthroughanythingotherthananANDorORnode,we*aredefinitelynotattopquallevelanymore.*/locContext.isTopQual=false;returnexpression_tree_mutator(node,process_sublinks_mutator,(void*)&locContext);}三、使用Param替换上层变量

SQL例子参考"二、生成子链接执行计划"中的例2,这也是使用Param替代Var的一个例子.
源代码如下:

/**Replacecorrelationvars(uplevelvars)withParams.**UplevelPlaceHolderVarsandaggregatesarereplaced,too.**Note:itiscriticalthatthisrunsimmediatelyafterSS_process_sublinks.*SincewedonotrecurseintotheargumentsofuplevelPHVsandaggregates,*theywillgetcopiedtotheappropriatesubplanargslistintheparent*querywithuplevelvarsnotreplacedbyParams,butonlyadjustedinlevel*(seereplace_outer_placeholdervarandreplace_outer_agg).That'sexactly*whatwewantforthevarsoftheparentlevel---butifaPHV'sor*aggregate'sargumentcontainsanyfurther-upvariables,theyhavetobe*replacedwithParamsintheirturn.Thatwillhappenwhentheparentlevel*runsSS_replace_correlation_vars.Thereforeitmustdosoafterexpanding*itssublinkstosubplans.Andwedon'twantanystepsinbetween,else*thosestepswouldnevergetappliedtotheargumentexpressions,eitherin*theparentorthechildlevel.**AnotherfairlytrickythinggoingonhereisthehandlingofSubLinksin*theargumentsofuplevelPHVs/aggregates.Thosearenottouchedinsidethe*intermediatequerylevel,either.Instead,SS_process_sublinksrecurseson*themaftercopyingthePHVorAggrefexpressionintotheparentplanlevel*(thisisactuallytakencareofinbuild_subplan).*/Node*SS_replace_correlation_vars(PlannerInfo*root,Node*expr){/*Nosetupneededfortreewalk,soawaywego*///调用XX_mutator遍历处理returnreplace_correlation_vars_mutator(expr,root);}staticNode*replace_correlation_vars_mutator(Node*node,PlannerInfo*root){if(node==NULL)returnNULL;if(IsA(node,Var))//Var{if(((Var*)node)->varlevelsup>0)return(Node*)replace_outer_var(root,(Var*)node);//使用Param替换}if(IsA(node,PlaceHolderVar)){if(((PlaceHolderVar*)node)->phlevelsup>0)return(Node*)replace_outer_placeholdervar(root,(PlaceHolderVar*)node);}if(IsA(node,Aggref)){if(((Aggref*)node)->agglevelsup>0)return(Node*)replace_outer_agg(root,(Aggref*)node);}if(IsA(node,GroupingFunc)){if(((GroupingFunc*)node)->agglevelsup>0)return(Node*)replace_outer_grouping(root,(GroupingFunc*)node);}returnexpression_tree_mutator(node,replace_correlation_vars_mutator,(void*)root);}/**GenerateaParamnodetoreplacethegivenVar,*whichisexpectedtohavevarlevelsup>0(ie,itisnotlocal).*/staticParam*replace_outer_var(PlannerInfo*root,Var*var)//构造Param替换Var{Param*retval;inti;Assert(var->varlevelsup>0&&var->varlevelsup<root->query_level);/*FindtheVarintheappropriateplan_params,oradditifnotpresent*/i=assign_param_for_var(root,var);retval=makeNode(Param);retval->paramkind=PARAM_EXEC;retval->paramid=i;retval->paramtype=var->vartype;retval->paramtypmod=var->vartypmod;retval->paramcollid=var->varcollid;retval->location=var->location;returnretval;}四、转换表达式为隐式AND格式

源码如下:

List*make_ands_implicit(Expr*clause){/**NB:becausetheparsersetsthequalfieldtoNULLinaquerythathas*noWHEREclause,wemustconsideraNULLinputclauseasTRUE,even*thoughonemightmorereasonablythinkitFALSE.Grumble.Ifthis*causestrouble,considerchangingtheparser'sbehavior.*/if(clause==NULL)//如为NULL,返回空指针returnNIL;/*NULL->NILlist==TRUE*/elseif(and_clause((Node*)clause))//AND语句,直接返回AND中的args参数return((BoolExpr*)clause)->args;elseif(IsA(clause,Const)&&!((Const*)clause)->constisnull&&DatumGetBool(((Const*)clause)->constvalue))returnNIL;/*常量TRUE,返回空指针constantTRUEinput->NILlist*/elsereturnlist_make1(clause);//返回List}

以上是“PostgreSQL中如何生成子链接执行计划、使用Param替换上层Vars以及转换表达式为隐式AND格式”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！