PostgreSQL中create_index_path函数有什么作用

2025-03-11 技术教程

本篇内容主要讲解“PostgreSQL中create_index_path函数有什么作用”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“PostgreSQL中create_index_path函数有什么作用”吧!

函数build_index_paths中的子函数create_index_path实现了索引扫描成本的估算主逻辑。

一、数据结构

IndexOptInfo
回顾IndexOptInfo索引信息结构体

typedefstructIndexOptInfo{NodeTagtype;Oidindexoid;/*Index的OID,OIDoftheindexrelation*/Oidreltablespace;/*Index的表空间,tablespaceofindex(nottable)*/RelOptInfo*rel;/*指向Relation的指针,back-linktoindex'stable*//*index-sizestatistics(frompg_classandelsewhere)*/BlockNumberpages;/*Index的pages,numberofdiskpagesinindex*/doubletuples;/*Index的元组数,numberofindextuplesinindex*/inttree_height;/*索引高度,indextreeheight,or-1ifunknown*//*indexdescriptorinformation*/intncolumns;/*索引的列数,numberofcolumnsinindex*/intnkeycolumns;/*索引的关键列数,numberofkeycolumnsinindex*/int*indexkeys;/*columnnumbersofindex'sattributesboth*keyandincludedcolumns,or0*/Oid*indexcollations;/*OIDsofcollationsofindexcolumns*/Oid*opfamily;/*OIDsofoperatorfamiliesforcolumns*/Oid*opcintype;/*OIDsofopclassdeclaredinputdatatypes*/Oid*sortopfamily;/*OIDsofbtreeopfamilies,iforderable*/bool*reverse_sort;/*倒序?issortorderdescending?*/bool*nulls_first;/*NULLs值优先?doNULLscomefirstinthesortorder?*/bool*canreturn;/*索引列可通过Index-OnlyScan返回?whichindexcolscanbereturnedinan*index-onlyscan?*/Oidrelam;/*访问方法OID,OIDoftheaccessmethod(inpg_am)*/List*indexprs;/*非简单索引列表达式链表,如函数索引,expressionsfornon-simpleindexcolumns*/List*indpred;/*部分索引的谓词链表,predicateifapartialindex,elseNIL*/List*indextlist;/*索引列(TargetEntry结构体链表),targetlistrepresentingindexcolumns*/List*indrestrictinfo;/*父关系的baserestrictinfo列表，*不包含索引谓词隐含的所有条件*(除非是目标rel，请参阅check_index_predicates()中的注释),*parentrelation'sbaserestrictinfo*list,lessanyconditionsimpliedby*theindex'spredicate(unlessit'sa*targetrel,seecommentsin*check_index_predicates())*/boolpredOK;/*True,如索引谓词满足查询要求,trueifindexpredicatematchesquery*/boolunique;/*是否唯一索引,trueifauniqueindex*/boolimmediate;/*唯一性校验是否立即生效,isuniquenessenforcedimmediately?*/boolhypothetical;/*是否虚拟索引,trueifindexdoesn'treallyexist*//*RemainingfieldsarecopiedfromtheindexAM'sAPIstruct:*///从IndexRelation拷贝过来的AM(访问方法)API信息boolamcanorderbyop;/*doesAMsupportorderbyoperatorresult?*/boolamoptionalkey;/*canqueryomitkeyforthefirstcolumn?*/boolamsearcharray;/*canAMhandleScalarArrayOpExprquals?*/boolamsearchnulls;/*canAMsearchforNULL/NOTNULLentries?*/boolamhasgettuple;/*doesAMhaveamgettupleinterface?*/boolamhasgetbitmap;/*doesAMhaveamgetbitmapinterface?*/boolamcanparallel;/*doesAMsupportparallelscan?*//*Ratherthanincludeamapi.hhere,wedeclareamcostestimatelikethis*/void(*amcostestimate)();/*访问方法的估算函数,AM'scostestimator*/}IndexOptInfo;

Cost相关
注意:实际使用的参数值通过系统配置文件定义,而不是这里的常量定义!

typedefdoubleCost;/*executioncost(inpage-accessunits)*//*defaultsforcostsize.c'sCostparameters*//*NB:cost-estimationcodeshouldusethevariables,nottheseconstants!*//*注意:实际值通过系统配置文件定义,而不是这里的常量定义!*//*Ifyouchangethese,updatebackend/utils/misc/postgresql.sample.conf*/#defineDEFAULT_SEQ_PAGE_COST1.0//顺序扫描page的成本#defineDEFAULT_RANDOM_PAGE_COST4.0//随机扫描page的成本#defineDEFAULT_CPU_TUPLE_COST0.01//处理一个元组的CPU成本#defineDEFAULT_CPU_INDEX_TUPLE_COST0.005//处理一个索引元组的CPU成本#defineDEFAULT_CPU_OPERATOR_COST0.0025//执行一次操作或函数的CPU成本#defineDEFAULT_PARALLEL_TUPLE_COST0.1//并行执行,从一个worker传输一个元组到另一个worker的成本#defineDEFAULT_PARALLEL_SETUP_COST1000.0//构建并行执行环境的成本#defineDEFAULT_EFFECTIVE_CACHE_SIZE524288/*先前已有介绍,measuredinpages*/doubleseq_page_cost=DEFAULT_SEQ_PAGE_COST;doublerandom_page_cost=DEFAULT_RANDOM_PAGE_COST;doublecpu_tuple_cost=DEFAULT_CPU_TUPLE_COST;doublecpu_index_tuple_cost=DEFAULT_CPU_INDEX_TUPLE_COST;doublecpu_operator_cost=DEFAULT_CPU_OPERATOR_COST;doubleparallel_tuple_cost=DEFAULT_PARALLEL_TUPLE_COST;doubleparallel_setup_cost=DEFAULT_PARALLEL_SETUP_COST;inteffective_cache_size=DEFAULT_EFFECTIVE_CACHE_SIZE;Costdisable_cost=1.0e10;//1后面10个0,通过设置一个巨大的成本,让优化器自动放弃此路径intmax_parallel_workers_per_gather=2;//每次gather使用的worker数二、源码解读

create_index_path
该函数创建索引扫描路径节点,其中调用函数cost_index计算索引扫描成本.

//-----------------------------------------------create_index_path/**create_index_path*Createsapathnodeforanindexscan.*创建索引扫描路径节点**'index'isausableindex.*'indexclauses'isalistofRestrictInfonodesrepresentingclauses*tobeusedasindexqualconditionsinthescan.*'indexclausecols'isanintegerlistofindexcolumnnumbers(zerobased)*theindexclausescanbeusedwith.*'indexorderbys'isalistofbareexpressions(noRestrictInfos)*tobeusedasindexorderingoperatorsinthescan.*'indexorderbycols'isanintegerlistofindexcolumnnumbers(zerobased)*theorderingoperatorscanbeusedwith.*'pathkeys'describestheorderingofthepath.*'indexscandir'isForwardScanDirectionorBackwardScanDirection*foranorderedindex,orNoMovementScanDirectionfor*anunorderedindex.*'indexonly'istrueifanindex-onlyscaniswanted.*'required_outer'isthesetofouterrelidsforaparameterizedpath.*'loop_count'isthenumberofrepetitionsoftheindexscantofactorinto*estimatesofcachingbehavior.*'partial_path'istrueifconstructingaparallelindexscanpath.**Returnsthenewpathnode.*/IndexPath*create_index_path(PlannerInfo*root,//优化器信息IndexOptInfo*index,//索引信息List*indexclauses,//索引约束条件链表List*indexclausecols,//索引约束条件列编号链表,与indexclauses一一对应List*indexorderbys,//ORDERBY原始表达式链表List*indexorderbycols,//ORDERBY列编号链表List*pathkeys,//排序路径键ScanDirectionindexscandir,//扫描方向boolindexonly,//纯索引扫描?Relidsrequired_outer,//需依赖的外部Relidsdoubleloop_count,//用于估计缓存的重复次数boolpartial_path)//是否并行索引扫描{IndexPath*pathnode=makeNode(IndexPath);//构建节点RelOptInfo*rel=index->rel;//索引对应的RelList*indexquals,*indexqualcols;pathnode->path.pathtype=indexonly?T_IndexOnlyScan:T_IndexScan;//路径类型pathnode->path.parent=rel;//Relationpathnode->path.pathtarget=rel->reltarget;//路径最终的投影列pathnode->path.param_info=get_baserel_parampathinfo(root,rel,required_outer);//参数化信息pathnode->path.parallel_aware=false;//pathnode->path.parallel_safe=rel->consider_parallel;//是否并行pathnode->path.parallel_workers=0;//worker数目pathnode->path.pathkeys=pathkeys;//排序路径键/*Convertclausestotheexecutorcanhandle*///转换条件子句(clauses)为执行器可处理的索引表达式(indexquals)expand_indexqual_conditions(index,indexclauses,indexclausecols,&indexquals,&indexqualcols);/*填充路径节点信息,Fillinthepathnode*/pathnode->indexinfo=index;pathnode->indexclauses=indexclauses;pathnode->indexquals=indexquals;pathnode->indexqualcols=indexqualcols;pathnode->indexorderbys=indexorderbys;pathnode->indexorderbycols=indexorderbycols;pathnode->indexscandir=indexscandir;cost_index(pathnode,root,loop_count,partial_path);//估算成本returnpathnode;}//------------------------------------expand_indexqual_conditions/**expand_indexqual_conditions*GivenalistofRestrictInfonodes,producealistofdirectlyusable*indexqualclauses.*给定RestrictInfo节点(约束条件),产生直接可用的索引表达式子句**Standardqualclauses(thoseintheindex'sopfamily)arepassedthrough*unchanged.Booleanclausesand"special"indexoperatorsareexpanded*intoclausesthattheindexscanmachinerywillknowwhattodowith.*RowCompareclausesaresimplifiedifnecessarytocreateaclausethatis*fullycheckablebytheindex.*标准的条件子句(位于索引opfamily中)可不作修改直接使用.*布尔子句和"special"索引操作符扩展为索引扫描执行器可以处理的子句.*如需要，RowCompare子句将简化为可由索引完全检查的子句。**Inadditiontotheexpressionsthemselves,thereareauxiliarylists*oftheindexcolumnnumbersthattheclausesaremeanttobeusedwith;*wegenerateanupdatedcolumnnumberlistfortheresult.(Thisisnot*theidenticallistbecauseoneinputclausesometimesproducesmorethan*oneoutputclause.)*除了表达式本身之外，还有索引列号的辅助链表，这些子句将使用这些列号.这些列号*将被更新用于结果返回(不是相同的链表，因为一个输入子句有时会产生多个输出子句。).**Theinputclausesaresortedbycolumnnumber,andsotheoutputistoo.*(Thisisdependedoninvariousplacesinbothplannerandexecutor.)*输入子句通过列号排序,输出子句也是如此.*/voidexpand_indexqual_conditions(IndexOptInfo*index,List*indexclauses,List*indexclausecols,List**indexquals_p,List**indexqualcols_p){List*indexquals=NIL;List*indexqualcols=NIL;ListCell*lcc,*lci;forboth(lcc,indexclauses,lci,indexclausecols)//扫描索引子句链表和匹配的列号{RestrictInfo*rinfo=(RestrictInfo*)lfirst(lcc);intindexcol=lfirst_int(lci);Expr*clause=rinfo->clause;//条件子句OidcurFamily;OidcurCollation;Assert(indexcol<index->nkeycolumns);curFamily=index->opfamily[indexcol];//索引列的opfamilycurCollation=index->indexcollations[indexcol];//排序规则/*Firstcheckforbooleancases*/if(IsBooleanOpfamily(curFamily))//布尔{Expr*boolqual;boolqual=expand_boolean_index_clause((Node*)clause,indexcol,index);//布尔表达式if(boolqual){indexquals=lappend(indexquals,make_simple_restrictinfo(boolqual));//添加到结果中indexqualcols=lappend_int(indexqualcols,indexcol);//列号continue;}}/**Elseitmustbeanopclause(usualcase),ScalarArrayOp,*RowCompare,orNullTest*/if(is_opclause(clause))//普通的操作符子句{indexquals=list_concat(indexquals,expand_indexqual_opclause(rinfo,curFamily,curCollation));//合并到结果链表中/*expand_indexqual_opclausecanproducemultipleclauses*/while(list_length(indexqualcols)<list_length(indexquals))indexqualcols=lappend_int(indexqualcols,indexcol);}elseif(IsA(clause,ScalarArrayOpExpr))//ScalarArrayOpExpr{/*noextraworkatthistime*/indexquals=lappend(indexquals,rinfo);indexqualcols=lappend_int(indexqualcols,indexcol);}elseif(IsA(clause,RowCompareExpr))//RowCompareExpr{indexquals=lappend(indexquals,expand_indexqual_rowcompare(rinfo,index,indexcol));indexqualcols=lappend_int(indexqualcols,indexcol);}elseif(IsA(clause,NullTest))//NullTest{Assert(index->amsearchnulls);indexquals=lappend(indexquals,rinfo);indexqualcols=lappend_int(indexqualcols,indexcol);}elseelog(ERROR,"unsupportedindexqualtype:%d",(int)nodeTag(clause));}*indexquals_p=indexquals;//结果赋值*indexqualcols_p=indexqualcols;}//------------------------------------cost_index/**cost_index*Determinesandreturnsthecostofscanningarelationusinganindex.*确定和返回索引扫描的成本**'path'describestheindexscanunderconsideration,andiscomplete*exceptforthefieldstobesetbythisroutine*path-位于考虑之列的索引扫描路径,除了本例程要设置的字段外其他信息已完整.**'loop_count'isthenumberofrepetitionsoftheindexscantofactorinto*estimatesofcachingbehavior*loop_count-用于估计缓存的重复次数**Inadditiontorows,startup_costandtotal_cost,cost_index()setsthe*path'sindextotalcostandindexselectivityfields.Thesevalueswillbe*needediftheIndexPathisusedinaBitmapIndexScan.*除了行、startup_cost和total_cost之外，函数cost_index*还设置了访问路径的indextotalcost和indexselectivity字段。*如果在位图索引扫描中使用IndexPath，则需要这些值。**NOTE:path->indexqualsmustcontainonlyclausesusableasindex*restrictions.Anyadditionalqualsevaluatedasqpqualsmayreducethe*numberofreturnedtuples,buttheywon'treducethenumberoftuples*wehavetofetchfromthetable,sotheydon'treducethescancost.*注意:path->indexquals必须仅包含用作索引约束条件的子句。任何作为qpquals评估的*额外条件可能会减少返回元组的数量，但它们不会减少必须从表中获取的元组*数量，因此它们不会降低扫描成本。*/voidcost_index(IndexPath*path,PlannerInfo*root,doubleloop_count,boolpartial_path){IndexOptInfo*index=path->indexinfo;//索引信息RelOptInfo*baserel=index->rel;//RelOptInfo信息boolindexonly=(path->path.pathtype==T_IndexOnlyScan);//是否纯索引扫描amcostestimate_functionamcostestimate;//索引访问方法成本估算函数List*qpquals;//qpquals链表Coststartup_cost=0;//启动成本Costrun_cost=0;//执行成本Costcpu_run_cost=0;//cpu执行成本CostindexStartupCost;//索引启动成本CostindexTotalCost;//索引总成本SelectivityindexSelectivity;//选择率doubleindexCorrelation,//csquared;//doublespc_seq_page_cost,spc_random_page_cost;Costmin_IO_cost,//最小IO成本max_IO_cost;//最大IO成本QualCostqpqual_cost;//表达式成本Costcpu_per_tuple;//每个tuple处理成本doubletuples_fetched;//取得的元组数量doublepages_fetched;//取得的page数量doublerand_heap_pages;//随机访问的堆page数量doubleindex_pages;//索引page数量/*Shouldonlybeappliedtobaserelations*/Assert(IsA(baserel,RelOptInfo)&&IsA(index,IndexOptInfo));Assert(baserel->relid>0);Assert(baserel->rtekind==RTE_RELATION);/**Markthepathwiththecorrectrowestimate,andidentifywhichquals*willneedtobeenforcedasqpquals.Weneednotcheckanyqualsthat*areimpliedbytheindex'spredicate,sowecanuseindrestrictinfonot*baserestrictinfoasthelistofrelevantrestrictionclausesforthe*rel.*/if(path->path.param_info)//存在参数化信息{path->path.rows=path->path.param_info->ppi_rows;/*qpqualscomefromtherel'srestrictionclausesandppi_clauses*/qpquals=list_concat(extract_nonindex_conditions(path->indexinfo->indrestrictinfo,path->indexquals),extract_nonindex_conditions(path->path.param_info->ppi_clauses,path->indexquals));}else{path->path.rows=baserel->rows;//基表的估算行数/*qpqualscomefromjusttherel'srestrictionclauses*/跑qpquals=extract_nonindex_conditions(path->indexinfo->indrestrictinfo,path->indexquals);//从rel的约束条件子句中获取qpquals}if(!enable_indexscan)startup_cost+=disable_cost;//禁用索引扫描/*wedon'tneedtocheckenable_indexonlyscan;indxpath.cdoesthat*//**Callindex-access-method-specificcodetoestimatetheprocessingcost*forscanningtheindex,aswellastheselectivityoftheindex(ie,*thefractionofmain-tabletupleswewillhavetoretrieve)andits*correlationtothemain-tabletupleorder.Weneedacastherebecause*relation.husesaweakfunctiontypetoavoidincludingamapi.h.*/amcostestimate=(amcostestimate_function)index->amcostestimate;//索引访问路径成本估算函数amcostestimate(root,path,loop_count,&indexStartupCost,&indexTotalCost,&indexSelectivity,&indexCorrelation,&index_pages);//调用函数btcostestimate/**Saveamcostestimate'sresultsforpossibleuseinbitmapscanplanning.*Wedon'tbothertosaveindexStartupCostorindexCorrelation,becausea*bitmapscandoesn'tcareabouteither.*/path->indextotalcost=indexTotalCost;//赋值path->indexselectivity=indexSelectivity;/*allcostsfortouchingindexitselfincludedhere*/startup_cost+=indexStartupCost;run_cost+=indexTotalCost-indexStartupCost;/*estimatenumberofmain-tabletuplesfetched*/tuples_fetched=clamp_row_est(indexSelectivity*baserel->tuples);//取得的元组数量/*fetchestimatedpagecostsfortablespacecontainingtable*/get_tablespace_page_costs(baserel->reltablespace,&spc_random_page_cost,&spc_seq_page_cost);//表空间访问page成本/*----------*Estimatenumberofmain-tablepagesfetched,andcomputeI/Ocost.**Whentheindexorderingisuncorrelatedwiththetableordering,*weuseanapproximationproposedbyMackertandLohman(see*index_pages_fetched()fordetails)tocomputethenumberofpages*fetched,andthenchargespc_random_page_costperpagefetched.**Whentheindexorderingisexactlycorrelatedwiththetableordering*(justafteraCLUSTER,forexample),thenumberofpagesfetchedshould*beexactlyselectivity*table_size.What'smore,allbutthefirst*willbesequentialfetches,nottherandomfetchesthatoccurinthe*uncorrelatedcase.Soifthenumberofpagesismorethan1,we*oughttocharge*spc_random_page_cost+(pages_fetched-1)*spc_seq_page_cost*Forpartially-correlatedindexes,weoughttochargesomewherebetween*thesetwoestimates.Wecurrentlyinterpolatelinearlybetweenthe*estimatesbasedonthecorrelationsquared(XXXisthatappropriate?).**Ifit'sanindex-onlyscan,thenwewillnotneedtofetchanyheap*pagesforwhichthevisibilitymapshowsalltuplesarevisible.*Hence,reducetheestimatednumberofheapfetchesaccordingly.*Weusethemeasuredfractionoftheentireheapthatisall-visible,*whichmightnotbeparticularlyrelevanttothesubsetoftheheap*thatthisquerywillfetch;butit'snotclearhowtodobetter.*----------*/if(loop_count>1)//次数>1{/**Forrepeatedindexscans,theappropriateestimateforthe*uncorrelatedcaseistoscaleupthenumberoftuplesfetchedin*theMackertandLohmanformulabythenumberofscans,sothatwe*estimatethenumberofpagesfetchedbyallthescans;then*pro-ratethecostsforonescan.Inthiscaseweassumeallthe*fetchesarerandomaccesses.*/pages_fetched=index_pages_fetched(tuples_fetched*loop_count,baserel->pages,(double)index->pages,root);if(indexonly)pages_fetched=ceil(pages_fetched*(1.0-baserel->allvisfrac));rand_heap_pages=pages_fetched;max_IO_cost=(pages_fetched*spc_random_page_cost)/loop_count;/**Intheperfectlycorrelatedcase,thenumberofpagestouchedby*eachscanisselectivity*table_size,andwecanusetheMackert*andLohmanformulaatthepageleveltoestimatehowmuchworkis*savedbycachingacrossscans.Westillassumeallthefetchesare*random,though,whichisanoverestimatethat'shardtocorrectfor*withoutdouble-countingthecacheeffects.(Butinmostcases*wheresuchaplanisactuallyinteresting,onlyonepagewouldget*fetchedperscananyway,soitshouldn'tmattermuch.)*/pages_fetched=ceil(indexSelectivity*(double)baserel->pages);pages_fetched=index_pages_fetched(pages_fetched*loop_count,baserel->pages,(double)index->pages,root);if(indexonly)pages_fetched=ceil(pages_fetched*(1.0-baserel->allvisfrac));min_IO_cost=(pages_fetched*spc_random_page_cost)/loop_count;}else//次数<=1{/**Normalcase:applytheMackertandLohmanformula,andthen*interpolatebetweenthatandthecorrelation-derivedresult.*/pages_fetched=index_pages_fetched(tuples_fetched,baserel->pages,(double)index->pages,root);//取得的page数量if(indexonly)pages_fetched=ceil(pages_fetched*(1.0-baserel->allvisfrac));//纯索引扫描rand_heap_pages=pages_fetched;//随机访问的堆page数量/*max_IO_costisfortheperfectlyuncorrelatedcase(csquared=0)*///最大IO成本,假定所有的page都是随机访问获得(csquared=0)max_IO_cost=pages_fetched*spc_random_page_cost;/*min_IO_costisfortheperfectlycorrelatedcase(csquared=1)*///最小IO成本,假定索引和堆数据都是顺序存储(csquared=1)pages_fetched=ceil(indexSelectivity*(double)baserel->pages);if(indexonly)pages_fetched=ceil(pages_fetched*(1.0-baserel->allvisfrac));if(pages_fetched>0){min_IO_cost=spc_random_page_cost;if(pages_fetched>1)min_IO_cost+=(pages_fetched-1)*spc_seq_page_cost;}elsemin_IO_cost=0;}if(partial_path)//并行{/**Forindexonlyscanscomputeworkersbasedonnumberofindexpages*fetched;thenumberofheappageswefetchmightbesosmallasto*effectivelyruleoutparallelism,whichwedon'twanttodo.*/if(indexonly)rand_heap_pages=-1;/**Estimatethenumberofparallelworkersrequiredtoscanindex.Use*thenumberofheappagescomputedconsideringheapfetcheswon'tbe*sequentialasforparallelscansthepagesareaccessedinrandom*order.*/path->path.parallel_workers=compute_parallel_worker(baserel,rand_heap_pages,index_pages,max_parallel_workers_per_gather);/**Falloutifworkerscan'tbeassignedforparallelscan,becausein*suchacasethispathwillberejected.Sothereisnobenefitin*doingextracomputation.*/if(path->path.parallel_workers<=0)return;path->path.parallel_aware=true;}/**Nowinterpolatebasedonestimatedindexordercorrelationtogettotal*diskI/Ocostformaintableaccesses.*根据估算的索引顺序关联来插值，以获得主表访问的总I/O成本*/csquared=indexCorrelation*indexCorrelation;run_cost+=max_IO_cost+csquared*(min_IO_cost-max_IO_cost);/**EstimateCPUcostspertuple.*估算处理每个元组的CPU成本**Whatwewanthereiscpu_tuple_costplustheevaluationcostsofany*qualclausesthatwehavetoevaluateasqpquals.*/cost_qual_eval(&qpqual_cost,qpquals,root);startup_cost+=qpqual_cost.startup;cpu_per_tuple=cpu_tuple_cost+qpqual_cost.per_tuple;cpu_run_cost+=cpu_per_tuple*tuples_fetched;/*tlistevalcostsarepaidperoutputrow,notpertuplescanned*/startup_cost+=path->path.pathtarget->cost.startup;cpu_run_cost+=path->path.pathtarget->cost.per_tuple*path->path.rows;/*Adjustcostingforparallelism,ifused.*/if(path->path.parallel_workers>0){doubleparallel_divisor=get_parallel_divisor(&path->path);path->path.rows=clamp_row_est(path->path.rows/parallel_divisor);/*TheCPUcostisdividedamongalltheworkers.*/cpu_run_cost/=parallel_divisor;}run_cost+=cpu_run_cost;path->path.startup_cost=startup_cost;path->path.total_cost=startup_cost+run_cost;}//-------------------------btcostestimatevoidbtcostestimate(PlannerInfo*root,IndexPath*path,doubleloop_count,Cost*indexStartupCost,Cost*indexTotalCost,Selectivity*indexSelectivity,double*indexCorrelation,double*indexPages){IndexOptInfo*index=path->indexinfo;List*qinfos;GenericCostscosts;Oidrelid;AttrNumbercolnum;VariableStatDatavardata;doublenumIndexTuples;CostdescentCost;List*indexBoundQuals;intindexcol;booleqQualHere;boolfound_saop;boolfound_is_null_op;doublenum_sa_scans;ListCell*lc;/*Dopreliminaryanalysisofindexquals*/qinfos=deconstruct_indexquals(path);//拆解路径,生成条件链表/**Forabtreescan,onlyleading'='qualsplusinequalityqualsforthe*immediatelynextattributecontributetoindexselectivity(theseare*the"boundaryquals"thatdeterminethestartingandstoppingpointsof*theindexscan).Additionalqualscansuppressvisitstotheheap,so*it'sOKtocounttheminindexSelectivity,buttheyshouldnotcount*forestimatingnumIndexTuples.Sowemustexaminethegivenindexquals*tofindoutwhichonescountasboundaryquals.Werelyonthe*knowledgethattheyaregiveninindexcolumnorder.*对于btree扫描，只有下一个属性的前导'='条件加上不等号条件*有助于索引选择性(这些是确定索引扫描起始和停止的“边界条件”)。*额外的条件可以抑制对堆数据的访问，所以在indexSelectivity中*统计它们是可以的，但是它们不应该在估算索引元组数目(索引也是元组的一种)的时*候统计。因此，必须检查给定的索引条件，以找出哪些被*算作边界条件。需依赖索引信息给出的索引列顺序进行判断.**ForaRowCompareExpr,weconsideronlythefirstcolumn,justas*rowcomparesel()does.**Ifthere'saScalarArrayOpExprinthequals,we'llactuallyperformN*indexscansnotone,buttheScalarArrayOpExpr'soperatorcanbe*consideredtoactthesameasitnormallydoes.*/indexBoundQuals=NIL;//索引边界条件indexcol=0;//索引列编号eqQualHere=false;//found_saop=false;found_is_null_op=false;num_sa_scans=1;foreach(lc,qinfos)//遍历条件链表{IndexQualInfo*qinfo=(IndexQualInfo*)lfirst(lc);RestrictInfo*rinfo=qinfo->rinfo;Expr*clause=rinfo->clause;Oidclause_op;intop_strategy;if(indexcol!=qinfo->indexcol)//indexcol匹配才进行后续处理{/*Beginningofanewcolumn'squals*/if(!eqQualHere)break;/*doneifno'='qualforindexcol*/eqQualHere=false;indexcol++;if(indexcol!=qinfo->indexcol)break;/*noqualsatallforindexcol*/}if(IsA(clause,ScalarArrayOpExpr))//ScalarArrayOpExpr{intalength=estimate_array_length(qinfo->other_operand);found_saop=true;/*countupnumberofSAscansinducedbyindexBoundQualsonly*/if(alength>1)num_sa_scans*=alength;}elseif(IsA(clause,NullTest)){NullTest*nt=(NullTest*)clause;if(nt->nulltesttype==IS_NULL){found_is_null_op=true;/*ISNULLislike=forselectivitydeterminationpurposes*/eqQualHere=true;}}/**Wewouldneedtocommutetheclause_opifnotvaronleft,except*thatweonlycareifit'sequalityornot,sothatrefinementis*unnecessary.*/clause_op=qinfo->clause_op;/*checkforequalityoperator*/if(OidIsValid(clause_op))//普通的操作符{op_strategy=get_op_opfamily_strategy(clause_op,index->opfamily[indexcol]);Assert(op_strategy!=0);/*notamemberofopfamily??*/if(op_strategy==BTEqualStrategyNumber)eqQualHere=true;}indexBoundQuals=lappend(indexBoundQuals,rinfo);}/**Ifindexisuniqueandwefoundan'='clauseforeachcolumn,wecan*justassumenumIndexTuples=1andskiptheexpensive*clauselist_selectivitycalculations.However,aScalarArrayOpor*NullTestinvalidatesthattheory,eventhoughitsetseqQualHere.*如果index是唯一的，并且我们为每个列找到了一个'='子句，那么可以*假设numIndexTuples=1，并跳过昂贵的clauselist_selectivity计算结果。*这种判断不适用于ScalarArrayOp或NullTest。*/if(index->unique&&indexcol==index->nkeycolumns-1&&eqQualHere&&!found_saop&&!found_is_null_op)numIndexTuples=1.0;//唯一索引else//非唯一索引{List*selectivityQuals;SelectivitybtreeSelectivity;//选择率/**Iftheindexispartial,ANDtheindexpredicatewiththe*index-boundqualstoproduceamoreaccurateideaofthenumberof*rowscoveredbytheboundconditions.*/selectivityQuals=add_predicate_to_quals(index,indexBoundQuals);//添加谓词btreeSelectivity=clauselist_selectivity(root,selectivityQuals,index->rel->relid,JOIN_INNER,NULL);//获取选择率numIndexTuples=btreeSelectivity*index->rel->tuples;//索引元组数目/**Asingenericcostestimate(),wehavetoadjustforany*ScalarArrayOpExprqualsincludedinindexBoundQuals,andthenround*tointeger.*/numIndexTuples=rint(numIndexTuples/num_sa_scans);}/**Nowdogenericindexcostestimation.*执行常规的索引成本估算*/MemSet(&costs,0,sizeof(costs));costs.numIndexTuples=numIndexTuples;genericcostestimate(root,path,loop_count,qinfos,&costs);/**AddaCPU-costcomponenttorepresentthecostsofinitialbtree*descent.Wedon'tchargeanyI/Ocostfortouchingupperbtreelevels,*sincetheytendtostayincache,butwestillhavetodoaboutlog2(N)*comparisonstodescendabtreeofNleaftuples.Wechargeone*cpu_operator_costpercomparison.*添加一个cpu成本组件来表示初始化BTree树层次下降的成本。*BTree上层节点可以认为已存在于缓存中,因此不耗成本,但沿着树往下沉时,需要*执行log2(N)次比较(N个叶子元组的BTree)。每次比较,成本为cpu_operator_cost**IfthereareScalarArrayOpExprs,chargethisonceperSAscan.The*onesafterthefirstonearenotstartupcostsofarastheoverall*planisconcerned,soaddthemonlyto"total"cost.*如存在ScalarArrayOpExprs,则每次SA扫描成本增加cpu_operator_cost*/if(index->tuples>1)/*avoidcomputinglog(0)*/{descentCost=ceil(log(index->tuples)/log(2.0))*cpu_operator_cost;costs.indexStartupCost+=descentCost;costs.indexTotalCost+=costs.num_sa_scans*descentCost;}/**Eventhoughwe'renotchargingI/Ocostfortouchingupperbtreepages,*it'sstillreasonabletochargesomeCPUcostperpagedescended*through.Moreover,ifwehadnosuchchargeatall,bloatedindexes*wouldappeartohavethesamesearchcostasunbloatedones,atleast*incaseswhereonlyasingleleafpageisexpectedtobevisited.This*costissomewhatarbitrarilysetat50xcpu_operator_costperpage*touched.Thenumberofsuchpagesisbtreetreeheightplusone(ie,*wechargefortheleafpagetoo).Asabove,chargeonceperSAscan.*BTree树往下遍历时的成本descentCost=(树高+1)*50*cpu_operator_cost*/descentCost=(index->tree_height+1)*50.0*cpu_operator_cost;costs.indexStartupCost+=descentCost;costs.indexTotalCost+=costs.num_sa_scans*descentCost;/**Ifwecangetanestimateofthefirstcolumn'sorderingcorrelationC*frompg_statistic,estimatetheindexcorrelationasCfora*single-columnindex,orC*0.75formultiplecolumns.(Theideahere*isthatmultiplecolumnsdilutetheimportanceofthefirstcolumn's*ordering,butdon'tnegateitentirely.Before8.0wedividedthe*correlationbythenumberofcolumns,butthatseemstoostrong.)*如果我们可以从pg_statistical中得到第一列排序相关C的估计，那么对于单列索引，*可以将索引相关性估计为C，对于多列，可以将其估计为C*0.75。*(这里的想法是，多列淡化了第一列排序的重要性，但不要完全否定它。*在8.0之前，我们将相关性除以列数，这种做法似乎太过了)。*/MemSet(&vardata,0,sizeof(vardata));if(index->indexkeys[0]!=0){/*Simplevariable---looktostatsfortheunderlyingtable*/RangeTblEntry*rte=planner_rt_fetch(index->rel->relid,root);Assert(rte->rtekind==RTE_RELATION);relid=rte->relid;Assert(relid!=InvalidOid);colnum=index->indexkeys[0];if(get_relation_stats_hook&&(*get_relation_stats_hook)(root,rte,colnum,&vardata)){/**Thehooktookcontrolofacquiringastatstuple.Ifitdid*supplyatuple,it'dbetterhavesuppliedafreefunc.*/if(HeapTupleIsValid(vardata.statsTuple)&&!vardata.freefunc)elog(ERROR,"nofunctionprovidedtoreleasevariablestatswith");}else{vardata.statsTuple=SearchSysCache3(STATRELATTINH,ObjectIdGetDatum(relid),Int16GetDatum(colnum),BoolGetDatum(rte->inh));vardata.freefunc=ReleaseSysCache;}}else{/*Expression---maybetherearestatsfortheindexitself*/relid=index->indexoid;colnum=1;if(get_index_stats_hook&&(*get_index_stats_hook)(root,relid,colnum,&vardata)){/**Thehooktookcontrolofacquiringastatstuple.Ifitdid*supplyatuple,it'dbetterhavesuppliedafreefunc.*/if(HeapTupleIsValid(vardata.statsTuple)&&!vardata.freefunc)elog(ERROR,"nofunctionprovidedtoreleasevariablestatswith");}else{vardata.statsTuple=SearchSysCache3(STATRELATTINH,ObjectIdGetDatum(relid),Int16GetDatum(colnum),BoolGetDatum(false));vardata.freefunc=ReleaseSysCache;}}if(HeapTupleIsValid(vardata.statsTuple)){Oidsortop;AttStatsSlotsslot;sortop=get_opfamily_member(index->opfamily[0],index->opcintype[0],index->opcintype[0],BTLessStrategyNumber);if(OidIsValid(sortop)&&get_attstatsslot(&sslot,vardata.statsTuple,STATISTIC_KIND_CORRELATION,sortop,ATTSTATSSLOT_NUMBERS)){doublevarCorrelation;Assert(sslot.nnumbers==1);varCorrelation=sslot.numbers[0];if(index->reverse_sort[0])varCorrelation=-varCorrelation;if(index->ncolumns>1)costs.indexCorrelation=varCorrelation*0.75;elsecosts.indexCorrelation=varCorrelation;free_attstatsslot(&sslot);}}ReleaseVariableStats(vardata);*indexStartupCost=costs.indexStartupCost;*indexTotalCost=costs.indexTotalCost;*indexSelectivity=costs.indexSelectivity;*indexCorrelation=costs.indexCorrelation;*indexPages=costs.numIndexPages;}//-------------------------index_pages_fetched/**index_pages_fetched*Estimatethenumberofpagesactuallyfetchedafteraccountingfor*cacheeffects.*估算在考虑缓存影响后实际获取的页面数量。**估算方法是Mackert和Lohman提出的方法:*"IndexScansUsingaFiniteLRUBuffer:AValidatedI/OModel"*WeuseanapproximationproposedbyMackertandLohman,"IndexScans*UsingaFiniteLRUBuffer:AValidatedI/OModel",ACMTransactions*onDatabaseSystems,Vol.14,No.3,September1989,Pages401-424.*TheMackertandLohmanapproximationisthatthenumberofpages*fetchedis*PF=*min(2TNs/(2T+Ns),T)whenT<=b*2TNs/(2T+Ns)whenT>bandNs<=2Tb/(2T-b)*b+(Ns-2Tb/(2T-b))*(T-b)/TwhenT>bandNs>2Tb/(2T-b)*where*T=#pagesintable*N=#tuplesintable*s=selectivity=fractionoftabletobescanned*b=#bufferpagesavailable(weincludekernelspacehere)**Weassumethateffective_cache_sizeisthetotalnumberofbufferpages*availableforthewholequery,andpro-ratethatspaceacrossallthe*tablesinthequeryandtheindexcurrentlyunderconsideration.(This*ignoresspaceneededforotherindexesusedbythequery,butsincewe*don'tknowwhichindexeswillgetused,wecan'testimatethatverywell;*andinanycasecountingallthetablesmaywellbeanoverestimate,since*dependingonthejoinplannotallthetablesmaybescannedconcurrently.)**TheproductNsisthenumberoftuplesfetched;wepassinthat*productratherthancalculatingithere."pages"isthenumberofpages*intheobjectunderconsideration(eitheranindexoratable).*"index_pages"istheamounttoaddtothetotaltablespace,whichwas*computedforusbyquery_planner.**Callerisexpectedtohaveensuredthattuples_fetchedisgreaterthanzero*androundedtointeger(seeclamp_row_est).Theresultwilllikewisebe*greaterthanzeroandintegral.*/doubleindex_pages_fetched(doubletuples_fetched,BlockNumberpages,doubleindex_pages,PlannerInfo*root){doublepages_fetched;doubletotal_pages;doubleT,b;/*Tis#pagesintable,butdon'tallowittobezero*/T=(pages>1)?(double)pages:1.0;/*Computenumberofpagesassumedtobecompetingforcachespace*/total_pages=root->total_table_pages+index_pages;total_pages=Max(total_pages,1.0);Assert(T<=total_pages);/*bispro-ratedshareofeffective_cache_size*/b=(double)effective_cache_size*T/total_pages;/*forceitpositiveandintegral*/if(b<=1.0)b=1.0;elseb=ceil(b);/*ThispartistheMackertandLohmanformula*/if(T<=b){pages_fetched=(2.0*T*tuples_fetched)/(2.0*T+tuples_fetched);if(pages_fetched>=T)pages_fetched=T;elsepages_fetched=ceil(pages_fetched);}else{doublelim;lim=(2.0*T*b)/(2.0*T-b);if(tuples_fetched<=lim){pages_fetched=(2.0*T*tuples_fetched)/(2.0*T+tuples_fetched);}else{pages_fetched=b+(tuples_fetched-lim)*(T-b)/T;}pages_fetched=ceil(pages_fetched);}returnpages_fetched;}三、跟踪分析

测试脚本如下

selecta.*,b.grbh,b.jefromt_dwxxa,lateral(selectt1.dwbh,t1.grbh,t2.jefromt_grxxt1innerjoint_jfxxt2ont1.dwbh=a.dwbhandt1.grbh=t2.grbh)bwherea.dwbh='1001'orderbyb.dwbh;

启动gdb

(gdb)bcreate_index_pathBreakpoint1at0x78f050:filepathnode.c,line1037.(gdb)cContinuing.

主要考察t_grxx上的索引访问路径,即t_grxx.dwbh = '1001'(通过等价类产生并下推的限制条件)

(gdb)cContinuing.Breakpoint1,create_index_path(root=0x2737d70,index=0x274be80,indexclauses=0x274f1f8,indexclausecols=0x274f248,indexorderbys=0x0,indexorderbycols=0x0,pathkeys=0x0,indexscandir=ForwardScanDirection,indexonly=false,required_outer=0x0,loop_count=1,partial_path=false)atpathnode.c:10371037IndexPath*pathnode=makeNode(IndexPath);

索引信息:树高度为1/索引列1个/indexlist链表,元素为TargetEntry,相关信息为varno = 3, varattno = 1,索引访问方法成本估算使用的函数为btcostestimate

(gdb)p*index$3={type=T_IndexOptInfo,indexoid=16752,reltablespace=0,rel=0x274b870,pages=276,tuples=100000,tree_height=1,ncolumns=1,nkeycolumns=1,indexkeys=0x274bf90,indexcollations=0x274bfa8,opfamily=0x274bfc0,opcintype=0x274bfd8,sortopfamily=0x274bfc0,reverse_sort=0x274c008,nulls_first=0x274c020,canreturn=0x274bff0,relam=403,indexprs=0x0,indpred=0x0,indextlist=0x274c0f8,indrestrictinfo=0x274dc58,predOK=false,unique=false,immediate=true,hypothetical=false,amcanorderbyop=false,amoptionalkey=true,amsearcharray=true,amsearchnulls=true,amhasgettuple=true,amhasgetbitmap=true,amcanparallel=true,amcostestimate=0x94f0ad<btcostestimate>}

执行各项赋值操作

(gdb)n1038RelOptInfo*rel=index->rel;(gdb)1042pathnode->path.pathtype=indexonly?T_IndexOnlyScan:T_IndexScan;(gdb)1043pathnode->path.parent=rel;(gdb)1044pathnode->path.pathtarget=rel->reltarget;(gdb)1045pathnode->path.param_info=get_baserel_parampathinfo(root,rel,(gdb)1047pathnode->path.parallel_aware=false;(gdb)1048pathnode->path.parallel_safe=rel->consider_parallel;(gdb)1049pathnode->path.parallel_workers=0;(gdb)1050pathnode->path.pathkeys=pathkeys;(gdb)1053expand_indexqual_conditions(index,indexclauses,indexclausecols,(gdb)1057pathnode->indexinfo=index;

执行expand_indexqual_conditions,给定RestrictInfo节点(约束条件),产生直接可用的索引表达式子句

(gdb)p*indexclauses$4={type=T_List,length=1,head=0x274f1d8,tail=0x274f1d8}-->t_grxx.dwbh='1001'(gdb)p*indexclausecols$9={type=T_IntList,length=1,head=0x274f228,tail=0x274f228}(gdb)pindexclausecols->head->data.int_value$10=0

进入cost_index函数

(gdb)1065cost_index(pathnode,root,loop_count,partial_path);(gdb)stepcost_index(path=0x274ecb8,root=0x2737d70,loop_count=1,partial_path=false)atcostsize.c:480480IndexOptInfo*index=path->indexinfo;

调用访问方法成本估算函数

...(gdb)547amcostestimate(root,path,loop_count,(gdb)557path->indextotalcost=indexTotalCost;