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

RelOptInfo
查询语句经过查询重写/表达式简化/外连接消除等处理后,查询树Query已完成规范化,查询树中的RangeTblEntry(RTE)数据结构已完成其历史使命,由此可进入逻辑优化处理,在此阶段使用RelOptInfo数据结构.

typedefenumRelOptKind{RELOPT_BASEREL,//基本关系(如基表/子查询等)RELOPT_JOINREL,//连接产生的关系,要注意的是通过连接等方式产生的结果亦可以视为关系RELOPT_OTHER_MEMBER_REL,RELOPT_OTHER_JOINREL,RELOPT_UPPER_REL,//上层的关系RELOPT_OTHER_UPPER_REL,RELOPT_DEADREL}RelOptKind;/**Isthegivenrelationasimplerelationi.eabaseor"other"member*relation?*/#defineIS_SIMPLE_REL(rel)\((rel)->reloptkind==RELOPT_BASEREL||\(rel)->reloptkind==RELOPT_OTHER_MEMBER_REL)/*Isthegivenrelationajoinrelation?*/#defineIS_JOIN_REL(rel)\((rel)->reloptkind==RELOPT_JOINREL||\(rel)->reloptkind==RELOPT_OTHER_JOINREL)/*Isthegivenrelationanupperrelation?*/#defineIS_UPPER_REL(rel)\((rel)->reloptkind==RELOPT_UPPER_REL||\(rel)->reloptkind==RELOPT_OTHER_UPPER_REL)/*Isthegivenrelationan"other"relation?*/#defineIS_OTHER_REL(rel)\((rel)->reloptkind==RELOPT_OTHER_MEMBER_REL||\(rel)->reloptkind==RELOPT_OTHER_JOINREL||\(rel)->reloptkind==RELOPT_OTHER_UPPER_REL)typedefstructRelOptInfo{NodeTagtype;//节点标识RelOptKindreloptkind;//RelOpt类型/*allrelationsincludedinthisRelOptInfo*/Relidsrelids;/*Relids(rtindex)集合setofbaserelids(rangetableindexes)*//*sizeestimatesgeneratedbyplanner*/doublerows;/*结果元组的估算数量estimatednumberofresulttuples*//*per-relationplannercontrolflags*/boolconsider_startup;/*是否考虑启动成本?是,需要保留启动成本低的路径keepcheap-startup-costpaths?*/boolconsider_param_startup;/*是否考虑参数化?的路径ditto,forparameterizedpaths?*/boolconsider_parallel;/*是否考虑并行处理路径considerparallelpaths?*//*defaultresulttargetlistforPathsscanningthisrelation*/structPathTarget*reltarget;/*扫描该Relation时默认的结果listofVars/Exprs,cost,width*//*materializationinformation*/List*pathlist;/*访问路径链表Pathstructures*/List*ppilist;/*路径链表中使用参数化路径进行ParamPathInfosusedinpathlist*/List*partial_pathlist;/*partialPaths*/structPath*cheapest_startup_path;//代价最低的启动路径structPath*cheapest_total_path;//代价最低的整体路径structPath*cheapest_unique_path;//代价最低的获取唯一值的路径List*cheapest_parameterized_paths;//代价最低的参数化?路径链表/*parameterizationinformationneededforbothbaserelsandjoinrels*//*(seealsolateral_varsandlateral_referencers)*/Relidsdirect_lateral_relids;/*使用lateral语法,需依赖的Relidsrelsdirectlylaterallyreferenced*/Relidslateral_relids;/*minimumparameterizationofrel*//*informationaboutabaserel(notsetforjoinrels!)*///reloptkind=RELOPT_BASEREL时使用的数据结构Indexrelid;/*RelationID*/Oidreltablespace;/*表空间containingtablespace*/RTEKindrtekind;/*基表?子查询?还是函数等等?RELATION,SUBQUERY,FUNCTION,etc*/AttrNumbermin_attr;/*最小的属性编号smallestattrnoofrel(often<0)*/AttrNumbermax_attr;/*最大的属性编号largestattrnoofrel*/Relids*attr_needed;/*数组arrayindexed[min_attr..max_attr]*/int32*attr_widths;/*属性宽度arrayindexed[min_attr..max_attr]*/List*lateral_vars;/*关系依赖的Vars/PHVsLATERALVarsandPHVsreferencedbyrel*/Relidslateral_referencers;/*依赖该关系的Relidsrelsthatreferencemelaterally*/List*indexlist;/*该关系的IndexOptInfo链表listofIndexOptInfo*/List*statlist;/*统计信息链表listofStatisticExtInfo*/BlockNumberpages;/*块数sizeestimatesderivedfrompg_class*/doubletuples;/*元组数*/doubleallvisfrac;/*?*/PlannerInfo*subroot;/*如为子查询,存储子查询的rootifsubquery*/List*subplan_params;/*如为子查询,存储子查询的参数ifsubquery*/intrel_parallel_workers;/*并行执行,需要多少个workers?wantednumberofparallelworkers*//*Informationaboutforeigntablesandforeignjoins*///FWD相关信息Oidserverid;/*identifiesserverforthetableorjoin*/Oiduserid;/*identifiesusertocheckaccessas*/booluseridiscurrent;/*joinisonlyvalidforcurrentuser*//*use"structFdwRoutine"toavoidincludingfdwapi.hhere*/structFdwRoutine*fdwroutine;void*fdw_private;/*cachespaceforrememberingifwehaveproventhisrelationunique*///已知的,可保证唯一的Relids链表List*unique_for_rels;/*knownuniquefortheseotherrelid*set(s)*/List*non_unique_for_rels;/*已知的,不唯一的Relids链表knownnotuniquefortheseset(s)*//*usedbyvariousscansandjoins:*/List*baserestrictinfo;/*如为基本关系,存储约束条件RestrictInfostructures(ifbaserel)*/QualCostbaserestrictcost;/*解析约束表达式的成本?costofevaluatingtheabove*/Indexbaserestrict_min_security;/*最低安全等级minsecurity_levelfoundin*baserestrictinfo*/List*joininfo;/*连接语句的约束条件信息RestrictInfostructuresforjoinclauses*involvingthisrel*/boolhas_eclass_joins;/*是否存在等价类连接?Tmeansjoininfoisincomplete*//*usedbypartitionwisejoins:*/boolconsider_partitionwise_join;/*分区?considerpartitionwise*joinpaths?(if*partitionedrel)*/Relidstop_parent_relids;/*Relidsoftopmostparents(if"other"*rel)*//*usedforpartitionedrelations*///分区表使用PartitionSchemepart_scheme;/*分区的schemaPartitioningscheme.*/intnparts;/*分区数numberofpartitions*/structPartitionBoundInfoData*boundinfo;/*分区边界信息Partitionbounds*/List*partition_qual;/*分区约束partitionconstraint*/structRelOptInfo**part_rels;/*分区的RelOptInfo数组ArrayofRelOptInfosofpartitions,*storedinthesameorderofbounds*/List**partexprs;/*非空分区键表达式Non-nullablepartitionkeyexpressions.*/List**nullable_partexprs;/*可为空的分区键表达式Nullablepartitionkeyexpressions.*/List*partitioned_child_rels;/*RTIndexes链表ListofRTindexes.*/}RelOptInfo;

PathCostComparison

typedefenum{COSTS_EQUAL,/*近似相等pathcostsarefuzzilyequal*/COSTS_BETTER1,/*第一个路径成本较低firstpathischeaperthansecond*/COSTS_BETTER2,/*第二个相对较低secondpathischeaperthanfirst*/COSTS_DIFFERENT/*不管是哪个路径,成本上都不占优势neitherpathdominatestheotheroncost*/}PathCostComparison;

ResultPath

/**ResultPathrepresentsuseofaResultplannodetocomputeavariable-free*targetlistwithnounderlyingtables(a"SELECTexpressions"query).*ThequerycouldhaveaWHEREclause,too,representedby"quals".**Notethatqualsisalistofbareclauses,notRestrictInfos.*/typedefstructResultPath//表示无基础表的结果计划节点{Pathpath;//扫描路径List*quals;//where语句表达式,bareclauses,notRestrictInfos}ResultPath;二、源码解读

/**query_planner*Generateapath(thatis,asimplifiedplan)forabasicquery,*whichmayinvolvejoinsbutnotanyfancierfeatures.**为一个基本的查询(可能涉及连接)生成访问路径(也可以视为一个简化的计划).**Sincequery_plannerdoesnothandlethetoplevelprocessing(grouping,*sorting,etc)itcannotselectthebestpathbyitself.Instead,it*returnstheRelOptInfoforthetoplevelofjoining,andthecaller*(grouping_planner)canchooseamongthesurvivingpathsfortherel.**query_planner不会处理顶层的处理过程(如最后的分组/排序等操作),因此,不能选择最优的访问路径*该函数会返回RelOptInfo给最高层的连接,grouping_planner可以在剩下的路径中进行选择**rootdescribesthequerytoplan*tlististhetargetlistthequeryshouldproduce*(thisisNOTnecessarilyroot->parse->targetList!)*qp_callbackisafunctiontocomputequery_pathkeysonceit'ssafetodoso*qp_extraisoptionalextradatatopasstoqp_callback**root是计划信息/tlist是投影列*qp_callback是计算query_pathkeys的函数/qp_extra是传递给qp_callback的函数**Note:thePlannerInfonodealsoincludesaquery_pathkeysfield,which*tellsquery_plannerthesortorderthatisdesiredinthefinaloutput*plan.Thisvalueis*not*availableatcalltime,butiscomputedby*qp_callbackoncewehavecompletedmergingthequery'sequivalenceclasses.*(Wecannotconstructcanonicalpathkeysuntilthat'sdone.)*/RelOptInfo*query_planner(PlannerInfo*root,List*tlist,query_pathkeys_callbackqp_callback,void*qp_extra){Query*parse=root->parse;//查询树List*joinlist;RelOptInfo*final_rel;//结果Indexrti;//RTE的indexdoubletotal_pages;//总pages数/**Ifthequeryhasanemptyjointree,thenit'ssomethingeasylike*"SELECT2+2;"or"INSERT...VALUES()".Fallthroughquickly.*/if(parse->jointree->fromlist==NIL)//简单SQL,无FROM/WHERE语句{/*Weneedadummyjoinreltodescribetheemptysetofbaserels*/final_rel=build_empty_join_rel(root);//创建返回结果/**Ifqueryallowsparallelismingeneral,checkwhetherthequalsare*parallel-restricted.(Weneednotcheckfinal_rel->reltarget*becauseit'semptyatthispoint.Anythingparallel-restrictedin*thequerytlistwillbedealtwithlater.)*/if(root->glob->parallelModeOK)//并行模式?final_rel->consider_parallel=is_parallel_safe(root,parse->jointree->quals);/*TheonlypathforitisatrivialResultpath*/add_path(final_rel,(Path*)create_result_path(root,final_rel,final_rel->reltarget,(List*)parse->jointree->quals));//添加访问路径/*Selectcheapestpath(prettyeasyinthiscase...)*/set_cheapest(final_rel);//选择最优的访问路径/**Westillarerequiredtocallqp_callback,incaseit'ssomething*like"SELECT2+2ORDERBY1".*/root->canon_pathkeys=NIL;(*qp_callback)(root,qp_extra);//回调函数returnfinal_rel;//返回}//其他代码...}

add_path/set_cheapest
后续再行介绍

create_result_path

/**create_result_path*CreatesapathrepresentingaResult-and-nothing-elseplan.**Thisisonlyusedfordegeneratecases,suchasaquerywithanempty*jointree.*/ResultPath*create_result_path(PlannerInfo*root,RelOptInfo*rel,PathTarget*target,List*resconstantqual){ResultPath*pathnode=makeNode(ResultPath);//结果pathnode->path.pathtype=T_Result;//扫描路径类型pathnode->path.parent=rel;//路径的partentbuild_empty_join_relpathnode->path.pathtarget=target;//目标列pathnode->path.param_info=NULL;/*therearenootherrels...*/pathnode->path.parallel_aware=false;pathnode->path.parallel_safe=rel->consider_parallel;pathnode->path.parallel_workers=0;//并行workers数目,设置为0pathnode->path.pathkeys=NIL;//pathnode->quals=resconstantqual;//表达式/*Hardlyworthdefiningacost_result()function...justdoit*/pathnode->path.rows=1;//行数为1pathnode->path.startup_cost=target->cost.startup;pathnode->path.total_cost=target->cost.startup+cpu_tuple_cost+target->cost.per_tuple;/**Addcostofqual,ifany---butweignoreitsselectivity,sinceour*rowcountestimateshouldbe1nomatterwhatthequalis.*/if(resconstantqual){QualCostqual_cost;cost_qual_eval(&qual_cost,resconstantqual,root);/*resconstantqualisevaluatedonceatstartup*/pathnode->path.startup_cost+=qual_cost.startup+qual_cost.per_tuple;pathnode->path.total_cost+=qual_cost.startup+qual_cost.per_tuple;}returnpathnode;}

build_empty_join_rel

/**build_empty_join_rel*Buildadummyjoinrelationdescribinganemptysetofbaserels.**ThisisusedforquerieswithemptyFROMclauses,suchas"SELECT2+2"or*"INSERTINTOfooVALUES(...)".Wedon'ttryveryhardtomaketheempty*joinrelcompletelyvalid,sincenorealplanningwillbedonewithit---*wejustneedittocarryasimpleResultpathoutofquery_planner().*/RelOptInfo*build_empty_join_rel(PlannerInfo*root){RelOptInfo*joinrel;/*Thedummyjoinrelationshouldbetheonlyone...*/Assert(root->join_rel_list==NIL);joinrel=makeNode(RelOptInfo);joinrel->reloptkind=RELOPT_JOINREL;joinrel->relids=NULL;/*emptyset*/joinrel->rows=1;/*weproduceonerowforsuchcases*/joinrel->rtekind=RTE_JOIN;joinrel->reltarget=create_empty_pathtarget();root->join_rel_list=lappend(root->join_rel_list,joinrel);returnjoinrel;}三、跟踪分析

(gdb)bquery_plannerBreakpoint1at0x76942c:fileplanmain.c,line57.(gdb)cContinuing.Breakpoint1,query_planner(root=0x275c878,tlist=0x277fd78,qp_callback=0x76e97d<standard_qp_callback>,qp_extra=0x7ffdd435d490)atplanmain.c:5757Query*parse=root->parse;(gdb)n67if(parse->jointree->fromlist==NIL)(gdb)70final_rel=build_empty_join_rel(root);(gdb)78if(root->glob->parallelModeOK)#创建的空RELOPT_JOINREL(gdb)p*final_rel$4={type=T_RelOptInfo,reloptkind=RELOPT_JOINREL,relids=0x0,rows=1,consider_startup=false,consider_param_startup=false,consider_parallel=false,reltarget=0x277fda8,pathlist=0x0,ppilist=0x0,partial_pathlist=0x0,cheapest_startup_path=0x0,cheapest_total_path=0x0,cheapest_unique_path=0x0,cheapest_parameterized_paths=0x0,direct_lateral_relids=0x0,lateral_relids=0x0,relid=0,reltablespace=0,rtekind=RTE_JOIN,min_attr=0,max_attr=0,attr_needed=0x0,attr_widths=0x0,lateral_vars=0x0,lateral_referencers=0x0,indexlist=0x0,statlist=0x0,pages=0,tuples=0,allvisfrac=0,subroot=0x0,subplan_params=0x0,rel_parallel_workers=0,serverid=0,userid=0,useridiscurrent=false,fdwroutine=0x0,fdw_private=0x0,unique_for_rels=0x0,non_unique_for_rels=0x0,baserestrictinfo=0x0,baserestrictcost={startup=0,per_tuple=0},baserestrict_min_security=0,joininfo=0x0,has_eclass_joins=false,top_parent_relids=0x0,part_scheme=0x0,nparts=0,boundinfo=0x0,partition_qual=0x0,part_rels=0x0,partexprs=0x0,nullable_partexprs=0x0,partitioned_child_rels=0x0}...(gdb)stepadd_path(parent_rel=0x275cc88,new_path=0x275c498)atpathnode.c:424424boolaccept_new=true;/*unlesswefindasuperioroldpath*/#创建的path(ResultPath)(gdb)p*new_path$6={type=T_ResultPath,pathtype=T_Result,parent=0x275cc88,pathtarget=0x277fda8,param_info=0x0,parallel_aware=false,parallel_safe=true,parallel_workers=0,rows=1,startup_cost=0,total_cost=0.01,pathkeys=0x0}(gdb)finishRuntillexitfrom#0add_path(parent_rel=0x275cc88,new_path=0x275c498)atpathnode.c:425query_planner(root=0x275c878,tlist=0x277fd78,qp_callback=0x76e97d<standard_qp_callback>,qp_extra=0x7ffdd435d490)atplanmain.c:8989set_cheapest(final_rel);...98returnfinal_rel;(gdb)267}#返回值(gdb)p*final_rel$8={type=T_RelOptInfo,reloptkind=RELOPT_JOINREL,relids=0x0,rows=1,consider_startup=false,consider_param_startup=false,consider_parallel=true,reltarget=0x277fda8,pathlist=0x277fe68,ppilist=0x0,partial_pathlist=0x0,cheapest_startup_path=0x275c498,cheapest_total_path=0x275c498,cheapest_unique_path=0x0,cheapest_parameterized_paths=0x277feb8,direct_lateral_relids=0x0,lateral_relids=0x0,relid=0,reltablespace=0,rtekind=RTE_JOIN,min_attr=0,max_attr=0,attr_needed=0x0,attr_widths=0x0,lateral_vars=0x0,lateral_referencers=0x0,indexlist=0x0,statlist=0x0,pages=0,tuples=0,allvisfrac=0,subroot=0x0,subplan_params=0x0,rel_parallel_workers=0,serverid=0,userid=0,useridiscurrent=false,fdwroutine=0x0,fdw_private=0x0,unique_for_rels=0x0,non_unique_for_rels=0x0,baserestrictinfo=0x0,baserestrictcost={startup=0,per_tuple=0},baserestrict_min_security=0,joininfo=0x0,has_eclass_joins=false,top_parent_relids=0x0,part_scheme=0x0,nparts=0,boundinfo=0x0,partition_qual=0x0,part_rels=0x0,partexprs=0x0,nullable_partexprs=0x0,partitioned_child_rels=0x0}(gdb)p*final_rel->cheapest_total_path$9={type=T_ResultPath,pathtype=T_Result,parent=0x275cc88,pathtarget=0x277fda8,param_info=0x0,parallel_aware=false,parallel_safe=true,parallel_workers=0,rows=1,startup_cost=0,total_cost=0.01,pathkeys=0x0}(gdb)#DONE!

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