这篇文章主要介绍“PostgreSQL查询语句分析”，在日常操作中，相信很多人在PostgreSQL查询语句分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL查询语句分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

子查询上拉在函数pull_up_subqueries中实现,该函数调用pull_up_subqueries_recurse函数递归实现子查询上拉.
pull_up_subqueries

/**pull_up_subqueries*Lookforsubqueriesintherangetablethatcanbepulledupinto*theparentquery.Ifthesubqueryhasnospecialfeatureslike*grouping/aggregationthenwecanmergeitintotheparent'sjointree.*Also,subqueriesthataresimpleUNIONALLstructurescanbe*convertedinto"appendrelations".*/voidpull_up_subqueries(PlannerInfo*root){/*ToplevelofjointreemustalwaysbeaFromExpr*/Assert(IsA(root->parse->jointree,FromExpr));/*Resetflagsayingweneedadeletioncleanuppass*/root->hasDeletedRTEs=false;/*Recursionstartswithnocontainingjoinnorappendrel*/root->parse->jointree=(FromExpr*)pull_up_subqueries_recurse(root,(Node*)root->parse->jointree,NULL,NULL,NULL,false);/*Applycleanupphaseifnecessary*/if(root->hasDeletedRTEs)root->parse->jointree=(FromExpr*)pull_up_subqueries_cleanup((Node*)root->parse->jointree);Assert(IsA(root->parse->jointree,FromExpr));}

pull_up_subqueries_recurse

/**pull_up_subqueries_recurse*Recursivegutsofpull_up_subqueries.**Thisrecursivelyprocessesthejointreeandreturnsamodifiedjointree.*Or,ifit'svalidtodropthecurrentnodefromthejointreecompletely,*itreturnsNULL.**Ifthisjointreenodeiswithineithersideofanouterjoin,then*lowest_outer_joinreferencesthelowestsuchJoinExprnode;otherwise*itisNULL.WeusethistoconstraintheeffectsofLATERALsubqueries.**Ifthisjointreenodeiswithinthenullablesideofanouterjoin,then*lowest_nulling_outer_joinreferencesthelowestsuchJoinExprnode;*otherwiseitisNULL.ThisforcesuseofthePlaceHolderVarmechanismfor*referencestonon-nullabletargetlistitems,butonlyforreferencesabove*thatjoin.**Ifwearelookingatamembersubqueryofanappendrelation,*containing_appendreldescribesthatrelation;elseitisNULL.*ThisforcesuseofthePlaceHolderVarmechanismforallnon-Vartargetlist*items,andputssomeadditionalrestrictionsonwhatcanbepulledup.**deletion_okistrueifthecallercancopewithusreturningNULLfora*deletableleafnode(forexample,aVALUESRTEthatcouldbepulledup).*Ifit'sfalse,we'llavoidpullupinsuchcases.**Atrickyaspectofthiscodeisthatifwepullupasubquerywehave*toreplaceVarsthatreferencethesubquery'soutputsthroughoutthe*parentquery,includingqualsattachedtojointreenodesabovetheone*wearecurrentlyprocessing!Wehandlethisbybeingcarefulnotto*changethejointreestructurewhilerecursing:nonodesotherthanleaf*RangeTblRefentriesandentirely-emptyFromExprswillbereplacedor*deleted.Also,wecan'tturnpullup_replace_varslooseonthewhole*jointree,becauseit'llreturnamutatedcopyofthetree;wehaveto*invokeitjustonthequals,instead.Thisbehavioriswhatmakesit*reasonabletopasslowest_outer_joinandlowest_nulling_outer_joinas*pointersratherthansomemore-indirectwayofidentifyingthelowest*OJs.Likewise,wedon'treplaceappend_rel_listmembersbutonlytheir*substructure,sothecontaining_appendrelreferenceissafetouse.**Becauseoftherulethatnojointreenodeswithsubstructurecanbe*replaced,wecannotfullyhandlethecaseofdeletingnodesfromthetree:*whenwedeleteonechildofaJoinExpr,weneedtoreplacetheJoinExpr*withaFromExpr,andthatcan'thappenhere.Instead,wesetthe*root->hasDeletedRTEsflag,whichtellspull_up_subqueries()thatan*additionalpassoverthetreeisneededtocleanup.*//*输入参数:root-计划器相关信息jtnode-需要处理的Node(jointree)lowest_outer_join-如该节点位于外连接的任意一侧,则该指针指向此节点lowest_nulling_outer_join-如该节点位于外连接的可空一侧,,则该指针指向此节点containing_appendrel-Append操作中的Relationdeletion_ok-调用方可处理在可删除的叶子节点的情况下返回NULL,此值为true输出参数:*/staticNode*pull_up_subqueries_recurse(PlannerInfo*root,Node*jtnode,JoinExpr*lowest_outer_join,JoinExpr*lowest_nulling_outer_join,AppendRelInfo*containing_appendrel,booldeletion_ok){Assert(jtnode!=NULL);if(IsA(jtnode,RangeTblRef))//如为RTR{//获取该RTR相应的RTEintvarno=((RangeTblRef*)jtnode)->rtindex;RangeTblEntry*rte=rt_fetch(varno,root->parse->rtable);/**IsthisasubqueryRTE,andifso,isthesubquerysimpleenoughto*pullup?**Ifwearelookingatanappend-relationmember,wecan'tpullitup*unlessis_safe_append_membersaysso.*/if(rte->rtekind==RTE_SUBQUERY&&is_simple_subquery(rte->subquery,rte,lowest_outer_join,deletion_ok)&&(containing_appendrel==NULL||is_safe_append_member(rte->subquery)))//简单子查询returnpull_up_simple_subquery(root,jtnode,rte,lowest_outer_join,lowest_nulling_outer_join,containing_appendrel,deletion_ok);/**Alternatively,isitasimpleUNIONALLsubquery?Ifso,flatten*intoan"appendrelation".**It'ssafetodothisregardlessofwhetherthisqueryisitselfan*appendrelmember.(Ifyou'rethinkingweshouldtrytoflattenthe*twolevelsofappendreltogether,you'reright;butwehandlethat*inset_append_rel_pathlist,nothere.)*/if(rte->rtekind==RTE_SUBQUERY&&is_simple_union_all(rte->subquery))//UNIONALL子查询returnpull_up_simple_union_all(root,jtnode,rte);/**Orperhapsit'sasimpleVALUESRTE?**Wedon'tallowVALUESpullupbelowanouterjoinnorintoan*appendrel(suchcasesareimpossibleanywayatthemoment).*/if(rte->rtekind==RTE_VALUES&&lowest_outer_join==NULL&&containing_appendrel==NULL&&is_simple_values(root,rte,deletion_ok))//VALUES子查询returnpull_up_simple_values(root,jtnode,rte);/*Otherwise,donothingatthisnode.*/}elseif(IsA(jtnode,FromExpr))//如为FromExpr{FromExpr*f=(FromExpr*)jtnode;boolhave_undeleted_child=false;ListCell*l;Assert(containing_appendrel==NULL);/**IftheFromExprhasquals,it'snotdeletableevenifitsparent*wouldallowdeletion.*/if(f->quals)deletion_ok=false;foreach(l,f->fromlist){/**Inanon-deletableFromExpr,wecanallowdeletionofchild*nodessolongasatleastonechildremains;soit'sokay*eitherifanypreviouschildsurvives,orifthere'smoreto*come.Ifallchildrenaredeletableinthemselves,we'llforce*thelastonetoremainunflattened.**Asaseparatematter,wecanallowdeletionofallchildrenof*thetop-levelFromExprinaquery,sincethat'saspecialcase*anyway.*/boolsub_deletion_ok=(deletion_ok||have_undeleted_child||lnext(l)!=NULL||f==root->parse->jointree);lfirst(l)=pull_up_subqueries_recurse(root,lfirst(l),lowest_outer_join,lowest_nulling_outer_join,NULL,sub_deletion_ok);//递归调用if(lfirst(l)!=NULL)have_undeleted_child=true;}if(deletion_ok&&!have_undeleted_child){/*OKtodeletethisFromExprentirely*/root->hasDeletedRTEs=true;/*probablyissetalready*/returnNULL;}}elseif(IsA(jtnode,JoinExpr))//如为JoinExpr{JoinExpr*j=(JoinExpr*)jtnode;Assert(containing_appendrel==NULL);/*Recurse,beingcarefultotellmyselfwheninsideouterjoin*/switch(j->jointype){caseJOIN_INNER:/**INNERJOINcanallowdeletionofeitherchildnode,butnot*both.Sorightchildgetspermissiontodeleteonlyif*leftchilddidn'tgetremoved.*/j->larg=pull_up_subqueries_recurse(root,j->larg,lowest_outer_join,lowest_nulling_outer_join,NULL,true);j->rarg=pull_up_subqueries_recurse(root,j->rarg,lowest_outer_join,lowest_nulling_outer_join,NULL,j->larg!=NULL);break;caseJOIN_LEFT:caseJOIN_SEMI:caseJOIN_ANTI:j->larg=pull_up_subqueries_recurse(root,j->larg,j,lowest_nulling_outer_join,NULL,false);j->rarg=pull_up_subqueries_recurse(root,j->rarg,j,j,NULL,false);break;caseJOIN_FULL:j->larg=pull_up_subqueries_recurse(root,j->larg,j,j,NULL,false);j->rarg=pull_up_subqueries_recurse(root,j->rarg,j,j,NULL,false);break;caseJOIN_RIGHT:j->larg=pull_up_subqueries_recurse(root,j->larg,j,j,NULL,false);j->rarg=pull_up_subqueries_recurse(root,j->rarg,j,lowest_nulling_outer_join,NULL,false);break;default:elog(ERROR,"unrecognizedjointype:%d",(int)j->jointype);break;}}elseelog(ERROR,"unrecognizednodetype:%d",(int)nodeTag(jtnode));returnjtnode;}

/**pull_up_simple_subquery*Attempttopullupasinglesimplesubquery.**jtnodeisaRangeTblRefthathasbeententativelyidentifiedasasimple*subquerybypull_up_subqueries.Wereturnthereplacementjointreenode,*orNULLifthesubquerycanbedeletedentirely,orjtnodeitselfifwe*determinethatthesubquerycan'tbepulledupafterall.**rteistheRangeTblEntryreferencedbyjtnode.Remainingparametersare*asforpull_up_subqueries_recurse.*/staticNode*pull_up_simple_subquery(PlannerInfo*root,Node*jtnode,RangeTblEntry*rte,JoinExpr*lowest_outer_join,JoinExpr*lowest_nulling_outer_join,AppendRelInfo*containing_appendrel,booldeletion_ok){Query*parse=root->parse;//查询树intvarno=((RangeTblRef*)jtnode)->rtindex;//RTR中的index,指向rtable中的位置Query*subquery;//子查询PlannerInfo*subroot;//子rootintrtoffset;//rtable中的偏移pullup_replace_vars_contextrvcontext;//上下文ListCell*lc;//临时变量/**Needamodifiablecopyofthesubquerytohackon.Evenifwedidn't*sometimeschoosenottopullupbelow,wemustdothistoavoid*problemsifthesamesubqueryisreferencedfrommultiplejointree*items(whichcan'thappennormally,butmightafterrulerewriting).*/subquery=copyObject(rte->subquery);//子查询/**CreateaPlannerInfodatastructureforthissubquery.**NOTE:thenextfewstepsshouldmatchthefirstprocessingin*subquery_planner().Canwerefactortoavoidcodeduplication,or*wouldthatjustmakethingsuglier?*///为子查询构建PlannerInfo,尝试对此子查询进行上拉subroot=makeNode(PlannerInfo);subroot->parse=subquery;subroot->glob=root->glob;subroot->query_level=root->query_level;subroot->parent_root=root->parent_root;subroot->plan_params=NIL;subroot->outer_params=NULL;subroot->planner_cxt=CurrentMemoryContext;subroot->init_plans=NIL;subroot->cte_plan_ids=NIL;subroot->multiexpr_params=NIL;subroot->eq_classes=NIL;subroot->append_rel_list=NIL;subroot->rowMarks=NIL;memset(subroot->upper_rels,0,sizeof(subroot->upper_rels));memset(subroot->upper_targets,0,sizeof(subroot->upper_targets));subroot->processed_tlist=NIL;subroot->grouping_map=NULL;subroot->minmax_aggs=NIL;subroot->qual_security_level=0;subroot->inhTargetKind=INHKIND_NONE;subroot->hasRecursion=false;subroot->wt_param_id=-1;subroot->non_recursive_path=NULL;/*NoCTEstoworryabout*/Assert(subquery->cteList==NIL);/**PullupanySubLinkswithinthesubquery'squals,sothatwedon't*leaveunoptimizedSubLinksbehind.*/if(subquery->hasSubLinks)//子链接?上拉子链接pull_up_sublinks(subroot);/**Similarly,inlineanyset-returningfunctionsinitsrangetable.*/inline_set_returning_functions(subroot);/**Recursivelypullupthesubquery'ssubqueries,sothat*pull_up_subqueries'processingiscompleteforitsjointreeand*rangetable.**Note:it'sokaythatthesubquery'srecursionstartswithNULLfor*containing-joininfo,evenifwearewithinanouterjoinintheupper*query;thelowerquerystartswithacleanslateforouter-join*semantics.Likewise,weneedn'tpassdownappendrelstate.*/pull_up_subqueries(subroot);//递归上拉子查询中的子查询/**Nowwemustrecheckwhetherthesubqueryisstillsimpleenoughtopull*up.Ifnot,abandonprocessingit.**Wedon'treallyneedtorecheckalltheconditionsinvolved,butit's*easierjusttokeepthis"if"lookingthesameastheonein*pull_up_subqueries_recurse.*///子查询中子链接&子查询上拉后,再次检查,确保本次上拉没有问题if(is_simple_subquery(subquery,rte,lowest_outer_join,deletion_ok)&&(containing_appendrel==NULL||is_safe_append_member(subquery))){/*goodtogo*/}else{/**Giveup,returnunmodifiedRangeTblRef.**Note:Theworkwejustdidwillberedonewhenthesubquerygets*plannedonitsown.Perhapswecouldavoidthatbystoringthe*modifiedsubquerybackintotherangetable,butI'mnotgonnarisk*itnow.*/returnjtnode;}/**WemustflattenanyjoinaliasVarsinthesubquery'stargetlist,*becausepullingupthesubquery'ssubqueriesmighthavechangedtheir*expansionsintoarbitraryexpressions,whichcouldaffect*pullup_replace_vars'decisionsaboutwhetherPlaceHolderVarwrappers*areneededfortlistentries.(Likelyit'dbebettertodo*flatten_join_alias_varsonthewholequerytreeatsomeearlierstage,*maybeevenintherewriter;butfornowlet'sjustfixthiscasehere.)*///子查询中的targetList扁平化处理subquery->targetList=(List*)flatten_join_alias_vars(subroot,(Node*)subquery->targetList);/**Adjustlevel-0varnosinsubquerysothatwecanappenditsrangetable*toupperquery's.Wehavetofixthesubquery'sappend_rel_listas*well.*///调整Var.varnortoffset=list_length(parse->rtable);OffsetVarNodes((Node*)subquery,rtoffset,0);OffsetVarNodes((Node*)subroot->append_rel_list,rtoffset,0);/**Upper-levelvarsinsubqueryarenowonelevelclosertotheirparent*thanbefore.*///调整Var.varlevelsupIncrementVarSublevelsUp((Node*)subquery,-1,1);IncrementVarSublevelsUp((Node*)subroot->append_rel_list,-1,1);/**Thesubquery'stargetlistitemsarenowintheappropriateformto*insertintothetopquery,exceptthatwemayneedtowrapthemin*PlaceHolderVars.Setuprequiredcontextdataforpullup_replace_vars.*/rvcontext.root=root;rvcontext.targetlist=subquery->targetList;rvcontext.target_rte=rte;if(rte->lateral)rvcontext.relids=get_relids_in_jointree((Node*)subquery->jointree,true);else/*won'tneedrelids*/rvcontext.relids=NULL;rvcontext.outer_hasSubLinks=&parse->hasSubLinks;rvcontext.varno=varno;/*theseflagswillbesetbelow,ifneeded*/rvcontext.need_phvs=false;rvcontext.wrap_non_vars=false;/*initializecachearraywithindexes0..length(tlist)*/rvcontext.rv_cache=palloc0((list_length(subquery->targetList)+1)*sizeof(Node*));/**Ifweareunderanouterjointhennon-nullableitemsandlateral*referencesmayhavetobeturnedintoPlaceHolderVars.*/if(lowest_nulling_outer_join!=NULL)rvcontext.need_phvs=true;/**Ifwearedealingwithanappendrelmemberthenanythingthat'snota*simpleVarhastobeturnedintoaPlaceHolderVar.Weforcethisto*ensurethatwhatwepullupdoesn'tgetmergedintoasurrounding*expressionduringlaterprocessingandthenfailtomatchthe*expressionactuallyavailablefromtheappendrel.*/if(containing_appendrel!=NULL){rvcontext.need_phvs=true;rvcontext.wrap_non_vars=true;}/**Iftheparentqueryusesgroupingsets,weneedaPlaceHolderVarfor*anythingthat'snotasimpleVar.Again,thisensuresthatexpressions*retaintheirseparateidentitysothattheywillmatchgroupingset*columnswhenappropriate.(It'dbesufficienttowrapvaluesusedin*groupingsetcolumns,anddosoonlyinnon-aggregatedportionsofthe*tlistandhavingQual,butthatwouldrequirealotofinfrastructure*thatpullup_replace_varshasn'tcurrentlygot.)*/if(parse->groupingSets){rvcontext.need_phvs=true;rvcontext.wrap_non_vars=true;}/**Replaceallofthetopquery'sreferencestothesubquery'soutputs*withcopiesoftheadjustedsubtlistitems,beingcarefulnotto*replaceanyofthejointreestructure.(This'dbealotcleanerifwe*couldusequery_tree_mutator.)WehavetousePHVsinthetargetList,*returningList,andhavingQual,sincethosearecertainlyaboveany*outerjoin.replace_vars_in_jointreetracksitslocationinthe*jointreeandusesPHVsornotappropriately.*///处理投影parse->targetList=(List*)pullup_replace_vars((Node*)parse->targetList,&rvcontext);parse->returningList=(List*)pullup_replace_vars((Node*)parse->returningList,&rvcontext);if(parse->onConflict){parse->onConflict->onConflictSet=(List*)pullup_replace_vars((Node*)parse->onConflict->onConflictSet,&rvcontext);parse->onConflict->onConflictWhere=pullup_replace_vars(parse->onConflict->onConflictWhere,&rvcontext);/**WeassumeONCONFLICT'sarbiterElems,arbiterWhere,exclRelTlist*can'tcontainanyreferencestoasubquery*/}replace_vars_in_jointree((Node*)parse->jointree,&rvcontext,lowest_nulling_outer_join);Assert(parse->setOperations==NULL);parse->havingQual=pullup_replace_vars(parse->havingQual,&rvcontext);/**Replacereferencesinthetranslated_varslistsofappendrels.When*pullingupanappendrelmember,wedonotneedPHVsinthelistofthe*parentappendrel---thereisn'tanyouterjoinbetween.Elsewhere,use*PHVsforsafety.(Thisanalysiscouldbemadetighterbutitseems*unlikelytobeworthmuchtrouble.)*///处理appendrels中的信息foreach(lc,root->append_rel_list){AppendRelInfo*appinfo=(AppendRelInfo*)lfirst(lc);boolsave_need_phvs=rvcontext.need_phvs;if(appinfo==containing_appendrel)rvcontext.need_phvs=false;appinfo->translated_vars=(List*)pullup_replace_vars((Node*)appinfo->translated_vars,&rvcontext);rvcontext.need_phvs=save_need_phvs;}/**ReplacereferencesinthejoinaliasvarslistsofjoinRTEs.**YoumightthinkthatwecouldavoidusingPHVsforaliasvarsofjoins*belowlowest_nulling_outer_join,butthatdoesn'tworkbecausethe*aliasvarscouldbereferencedabovethatjoin;weneedthePHVstobe*presentinsuchreferencesafterthealiasvarsgetflattened.(It*mightbeworthtryingtobesmarterhere,someday.)*///处理RTE中类型为RTE_JOIN的节点foreach(lc,parse->rtable){RangeTblEntry*otherrte=(RangeTblEntry*)lfirst(lc);if(otherrte->rtekind==RTE_JOIN)otherrte->joinaliasvars=(List*)pullup_replace_vars((Node*)otherrte->joinaliasvars,&rvcontext);}/**IfthesubqueryhadaLATERALmarker,propagatethattoanyofits*childRTEsthatcouldpossiblynowcontainlateralcross-references.*Thechildrenmightormightnotcontainanyactuallateral*cross-references,butwehavetomarkthepulled-upchildRTEssothat*laterplannerstageswillcheckforsuch.*///LATERAL支持if(rte->lateral){foreach(lc,subquery->rtable){RangeTblEntry*child_rte=(RangeTblEntry*)lfirst(lc);switch(child_rte->rtekind){caseRTE_RELATION:if(child_rte->tablesample)child_rte->lateral=true;break;caseRTE_SUBQUERY:caseRTE_FUNCTION:caseRTE_VALUES:caseRTE_TABLEFUNC:child_rte->lateral=true;break;caseRTE_JOIN:caseRTE_CTE:caseRTE_NAMEDTUPLESTORE:/*thesecan'tcontainanylateralreferences*/break;}}}/**Nowappendtheadjustedrtableentriestoupperquery.(Weholdoff*untilafterfixingtheupperrtableentries;nopointinrunningthat*codeonthesubqueryonestoo.)*///子查询中的RTE填充至父查询中parse->rtable=list_concat(parse->rtable,subquery->rtable);/**PullupanyFORUPDATE/SHAREmarkers,too.(OffsetVarNodesalready*adjustedthemarkerrtindexes,sojustconcatthelists.)*/parse->rowMarks=list_concat(parse->rowMarks,subquery->rowMarks);/**WealsohavetofixtherelidsetsofanyPlaceHolderVarnodesinthe*parentquery.(Thiscouldperhapsbedonebypullup_replace_vars(),*butitseemscleanertousetwopasses.)Noteinparticularthatany*PlaceHolderVarnodesjustcreatedbypullup_replace_vars()willbe*adjusted,sohavingcreatedthemwiththesubquery'svarnoiscorrect.**Likewise,relidsappearinginAppendRelInfonodeshavetobefixed.We*alreadycheckedthatthiswon'trequireintroducingmultiplesubrelids*intothesingle-slotAppendRelInfostructs.*/if(parse->hasSubLinks||root->glob->lastPHId!=0||root->append_rel_list){Relidssubrelids;subrelids=get_relids_in_jointree((Node*)subquery->jointree,false);substitute_multiple_relids((Node*)parse,varno,subrelids);fix_append_rel_relids(root->append_rel_list,varno,subrelids);}/**Andnowaddsubquery'sAppendRelInfostoourlist.*/root->append_rel_list=list_concat(root->append_rel_list,subroot->append_rel_list);/**Wedon'thavetodotheequivalentbookkeepingforouter-joininfo,*becausethathasn'tbeensetupyet.placeholder_listlikewise.*/Assert(root->join_info_list==NIL);Assert(subroot->join_info_list==NIL);Assert(root->placeholder_list==NIL);Assert(subroot->placeholder_list==NIL);/**Miscellaneoushousekeeping.**Althoughreplace_rte_variables()faithfullyupdatedparse->hasSubLinks*ifitcopiedanySubLinksoutofthesubquery'stargetlist,westill*couldhaveSubLinksaddedtothequeryintheexpressionsofFUNCTION*andVALUESRTEscopiedupfromthesubquery.Soit'snecessarytocopy*subquery->hasSubLinksanyway.Perhapsthiscanbeimprovedsomeday.*/parse->hasSubLinks|=subquery->hasSubLinks;/*IfsubqueryhadanyRLSconditions,nowmainquerydoestoo*/parse->hasRowSecurity|=subquery->hasRowSecurity;/**subquerywon'tbepulledupifithasAggs,hasWindowFuncs,or*hasTargetSRFs,sonoworkneededonthoseflags*//**ReturntheadjustedsubqueryjointreetoreplacetheRangeTblRefentry*inparent'sjointree;or,ifwe'reflatteningasubquerywithempty*FROMlist,returnNULLtosignaldeletionofthesubqueryfromthe*parentjointree(andsethasDeletedRTEstoensurecleanuplater).*/if(subquery->jointree->fromlist==NIL){Assert(deletion_ok);Assert(subquery->jointree->quals==NULL);root->hasDeletedRTEs=true;returnNULL;}return(Node*)subquery->jointree;}

is_simple_subquery

/**is_simple_subquery*Checkasubqueryintherangetabletoseeifit'ssimpleenough*topullupintotheparentquery.**rteistheRTE_SUBQUERYRangeTblEntrythatcontainedthesubquery.*(Notesubqueryisnotnecessarilyequaltorte->subquery;itcouldbea*processedcopyofthat.)*lowest_outer_joinisthelowestouterjoinabovethesubquery,orNULL.*deletion_okistrueifit'dbeokaytodeletethesubqueryentirely.*/staticboolis_simple_subquery(Query*subquery,RangeTblEntry*rte,JoinExpr*lowest_outer_join,booldeletion_ok){/**Let'sjustmakesureit'savalidsubselect...*/if(!IsA(subquery,Query)||subquery->commandType!=CMD_SELECT)elog(ERROR,"subqueryisbogus");/**Can'tcurrentlypullupaquerywithsetops(unlessit'ssimpleUNION*ALL,whichishandledbyadifferentcodepath).Maybeafterquerytree*redesign...*/if(subquery->setOperations)returnfalse;//存在集合操作/**Can'tpullupasubqueryinvolvinggrouping,aggregation,SRFs,*sorting,limiting,orWITH.(XXXWITHcouldpossiblybeallowedlater)**Wealsodon'tpullupasubquerythathasexplicitFORUPDATE/SHARE*clauses,becausepullupwouldcausethelockingtooccursemantically*higherthanitshould.ImplicitFORUPDATE/SHAREisokaybecausein*thatcasethelockingwasoriginallydeclaredintheupperquery*anyway.*/if(subquery->hasAggs||subquery->hasWindowFuncs||subquery->hasTargetSRFs||subquery->groupClause||subquery->groupingSets||subquery->havingQual||subquery->sortClause||subquery->distinctClause||subquery->limitOffset||subquery->limitCount||subquery->hasForUpdate||subquery->cteList)returnfalse;//存在聚合函数/窗口函数.../**Don'tpullupiftheRTErepresentsasecurity-barrierview;we*couldn'tpreventinformationleakageoncetheRTE'sVarsarescattered*aboutintheupperquery.*/if(rte->security_barrier)returnfalse;///**Don'tpullupasubquerywithanemptyjointree,unlessithasnoquals*anddeletion_okistrueandwe'renotunderneathanouterjoin.**query_planner()willcorrectlygenerateaResultplanforajointree*that'stotallyempty,butwecan'tcopewithanemptyFromExpr*appearinglowerdowninajointree:weidentifyjoinrelsviabaserelid*sets,sowecouldn'tdistinguishajoincontainingsuchaFromExprfrom*onewithoutit.Wecanonlyhandlesuchcasesiftheplacewherethe*subqueryislinkedisaFromExprorinnerJOINthatwouldstillbe*nonemptyafterremovalofthesubquery,sothatit'sstillidentifiable*viaitscontainedbaserelids.Safecontextsaresignaledby*deletion_ok.**Buteveninasafecontext,wemustkeepthesubqueryifithasany*quals,becauseit'sunclearwheretoputthemintheupperquery.**Also,wemustforbidpullupifsuchasubqueryisunderneathanouter*join,becausethenwemightneedtowrapitsoutputcolumnswith*PlaceHolderVars,andthePHVswouldthenhaveemptyrelidsetsmeaning*wecouldn'ttellwheretoevaluatethem.(Thistestisseparatefrom*thedeletion_okflagforpossiblefutureexpansion:deletion_oktells*whethertheimmediateparentsiteinthejointreecouldcope,not*whetherwe'dhavePHVissues.It'spossiblethisrestrictioncouldbe*fixedbylettingthePHVsusetherelidsoftheparentjointreeitem,*butthatcomplicationisforanotherday.)**Notethatdeletionofasubqueryisalsodependentonthecheckbelow*thatitstargetlistcontainsnoset-returningfunctions.Deletionfrom*aFROMlistorinnerJOINisokayonlyifthesubquerymustreturn*exactlyonerow.*/if(subquery->jointree->fromlist==NIL&&(subquery->jointree->quals!=NULL||!deletion_ok||lowest_outer_join!=NULL))returnfalse;/**IfthesubqueryisLATERAL,checkforpulluprestrictionsfromthat.*/if(rte->lateral){boolrestricted;Relidssafe_upper_varnos;/**Thesubquery'sWHEREandJOIN/ONqualsmustn'tcontainanylateral*referencestorelsoutsideahigherouterjoin(includingthecase*wheretheouterjoiniswithinthesubqueryitself).Insucha*case,pullingupwouldresultinasituationwhereweneedto*postponequalsfrombelowanouterjointoaboveit,whichis*probablycompletelywrongandinanycaseisacomplicationthat*doesn'tseemworthaddressingatthemoment.*/if(lowest_outer_join!=NULL){restricted=true;safe_upper_varnos=get_relids_in_jointree((Node*)lowest_outer_join,true);}else{restricted=false;safe_upper_varnos=NULL;/*doesn'tmatter*/}if(jointree_contains_lateral_outer_refs((Node*)subquery->jointree,restricted,safe_upper_varnos))returnfalse;/**Ifthere'sanouterjoinabovetheLATERALsubquery,alsodisallow*pullupifthesubquery'stargetlisthasanyreferencestorels*outsidetheouterjoin,sincethesemightgetpulledintoquals*abovethesubquery(butinorbelowtheouterjoin)andthenlead*toqual-postponementissuessimilartothecasecheckedforabove.*(Wewouldn'tneedtopreventpullupifnosuchreferencesappearin*outer-queryquals,butwedon'thaveenoughinfoheretocheck*that.Also,maybethisrestrictioncouldberemovedifweforced*suchrefstobewrappedinPlaceHolderVars,evenwhenthey'rebelow*thenearestouterjoin?Butit'saprettyhokeyusage,sonot*clearthisisworthsweatingover.)*/if(lowest_outer_join!=NULL){Relidslvarnos=pull_varnos_of_level((Node*)subquery->targetList,1);if(!bms_is_subset(lvarnos,safe_upper_varnos))returnfalse;}}/**Don'tpullupasubquerythathasanyvolatilefunctionsinits*targetlist.Otherwisewemightintroducemultipleevaluationsofthese*functions,iftheygetcopiedtomultipleplacesintheupperquery,*leadingtosurprisingresults.(Note:thePlaceHolderVarmechanism*doesn'tquiteguaranteesingleevaluation;elsewecouldpullupanyway*andjustwrapsuchitemsinPlaceHolderVars...)*/if(contain_volatile_functions((Node*)subquery->targetList))returnfalse;//存在易变函数returntrue;}

pull_up_subqueries_cleanup

/**pull_up_subqueries_cleanup*Recursivelyfixupjointreeafterdeletionofsomesubqueries.**ThejointreenowcontainssomeNULLsubtrees,whichweneedtogetridof.*InaFromExpr,justrebuildthechild-nodelistwithnullentriesdeleted.*InaninnerJOIN,replacetheJoinExprnodewithaone-childFromExpr.*/staticNode*pull_up_subqueries_cleanup(Node*jtnode){Assert(jtnode!=NULL);if(IsA(jtnode,RangeTblRef)){/*Nothingtodoatleafnodes.*/}elseif(IsA(jtnode,FromExpr)){FromExpr*f=(FromExpr*)jtnode;List*newfrom=NIL;ListCell*l;foreach(l,f->fromlist){Node*child=(Node*)lfirst(l);if(child==NULL)continue;child=pull_up_subqueries_cleanup(child);newfrom=lappend(newfrom,child);}f->fromlist=newfrom;}elseif(IsA(jtnode,JoinExpr)){JoinExpr*j=(JoinExpr*)jtnode;if(j->larg)j->larg=pull_up_subqueries_cleanup(j->larg);if(j->rarg)j->rarg=pull_up_subqueries_cleanup(j->rarg);if(j->larg==NULL){Assert(j->jointype==JOIN_INNER);Assert(j->rarg!=NULL);return(Node*)makeFromExpr(list_make1(j->rarg),j->quals);}elseif(j->rarg==NULL){Assert(j->jointype==JOIN_INNER);return(Node*)makeFromExpr(list_make1(j->larg),j->quals);}}elseelog(ERROR,"unrecognizednodetype:%d",(int)nodeTag(jtnode));returnjtnode;}三、跟踪分析

gdb跟踪分析:

(gdb)bpull_up_subqueriesBreakpoint1at0x77d63b:fileprepjointree.c,line612.(gdb)cContinuing.Breakpoint1,pull_up_subqueries(root=0x1d092d0)atprepjointree.c:612612root->hasDeletedRTEs=false;(gdb)#输入参数,root参见上拉子链接中的说明#进入pull_up_subqueries_recurse(gdb)step615pull_up_subqueries_recurse(root,(Node*)root->parse->jointree,(gdb)steppull_up_subqueries_recurse(root=0x1d092d0,jtnode=0x1d092a0,lowest_outer_join=0x0,lowest_nulling_outer_join=0x0,containing_appendrel=0x0,deletion_ok=false)atprepjointree.c:680680if(IsA(jtnode,RangeTblRef))(gdb)#输入参数:#1.root,同pull_up_subqueries#2.jtnode,Query查询树#3/4/5.lowest_outer_join/lowest_nulling_outer_join/containing_appendrel均为NULL#6.deletion_ok,false...(gdb)p*jtnode$2={type=T_FromExpr}#FromExpr,进入相应的分支...#递归调用pull_up_subqueries_recurse(gdb)763lfirst(l)=pull_up_subqueries_recurse(root,lfirst(l),(gdb)steppull_up_subqueries_recurse(root=0x1d092d0,jtnode=0x1c73078,lowest_outer_join=0x0,lowest_nulling_outer_join=0x0,containing_appendrel=0x0,deletion_ok=true)atprepjointree.c:680680if(IsA(jtnode,RangeTblRef))#注意:这时候的jtnode类型为RangeTblRef(gdb)n682intvarno=((RangeTblRef*)jtnode)->rtindex;(gdb)683RangeTblEntry*rte=rt_fetch(varno,root->parse->rtable);(gdb)692if(rte->rtekind==RTE_SUBQUERY&&(gdb)pvarno$4=1#rtable中第1个RTE是父查询的Relation(即t_dwxx),不是子查询(gdb)p*rte$5={type=T_RangeTblEntry,rtekind=RTE_RELATION,relid=16394,relkind=114'r',tablesample=0x0,subquery=0x0,security_barrier=false,jointype=JOIN_INNER,joinaliasvars=0x0,functions=0x0,funcordinality=false,tablefunc=0x0,values_lists=0x0,ctename=0x0,ctelevelsup=0,self_reference=false,coltypes=0x0,coltypmods=0x0,colcollations=0x0,enrname=0x0,enrtuples=0,alias=0x1c4fd58,eref=0x1c72c98,lateral=false,inh=true,inFromCl=true,requiredPerms=2,checkAsUser=0,selectedCols=0x1d07698,insertedCols=0x0,updatedCols=0x0,securityQuals=0x0}(gdb)n712if(rte->rtekind==RTE_SUBQUERY&&(gdb)722if(rte->rtekind==RTE_VALUES&&(gdb)852returnjtnode;(gdb)...#rtable中的第2个元素,类型为RTE_SUBQUERY(gdb)steppull_up_subqueries_recurse(root=0x1d092d0,jtnode=0x1d07358,lowest_outer_join=0x0,lowest_nulling_outer_join=0x0,containing_appendrel=0x0,deletion_ok=true)atprepjointree.c:680680if(IsA(jtnode,RangeTblRef))(gdb)n682intvarno=((RangeTblRef*)jtnode)->rtindex;(gdb)(gdb)p*rte$7={type=T_RangeTblEntry,rtekind=RTE_SUBQUERY,relid=0,relkind=0'\000',tablesample=0x0,subquery=0x1c72968,security_barrier=false,jointype=JOIN_INNER,joinaliasvars=0x0,functions=0x0,funcordinality=false,tablefunc=0x0,values_lists=0x0,ctename=0x0,ctelevelsup=0,self_reference=false,coltypes=0x0,coltypmods=0x0,colcollations=0x0,enrname=0x0,enrtuples=0,alias=0x1c50548,eref=0x1d071a0,lateral=false,inh=false,inFromCl=true,requiredPerms=0,checkAsUser=0,selectedCols=0x0,insertedCols=0x0,updatedCols=0x0,securityQuals=0x0}...#进入pull_up_simple_subquery697returnpull_up_simple_subquery(root,jtnode,rte,(gdb)steppull_up_simple_subquery(root=0x1d092d0,jtnode=0x1d07358,rte=0x1c72a78,lowest_outer_join=0x0,lowest_nulling_outer_join=0x0,containing_appendrel=0x0,deletion_ok=true)atprepjointree.c:874874Query*parse=root->parse;...1247return(Node*)subquery->jointree;(gdb)1248}(gdb)pull_up_subqueries_recurse(root=0x1d09838,jtnode=0x1c736e0,lowest_outer_join=0x0,lowest_nulling_outer_join=0x0,containing_appendrel=0x0,deletion_ok=true)atprepjointree.c:853853}(gdb)

到此，关于“PostgreSQL查询语句分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！