这篇文章主要讲解了“PostgreSQL物理优化中的create_index_paths->choose_bitmap_and函数分析”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“PostgreSQL物理优化中的create_index_paths->choose_bitmap_and函数分析”吧！

该函数执行Bitmap AND操作后创建位图索引扫描访问路径(BitmapAndPath)节点。

下面是BitmapAnd访问路径的样例:

testdb=#explainverboseselectt1.*testdb-#fromt_dwxxt1testdb-#where(dwbh>'10000'anddwbh<'15000')AND(dwdzbetween'DWDZ10000'and'DWDZ15000');QUERYPLAN----------------------------------------------------------------------------------------------BitmapHeapScanonpublic.t_dwxxt1(cost=32.33..88.38rows=33width=20)Output:dwmc,dwbh,dwdzRecheckCond:(((t1.dwbh)::text>'10000'::text)AND((t1.dwbh)::text<'15000'::text)AND((t1.dwdz)::text>='DWDZ10000'::text)AND((t1.dwdz)::text<='DWDZ15000'::text))->BitmapAnd(cost=32.33..32.33rows=33width=0)-->BitmapAnd->BitmapIndexScanont_dwxx_pkey(cost=0.00..13.86rows=557width=0)IndexCond:(((t1.dwbh)::text>'10000'::text)AND((t1.dwbh)::text<'15000'::text))->BitmapIndexScanonidx_dwxx_dwdz(cost=0.00..18.21rows=592width=0)IndexCond:(((t1.dwdz)::text>='DWDZ10000'::text)AND((t1.dwdz)::text<='DWDZ15000'::text))(8rows)一、数据结构

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数

PathClauseUsage

/*Per-pathdatausedwithinchoose_bitmap_and()*/typedefstruct{Path*path;/*访问路径链表,IndexPath,BitmapAndPath,orBitmapOrPath*/List*quals;/*限制条件子句链表,theWHEREclausesituses*/List*preds;/*部分索引谓词链表,predicatesofitspartialindex(es)*/Bitmapset*clauseids;/*位图集合,quals+predsrepresentedasabitmapset*/}PathClauseUsage;二、源码解读

choose_bitmap_and函数
create_index_paths->choose_bitmap_and函数,该函数给定非空的位图访问路径链表,执行AND操作后合并到一条路径中,最终得到位图索引扫描访问路径节点.

/**choose_bitmap_and*Givenanonemptylistofbitmappaths,ANDthemintoonepath.*给定非空的位图访问路径链表,执行AND操作后合并到一条路径中**Thisisanontrivialdecisionsincewecanlegallyuseanysubsetofthe*givenpathset.Wewanttochooseagoodtradeoffbetweenselectivity*andcostofcomputingthebitmap.*这是一个非常重要的策略，因为这样可以合法地使用给定路径集的任何子集。**Theresultiseitherasingleoneoftheinputs,oraBitmapAndPath*combiningmultipleinputs.*输出结果要么是输出的其中之一,要么是融合多个输入之后的BitmapAndPath*/staticPath*choose_bitmap_and(PlannerInfo*root,RelOptInfo*rel,List*paths){intnpaths=list_length(paths);PathClauseUsage**pathinfoarray;PathClauseUsage*pathinfo;List*clauselist;List*bestpaths=NIL;Costbestcost=0;inti,j;ListCell*l;Assert(npaths>0);/*elsecallererror*/if(npaths==1)return(Path*)linitial(paths);/*easycase*//**Intheoryweshouldconsidereverynonemptysubsetofthegivenpaths.*Inpracticethatseemslikeoverkill,giventhecrudenatureofthe*estimates,nottomentionthepossibleeffectsofhigher-levelANDand*ORclauses.Moreover,it'scompletelyimpracticaliftherearealarge*numberofpaths,sincetheworkwouldgrowasO(2^N).*理论上，我们应该考虑给定路径的所有非空子集。在实践中，*考虑到估算的不确定性和成本，以及更高级别的AND和OR约束可能产生的影响,这样的做法并不合适.*此外,它并不切合实际,如果有大量的路径,这项工作的复杂度会是指数级的O(2^N)。**Asaheuristic,wefirstcheckforpathsusingexactlythesamesetsof*WHEREclauses+indexpredicateconditions,andrejectallbutthe*cheapest-to-scaninanysuchgroup.Thisprimarilygetsridofindexes*thatincludetheinterestingcolumnsbutalsoirrelevantcolumns.(In*situationswheretheDBAhasgoneoverboardoncreatingvariant*indexes,thiscanmakeforaverylargereductioninthenumberof*pathsconsideredfurther.)*作为一种启发式方法，首先使用完全相同的WHERE子句+索引谓词条件集检查路径，*并去掉这类条件组中除成本最低之外的所有路径。*这主要是去掉了包含interesting列和不相关列的索引。*(在DBA过度创建索引的情况下，这会大大减少进一步考虑的路径数量。)**Wethensortthesurvivingpathswiththecheapest-to-scanfirst,and*foreachpath,considerusingthatpathaloneasthebasisforabitmap*scan.ThenweconsiderbitmapANDscansformedfromthatpathplus*eachsubsequent(higher-cost)path,addingonasubsequentpathifit*resultsinareductionintheestimatedtotalscancost.Thismeanswe*consideraboutO(N^2)ratherthanO(2^N)pathcombinations,whichis*quitetolerable,especiallygiventhanNisusuallyreasonablysmall*becauseoftheprefilteringstep.Thecheapestoftheseisreturned.*然后，我们首先使用成本最低的扫描路径对现存的路径进行排序，*对于每个路径，考虑单独使用该路径作为位图扫描的基础。*然后我们考虑位图和从该路径形成的扫描加上每个后续的(更高成本的)路径，*如果后续路径导致估算的总扫描成本减少，那么就添加一个后续路径。*这意味着我们只需要处理O(N^2),而不是O(2^N)个路径组合,*这样的成本完全可以接受,特别是N通常相当小时。函数返回成本最低的路径。**WewillonlyconsiderANDcombinationsinwhichnotwoindexesusethe*sameWHEREclause.Thisisabitofakluge:it'sneededbecause*costsize.candclausesel.caren'tverysmartaboutredundantclauses.*Theywillusuallydouble-counttheredundantclauses,producinga*too-smallselectivitythatmakesaredundantANDsteplooklikeit*reducesthetotalcost.Perhapssomedaythatcodewillbesmarterand*wecanremovethislimitation.(Butnotethatthisalsodefends*againstflat-outduplicateinputpaths,whichcanhappenbecause*match_join_clauses_to_indexwillfindthesameORjoinclausesthat*extract_restriction_or_clauseshaspulledORrestrictionclausesout*of.)*我们将只考虑没有两个索引同时使用相同的WHERE子句的AND组合。*这是一个有点蹩脚的做法:之所以这样是因为cost.c和clausesel.c未能足够聪明的处理多余的子句。*它们通常会重复计算冗余子句，从而产生很小的选择性，使冗余子句看起来像是减少了总成本。*也许有一天，代码会变得更聪明，我们可以消除这个限制。*(但是要注意，这也可以防止完全重复的输入路径，*因为match_join_clauses_to_index会找到相同的OR连接子句,而这些子句*已通过extract_restriction_or_clauses函数提升到外面去了.)**Forthesamereason,werejectANDcombinationsinwhichanindex*predicateclauseduplicatesanotherclause.Herewefinditnecessary*tobeevenstricter:we'llrejectapartialindexifanyofits*predicateclausesareimpliedbythesetofWHEREclausesandpredicate*clausesusedsofar.Thiscoverscasessuchasacondition"x=42"*usedwithaplainindex,followedbyaclauselessscanofapartial*index"WHEREx>=40ANDx<50".Thepartialindexhasbeenaccepted*onlybecause"x=42"waspresent,andsoallowingitwouldpartially*double-countselectivity.(Wecouldusepredicate_implied_byon*regularqualclausestoo,tohaveamoreintelligent,butmuchmore*expensive,checkforredundancy---butinmostcasessimpleequality*seemstosuffice.)*出于同样的原因，我们不会组合索引谓词子句与另一个重复的子句。*在这里，有必要更加严格:如果部分索引的任何谓词子句*隐含在WHERE子句中，则不能使用此索引。*这里包括了形如使用普通索引的“x=42”和使用部分索引“x>=40和x<50”的情况。*部分索引被接受，是因为存在“x=42”，因此允许它部分重复计数选择性。*(我们也可以在普通的qual子句上使用predicate_implied_by函数，*这样就可以更智能但更昂贵地检查冗余——但在大多数情况下，简单的等式似乎就足够了。)*//**Extractclauseusageinfoanddetectanypathsthatuseexactlythe*samesetofclauses;keeponlythecheapest-to-scanofanysuchgroups.*Thesurvivingpathsareputintoanarrayforqsort'ing.*提取子句使用信息并检测使用完全相同子句集的所有路径;*只保留这类路径中成本最低的,这些路径被放入一个数组中进行qsort'ing*/pathinfoarray=(PathClauseUsage**)palloc(npaths*sizeof(PathClauseUsage*));//数组clauselist=NIL;npaths=0;foreach(l,paths)//遍历paths{Path*ipath=(Path*)lfirst(l);pathinfo=classify_index_clause_usage(ipath,&clauselist);//归类路径信息for(i=0;i<npaths;i++){if(bms_equal(pathinfo->clauseids,pathinfoarray[i]->clauseids))break;//只要发现子句集一样,就继续执行}if(i<npaths)//发现相同的{/*duplicateclauseids,keepthecheaperone*///相同的约束条件,只保留成本最低的Costncost;Costocost;Selectivitynselec;Selectivityoselec;cost_bitmap_tree_node(pathinfo->path,&ncost,&nselec);//计算成本cost_bitmap_tree_node(pathinfoarray[i]->path,&ocost,&oselec);if(ncost<ocost)pathinfoarray[i]=pathinfo;}else//没有发现条件一样的,添加到数组中{/*notduplicateclauseids,addtoarray*/pathinfoarray[npaths++]=pathinfo;}}/*Ifonlyonesurvivingpath,we'redone*/if(npaths==1)//结果只有一条,则返回之returnpathinfoarray[0]->path;/*Sortthesurvivingpathsbyindexaccesscost*/qsort(pathinfoarray,npaths,sizeof(PathClauseUsage*),path_usage_comparator);//以索引访问成本排序现存路径/**Foreachsurvivingindex,consideritasan"ANDgroupleader",andsee*whetheraddingonanyofthelaterindexesresultsinanANDpathwith*cheapertotalcostthanbefore.ThentakethecheapestANDgroup.*对于现存的索引,把它视为"ANDgroupleader",*并查看是否添加了以后的索引后，会得到一个总成本比以前更低的AND路径。*选择成本最低的AND组.**/for(i=0;i<npaths;i++)//遍历这些路径{Costcostsofar;List*qualsofar;Bitmapset*clauseidsofar;ListCell*lastcell;pathinfo=pathinfoarray[i];//PathClauseUsage结构体paths=list_make1(pathinfo->path);//路径链表costsofar=bitmap_scan_cost_est(root,rel,pathinfo->path);//当前的成本qualsofar=list_concat(list_copy(pathinfo->quals),list_copy(pathinfo->preds));clauseidsofar=bms_copy(pathinfo->clauseids);lastcell=list_head(paths);/*用于快速删除,forquickdeletions*/for(j=i+1;j<npaths;j++)//扫描后续的路径{Costnewcost;pathinfo=pathinfoarray[j];/*Checkforredundancy*/if(bms_overlap(pathinfo->clauseids,clauseidsofar))continue;/*多余的路径,consideritredundant*/if(pathinfo->preds)//部分索引?{boolredundant=false;/*wecheckeachpredicateclauseseparately*///单独检查每一个谓词foreach(l,pathinfo->preds){Node*np=(Node*)lfirst(l);if(predicate_implied_by(list_make1(np),qualsofar,false)){redundant=true;break;/*outofinnerforeachloop*/}}if(redundant)continue;}/*tentativelyaddnewpathtopaths,sowecanestimatecost*///尝试在路径中添加新路径，这样我们就可以估算成本paths=lappend(paths,pathinfo->path);newcost=bitmap_and_cost_est(root,rel,paths);//估算成本if(newcost<costsofar)//新成本更低{/*keepnewpathinpaths,updatesubsidiaryvariables*/costsofar=newcost;qualsofar=list_concat(qualsofar,list_copy(pathinfo->quals));//添加此条件qualsofar=list_concat(qualsofar,list_copy(pathinfo->preds));//添加此谓词clauseidsofar=bms_add_members(clauseidsofar,pathinfo->clauseids);//添加此子句IDlastcell=lnext(lastcell);}else{/*rejectnewpath,removeitfrompathslist*/paths=list_delete_cell(paths,lnext(lastcell),lastcell);//去掉新路径}Assert(lnext(lastcell)==NULL);}/*KeepthecheapestAND-group(orsingleton)*/if(i==0||costsofar<bestcost)//单条路径或者取得最小的成本{bestpaths=paths;bestcost=costsofar;}/*someeasycleanup(wedon'ttryrealhardthough)*/list_free(qualsofar);}if(list_length(bestpaths)==1)return(Path*)linitial(bestpaths);/*无需AND路径,noneedforAND*/return(Path*)create_bitmap_and_path(root,rel,bestpaths);//生成BitmapAndPath}//--------------------------------------------------------------------------bitmap_scan_cost_est/**Estimatethecostofactuallyexecutingabitmapscanwithasingle*indexpath(noBitmapAnd,atleastnotatthislevel;butitcouldbe*aBitmapOr).*/staticCostbitmap_scan_cost_est(PlannerInfo*root,RelOptInfo*rel,Path*ipath){BitmapHeapPathbpath;Relidsrequired_outer;/*Identifyrequiredouterrels,incaseit'saparameterizedscan*/required_outer=get_bitmap_tree_required_outer(ipath);/*SetupadummyBitmapHeapPath*/bpath.path.type=T_BitmapHeapPath;bpath.path.pathtype=T_BitmapHeapScan;bpath.path.parent=rel;bpath.path.pathtarget=rel->reltarget;bpath.path.param_info=get_baserel_parampathinfo(root,rel,required_outer);bpath.path.pathkeys=NIL;bpath.bitmapqual=ipath;/**Checkthecostoftemporarypathwithoutconsideringparallelism.*Parallelbitmapheappathwillbeconsideredatlaterstage.*/bpath.path.parallel_workers=0;cost_bitmap_heap_scan(&bpath.path,root,rel,bpath.path.param_info,ipath,get_loop_count(root,rel->relid,required_outer));//BitmapHeapPath计算成本returnbpath.path.total_cost;}//--------------------------------------------------------------------------bitmap_and_cost_est/**EstimatethecostofactuallyexecutingaBitmapAndscanwiththegiven*inputs.*给定输入,估算实际执行BitmapAnd扫描的实际成本*/staticCostbitmap_and_cost_est(PlannerInfo*root,RelOptInfo*rel,List*paths){BitmapAndPathapath;BitmapHeapPathbpath;Relidsrequired_outer;/*SetupadummyBitmapAndPath*/apath.path.type=T_BitmapAndPath;apath.path.pathtype=T_BitmapAnd;apath.path.parent=rel;apath.path.pathtarget=rel->reltarget;apath.path.param_info=NULL;/*notusedinbitmaptrees*/apath.path.pathkeys=NIL;apath.bitmapquals=paths;cost_bitmap_and_node(&apath,root);/*Identifyrequiredouterrels,incaseit'saparameterizedscan*/required_outer=get_bitmap_tree_required_outer((Path*)&apath);/*SetupadummyBitmapHeapPath*/bpath.path.type=T_BitmapHeapPath;bpath.path.pathtype=T_BitmapHeapScan;bpath.path.parent=rel;bpath.path.pathtarget=rel->reltarget;bpath.path.param_info=get_baserel_parampathinfo(root,rel,required_outer);bpath.path.pathkeys=NIL;bpath.bitmapqual=(Path*)&apath;/**Checkthecostoftemporarypathwithoutconsideringparallelism.*Parallelbitmapheappathwillbeconsideredatlaterstage.*/bpath.path.parallel_workers=0;/*Nowwecandocost_bitmap_heap_scan*/cost_bitmap_heap_scan(&bpath.path,root,rel,bpath.path.param_info,(Path*)&apath,get_loop_count(root,rel->relid,required_outer));//BitmapHeapPath计算成本returnbpath.path.total_cost;}//--------------------------------------------------------------------------create_bitmap_and_path/**create_bitmap_and_path*CreatesapathnoderepresentingaBitmapAnd.*/BitmapAndPath*create_bitmap_and_path(PlannerInfo*root,RelOptInfo*rel,List*bitmapquals){BitmapAndPath*pathnode=makeNode(BitmapAndPath);pathnode->path.pathtype=T_BitmapAnd;pathnode->path.parent=rel;pathnode->path.pathtarget=rel->reltarget;pathnode->path.param_info=NULL;/*notusedinbitmaptrees*//**Currently,aBitmapHeapPath,BitmapAndPath,orBitmapOrPathwillbe*parallel-safeifandonlyifrel->consider_parallelisset.So,wecan*settheflagforthispathbasedonlyontherelation-levelflag,*withoutactuallyiteratingoverthelistofchildren.*/pathnode->path.parallel_aware=false;pathnode->path.parallel_safe=rel->consider_parallel;pathnode->path.parallel_workers=0;pathnode->path.pathkeys=NIL;/*alwaysunordered*/pathnode->bitmapquals=bitmapquals;/*thissetsbitmapselectivityaswellastheregularcostfields:*/cost_bitmap_and_node(pathnode,root);//计算成本returnpathnode;}//-----------------------------------------------------cost_bitmap_and_node/**cost_bitmap_and_node*EstimatethecostofaBitmapAndnode*估算BitmapAnd节点成本**Notethatthisconsidersonlythecostsofindexscanningandbitmap*creation,nottheeventualheapaccess.Inthatsensetheobjectisn't*trulyaPath,butithasenoughpath-likeproperties(costsinparticular)*towarranttreatingitasone.Wedon'tbothertosetthepathrowsfield,*however.*/voidcost_bitmap_and_node(BitmapAndPath*path,PlannerInfo*root){CosttotalCost;Selectivityselec;ListCell*l;/**WeestimateANDselectivityontheassumptionthattheinputsare*independent.Thisisprobablyoftenwrong,butwedon'thavetheinfo*todobetter.**TheruntimecostoftheBitmapAnditselfisestimatedat100x*cpu_operator_costforeachtbm_intersectneeded.Probablytoosmall,*definitelytoosimplistic?*/totalCost=0.0;selec=1.0;foreach(l,path->bitmapquals){Path*subpath=(Path*)lfirst(l);CostsubCost;Selectivitysubselec;cost_bitmap_tree_node(subpath,&subCost,&subselec);selec*=subselec;totalCost+=subCost;if(l!=list_head(path->bitmapquals))totalCost+=100.0*cpu_operator_cost;}path->bitmapselectivity=selec;path->path.rows=0;/*perabove,notused*/path->path.startup_cost=totalCost;path->path.total_cost=totalCost;}三、跟踪分析

测试脚本如下

selectt1.*fromt_dwxxt1where(dwbh>'10000'anddwbh<'15000')AND(dwdzbetween'DWDZ10000'and'DWDZ15000');

启动gdb跟踪

(gdb)bchoose_bitmap_andBreakpoint1at0x74e8c2:fileindxpath.c,line1372.(gdb)cContinuing.Breakpoint1,choose_bitmap_and(root=0x1666638,rel=0x1666a48,paths=0x166fdf0)atindxpath.c:13721372intnpaths=list_length(paths);

输入参数

(gdb)p*paths$1={type=T_List,length=2,head=0x166fe20,tail=0x16706b8}(gdb)p*(Node*)paths->head->data.ptr_value$2={type=T_IndexPath}(gdb)p*(Node*)paths->head->next->data.ptr_value$3={type=T_IndexPath}(gdb)set$p1=(IndexPath*)paths->head->data.ptr_value(gdb)set$p2=(IndexPath*)paths->head->next->data.ptr_value(gdb)p*$p1$4={path={type=T_IndexPath,pathtype=T_IndexScan,parent=0x1666a48,pathtarget=0x166d988,param_info=0x0,parallel_aware=false,parallel_safe=true,parallel_workers=0,rows=33,startup_cost=0.28500000000000003,total_cost=116.20657683302848,pathkeys=0x0},indexinfo=0x166e420,indexclauses=0x166f528,indexquals=0x166f730,indexqualcols=0x166f780,indexorderbys=0x0,indexorderbycols=0x0,indexscandir=ForwardScanDirection,indextotalcost=18.205000000000002,indexselectivity=0.059246954595791879}(gdb)p*$p2$5={path={type=T_IndexPath,pathtype=T_IndexScan,parent=0x1666a48,pathtarget=0x166d988,param_info=0x0,parallel_aware=false,parallel_safe=true,parallel_workers=0,rows=33,startup_cost=0.28500000000000003,total_cost=111.33157683302848,pathkeys=0x0},indexinfo=0x1666c58,indexclauses=0x166fed0,indexquals=0x166ffc8,indexqualcols=0x1670018,indexorderbys=0x0,indexorderbycols=0x0,indexscandir=ForwardScanDirection,indextotalcost=13.855,indexselectivity=0.055688888888888899}

paths中的第1个元素对应(dwbh > '10000' and dwbh < '15000') ,第2个元素对应(dwdz between 'DWDZ10000' and 'DWDZ15000')

(gdb)set$ri1=(RestrictInfo*)$p1->indexclauses->head->data.ptr_value(gdb)set$tmp=(RelabelType*)((OpExpr*)$ri1->clause)->args->head->data.ptr_value(gdb)p*(Var*)$tmp->arg$17={xpr={type=T_Var},varno=1,varattno=3,vartype=1043,vartypmod=104,varcollid=100,varlevelsup=0,varnoold=1,varoattno=3,location=76}(gdb)p*(Node*)((OpExpr*)$ri1->clause)->args->head->next->data.ptr_value$18={type=T_Const}(gdb)p*(Const*)((OpExpr*)$ri1->clause)->args->head->next->data.ptr_value$19={xpr={type=T_Const},consttype=25,consttypmod=-1,constcollid=100,constlen=-1,constvalue=23636608,constisnull=false,constbyval=false,location=89}

开始遍历paths,提取子句条件并检测是否使用完全相同子句集的所有路径,只保留这些路径中成本最低的,这些路径被放入一个数组中进行qsort.

...(gdb)1444npaths=0;(gdb)1445foreach(l,paths)(gdb)

收集信息到PathClauseUsage数组中

...(gdb)n1471pathinfoarray[npaths++]=pathinfo;(gdb)1445foreach(l,paths)(gdb)1476if(npaths==1)(gdb)pnpaths$26=2(gdb)

按成本排序

(gdb)n1480qsort(pathinfoarray,npaths,sizeof(PathClauseUsage*),

遍历路径,找到成本最低的AND group

1488for(i=0;i<npaths;i++)(gdb)n1495pathinfo=pathinfoarray[i];(gdb)1496paths=list_make1(pathinfo->path);(gdb)1497costsofar=bitmap_scan_cost_est(root,rel,pathinfo->path);(gdb)1499list_copy(pathinfo->preds));

获取当前的成本,设置当前的条件子句

(gdb)pcostsofar$27=89.003250000000008(gdb)n1498qualsofar=list_concat(list_copy(pathinfo->quals),

执行AND操作(路径叠加),成本更低,调整当前成本和相关变量

(gdb)n1531newcost=bitmap_and_cost_est(root,rel,paths);(gdb)1532if(newcost<costsofar)(gdb)pnewcost$30=88.375456720095343(gdb)n1535costsofar=newcost;(gdb)n1537list_copy(pathinfo->quals));(gdb)1536qualsofar=list_concat(qualsofar,(gdb)1539list_copy(pathinfo->preds));

处理下一个AND条件,单个AND条件比上一个条件成本高,保留原来的

1488for(i=0;i<npaths;i++)(gdb)1495pathinfo=pathinfoarray[i];(gdb)1496paths=list_make1(pathinfo->path);(gdb)1497costsofar=bitmap_scan_cost_est(root,rel,pathinfo->path);(gdb)1499list_copy(pathinfo->preds));(gdb)pcostsofar$34=94.053250000000006(gdb)n1498qualsofar=list_concat(list_copy(pathinfo->quals),(gdb)1500clauseidsofar=bms_copy(pathinfo->clauseids);(gdb)1501lastcell=list_head(paths);/*forquickdeletions*/(gdb)1503for(j=i+1;j<npaths;j++)(gdb)1553if(i==0||costsofar<bestcost)(gdb)pi$35=1(gdb)pcostsofar$36=94.053250000000006(gdb)pbestcost$37=88.375456720095343(gdb)

构建BitmapAndPath,返回

(gdb)n1563if(list_length(bestpaths)==1)(gdb)1565return(Path*)create_bitmap_and_path(root,rel,bestpaths);(gdb)1566}

DONE!

(gdb)ncreate_index_paths(root=0x1666638,rel=0x1666a48)atindxpath.c:337337bpath=create_bitmap_heap_path(root,rel,bitmapqual,

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