这篇文章主要讲解了“PostgreSQL中create_sort_plan函数实现逻辑是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“PostgreSQL中create_sort_plan函数实现逻辑是什么”吧！

Plan
所有计划节点通过将Plan结构作为第一个字段从Plan结构“派生”。这确保了在将节点转换为计划节点时，一切都能正常工作。(在执行器中以通用方式传递时，节点指针经常被转换为Plan *)

/*----------------*Plannode**Allplannodes"derive"fromthePlanstructurebyhavingthe*Planstructureasthefirstfield.Thisensuresthateverythingworks*whennodesarecasttoPlan's.(nodepointersarefrequentlycasttoPlan**whenpassedaroundgenericallyintheexecutor)*所有计划节点通过将Plan结构作为第一个字段从Plan结构“派生”。*这确保了在将节点转换为计划节点时，一切都能正常工作。*(在执行器中以通用方式传递时，节点指针经常被转换为Plan*)**WeneveractuallyinstantiateanyPlannodes;thisisjustthecommon*abstractsuperclassforallPlan-typenodes.*从未实例化任何Plan节点;这只是所有Plan-type节点的通用抽象超类。*----------------*/typedefstructPlan{NodeTagtype;//节点类型/**成本估算信息;estimatedexecutioncostsforplan(seecostsize.cformoreinfo)*/Coststartup_cost;/*启动成本;costexpendedbeforefetchinganytuples*/Costtotal_cost;/*总成本;totalcost(assumingalltuplesfetched)*//**优化器估算信息;planner'sestimateofresultsizeofthisplanstep*/doubleplan_rows;/*行数;numberofrowsplanisexpectedtoemit*/intplan_width;/*平均行大小(Byte为单位);averagerowwidthinbytes*//**并行执行相关的信息;informationneededforparallelquery*/boolparallel_aware;/*是否参与并行执行逻辑?engageparallel-awarelogic?*/boolparallel_safe;/*是否并行安全;OKtouseaspartofparallelplan?*//**Plan类型节点通用的信息.CommonstructuraldataforallPlantypes.*/intplan_node_id;/*uniqueacrossentirefinalplantree*/List*targetlist;/*targetlisttobecomputedatthisnode*/List*qual;/*implicitly-ANDedqualconditions*/structPlan*lefttree;/*inputplantree(s)*/structPlan*righttree;List*initPlan;/*InitPlannodes(un-correlatedexpr*subselects)*//**Informationformanagementofparameter-change-drivenrescanning*parameter-change-driven重扫描的管理信息.**extParamincludestheparamIDsofallexternalPARAM_EXECparams*affectingthisplannodeoritschildren.setParamparamsfromthe*node'sinitPlansarenotincluded,buttheirextParamsare.**allParamincludesalltheextParamparamIDs,plustheIDsoflocal*paramsthataffectthenode(i.e.,thesetParamsofitsinitplans).*Theseare_all_thePARAM_EXECparamsthataffectthisnode.*/Bitmapset*extParam;Bitmapset*allParam;}Plan;二、源码解读

create_plan->create_plan_recurse->create_projection_plan函数创建计划树,执行投影操作并通过递归的方式为子访问路径生成执行计划。create_sort_plan函数创建Sort计划节点。

//----------------------------------------------------------------create_projection_plan/**create_projection_plan**Createaplantreetodoaprojectionstepand(recursively)plans*foritssubpaths.WemayneedaResultnodefortheprojection,*butsometimeswecanjustletthesubplandothework.*创建计划树,执行投影操作并通过递归的方式为子访问路径生成执行计划.*一般来说需要一个Result节点用于投影操作,但有时候可以让子计划执行此项任务.*/staticPlan*create_projection_plan(PlannerInfo*root,ProjectionPath*best_path,intflags){Plan*plan;Plan*subplan;List*tlist;boolneeds_result_node=false;/**ConvertoursubpathtoaPlananddeterminewhetherweneedaResult*node.*转换subpath为Plan,并确定是否需要Result节点.**Inmostcaseswherewedon'tneedtoproject,creation_projection_path*willhavesetdummypp,butnotalways.First,somecreateplan.c*routineschangethetlistsoftheirnodes.(Anexampleisthat*create_merge_append_planmightaddresjunksortcolumnstoa*MergeAppend.)Second,create_projection_pathhasnowayofknowing*whatpathnodewillbeplacedontopoftheprojectionpathand*thereforecan'tpredictwhetheritwillrequireanexacttlist.For*bothofthesereasons,wehavetorecheckhere.*在大多数情况下，我们不需要投影运算，creation_projection_path将设置dummypp标志，但并不总是如此。*首先,一些createplan.c中的函数更改其节点的tlist。*(例如，create_merge_append_plan可能会向MergeAppend添加resjunksort列)。*其次，create_projection_path无法知道将在投影路径顶部放置哪些路径节点，因此无法预测它是否需要一个确切的tlist。*由于这两个原因，我们不得不在这里重新检查。*/if(use_physical_tlist(root,&best_path->path,flags)){/**Ourcallerdoesn'treallycarewhattlistwereturn,sowedon't*actuallyneedtoproject.However,wemaystillneedtoensure*propersortgroupreflabels,ifthecallercaresaboutthose.*如果我们的调用者并不关心返回的结果tlist，所以实际上不需要投影运算。*然而，如果调用者关心sortgroupref标签，可能仍然需要确保正确的sortgroupref数据。*/subplan=create_plan_recurse(root,best_path->subpath,0);tlist=subplan->targetlist;if(flags&CP_LABEL_TLIST)apply_pathtarget_labeling_to_tlist(tlist,best_path->path.pathtarget);}elseif(is_projection_capable_path(best_path->subpath)){/**Ourcallerrequiresthatwereturntheexacttlist,butnoseparate*resultnodeisneededbecausethesubpathisprojection-capable.*Tellcreate_plan_recursethatwe'regoingtoignorethetlistit*produces.*调用者要求返回精确的tlist，但是不需要单独的Result节点，因为子路径支持投影。*调用create_plan_recurse时忽略它生成的tlist。*/subplan=create_plan_recurse(root,best_path->subpath,CP_IGNORE_TLIST);tlist=build_path_tlist(root,&best_path->path);}else{/**Itlookslikeweneedaresultnode,unlessbygoodfortunethe*requestedtlistisexactlytheonethechildwantstoproduce.*看起来需要一个Result节点，除非幸运的是，请求的tlist正是子节点想要生成的。*/subplan=create_plan_recurse(root,best_path->subpath,0);tlist=build_path_tlist(root,&best_path->path);needs_result_node=!tlist_same_exprs(tlist,subplan->targetlist);}/**IfwemakeadifferentdecisionaboutwhethertoincludeaResultnode*thancreate_projection_pathdid,we'llhavemadeslightlywrongcost*estimates;butlabeltheplanwiththecostestimatesweactuallyused,*not"corrected"ones.(XXXthiscouldbecleanedupifwemovedmore*ofthesortcolumnsetuplogicintoPathcreation,butthatwouldadd*expensetocreatingPathswemightendupnotusing.)*如果我们对是否包含一个Result节点做出与create_projection_path不同的决定，*我们就会做出略微错误的成本估算;*但是在这个计划上标上我们实际使用的成本估算，而不是“修正的”成本估算。*(如果我们将更多的sortcolumn设置逻辑移到路径创建中，这个问题就可以解决了，*但是这会增加创建路径的成本，而最终可能不会使用这些路径。)*/if(!needs_result_node){/*Don'tneedaseparateResult,justassigntlisttosubplan*///不需要单独的Result节点,把tlist赋值给subplanplan=subplan;plan->targetlist=tlist;/*Labelplanwiththeestimatedcostsweactuallyused*///标记估算成本plan->startup_cost=best_path->path.startup_cost;plan->total_cost=best_path->path.total_cost;plan->plan_rows=best_path->path.rows;plan->plan_width=best_path->path.pathtarget->width;plan->parallel_safe=best_path->path.parallel_safe;/*...butdon'tchangesubplan'sparallel_awareflag*/}else{/*WeneedaResultnode*///需要Result节点plan=(Plan*)make_result(tlist,NULL,subplan);copy_generic_path_info(plan,(Path*)best_path);}returnplan;}//----------------------------------------------------------------create_sort_plan/**create_sort_plan**CreateaSortplanfor'best_path'and(recursively)plans*foritssubpaths.*创建Sort计划节点*/staticSort*create_sort_plan(PlannerInfo*root,SortPath*best_path,intflags){Sort*plan;Plan*subplan;/**Wedon'twantanyexcesscolumnsinthesortedtuples,sorequesta*smallertlist.Otherwise,sinceSortdoesn'tproject,tlist*requirementspassthrough.*我们不希望在排序元组中有任何多余的列，所以希望得到一个更小的tlist。*否则，由于Sort不执行投影运算，tlist就会通过完整的保留传递。*/subplan=create_plan_recurse(root,best_path->subpath,flags|CP_SMALL_TLIST);/**make_sort_from_pathkeys()indirectlycallsfind_ec_member_for_tle(),*whichwillignoreanychildECmembersthatdon'tbelongtothegiven*relids.Thus,ifthissortpathisbasedonachildrelation,wemust*passitsrelids.*make_sort_from_pathkeys()间接调用find_ec_member_for_tle()，它将忽略不属于给定relid的任何子EC成员。*因此，如果这个排序路径是基于子关系的，我们必须传递它的relids。*/plan=make_sort_from_pathkeys(subplan,best_path->path.pathkeys,IS_OTHER_REL(best_path->subpath->parent)?best_path->path.parent->relids:NULL);copy_generic_path_info(&plan->plan,(Path*)best_path);returnplan;}//------------------------------------------------build_path_tlist/**Buildatargetlist(ie,alistofTargetEntry)forthePath'soutput.*构建用于输出的target链表(比如TargetEntry节点链表)**Thisisalmostjustmake_tlist_from_pathtarget(),butwealsohaveto*dealwithreplacingnestloopparams.*该函数几乎就是make_tlist_from_pathtarget()的实现逻辑，但还必须处理替换nestloop参数。*/staticList*build_path_tlist(PlannerInfo*root,Path*path){List*tlist=NIL;Index*sortgrouprefs=path->pathtarget->sortgrouprefs;intresno=1;ListCell*v;foreach(v,path->pathtarget->exprs){Node*node=(Node*)lfirst(v);TargetEntry*tle;/**Ifit'saparameterizedpath,theremightbelateralreferencesin*thetlist,whichneedtobereplacedwithParams.There'snoneed*toremaketheTargetEntrynodes,soapplythistoeachlistitem*separately.*如果是参数化路径，那么tlist中可能有横向引用，需要用Params替换。*不需要重新构建TargetEntry节点，因此可以将其单独应用于每个链表项。*/if(path->param_info)node=replace_nestloop_params(root,node);tle=makeTargetEntry((Expr*)node,resno,NULL,false);if(sortgrouprefs)tle->ressortgroupref=sortgrouprefs[resno-1];tlist=lappend(tlist,tle);resno++;}returntlist;}三、跟踪分析

测试脚本如下

testdb=#explainselectdw.*,grjf.grbh,grjf.xm,grjf.ny,grjf.jetestdb-#fromt_dwxxdw,lateral(selectgr.grbh,gr.xm,jf.ny,jf.jetestdb(#fromt_grxxgrinnerjoint_jfxxjftestdb(#ongr.dwbh=dw.dwbhtestdb(#andgr.grbh=jf.grbh)grjftestdb-#orderbydw.dwbh;QUERYPLAN------------------------------------------------------------------------------------------Sort(cost=20070.93..20320.93rows=100000width=47)SortKey:dw.dwbh->HashJoin(cost=3754.00..8689.61rows=100000width=47)HashCond:((gr.dwbh)::text=(dw.dwbh)::text)->HashJoin(cost=3465.00..8138.00rows=100000width=31)HashCond:((jf.grbh)::text=(gr.grbh)::text)->SeqScanont_jfxxjf(cost=0.00..1637.00rows=100000width=20)->Hash(cost=1726.00..1726.00rows=100000width=16)->SeqScanont_grxxgr(cost=0.00..1726.00rows=100000width=16)->Hash(cost=164.00..164.00rows=10000width=20)->SeqScanont_dwxxdw(cost=0.00..164.00rows=10000width=20)(11rows)

启动gdb,设置断点,进入create_projection_plan函数

(gdb)bcreate_projection_planBreakpoint2at0x7b95a8:filecreateplan.c,line1627.(gdb)cContinuing.Breakpoint2,create_projection_plan(root=0x26c1258,best_path=0x2722d00,flags=1)atcreateplan.c:16271627boolneeds_result_node=false;

转换subpath为Plan,并确定是否需要Result节点,并且判断是否需要生成Result节点

...(gdb)n1642if(use_physical_tlist(root,&best_path->path,flags))(gdb)n1655elseif(is_projection_capable_path(best_path->subpath))(gdb)1673subplan=create_plan_recurse(root,best_path->subpath,0);

查看best_path&best_path->subpath变量

(gdb)p*best_path$3={path={type=T_ProjectionPath,pathtype=T_Result,parent=0x2722998,pathtarget=0x27226f8,param_info=0x0,parallel_aware=false,parallel_safe=true,parallel_workers=0,rows=100000,startup_cost=20070.931487218411,total_cost=20320.931487218411,pathkeys=0x26cfe98},subpath=0x2722c68,dummypp=true}(gdb)p*(SortPath*)best_path->subpath$16={path={type=T_SortPath,pathtype=T_Sort,parent=0x2722998,pathtarget=0x27212d8,param_info=0x0,parallel_aware=false,parallel_safe=true,parallel_workers=0,rows=100000,startup_cost=20070.931487218411,total_cost=20320.931487218411,pathkeys=0x26cfe98},subpath=0x2721e60}

创建subpath(SortPath)的执行计划

(gdb)stepcreate_plan_recurse(root=0x26c1258,best_path=0x2722c68,flags=0)atcreateplan.c:364364check_stack_depth();(gdb)n366switch(best_path->pathtype)(gdb)447plan=(Plan*)create_sort_plan(root,

进入create_sort_plan

(gdb)stepcreate_sort_plan(root=0x26c1258,best_path=0x2722c68,flags=0)atcreateplan.c:17591759subplan=create_plan_recurse(root,best_path->subpath,

SortPath的subpath是HashPath

(gdb)pbest_path->subpath->type$17=T_HashPath(gdb)p*(HashPath*)best_path->subpath$18={jpath={path={type=T_HashPath,pathtype=T_HashJoin,parent=0x27210c0,pathtarget=0x27212d8,param_info=0x0,parallel_aware=false,parallel_safe=true,parallel_workers=0,rows=100000,startup_cost=3754,total_cost=8689.6112499999999,pathkeys=0x0},jointype=JOIN_INNER,inner_unique=true,outerjoinpath=0x2720f68,innerjoinpath=0x26d0598,joinrestrictinfo=0x2722068},path_hashclauses=0x27223c0,num_batches=1,inner_rows_total=10000}

完成SortPath执行计划的构建

(gdb)1774returnplan;(gdb)1775}(gdb)p*plan$20={plan={type=T_Sort,startup_cost=20070.931487218411,total_cost=20320.931487218411,plan_rows=100000,plan_width=47,parallel_aware=false,parallel_safe=true,plan_node_id=0,targetlist=0x2723208,qual=0x0,lefttree=0x27243d0,righttree=0x0,initPlan=0x0,extParam=0x0,allParam=0x0},numCols=1,sortColIdx=0x27222a0,sortOperators=0x2724468,collations=0x2724488,nullsFirst=0x27244a8}

回到上一层

(gdb)ncreate_plan_recurse(root=0x26c1258,best_path=0x2722c68,flags=0)atcreateplan.c:450450break;

回到create_projection_plan函数

(gdb)n504returnplan;(gdb)505}(gdb)create_projection_plan(root=0x26c1258,best_path=0x2722d00,flags=1)atcreateplan.c:16741674tlist=build_path_tlist(root,&best_path->path);

执行完毕,返回create_plan,结果,最外层的Plan为Sort

(gdb)1708returnplan;(gdb)1709}(gdb)p*plan$22={type=T_Sort,startup_cost=20070.931487218411,total_cost=20320.931487218411,plan_rows=100000,plan_width=47,parallel_aware=false,parallel_safe=true,plan_node_id=0,targetlist=0x2724548,qual=0x0,lefttree=0x27243d0,righttree=0x0,initPlan=0x0,extParam=0x0,allParam=0x0}(gdb)ncreate_plan_recurse(root=0x26c1258,best_path=0x2722d00,flags=1)atcreateplan.c:504504returnplan;(gdb)p*plan$23={type=T_Sort,startup_cost=20070.931487218411,total_cost=20320.931487218411,plan_rows=100000,plan_width=47,parallel_aware=false,parallel_safe=true,plan_node_id=0,targetlist=0x2724548,qual=0x0,lefttree=0x27243d0,righttree=0x0,initPlan=0x0,extParam=0x0,allParam=0x0}(gdb)n505}(gdb)create_plan(root=0x26c1258,best_path=0x2722d00)atcreateplan.c:329329if(!IsA(plan,ModifyTable))

感谢各位的阅读，以上就是“PostgreSQL中create_sort_plan函数实现逻辑是什么”的内容了，经过本文的学习后，相信大家对PostgreSQL中create_sort_plan函数实现逻辑是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！