本篇内容主要讲解“PostgreSQL中create_plan的实现逻辑是什么”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“PostgreSQL中create_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函数,递归遍历访问路径,相应的创建计划(Plan)节点。

/**create_plan*Createstheaccessplanforaquerybyrecursivelyprocessingthe*desiredtreeofpathnodes,startingatthenode'best_path'.For*everypathnodefound,wecreateacorrespondingplannodecontaining*appropriateid,targetlist,andqualificationinformation.*从节点'best_path'开始,递归处理路径节点树,为查询语句创建执行计划。*对于找到的每个访问路径节点,创建一个相应的计划节点,其中包含合适的id、投影列和限定信息。**Thetlistsandqualsintheplantreearestillinplannerformat,*ie,Varsstillcorrespondtotheparser'snumbering.Thiswillbe*fixedlaterbysetrefs.c.*计划中的投影列和约束条件仍然以优化器的格式存储.*比如Vars对应着解析树的编号等,这些处理都在setrefs.c中完成**best_pathisthebestaccesspath*best_path是最优的访问路径**ReturnsaPlantree.*返回Plan树.*/Plan*create_plan(PlannerInfo*root,Path*best_path){Plan*plan;/*plan_paramsshouldnotbeinuseincurrentquerylevel*///plan_params在当前查询层次上不应使用(值为NULL)Assert(root->plan_params==NIL);/*Initializethismodule'sprivateworkspaceinPlannerInfo*///初始化该模块中优化器信息的私有工作空间root->curOuterRels=NULL;root->curOuterParams=NIL;/*Recursivelyprocessthepathtree,demandingthecorrecttlistresult*///递归处理计划树(tlist参数设置为CP_EXACT_TLIST)plan=create_plan_recurse(root,best_path,CP_EXACT_TLIST);/**Makesurethetopmostplannode'stargetlistexposestheoriginal*columnnamesandotherdecorativeinfo.Targetlistsgeneratedwithin*theplannerdon'tbotherwiththatstuff,butwemusthaveitonthe*top-leveltlistseenatexecutiontime.However,ModifyTableplan*nodesdon'thaveatlistmatchingthequerytreetargetlist.*确保最顶层计划节点的投影列targetlist可以获知原始列名和其他调整过的信息。*在计划器中生成的投影列targetlist不需要处理这些信息,但是必须在执行时看到最高层的投影列tlist。*注意:ModifyTable计划节点没有一个匹配查询树targetlist的tlist。*/if(!IsA(plan,ModifyTable))apply_tlist_labeling(plan->targetlist,root->processed_tlist);//非ModifyTable/**AttachanyinitPlanscreatedinthisqueryleveltothetopmostplan*node.(Inprincipletheinitplanscouldgoinanyplannodeator*abovewherethey'rereferenced,butthereseemsnoreasontoputthem*anylowerthanthetopmostnodeforthequerylevel.Also,see*commentsforSS_finalize_planbeforeyoutrytochangethis.)*将此查询级别中创建的任何initplan附加到最高层的计划节点中。*(原则上,initplans可以在引用它们的任何计划节点或以上的节点中访问,*但似乎没有理由将它们放在查询级别的最高层节点以下。*另外,如需尝试更改SS_finalize_plan,请参阅注释。)*/SS_attach_initplans(root,plan);/*CheckwesuccessfullyassignedallNestLoopParamstoplannodes*///检查已经为计划节点参数NestLoopParams赋值if(root->curOuterParams!=NIL)elog(ERROR,"failedtoassignallNestLoopParamstoplannodes");/**Resetplan_paramstoensureparamIDsusedfornestloopparamsarenot*re-usedlater*重置plan_params参数,以确保用于nestloop参数的参数IDs不会在后续被重复使用*/root->plan_params=NIL;returnplan;}//------------------------------------------------------------------------create_plan_recurse/**create_plan_recurse*Recursivegutsofcreate_plan().*create_plan()函数中的递归实现过程.*/staticPlan*create_plan_recurse(PlannerInfo*root,Path*best_path,intflags){Plan*plan;/*Guardagainststackoverflowduetooverlycomplexplans*///确保堆栈不会溢出check_stack_depth();switch(best_path->pathtype)//根据路径类型,执行相应的处理{caseT_SeqScan://顺序扫描caseT_SampleScan://采样扫描caseT_IndexScan://索引扫描caseT_IndexOnlyScan://索引快速扫描caseT_BitmapHeapScan://位图堆扫描caseT_TidScan://TID扫描caseT_SubqueryScan://子查询扫描caseT_FunctionScan://函数扫描caseT_TableFuncScan://表函数扫描caseT_ValuesScan://Values扫描caseT_CteScan://CTE扫描caseT_WorkTableScan://WorkTable扫描caseT_NamedTuplestoreScan://NamedTuplestore扫描caseT_ForeignScan://外表扫描caseT_CustomScan://自定义扫描plan=create_scan_plan(root,best_path,flags);//扫描计划break;caseT_HashJoin://Hash连接caseT_MergeJoin://合并连接caseT_NestLoop://内嵌循环连接plan=create_join_plan(root,(JoinPath*)best_path);//连接结合break;caseT_Append://追加(集合)plan=create_append_plan(root,(AppendPath*)best_path);//追加(集合并)计划break;caseT_MergeAppend://合并plan=create_merge_append_plan(root,(MergeAppendPath*)best_path);break;caseT_Result://投影操作if(IsA(best_path,ProjectionPath)){plan=create_projection_plan(root,(ProjectionPath*)best_path,flags);}elseif(IsA(best_path,MinMaxAggPath)){plan=(Plan*)create_minmaxagg_plan(root,(MinMaxAggPath*)best_path);}else{Assert(IsA(best_path,ResultPath));plan=(Plan*)create_result_plan(root,(ResultPath*)best_path);}break;caseT_ProjectSet://投影集合操作plan=(Plan*)create_project_set_plan(root,(ProjectSetPath*)best_path);break;caseT_Material://物化plan=(Plan*)create_material_plan(root,(MaterialPath*)best_path,flags);break;caseT_Unique://唯一处理if(IsA(best_path,UpperUniquePath)){plan=(Plan*)create_upper_unique_plan(root,(UpperUniquePath*)best_path,flags);}else{Assert(IsA(best_path,UniquePath));plan=create_unique_plan(root,(UniquePath*)best_path,flags);}break;caseT_Gather://汇总收集plan=(Plan*)create_gather_plan(root,(GatherPath*)best_path);break;caseT_Sort://排序plan=(Plan*)create_sort_plan(root,(SortPath*)best_path,flags);break;caseT_Group://分组plan=(Plan*)create_group_plan(root,(GroupPath*)best_path);break;caseT_Agg://聚集计算if(IsA(best_path,GroupingSetsPath))plan=create_groupingsets_plan(root,(GroupingSetsPath*)best_path);else{Assert(IsA(best_path,AggPath));plan=(Plan*)create_agg_plan(root,(AggPath*)best_path);}break;caseT_WindowAgg://窗口函数plan=(Plan*)create_windowagg_plan(root,(WindowAggPath*)best_path);break;caseT_SetOp://集合操作plan=(Plan*)create_setop_plan(root,(SetOpPath*)best_path,flags);break;caseT_RecursiveUnion://递归UNIONplan=(Plan*)create_recursiveunion_plan(root,(RecursiveUnionPath*)best_path);break;caseT_LockRows://锁定(forupdate)plan=(Plan*)create_lockrows_plan(root,(LockRowsPath*)best_path,flags);break;caseT_ModifyTable://更新plan=(Plan*)create_modifytable_plan(root,(ModifyTablePath*)best_path);break;caseT_Limit://限制操作plan=(Plan*)create_limit_plan(root,(LimitPath*)best_path,flags);break;caseT_GatherMerge://收集合并plan=(Plan*)create_gather_merge_plan(root,(GatherMergePath*)best_path);break;default://其他非法类型elog(ERROR,"unrecognizednodetype:%d",(int)best_path->pathtype);plan=NULL;/*keepcompilerquiet*/break;}returnplan;}//------------------------------------------------------------------------apply_tlist_labeling/**apply_tlist_labeling*ApplytheTargetEntrylabelingattributesofsrc_tlisttodest_tlist*将src_tlist的TargetEntry标记属性应用到dest_tlist**Thisisusefulforreattachingcolumnnamesetctoaplan'sfinaloutput*targetlist.*/voidapply_tlist_labeling(List*dest_tlist,List*src_tlist){ListCell*ld,*ls;Assert(list_length(dest_tlist)==list_length(src_tlist));forboth(ld,dest_tlist,ls,src_tlist){TargetEntry*dest_tle=(TargetEntry*)lfirst(ld);TargetEntry*src_tle=(TargetEntry*)lfirst(ls);Assert(dest_tle->resno==src_tle->resno);dest_tle->resname=src_tle->resname;dest_tle->ressortgroupref=src_tle->ressortgroupref;dest_tle->resorigtbl=src_tle->resorigtbl;dest_tle->resorigcol=src_tle->resorigcol;dest_tle->resjunk=src_tle->resjunk;}}//------------------------------------------------------------------------apply_tlist_labeling/**SS_attach_initplans-attachinitplanstotopmostplannode*将initplans附加到最顶层的计划节点**Attachanyinitplanscreatedinthecurrentqueryleveltothespecified*plannode,whichshouldnormallybethetopmostnodeforthequerylevel.*(InprincipletheinitPlanscouldgoinanynodeatorabovewherethey're*referenced;butthereseemsnoreasontoputthemanylowerthanthe*topmostnode,sowedon'tbothertotrackexactlywheretheycamefrom.)*Wedonottouchtheplannode'scost;theinitplansshouldhavebeen*accountedforinpathcosting.*/voidSS_attach_initplans(PlannerInfo*root,Plan*plan){plan->initPlan=root->init_plans;}三、跟踪分析

测试脚本如下

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,设置断点,进入

(gdb)infobreakNumTypeDispEnbAddressWhat2breakpointkeepy0x00000000007b76c1increate_planatcreateplan.c:313(gdb)cContinuing.Breakpoint2,create_plan(root=0x26c1258,best_path=0x2722d00)atcreateplan.c:313313Assert(root->plan_params==NIL);

进入create_plan_recurse函数

313Assert(root->plan_params==NIL);(gdb)n316root->curOuterRels=NULL;(gdb)317root->curOuterParams=NIL;(gdb)320plan=create_plan_recurse(root,best_path,CP_EXACT_TLIST);(gdb)stepcreate_plan_recurse(root=0x26c1258,best_path=0x2722d00,flags=1)atcreateplan.c:364364check_stack_depth();

根据访问路径类型(T_ProjectionPath)选择处理分支

(gdb)p*best_path$1={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}

调用create_projection_plan函数

(gdb)n400if(IsA(best_path,ProjectionPath))(gdb)402plan=create_projection_plan(root,

创建相应的Plan(T_Sort,存在左右子树),下一节将详细解释create_projection_plan函数

(gdb)504returnplan;(gdb)p*plan$1={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)create_plan(root=0x270f9c8,best_path=0x2722d00)atcreateplan.c:329329if(!IsA(plan,ModifyTable))(gdb)330apply_tlist_labeling(plan->targetlist,root->processed_tlist);(gdb)339SS_attach_initplans(root,plan);(gdb)342if(root->curOuterParams!=NIL)(gdb)349root->plan_params=NIL;(gdb)351returnplan;

到此,相信大家对“PostgreSQL中create_plan的实现逻辑是什么”有了更深的了解,不妨来实际操作一番吧!这里是亿速云网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!