这篇文章给大家分享的是有关PostgreSQL如何为append relation构建访问路径的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

AppendRelInfo
当我们将可继承表(分区表)或UNION-ALL子查询展开为“追加关系”(本质上是子RTE的链表)时，为每个子RTE构建一个AppendRelInfo。

/**Append-relationinfo.*Append-relation信息.**WhenweexpandaninheritabletableoraUNION-ALLsubselectintoan*"appendrelation"(essentially,alistofchildRTEs),webuildan*AppendRelInfoforeachchildRTE.ThelistofAppendRelInfosindicates*whichchildRTEsmustbeincludedwhenexpandingtheparent,andeachnode*carriesinformationneededtotranslateVarsreferencingtheparentinto*Varsreferencingthatchild.*当我们将可继承表(分区表)或UNION-ALL子查询展开为“追加关系”(本质上是子RTE的链表)时，*为每个子RTE构建一个AppendRelInfo。*AppendRelInfos链表指示在展开父节点时必须包含哪些子rte，*每个节点具有将引用父节点的Vars转换为引用该子节点的Vars所需的所有信息。**ThesestructsarekeptinthePlannerInfonode'sappend_rel_list.*Notethatwejustthrowallthestructsintoonelist,andscanthe*wholelistwhendesiringtoexpandanyoneparent.Wecouldhaveused*amorecomplexdatastructure(eg,onelistperparent),butthiswould*behardertoupdateduringoperationssuchaspullingupsubqueries,*andnotreallyanyeasiertoscan.Consideringthattypicalqueries*willnothavemanydifferentappendparents,itdoesn'tseemworthwhile*tocomplicatethings.*这些结构体保存在PlannerInfo节点的append_rel_list中。*注意，只是将所有的结构体放入一个链表中，并在希望展开任何父类时扫描整个链表。*本可以使用更复杂的数据结构(例如，每个父节点一个列表)，*但是在提取子查询之类的操作中更新它会更困难，*而且实际上也不会更容易扫描。*考虑到典型的查询不会有很多不同的附加项，因此似乎不值得将事情复杂化。**Note:aftercompletionoftheplannerprepphase,anygivenRTEisan*appendparenthavingentriesinappend_rel_listifandonlyifits*"inh"flagisset.Weclear"inh"forplaintablesthatturnoutnot*tohaveinheritancechildren,and(inanabuseoftheoriginalmeaning*oftheflag)weset"inh"forsubqueryRTEsthatturnouttobe*flattenableUNIONALLqueries.Thisletsusavoiduselesssearches*ofappend_rel_list.*注意:计划准备阶段完成后,*当且仅当它的“inh”标志已设置时,给定的RTE是一个appendparent在append_rel_list中的一个条目。*我们为没有child的平面表清除“inh”标记,*同时(有滥用标记的嫌疑)为UNIONALL查询中的子查询RTEs设置“inh”标记。*这样可以避免对append_rel_list进行无用的搜索。**Note:thedatastructureassumesthatappend-relmembersaresingle*baserels.ThisisOKforinheritance,butitpreventsusfrompulling*upaUNIONALLmembersubqueryifitcontainsajoin.Whilethatcould*befixedwithamorecomplexdatastructure,atpresentthere'snotmuch*pointbecausenoimprovementintheplancouldresult.*注意:数据结构假定附加的rel成员是独立的baserels。*这对于继承来说是可以的，但是如果UNIONALLmember子查询包含一个join，*那么它将阻止我们提取UNIONALLmember子查询。*虽然可以用更复杂的数据结构解决这个问题，但目前没有太大意义，因为该计划可能不会有任何改进。*/typedefstructAppendRelInfo{NodeTagtype;/**Thesefieldsuniquelyidentifythisappendrelationship.Therecanbe*(infact,alwaysshouldbe)multipleAppendRelInfosforthesame*parent_relid,butnevermorethanoneperchild_relid,sinceagiven*RTEcannotbeachildofmorethanoneappendparent.*这些字段惟一地标识这个appendrelationship。*对于同一个parent_relid可以有(实际上应该总是)多个AppendRelInfos，*但是每个child_relid不能有多个AppendRelInfos，*因为给定的RTE不能是多个appendparent的子节点。*/Indexparent_relid;/*parentrel的RT索引;RTindexofappendparentrel*/Indexchild_relid;/*childrel的RT索引;RTindexofappendchildrel*//**Foraninheritanceappendrel,theparentandchildarebothregular*relations,andwestoretheirrowtypeOIDshereforuseintranslating*whole-rowVars.ForaUNION-ALLappendrel,theparentandchildare*bothsubquerieswithnonamedrowtype,andwestoreInvalidOidhere.*对于继承appendrel，父类和子类都是普通关系，*我们将它们的rowtypeOIDs存储在这里，用于转换whole-rowVars。*对于UNION-ALLappendrel，父查询和子查询都是没有指定行类型的子查询，*我们在这里存储InvalidOid。*/Oidparent_reltype;/*OIDofparent'scompositetype*/Oidchild_reltype;/*OIDofchild'scompositetype*//**TheN'thelementofthislistisaVarorexpressionrepresentingthe*childcolumncorrespondingtotheN'thcolumnoftheparent.Thisis*usedtotranslateVarsreferencingtheparentrelintoreferencesto*thechild.AlistelementisNULLifitcorrespondstoadropped*columnoftheparent(thisisonlypossibleforinheritancecases,not*UNIONALL).ThelistelementsarealwayssimpleVarsforinheritance*cases,butcanbearbitraryexpressionsinUNIONALLcases.*这个列表的第N个元素是一个Var或表达式，表示与父元素的第N列对应的子列。*这用于将引用parentrel的Vars转换为对子rel的引用。*如果链表元素与父元素的已删除列相对应，则该元素为NULL*(这只适用于继承情况，而不是UNIONALL)。*对于继承情况，链表元素总是简单的变量，但是可以是UNIONALL情况下的任意表达式。**Noticeweonlystoreentriesforusercolumns(attno>0).Whole-row*Varsarespecial-cased,andsystemcolumns(attno<0)neednospecial*translationsincetheirattnosarethesameforalltables.*注意，我们只存储用户列的条目(attno>0)。*Whole-rowVars是大小写敏感的，系统列(attno<0)不需要特别的转换，*因为它们的attno对所有表都是相同的。**Caution:theVarshavevarlevelsup=0.Becarefultoadjustasneeded*whencopyingintoasubquery.*注意:Vars的varlevelsup=0。*在将数据复制到子查询时，要注意根据需要进行调整。*///child'sVars中的表达式List*translated_vars;/*Expressionsinthechild'sVars*//**Westoretheparenttable'sOIDhereforinheritance,orInvalidOidfor*UNIONALL.Thisisonlyneededtohelpingeneratingerrormessagesif*anattemptismadetoreferenceadroppedparentcolumn.*我们将父表的OID存储在这里用于继承，*如为UNIONALL,则这里存储的是InvalidOid。*只有在试图引用已删除的父列时，才需要这样做来帮助生成错误消息。*/Oidparent_reloid;/*OIDofparentrelation*/}AppendRelInfo;

RelOptInfo
规划器/优化器使用的关系信息结构体
参见PostgreSQL 源码解读（99）- 分区表#5（数据查询路由#2-RelOptInfo数据结构）

set_rel_pathlist函数为基础关系构建访问路径.

//是否DUMMY访问路径#defineIS_DUMMY_PATH(p)\(IsA((p),AppendPath)&&((AppendPath*)(p))->subpaths==NIL)/*Arelationthat'sbeenprovenemptywillhaveonepaththatisdummy*///已被证明为空的关系会含有一个虚拟dummy的访问路径#defineIS_DUMMY_REL(r)\((r)->cheapest_total_path!=NULL&&\IS_DUMMY_PATH((r)->cheapest_total_path))/**set_rel_pathlist*Buildaccesspathsforabaserelation*为基础关系构建访问路径*/staticvoidset_rel_pathlist(PlannerInfo*root,RelOptInfo*rel,Indexrti,RangeTblEntry*rte){if(IS_DUMMY_REL(rel)){/*Wealreadyprovedtherelationempty,sonothingmoretodo*///不需要做任何处理}elseif(rte->inh){/*It'san"appendrelation",processaccordingly*///appendrelation,调用set_append_rel_pathlist处理set_append_rel_pathlist(root,rel,rti,rte);}else{//其他类型的关系switch(rel->rtekind){caseRTE_RELATION://基础关系if(rte->relkind==RELKIND_FOREIGN_TABLE){/*Foreigntable*///外部表set_foreign_pathlist(root,rel,rte);}elseif(rte->tablesample!=NULL){/*Sampledrelation*///数据表采样set_tablesample_rel_pathlist(root,rel,rte);}else{/*Plainrelation*///常规关系set_plain_rel_pathlist(root,rel,rte);}break;caseRTE_SUBQUERY:/*Subquery---fullyhandledduringset_rel_size*///子查询break;caseRTE_FUNCTION:/*RangeFunction*/set_function_pathlist(root,rel,rte);break;caseRTE_TABLEFUNC:/*TableFunction*/set_tablefunc_pathlist(root,rel,rte);break;caseRTE_VALUES:/*Valueslist*/set_values_pathlist(root,rel,rte);break;caseRTE_CTE:/*CTEreference---fullyhandledduringset_rel_size*/break;caseRTE_NAMEDTUPLESTORE:/*tuplestorereference---fullyhandledduringset_rel_size*/break;default:elog(ERROR,"unexpectedrtekind:%d",(int)rel->rtekind);break;}}/**Ifthisisabaserel,weshouldnormallyconsidergatheringanypartial*pathswemayhavecreatedforit.*如为基础关系,通常来说需要考虑聚集gathering所有的部分路径(先前已构建)**However,ifthisisaninheritancechild,skipit.Otherwise,wecould*endupwithaverylargenumberofgathernodes,eachtryingtograb*itsownpoolofworkers.Instead,we'llconsidergatheringpartial*pathsfortheparentappendrel.*但是,如果这是一个继承关系中的子表,忽略它.*否则的话,我们最好可能会有很大量的Gather节点,每一个都尝试grab它自己worker的输出.*相反我们的处理是为父appendrelation生成gathering部分访问路径.**Also,ifthisisthetopmostscan/joinrel(thatis,theonlybaserel),*wepostponethisuntilthefinalscan/jointargelistisavailable(see*grouping_planner).*另外,如果这是最高层的scan/join关系(基础关系),*在完成了最后的scan/join投影列处理后才进行相应的处理.*/if(rel->reloptkind==RELOPT_BASEREL&&bms_membership(root->all_baserels)!=BMS_SINGLETON)generate_gather_paths(root,rel,false);/**AllowaplugintoeditorializeonthesetofPathsforthisbase*relation.Itcouldaddnewpaths(suchasCustomPaths)bycalling*add_path(),ordeleteormodifypathsaddedbythecorecode.*调用钩子函数.*插件可以通过调用add_path添加新的访问路径(比如CustomPaths等),*或者删除/修改核心代码生成的访问路径.*/if(set_rel_pathlist_hook)(*set_rel_pathlist_hook)(root,rel,rti,rte);/*Nowfindthecheapestofthepathsforthisrel*///为rel找到成本最低的访问路径set_cheapest(rel);#ifdefOPTIMIZER_DEBUGdebug_print_rel(root,rel);#endif}/**set_append_rel_pathlist*Buildaccesspathsforan"appendrelation"*为appendrelation构建访问路径*/staticvoidset_append_rel_pathlist(PlannerInfo*root,RelOptInfo*rel,Indexrti,RangeTblEntry*rte){intparentRTindex=rti;List*live_childrels=NIL;ListCell*l;/**Generateaccesspathsforeachmemberrelation,andrememberthe*non-dummychildren.*为每个成员关系生成访问路径，并"记住"非虚拟子节点。*/foreach(l,root->append_rel_list)//遍历链表{AppendRelInfo*appinfo=(AppendRelInfo*)lfirst(l);//获取AppendRelInfointchildRTindex;RangeTblEntry*childRTE;RelOptInfo*childrel;/*append_rel_listcontainsallappendrels;ignoreothers*///append_rel_list含有所有的appendrelations,忽略其他的relsif(appinfo->parent_relid!=parentRTindex)continue;/*Re-locatethechildRTEandRelOptInfo*///重新定位子RTE和RelOptInfochildRTindex=appinfo->child_relid;childRTE=root->simple_rte_array[childRTindex];childrel=root->simple_rel_array[childRTindex];/**Ifset_append_rel_size()decidedtheparentappendrelwas*parallel-unsafeatsomepointaftervisitingthischildrel,we*needtopropagatetheunsafetymarkingdowntothechild,sothat*wedon'tgenerateuselesspartialpathsforit.*如果set_append_rel_size()函数在访问了子关系后,*在某些点上断定父appendrelation是非并行安全的,*我们需要分发不安全的标记到子关系中,以免产生无用的部分访问路径.*/if(!rel->consider_parallel)childrel->consider_parallel=false;/**Computethechild'saccesspaths.*"计算"子关系的访问路径*/set_rel_pathlist(root,childrel,childRTindex,childRTE);/**Ifchildisdummy,ignoreit.*如为虚拟关系,忽略之*/if(IS_DUMMY_REL(childrel))continue;/*Bubbleupchildrel'spartitionedchildren.*///if(rel->part_scheme)rel->partitioned_child_rels=list_concat(rel->partitioned_child_rels,list_copy(childrel->partitioned_child_rels));/**Childislive,soaddittothelive_childrelslistforusebelow.*添加到live_childrels链表中*/live_childrels=lappend(live_childrels,childrel);}/*Addpathstotheappendrelation.*///添加访问路径到appendrelation中add_paths_to_append_rel(root,rel,live_childrels);}/**add_paths_to_append_rel*Generatepathsforthegivenappendrelationgiventhesetofnon-dummy*childrels.*基于非虚拟子关系集合为给定的appendrelation生成访问路径**Thefunctioncollectsallparameterizationsandorderingssupportedbythe*non-dummychildren.Foreverysuchparameterizationorordering,itcreates*anappendpathcollectingonepathfromeachnon-dummychildwithgiven*parameterizationorordering.Similarlyitcollectspartialpathsfrom*non-dummychildrentocreatepartialappendpaths.*该函数收集所有的非虚拟关系支持的参数化和排序访问路径.*对于每一个参数化或排序的访问路径,它创建一个附加路径，*从每个具有给定参数化或排序的非虚拟子节点收集相关信息。*类似的,从非虚拟子关系中收集部分访问路径用以创建部分append路径.*/voidadd_paths_to_append_rel(PlannerInfo*root,RelOptInfo*rel,List*live_childrels){List*subpaths=NIL;boolsubpaths_valid=true;List*partial_subpaths=NIL;List*pa_partial_subpaths=NIL;List*pa_nonpartial_subpaths=NIL;boolpartial_subpaths_valid=true;boolpa_subpaths_valid;List*all_child_pathkeys=NIL;List*all_child_outers=NIL;ListCell*l;List*partitioned_rels=NIL;doublepartial_rows=-1;/*Ifappropriate,considerparallelappend*///如可以,考虑并行appendpa_subpaths_valid=enable_parallel_append&&rel->consider_parallel;/**AppendPathgeneratedforpartitionedtablesmustrecordtheRTindexes*ofpartitionedtablesthataredirectorindirectchildrenofthis*Appendrel.*为分区表生成的AppendPath必须记录属于该分区表(AppendRel)的直接或间接子关系的RT索引**AppendPathmaybeforasub-queryRTE(UNIONALL),inwhichcase,'rel'*itselfdoesnotrepresentapartitionedrelation,butthechildsub-*queriesmaycontainreferencestopartitionedrelations.Theloop*belowwilllookforsuchchildrenandcollecttheminalisttobe*passedtothepathcreationfunction.(Thisassumesthatwedon'tneed*tolookthroughmultiplelevelsofsubqueryRTEs;ifweeverdo,we*couldconsiderstuffingthelistwegeneratehereintosub-queryRTE's*RelOptInfo,justlikewedoforpartitionedrels,whichwouldbeused*whenpopulatingourparentrelwithpaths.Forthepresent,that*appearstobeunnecessary.)*AppendPath可能是子查询RTE(UNIONALL),在这种情况下,rel自身并不代表分区表,*但childsub-queries可能含有分区表的依赖.*以下的循环会寻找这样的子关系,存储在链表中,并作为参数传给访问路径创建函数.*(这是假设我们不需要遍历多个层次的子查询RTEs,如果我们曾经这样做了,*这好比分区表的做法,可以考虑把生成的链表放到子查询的RTE'sRelOptInfo结构体中,*用于使用访问路径填充父关系.不过目前来说,这样做是不需要的.)*/if(rel->part_scheme!=NULL){if(IS_SIMPLE_REL(rel))partitioned_rels=list_make1(rel->partitioned_child_rels);elseif(IS_JOIN_REL(rel)){intrelid=-1;List*partrels=NIL;/**Forapartitionedjoinrel,concatenatethecomponentrels'*partitioned_child_relslists.*对于分区连接rel,连接rels的partitioned_child_rels链表**/while((relid=bms_next_member(rel->relids,relid))>=0){RelOptInfo*component;Assert(relid>=1&&relid<root->simple_rel_array_size);component=root->simple_rel_array[relid];Assert(component->part_scheme!=NULL);Assert(list_length(component->partitioned_child_rels)>=1);partrels=list_concat(partrels,list_copy(component->partitioned_child_rels));}partitioned_rels=list_make1(partrels);}Assert(list_length(partitioned_rels)>=1);}/**Foreverynon-dummychild,rememberthecheapestpath.Also,identify*allpathkeys(orderings)andparameterizations(required_outersets)*availableforthenon-dummymemberrelations.*对于每一个非虚拟子关系,记录成本最低的访问路径.*同时,为每一个非虚拟成员关系标识所有的路径键(排序)和可用的参数化信息(required_outer集合)*/foreach(l,live_childrels)//遍历{RelOptInfo*childrel=lfirst(l);ListCell*lcp;Path*cheapest_partial_path=NULL;/**ForUNIONALLswithnon-emptypartitioned_child_rels,accumulate*theListsofchildrelations.*对于包含非空的partitioned_child_rels的UNIONALLs操作,*累积子关系链表*/if(rel->rtekind==RTE_SUBQUERY&&childrel->partitioned_child_rels!=NIL)partitioned_rels=lappend(partitioned_rels,childrel->partitioned_child_rels);/**Ifchildhasanunparameterizedcheapest-totalpath,addthatto*theunparameterizedAppendpathweareconstructingfortheparent.*Ifnot,there'snoworkableunparameterizedpath.*如果子关系存在非参数化的总成本最低的访问路径,*添加此路径到我们为父关系构建的非参数化的Append访问路径中.**Withpartitionwiseaggregates,thechildrel'spathlistmaybe*empty,sodon'tassumethatapathexistshere.*使用partitionwise聚合,子关系的访问路径链表可能是空的,不能假设其中存在访问路径*/if(childrel->pathlist!=NIL&&childrel->cheapest_total_path->param_info==NULL)accumulate_append_subpath(childrel->cheapest_total_path,&subpaths,NULL);elsesubpaths_valid=false;/*Sameidea,butforapartialplan.*///同样的思路,处理并行处理中的部分计划if(childrel->partial_pathlist!=NIL){cheapest_partial_path=linitial(childrel->partial_pathlist);accumulate_append_subpath(cheapest_partial_path,&partial_subpaths,NULL);}elsepartial_subpaths_valid=false;/**Sameidea,butforaparallelappendmixingpartialandnon-partial*paths.*同样的,处理并行append混合并行/非并行访问路径*/if(pa_subpaths_valid){Path*nppath=NULL;nppath=get_cheapest_parallel_safe_total_inner(childrel->pathlist);if(cheapest_partial_path==NULL&&nppath==NULL){/*Neitherapartialnoraparallel-safepath?Forgetit.*///不是部分路径,也不是并行安全的路径,跳过pa_subpaths_valid=false;}elseif(nppath==NULL||(cheapest_partial_path!=NULL&&cheapest_partial_path->total_cost<nppath->total_cost)){/*Partialpathischeaperortheonlyoption.*///部分路径成本更低或者是唯一的选项Assert(cheapest_partial_path!=NULL);accumulate_append_subpath(cheapest_partial_path,&pa_partial_subpaths,&pa_nonpartial_subpaths);}else{/**Eitherwe'vegotonlyanon-partialpath,orwethinkthat*asinglebackendcanexecutethebestnon-partialpath*fasterthanalltheparallelbackendsworkingtogethercan*executethebestpartialpath.*这时候,要么得到了一个非并行访问路径,或者我们认为一个单独的后台进程*执行最好的非并行访问路径会比索引的并行进程一起执行最好的部分路径还要好.**Itmightmakesensetobemoreaggressivehere.Evenif*thebestnon-partialpathismoreexpensivethanthebest*partialpath,itcouldstillbebettertochoosethe*non-partialpathifthereareseveralsuchpathsthatcan*begiventodifferentworkers.Fornow,wedon'ttryto*figurethatout.*在这里,采取更积极的态度是有道理的.*甚至最好的非部分路径比最好的并行部分路径成本更高,仍然需要选择非并行路径,*如果多个这样的路径可能会分派到不同的worker上.*现在不需要指出这一点.*/accumulate_append_subpath(nppath,&pa_nonpartial_subpaths,NULL);}}/**Collectlistsofalltheavailablepathorderingsand*parameterizationsforallthechildren.Weusetheseasa*heuristictoindicatewhichsortorderingsandparameterizationswe*shouldbuildAppendandMergeAppendpathsfor.*收集子关系所有可用的排序和参数化路径链表.*我们采用启发式的规则判断Append和MergeAppend访问路径使用哪个排序和参数化信息*/foreach(lcp,childrel->pathlist){Path*childpath=(Path*)lfirst(lcp);List*childkeys=childpath->pathkeys;Relidschildouter=PATH_REQ_OUTER(childpath);/*Unsortedpathsdon'tcontributetopathkeylist*///未排序的访问路径,不需要分发到路径键链表中if(childkeys!=NIL){ListCell*lpk;boolfound=false;/*Havewealreadyseenthisordering?*/foreach(lpk,all_child_pathkeys){List*existing_pathkeys=(List*)lfirst(lpk);if(compare_pathkeys(existing_pathkeys,childkeys)==PATHKEYS_EQUAL){found=true;break;}}if(!found){/*No,soaddittoall_child_pathkeys*/all_child_pathkeys=lappend(all_child_pathkeys,childkeys);}}/*Unparameterizedpathsdon'tcontributetoparam-setlist*///非参数访问路径无需分发到参数化集合链表中if(childouter){ListCell*lco;boolfound=false;/*Havewealreadyseenthisparamset?*/foreach(lco,all_child_outers){Relidsexisting_outers=(Relids)lfirst(lco);if(bms_equal(existing_outers,childouter)){found=true;break;}}if(!found){/*No,soaddittoall_child_outers*/all_child_outers=lappend(all_child_outers,childouter);}}}}/**Ifwefoundunparameterizedpathsforallchildren,buildanunordered,*unparameterizedAppendpathfortherel.(Note:thisiscorrecteven*ifwehavezerooronelivesubpathduetoconstraintexclusion.)*如存在子关系的非参数化访问路径,构建未排序/未参数化的Append访问路径.*(注意:如果存在约束排除,我们只剩下有0或1个存活的subpath,这样的处理也说OK的)*/if(subpaths_valid)add_path(rel,(Path*)create_append_path(root,rel,subpaths,NIL,NULL,0,false,partitioned_rels,-1));/**Consideranappendofunordered,unparameterizedpartialpaths.Make*itparallel-awareifpossible.*尝试未排序/未参数化的部分Append访问路径.*如可能,构建parallel-aware访问路径.*/if(partial_subpaths_valid){AppendPath*appendpath;ListCell*lc;intparallel_workers=0;/*Findthehighestnumberofworkersrequestedforanysubpath.*///为子访问路径寻找最多数量的wokersforeach(lc,partial_subpaths){Path*path=lfirst(lc);parallel_workers=Max(parallel_workers,path->parallel_workers);}Assert(parallel_workers>0);/**Iftheuseofparallelappendispermitted,alwaysrequestatleast*log2(#ofchildren)workers.Weassumeitcanbeusefultohave*extraworkersinthiscasebecausetheywillbespreadoutacross*thechildren.Thepreciseformulaisjustaguess,butwedon't*wanttoendupwitharadicallydifferentanswerforatablewithN*partitionsvs.anunpartitionedtablewiththesamedata,sothe*useofsomekindoflog-scalinghereseemstomakesomesense.*如允许使用并行append,那么至少需要log2(子关系个数)个workers.*经过扩展后,假定可以使用额外的workers.*并不存在精确的计算公式,目前只是猜测而已,但是对于有相同数据的N个分区的分区表和非分区表来说,*答案是不一样的,因此在这里使用对数的计算方法是OK的.*/if(enable_parallel_append){parallel_workers=Max(parallel_workers,fls(list_length(live_childrels)));//上限值parallel_workers=Min(parallel_workers,max_parallel_workers_per_gather);//下限值}Assert(parallel_workers>0);/*Generateapartialappendpath.*///生成并行部分append访问路径appendpath=create_append_path(root,rel,NIL,partial_subpaths,NULL,parallel_workers,enable_parallel_append,partitioned_rels,-1);/**Makesureanysubsequentpartialpathsusethesamerowcount*estimate.*确保所有的子部分路径使用相同的行数估算*/partial_rows=appendpath->path.rows;/*Addthepath.*///添加路径add_partial_path(rel,(Path*)appendpath);}/**Consideraparallel-awareappendusingamixofpartialandnon-partial*paths.(Thisonlymakessenseifthere'satleastonechildwhichhas*anon-partialpaththatissubstantiallycheaperthananypartialpath;*otherwise,weshouldusetheappendpathaddedinthepreviousstep.)*使用混合的部分和非部分并行的append.*/if(pa_subpaths_valid&&pa_nonpartial_subpaths!=NIL){AppendPath*appendpath;ListCell*lc;intparallel_workers=0;/**Findthehighestnumberofworkersrequestedforanypartial*subpath.*/foreach(lc,pa_partial_subpaths){Path*path=lfirst(lc);parallel_workers=Max(parallel_workers,path->parallel_workers);}/**Sameformulahereasabove.It'sevenmoreimportantinthis*instancebecausethenon-partialpathswon'tcontributeanythingto*theplannednumberofparallelworkers.*/parallel_workers=Max(parallel_workers,fls(list_length(live_childrels)));parallel_workers=Min(parallel_workers,max_parallel_workers_per_gather);Assert(parallel_workers>0);appendpath=create_append_path(root,rel,pa_nonpartial_subpaths,pa_partial_subpaths,NULL,parallel_workers,true,partitioned_rels,partial_rows);add_partial_path(rel,(Path*)appendpath);}/**AlsobuildunparameterizedMergeAppendpathsbasedonthecollected*listofchildpathkeys.*基于收集的子路径键,构建非参数化的MergeAppend访问路径*/if(subpaths_valid)generate_mergeappend_paths(root,rel,live_childrels,all_child_pathkeys,partitioned_rels);/**BuildAppendpathsforeachparameterizationseenamongthechildrels.*(Thismaylookprettyexpensive,butinmostcasesofpractical*interest,thechildrelswillexposemostlythesameparameterizations,*sothatnotthatmanycasesactuallygetconsideredhere.)*为每一个参数化的子关系构建Append访问路径.*(看起来成本客观,但在大多数情况下,子关系使用同样的参数化信息,因此实际上并不是经常发现)**TheAppendnodeitselfcannotenforcequals,soallqualcheckingmust*bedoneinthechildpaths.Thismeansthattohaveaparameterized*Appendpath,wemusthavetheexactsameparameterizationforeach*childpath;otherwisesomechildrenmightbefailingtocheckthe*moved-downquals.Tomakethemmatchup,wecantrytoincreasethe*parameterizationoflesser-parameterizedpaths.*/foreach(l,all_child_outers){Relidsrequired_outer=(Relids)lfirst(l);ListCell*lcr;/*SelectthechildpathsforanAppendwiththisparameterization*/subpaths=NIL;subpaths_valid=true;foreach(lcr,live_childrels){RelOptInfo*childrel=(RelOptInfo*)lfirst(lcr);Path*subpath;if(childrel->pathlist==NIL){/*failedtomakeasuitablepathforthischild*/subpaths_valid=false;break;}subpath=get_cheapest_parameterized_child_path(root,childrel,required_outer);if(subpath==NULL){/*failedtomakeasuitablepathforthischild*/subpaths_valid=false;break;}accumulate_append_subpath(subpath,&subpaths,NULL);}if(subpaths_valid)add_path(rel,(Path*)create_append_path(root,rel,subpaths,NIL,required_outer,0,false,partitioned_rels,-1));}}三、跟踪分析

测试脚本如下

testdb=#explainverboseselect*fromt_hash_partitionwherec1=1ORc1=2;QUERYPLAN-------------------------------------------------------------------------------------Append(cost=0.00..30.53rows=6width=200)->SeqScanonpublic.t_hash_partition_1(cost=0.00..15.25rows=3width=200)Output:t_hash_partition_1.c1,t_hash_partition_1.c2,t_hash_partition_1.c3Filter:((t_hash_partition_1.c1=1)OR(t_hash_partition_1.c1=2))->SeqScanonpublic.t_hash_partition_3(cost=0.00..15.25rows=3width=200)Output:t_hash_partition_3.c1,t_hash_partition_3.c2,t_hash_partition_3.c3Filter:((t_hash_partition_3.c1=1)OR(t_hash_partition_3.c1=2))(7rows)

启动gdb,设置断点

(gdb)bset_rel_pathlistBreakpoint1at0x796823:fileallpaths.c,line425.(gdb)cContinuing.Breakpoint1,set_rel_pathlist(root=0x1f1e400,rel=0x1efaba0,rti=1,rte=0x1efa3d0)atallpaths.c:425425if(IS_DUMMY_REL(rel))(gdb)

通过rte->inh判断是否分区表或者UNION ALL

(gdb)prte->inh$1=true(gdb)

进入set_append_rel_pathlist函数

(gdb)n429elseif(rte->inh)(gdb)432set_append_rel_pathlist(root,rel,rti,rte);(gdb)stepset_append_rel_pathlist(root=0x1f1e400,rel=0x1efaba0,rti=1,rte=0x1efa3d0)atallpaths.c:12961296intparentRTindex=rti;

遍历子关系

(gdb)n1297List*live_childrels=NIL;(gdb)1304foreach(l,root->append_rel_list)(gdb)(gdb)p*root->append_rel_list$2={type=T_List,length=6,head=0x1fc1f98,tail=0x1fc2ae8}

获取AppendRelInfo,判断父关系是否正在处理的父关系

(gdb)n1306AppendRelInfo*appinfo=(AppendRelInfo*)lfirst(l);(gdb)1312if(appinfo->parent_relid!=parentRTindex)(gdb)p*appinfo$3={type=T_AppendRelInfo,parent_relid=1,child_relid=3,parent_reltype=16988,child_reltype=16991,translated_vars=0x1fc1e60,parent_reloid=16986}(gdb)

获取子关系的相关信息,递归调用set_rel_pathlist

(gdb)n1316childRTindex=appinfo->child_relid;(gdb)1317childRTE=root->simple_rte_array[childRTindex];(gdb)1318childrel=root->simple_rel_array[childRTindex];(gdb)1326if(!rel->consider_parallel)(gdb)1332set_rel_pathlist(root,childrel,childRTindex,childRTE);(gdb)(gdb)Breakpoint1,set_rel_pathlist(root=0x1f1e400,rel=0x1f1c8a0,rti=3,rte=0x1efa658)atallpaths.c:425425if(IS_DUMMY_REL(rel))(gdb)finishRuntillexitfrom#0set_rel_pathlist(root=0x1f1e400,rel=0x1f1c8a0,rti=3,rte=0x1efa658)atallpaths.c:425set_append_rel_pathlist(root=0x1f1e400,rel=0x1efaba0,rti=1,rte=0x1efa3d0)atallpaths.c:1337

如为虚拟关系,则忽略之

1337if(IS_DUMMY_REL(childrel))

该子关系不是虚拟关系,继续处理,加入到rel->partitioned_child_rels和live_childrels链表中

(gdb)n1341if(rel->part_scheme)(gdb)1344list_copy(childrel->partitioned_child_rels));(gdb)1343list_concat(rel->partitioned_child_rels,(gdb)1342rel->partitioned_child_rels=(gdb)1349live_childrels=lappend(live_childrels,childrel);(gdb)p*rel->partitioned_child_rels$4={type=T_IntList,length=1,head=0x1fc4d78,tail=0x1fc4d78}(gdb)prel->partitioned_child_rels->head->data.int_value$6=1(gdb)(gdb)n1304foreach(l,root->append_rel_list)(gdb)plive_childrels$7=(List*)0x1fd0a60(gdb)p*live_childrels$8={type=T_List,length=1,head=0x1fd0a38,tail=0x1fd0a38}(gdb)p*(Node*)live_childrels->head->data.ptr_value$9={type=T_RelOptInfo}(gdb)p*(RelOptInfo*)live_childrels->head->data.ptr_value$10={type=T_RelOptInfo,reloptkind=RELOPT_OTHER_MEMBER_REL,relids=0x1fc3590,rows=3,consider_startup=false,consider_param_startup=false,consider_parallel=true,reltarget=0x1fc35b0,pathlist=0x1fd0940,ppilist=0x0,partial_pathlist=0x1fd09a0,cheapest_startup_path=0x1fc44f8,cheapest_total_path=0x1fc44f8,cheapest_unique_path=0x0,cheapest_parameterized_paths=0x1fd0a00,direct_lateral_relids=0x0,lateral_relids=0x0,relid=3,reltablespace=0,rtekind=RTE_RELATION,min_attr=-7,max_attr=3,attr_needed=0x1fc2e38,attr_widths=0x1fc3628,lateral_vars=0x0,lateral_referencers=0x0,indexlist=0x0,statlist=0x0,pages=10,tuples=350,allvisfrac=0,subroot=0x0,subplan_params=0x0,rel_parallel_workers=-1,serverid=0,userid=0,useridiscurrent=false,fdwroutine=0x0,fdw_private=0x0,unique_for_rels=0x0,non_unique_for_rels=0x0,baserestrictinfo=0x1fc68d8,baserestrictcost={startup=0,per_tuple=0.0050000000000000001},baserestrict_min_security=0,joininfo=0x0,has_eclass_joins=false,consider_partitionwise_join=false,top_parent_relids=0x1fc3608,part_scheme=0x0,nparts=0,boundinfo=0x0,partition_qual=0x0,part_rels=0x0,partexprs=0x0,nullable_partexprs=0x0,partitioned_child_rels=0x0}

对于虚拟子关系(上一节介绍的被裁剪的分区),直接跳过

(gdb)n1306AppendRelInfo*appinfo=(AppendRelInfo*)lfirst(l);(gdb)1312if(appinfo->parent_relid!=parentRTindex)(gdb)1316childRTindex=appinfo->child_relid;(gdb)1317childRTE=root->simple_rte_array[childRTindex];(gdb)1318childrel=root->simple_rel_array[childRTindex];(gdb)1326if(!rel->consider_parallel)(gdb)1332set_rel_pathlist(root,childrel,childRTindex,childRTE);(gdb)1337if(IS_DUMMY_REL(childrel))(gdb)1338continue;

设置断点,进入add_paths_to_append_rel函数

(gdb)badd_paths_to_append_relBreakpoint2at0x797d88:fileallpaths.c,line1372.(gdb)cContinuing.Breakpoint2,add_paths_to_append_rel(root=0x1f1cdb8,rel=0x1fc1800,live_childrels=0x1fcfb10)atallpaths.c:13721372List*subpaths=NIL;(gdb)

输入参数,其中rel是父关系,live_childrels是经裁剪后仍存活的分区(子关系)

(gdb)n1373boolsubpaths_valid=true;(gdb)p*rel$11={type=T_RelOptInfo,reloptkind=RELOPT_BASEREL,relids=0x1fc1a18,rows=6,consider_startup=false,consider_param_startup=false,consider_parallel=true,reltarget=0x1fc1a38,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=1,reltablespace=0,rtekind=RTE_RELATION,min_attr=-7,max_attr=3,attr_needed=0x1fc1a90,attr_widths=0x1fc1b28,lateral_vars=0x0,lateral_referencers=0x0,indexlist=0x0,statlist=0x0,pages=0,tuples=6,allvisfrac=0,subroot=0x0,subplan_params=0x0,rel_parallel_workers=-1,serverid=0,userid=0,useridiscurrent=false,fdwroutine=0x0,fdw_private=0x0,unique_for_rels=0x0,non_unique_for_rels=0x0,baserestrictinfo=0x1fc3e20,baserestrictcost={startup=0,per_tuple=0},baserestrict_min_security=0,joininfo=0x0,has_eclass_joins=false,consider_partitionwise_join=false,top_parent_relids=0x0,part_scheme=0x1fc1b80,nparts=6,boundinfo=0x1fc1d30,partition_qual=0x0,part_rels=0x1fc2000,partexprs=0x1fc1f08,nullable_partexprs=0x1fc1fe0,partitioned_child_rels=0x1fc3ea0}

初始化变量

(gdb)n1374List*partial_subpaths=NIL;(gdb)1375List*pa_partial_subpaths=NIL;(gdb)1376List*pa_nonpartial_subpaths=NIL;(gdb)1377boolpartial_subpaths_valid=true;(gdb)1379List*all_child_pathkeys=NIL;(gdb)1380List*all_child_outers=NIL;(gdb)1382List*partitioned_rels=NIL;(gdb)1383doublepartial_rows=-1;(gdb)1386pa_subpaths_valid=enable_parallel_append&&rel->consider_parallel;(gdb)1404if(rel->part_scheme!=NULL)(gdb)ppa_subpaths_valid$17=true

构建partitioned_rels链表

(gdb)n1406if(IS_SIMPLE_REL(rel))(gdb)1407partitioned_rels=list_make1(rel->partitioned_child_rels);(gdb)1433Assert(list_length(partitioned_rels)>=1);(gdb)1441foreach(l,live_childrels)(gdb)p*partitioned_rels$18={type=T_List,length=1,head=0x1fcff40,tail=0x1fcff40}

开始遍历live_childrels,对于每一个非虚拟子关系,记录成本最低的访问路径.
如果子关系存在非参数化的总成本最低的访问路径,添加此路径到我们为父关系构建的非参数化的Append访问路径中.

(gdb)n1443RelOptInfo*childrel=lfirst(l);(gdb)1445Path*cheapest_partial_path=NULL;(gdb)1451if(rel->rtekind==RTE_SUBQUERY&&childrel->partitioned_child_rels!=NIL)(gdb)1463if(childrel->pathlist!=NIL&&(gdb)1464childrel->cheapest_total_path->param_info==NULL)(gdb)1463if(childrel->pathlist!=NIL&&(gdb)1465accumulate_append_subpath(childrel->cheapest_total_path,

同样的思路,处理并行处理中的部分计划

(gdb)n1471if(childrel->partial_pathlist!=NIL)(gdb)1473cheapest_partial_path=linitial(childrel->partial_pathlist);(gdb)1474accumulate_append_subpath(cheapest_partial_path,

同样的,处理并行append混合并行/非并行访问路径

(gdb)n1486Path*nppath=NULL;(gdb)1489get_cheapest_parallel_safe_total_inner(childrel->pathlist);(gdb)1488nppath=(gdb)1491if(cheapest_partial_path==NULL&&nppath==NULL)(gdb)1496elseif(nppath==NULL||(gdb)1498cheapest_partial_path->total_cost<nppath->total_cost))(gdb)1497(cheapest_partial_path!=NULL&&(gdb)1501Assert(cheapest_partial_path!=NULL);(gdb)1502accumulate_append_subpath(cheapest_partial_path,

收集子关系所有可用的排序和参数化路径链表.

(gdb)1534foreach(lcp,childrel->pathlist)(gdb)(gdb)n1536Path*childpath=(Path*)lfirst(lcp);(gdb)1537List*childkeys=childpath->pathkeys;(gdb)1538Relidschildouter=PATH_REQ_OUTER(childpath);(gdb)1541if(childkeys!=NIL)(gdb)1567if(childouter)(gdb)1534foreach(lcp,childrel->pathlist)

继续下一个子关系,完成处理

...(gdb)1441foreach(l,live_childrels)(gdb)1598if(subpaths_valid)

如存在子关系的非参数化访问路径,构建未排序/未参数化的Append访问路径.

(gdb)n1599add_path(rel,(Path*)create_append_path(root,rel,subpaths,NIL,(gdb)p*rel->pathlistCannotaccessmemoryataddress0x0(gdb)n1607if(partial_subpaths_valid)(gdb)p*rel->pathlist$22={type=T_List,length=1,head=0x1fd0230,tail=0x1fd0230}

尝试未排序/未参数化的部分Append访问路径.如可能,构建parallel-aware访问路径.

...(gdb)1641appendpath=create_append_path(root,rel,NIL,partial_subpaths,(gdb)1650partial_rows=appendpath->path.rows;(gdb)1653add_partial_path(rel,(Path*)appendpath);(gdb)

使用混合的部分和非部分并行的append.

1662if(pa_subpaths_valid&&pa_nonpartial_subpaths!=NIL)(gdb)

基于收集的子路径键,构建非参数化的MergeAppend访问路径

1701if(subpaths_valid)(gdb)1702generate_mergeappend_paths(root,rel,live_childrels,

完成调用

(gdb)1719foreach(l,all_child_outers)(gdb)1757}(gdb)set_append_rel_pathlist(root=0x1f1cdb8,rel=0x1fc1800,rti=1,rte=0x1efa3d0)atallpaths.c:13541354}(gdb)p*rel->pathlist$23={type=T_List,length=1,head=0x1fd0230,tail=0x1fd0230}(gdb)(gdb)p*(Node*)rel->pathlist->head->data.ptr_value$24={type=T_AppendPath}(gdb)p*(AppendPath*)rel->pathlist->head->data.ptr_value$25={path={type=T_AppendPath,pathtype=T_Append,parent=0x1fc1800,pathtarget=0x1fc1a38,param_info=0x0,parallel_aware=false,parallel_safe=true,parallel_workers=0,rows=6,startup_cost=0,total_cost=30.530000000000001,pathkeys=0x0},partitioned_rels=0x1fd01f8,subpaths=0x1fcffc8,first_partial_path=2}

结束调用

(gdb)nset_rel_pathlist(root=0x1f1cdb8,rel=0x1fc1800,rti=1,rte=0x1efa3d0)atallpaths.c:495495if(rel->reloptkind==RELOPT_BASEREL&&(gdb)496bms_membership(root->all_baserels)!=BMS_SINGLETON)(gdb)495if(rel->reloptkind==RELOPT_BASEREL&&(gdb)504if(set_rel_pathlist_hook)(gdb)508set_cheapest(rel);(gdb)513}(gdb)

感谢各位的阅读！关于“PostgreSQL如何为append relation构建访问路径”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！