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

在query_planner中,对root(PlannerInfo)结构进行初始化和处理,为后续的计划作准备.
PlannerInfo

/*----------*PlannerInfo*Per-queryinformationforplanning/optimization**Thisstructisconventionallycalled"root"inalltheplannerroutines.*Itholdslinkstoalloftheplanner'sworkingstate,inadditiontothe*originalQuery.Notethatatpresenttheplannerextensivelymodifies*thepassed-inQuerydatastructure;somedaythatshouldstop.*----------*/structAppendRelInfo;typedefstructPlannerInfo{NodeTagtype;//Node标识Query*parse;/*查询树,theQuerybeingplanned*/PlannerGlobal*glob;/*当前的planner全局信息,globalinfoforcurrentplannerrun*/Indexquery_level;/*查询层次,1标识最高层,1attheoutermostQuery*/structPlannerInfo*parent_root;/*如为子计划,则这里存储父计划器指针,NULL标识最高层,NULLatoutermostQuery*//**plan_paramscontainstheexpressionsthatthisquerylevelneedsto*makeavailabletoalowerquerylevelthatiscurrentlybeingplanned.*outer_paramscontainstheparamIdsofPARAM_EXECParamsthatouter*querylevelswillmakeavailabletothisquerylevel.*/List*plan_params;/*listofPlannerParamItems,seebelow*/Bitmapset*outer_params;/**simple_rel_arrayholdspointersto"baserels"and"otherrels"(see*commentsforRelOptInfoformoreinfo).Itisindexedbyrangetable*index(soentry0isalwayswasted).EntriescanbeNULLwhenanRTE*doesnotcorrespondtoabaserelation,suchasajoinRTEoran*unreferencedviewRTE;oriftheRelOptInfohasn'tbeenmadeyet.*//*RelOptInfo数组,存储"baserels",比如基表/子查询等.该数组与RTE的顺序一一对应,而且是从1开始,因此[0]无用*/structRelOptInfo**simple_rel_array;/*All1-relRelOptInfos*/intsimple_rel_array_size;/*数组大小,allocatedsizeofarray*//**simple_rte_arrayisthesamelengthassimple_rel_arrayandholds*pointerstotheassociatedrangetableentries.Thisletsusavoid*rt_fetch(),whichcanbeabitslowoncelargeinheritancesetshave*beenexpanded.*/RangeTblEntry**simple_rte_array;/*RTE数组,rangetableasanarray*//**append_rel_arrayisthesamelengthastheabovearrays,andholds*pointerstothecorrespondingAppendRelInfoentryindexedby*child_relid,orNULLifnone.Thearrayitselfisnotallocatedif*append_rel_listisempty.*/structAppendRelInfo**append_rel_array;//先前已介绍,在处理集合操作如UNIONALL时使用/**all_baserelsisaRelidssetofallbaserelids(butnot"other"*relids)inthequery;thatis,theRelidsidentifierofthefinaljoin*weneedtoform.Thisiscomputedinmake_one_rel,justbeforewe*startmakingPaths.*/Relidsall_baserels;//"baserels"/**nullable_baserelsisaRelidssetofbaserelidsthatarenullableby*someouterjoininthejointree;thesearerelsthatarepotentially*nullablebelowtheWHEREclause,SELECTtargetlist,etc.Thisis*computedindeconstruct_jointree.*/Relidsnullable_baserels;//Nullable-side端的"baserels"/**join_rel_listisalistofalljoin-relationRelOptInfoswehave*consideredinthisplanningrun.Forsmallproblemswejustscanthe*listtodolookups,butwhentherearemanyjoinrelationswebuilda*hashtableforfasterlookups.Thehashtableispresentandvalid*whenjoin_rel_hashisnotNULL.Notethatwestillmaintainthelist*evenwhenusingthehashtableforlookups;thissimplifieslifefor*GEQO.*/List*join_rel_list;/*参与连接的Relation的RelOptInfo链表,listofjoin-relationRelOptInfos*/structHTAB*join_rel_hash;/*可加快链表访问的hash表,optionalhashtableforjoinrelations*//**Whendoingadynamic-programming-stylejoinsearch,join_rel_level[k]*isalistofalljoin-relationRelOptInfosoflevelk,and*join_cur_levelisthecurrentlevel.Newjoin-relationRelOptInfosare*automaticallyaddedtothejoin_rel_level[join_cur_level]list.*join_rel_levelisNULLifnotinuse.*/List**join_rel_level;/*RelOptInfo指针链表数组,k层的join存储在[k]中,listsofjoin-relationRelOptInfos*/intjoin_cur_level;/*当前的join层次,indexoflistbeingextended*/List*init_plans;/*查询的初始化计划链表,initSubPlansforquery*/List*cte_plan_ids;/*CTE子计划ID链表,per-CTE-itemlistofsubplanIDs*/List*multiexpr_params;/*ListofListsofParamsforMULTIEXPR*subqueryoutputs*/List*eq_classes;/*活动的等价类链表,listofactiveEquivalenceClasses*/List*canon_pathkeys;/*规范化PathKey链表,listof"canonical"PathKeys*/List*left_join_clauses;/*外连接约束条件链表(左),listofRestrictInfosformergejoinable*outerjoinclausesw/nonnullablevaron*left*/List*right_join_clauses;/*外连接约束条件链表(右),listofRestrictInfosformergejoinable*outerjoinclausesw/nonnullablevaron*right*/List*full_join_clauses;/*全连接约束条件链表,listofRestrictInfosformergejoinable*fulljoinclauses*/List*join_info_list;/*特殊连接信息链表,listofSpecialJoinInfos*/List*append_rel_list;/*AppendRelInfo链表,listofAppendRelInfos*/List*rowMarks;/*listofPlanRowMarks*/List*placeholder_list;/*PHI链表,listofPlaceHolderInfos*/List*fkey_list;/*外键信息链表,listofForeignKeyOptInfos*/List*query_pathkeys;/*uery_planner()要求的PathKeys,desiredpathkeysforquery_planner()*/List*group_pathkeys;/*groupClausepathkeys,ifany*/List*window_pathkeys;/*pathkeysofbottomwindow,ifany*/List*distinct_pathkeys;/*distinctClausepathkeys,ifany*/List*sort_pathkeys;/*sortClausepathkeys,ifany*/List*part_schemes;/*已规范化的分区Schema,Canonicalisedpartitionschemesusedinthe*query.*/List*initial_rels;/*尝试连接的RelOptInfo链表,RelOptInfoswearenowtryingtojoin*//*Usefetch_upper_rel()togetanyparticularupperrel*/List*upper_rels[UPPERREL_FINAL+1];/*上层的RelOptInfo链表,upper-relRelOptInfos*//*Resulttlistschosenbygrouping_plannerforupper-stageprocessing*/structPathTarget*upper_targets[UPPERREL_FINAL+1];///**grouping_plannerpassesbackitsfinalprocessedtargetlisthere,for*useinrelabelingthetopmosttlistofthefinishedPlan.*/List*processed_tlist;//最后需处理的投影列/*Fieldsfilledduringcreate_plan()foruseinsetrefs.c*/AttrNumber*grouping_map;/*forGroupingFuncfixup*/List*minmax_aggs;/*ListofMinMaxAggInfos*/MemoryContextplanner_cxt;/*内存上下文,contextholdingPlannerInfo*/doubletotal_table_pages;/*所有的pages,#ofpagesinalltablesofquery*/doubletuple_fraction;/*query_planner输入参数:元组处理比例,tuple_fractionpassedtoquery_planner*/doublelimit_tuples;/*query_planner输入参数:limit_tuplespassedtoquery_planner*/Indexqual_security_level;/*表达式的最新安全等级,minimumsecurity_levelforquals*//*Note:qual_security_leveliszeroiftherearenosecurityQuals*/InheritanceKindinhTargetKind;/*indicatesifthetargetrelationisan*inheritancechildorpartitionora*partitionedtable*/boolhasJoinRTEs;/*存在RTE_JOIN的RTE,trueifanyRTEsareRTE_JOINkind*/boolhasLateralRTEs;/*存在标记为LATERAL的RTE,trueifanyRTEsaremarkedLATERAL*/boolhasDeletedRTEs;/*存在已在jointree删除的RTE,trueifanyRTEwasdeletedfromjointree*/boolhasHavingQual;/*存在Having子句,trueifhavingQualwasnon-null*/boolhasPseudoConstantQuals;/*trueifanyRestrictInfohas*pseudoconstant=true*/boolhasRecursion;/*递归语句,trueifplanningarecursiveWITHitem*//*ThesefieldsareusedonlywhenhasRecursionistrue:*/intwt_param_id;/*PARAM_EXECIDfortheworktable*/structPath*non_recursive_path;/*apathfornon-recursiveterm*//*Thesefieldsareworkspaceforcreateplan.c*/RelidscurOuterRels;/*outerrelsabovecurrentnode*/List*curOuterParams;/*not-yet-assignedNestLoopParams*//*optionalprivatedataforjoin_search_hook,e.g.,GEQO*/void*join_search_private;/*Doesthisquerymodifyanypartitionkeycolumns?*/boolpartColsUpdated;}PlannerInfo;二、源码解读

本节介绍query_planner的主流程以及setup_simple_rel_arrays和setup_append_rel_array两个子函数的实现逻辑.
query_planner

/**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;//返回}/**Initplannerliststoempty.**NOTE:append_rel_listwassetupbysubquery_planner,sodonottouch*here.*/root->join_rel_list=NIL;//初始化PlannerInforoot->join_rel_hash=NULL;root->join_rel_level=NULL;root->join_cur_level=0;root->canon_pathkeys=NIL;root->left_join_clauses=NIL;root->right_join_clauses=NIL;root->full_join_clauses=NIL;root->join_info_list=NIL;root->placeholder_list=NIL;root->fkey_list=NIL;root->initial_rels=NIL;/**Makeaflattenedversionoftherangetableforfasteraccess(thisis*OKbecausetherangetablewon'tchangeanymore),andsetupanempty*arrayforindexingbaserelations.*/setup_simple_rel_arrays(root);//初始化PlannerInfo->simple_rel/rte_array&size/**Populateappend_rel_arraywitheachAppendRelInfotoallowdirect*lookupsbychildrelid.*/setup_append_rel_array(root);//初始化PlannerInfo->append_rel_array(通过append_rel_list)/**ConstructRelOptInfonodesforallbaserelationsinquery,and*indirectlyforallappendrelmemberrelations("otherrels").This*willgiveusaRelOptInfoforevery"simple"(non-join)relinvolvedin*thequery.**Note:thereasonwefindtherelsbysearchingthejointreeand*appendrellist,ratherthanjustscanningtherangetable,isthatthe*rangetablemaycontainRTEsforrelsnotactivelypartofthequery,*forexampleviews.Wedon'twanttomakeRelOptInfosforthem.*/add_base_rels_to_query(root,(Node*)parse->jointree);//构建RelOptInfo节点/**Examinethetargetlistandjointree,addingentriestobaserel*targetlistsforallreferencedVars,andgeneratingPlaceHolderInfo*entriesforallreferencedPlaceHolderVars.Restrictandjoinclauses*areaddedtoappropriatelistsbelongingtothementionedrelations.We*alsobuildEquivalenceClassesforprovablyequivalentexpressions.The*SpecialJoinInfolistisalsobuilttoholdinformationaboutjoinorder*restrictions.Finally,weformatargetjoinlistformake_one_rel()to*workfrom.*/build_base_rel_tlists(root,tlist);//构建"baserels"的投影列find_placeholders_in_jointree(root);//处理jointree中的PHIfind_lateral_references(root);//处理jointree中Lateral依赖joinlist=deconstruct_jointree(root);//重构jointree/**Reconsideranypostponedouter-joinqualsnowthatwehavebuiltup*equivalenceclasses.(Thiscouldresultinfurtheradditionsor*mergingsofclasses.)*/reconsider_outer_join_clauses(root);//已创建等价类,那么需要重新考虑被推后处理的外连接表达式/**Ifweformedanyequivalenceclasses,generateadditionalrestriction*clausesasappropriate.(Impliedjoinclausesareformedon-the-fly*later.)*/generate_base_implied_equalities(root);//等价类构建后,生成因此外加的约束语句/**Wehavecompletedmergingequivalencesets,soit'snowpossibleto*generatepathkeysincanonicalform;socomputequery_pathkeysand*otherpathkeysfieldsinPlannerInfo.*/(*qp_callback)(root,qp_extra);//调用回调函数/**Examineany"placeholder"expressionsgeneratedduringsubquerypullup.*MakesurethattheVarstheyneedaremarkedasneededattherelevant*joinlevel.Thismustbedonebeforejoinremovalbecauseitmight*causeVarsorplaceholderstobeneededaboveajoinwhentheyweren't*somarkedbefore.*/fix_placeholder_input_needed_levels(root);//检查在子查询上拉时生成的PH表达式,确保Vars是OK的/**Removeanyuselessouterjoins.Ideallythiswouldbedoneduring*jointreepreprocessing,butthenecessaryinformationisn'tavailable*untilwe'vebuiltbasereldatastructuresandclassifiedqualclauses.*/joinlist=remove_useless_joins(root,joinlist);//清除无用的外连接/**Also,reduceanysemijoinswithuniqueinnerrelstoplaininnerjoins.*Likewise,thiscan'tbedoneuntilnowforlackofneededinfo.*/reduce_unique_semijoins(root);//消除半连接/**Nowdistribute"placeholders"tobaserelsasneeded.Thishastobe*doneafterjoinremovalbecauseremovalcouldchangewhethera*placeholderisevaluableatabaserel.*/add_placeholders_to_base_rels(root);//在"baserels"中添加PH/**Constructthelateralreferencesetsnowthatwehavefinalized*PlaceHolderVarevallevels.*/create_lateral_join_info(root);//创建Lateral连接信息/**Matchforeignkeystoequivalenceclassesandjoinquals.Thismustbe*doneafterfinalizingequivalenceclasses,andit'susefultowaittill*afterjoinremovalsothatwecanskipprocessingforeignkeys*involvingremovedrelations.*/match_foreign_keys_to_quals(root);//匹配外键信息/**LookforjoinORclausesthatwecanextractsingle-relation*restrictionORclausesfrom.*/extract_restriction_or_clauses(root);//在OR语句中抽取约束条件/**Weshouldnowhavesizeestimatesforeveryactualtableinvolvedin*thequery,andwealsoknowwhichifanyhavebeendeletedfromthe*querybyjoinremoval;sowecancomputetotal_table_pages.**Notethatappendrelsarenotdouble-countedhere,eventhoughwedon't*bothertodistinguishRelOptInfosforappendrelparents,becausethe*parentswillstillhavesizezero.**XXXifatableisself-joined,wewillcountitonceperappearance,*whichperhapsisthewrongthing...butthat'snotcompletelyclear,*anddetectingself-joinshereisdifficult,soignoreitfornow.*/total_pages=0;for(rti=1;rti<root->simple_rel_array_size;rti++)//计算总pages{RelOptInfo*brel=root->simple_rel_array[rti];if(brel==NULL)continue;Assert(brel->relid==rti);/*sanitycheckonarray*/if(IS_SIMPLE_REL(brel))total_pages+=(double)brel->pages;}root->total_table_pages=total_pages;//赋值/**Readytodotheprimaryplanning.*/final_rel=make_one_rel(root,joinlist);//执行主要的计划过程/*Checkthatwegotatleastoneusablepath*/if(!final_rel||!final_rel->cheapest_total_path||final_rel->cheapest_total_path->param_info!=NULL)elog(ERROR,"failedtoconstructthejoinrelation");//检查returnfinal_rel;//返回结果}

setup_simple_rel_arrays
初始化setup_simple_rel_arrays(注意:[0]无用)和setup_simple_rel_arrays

/**setup_simple_rel_arrays*Preparethearraysweuseforquicklyaccessingbaserelations.*/voidsetup_simple_rel_arrays(PlannerInfo*root){Indexrti;ListCell*lc;/*ArraysareaccessedusingRTindexes(1..N)*/root->simple_rel_array_size=list_length(root->parse->rtable)+1;/*simple_rel_arrayisinitializedtoallNULLs*/root->simple_rel_array=(RelOptInfo**)palloc0(root->simple_rel_array_size*sizeof(RelOptInfo*));/*simple_rte_arrayisanarrayequivalentofthertablelist*/root->simple_rte_array=(RangeTblEntry**)palloc0(root->simple_rel_array_size*sizeof(RangeTblEntry*));rti=1;foreach(lc,root->parse->rtable){RangeTblEntry*rte=(RangeTblEntry*)lfirst(lc);root->simple_rte_array[rti++]=rte;}}

setup_append_rel_array
源码比较简单,读取append_rel_list中的信息初始化append_rel_array

/**setup_append_rel_array*Populatetheappend_rel_arraytoallowdirectlookupsof*AppendRelInfosbychildrelid.**ThearrayremainsunallocatediftherearenoAppendRelInfos.*/voidsetup_append_rel_array(PlannerInfo*root){ListCell*lc;intsize=list_length(root->parse->rtable)+1;if(root->append_rel_list==NIL){root->append_rel_array=NULL;return;}root->append_rel_array=(AppendRelInfo**)palloc0(size*sizeof(AppendRelInfo*));foreach(lc,root->append_rel_list){AppendRelInfo*appinfo=lfirst_node(AppendRelInfo,lc);intchild_relid=appinfo->child_relid;/*Sanitycheck*/Assert(child_relid<size);if(root->append_rel_array[child_relid])elog(ERROR,"childrelationalreadyexists");root->append_rel_array[child_relid]=appinfo;}}

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