本篇内容介绍了“PostgreSQL中set_base_rel_sizes函数及其子函数案例分析”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

make_one_rel源代码:

RelOptInfo*make_one_rel(PlannerInfo*root,List*joinlist){RelOptInfo*rel;Indexrti;/**Constructtheall_baserelsRelidsset.*/root->all_baserels=NULL;for(rti=1;rti<root->simple_rel_array_size;rti++)//遍历RelOptInfo{RelOptInfo*brel=root->simple_rel_array[rti];/*theremaybeemptyslotscorrespondingtonon-baserelRTEs*/if(brel==NULL)continue;Assert(brel->relid==rti);/*sanitycheckonarray*//*ignoreRTEsthatare"otherrels"*/if(brel->reloptkind!=RELOPT_BASEREL)continue;root->all_baserels=bms_add_member(root->all_baserels,brel->relid);//添加到all_baserels遍历中}/*Markbaserelsastowhetherwecareaboutfast-startplans*///设置RelOptInfo的consider_param_startup变量,是否考量fast-startplansset_base_rel_consider_startup(root);/**Computesizeestimatesandconsider_parallelflagsforeachbaserel,*thengenerateaccesspaths.*/set_base_rel_sizes(root);//估算Relation的Size并且设置consider_parallel标记set_base_rel_pathlists(root);//生成Relation的扫描(访问)路径/**Generateaccesspathsfortheentirejointree.*通过动态规划或遗传算法生成连接路径*/rel=make_rel_from_joinlist(root,joinlist);/**Theresultshouldjoinallandonlythequery'sbaserels.*/Assert(bms_equal(rel->relids,root->all_baserels));//返回最上层的RelOptInforeturnrel;}一、数据结构

RelOptInfo

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

make_one_rel函数调用了set_base_rel_sizes,该函数的主要实现逻辑通过调用set_rel_size实现.现重点考察函数set_rel_size中对基础关系进行估算的处理逻辑,即函数set_plain_rel_size的实现逻辑.
set_plain_rel_size

/**set_plain_rel_size*Setsizeestimatesforaplainrelation(nosubquery,noinheritance)*/staticvoidset_plain_rel_size(PlannerInfo*root,RelOptInfo*rel,RangeTblEntry*rte){/**Testanypartialindexesofrelforapplicability.Wemustdothis*firstsincepartialuniqueindexescanaffectsizeestimates.*/check_index_predicates(root,rel);//验证部分(条件)索引的可用性/*Markrelwithestimatedoutputrows,width,etc*/set_baserel_size_estimates(root,rel);//标记rel的输出行数/行宽等信息}

set_plain_rel_size->check_index_predicates
如果部分(条件)索引的谓词与查询语句相匹配,则predOK设置为true
比如:
数据表t1(c1 int,c2 varchar(40),...),如存在索引idx_t1_partial_c1,条件为where c1 > 100
查询条件为where c1 > 100(是否支持>200?),那么该谓词与查询条件相匹配

//-----------------------------------------check_index_predicates/**check_index_predicates*Setthepredicate-derivedIndexOptInfofieldsforeachindex*ofthespecifiedrelation.**predOKissettrueiftheindexispartialanditspredicateissatisfied*forthisquery,iethequery'sWHEREclausesimplythepredicate.*如果部分(条件)索引的谓词与查询语句相匹配,则predOK设置为true*比如:*数据表t1(c1int,c2varchar(40),...),如存在索引idx_t1_partial_c1,条件为wherec1>100*查询条件为wherec1>100(是否支持>200?),那么该谓词与查询条件相匹配**indrestrictinfoissettotherelation'sbaserestrictinfolistlessany*conditionsthatareimpliedbytheindex'spredicate.(Obviously,fora*non-partialindex,thisisthesameasbaserestrictinfo.)Suchconditions*canbedroppedfromtheplanwhenusingtheindex,incertaincases.**indrestrictinfo会加入到rel的baserestrictinfo链表中,减少了索引谓词所隐含的限制条件.**Atonetimeitwaspossibleforthistogetre-runafteraddingmore*restrictionstotherel,thuspossiblylettingusprovemoreindexesOK.*Thatdoesn'thappenanymore(atleastnotinthecorecode'susage),*butthiscodestillsupportsitincaseextensionswanttomesswiththe*baserestrictinfolist.Weassumethataddingmorerestrictionscan'tmake*anindexnotpredOK.Wemustrecomputeindrestrictinfoeachtime,though,*tomakesureanynewly-addedrestrictionsgetintoitifneeded.*/voidcheck_index_predicates(PlannerInfo*root,RelOptInfo*rel){List*clauselist;//条件链表boolhave_partial;//是否包含部分索引boolis_target_rel;//目标rel?Relidsotherrels;//RelidsListCell*lc;//临时变量/*Indexesareavailableonlyonbaseor"other"memberrelations.*/Assert(IS_SIMPLE_REL(rel));//rel必须是基础关系/**Initializetheindrestrictinfoliststobeidenticalto*baserestrictinfo,andcheckwhetherthereareanypartialindexes.If*not,thisisallweneedtodo.*/have_partial=false;foreach(lc,rel->indexlist)//遍历index{IndexOptInfo*index=(IndexOptInfo*)lfirst(lc);index->indrestrictinfo=rel->baserestrictinfo;//设置索引约束条件if(index->indpred)have_partial=true;//存在部分索引}if(!have_partial)return;/**Constructalistofclausesthatwecanassumetrueforthepurposeof*provingtheindex(es)usable.Restrictionclausesfortherelare*alwaysusable,andsoareanyjoinclausesthatare"movableto"this*rel.Also,wecanconsideranyEC-derivablejoinclauses(whichmust*be"movableto"thisrel,bydefinition).*/clauselist=list_copy(rel->baserestrictinfo);//条件语句初始化/*Scantherel'sjoinclauses*/foreach(lc,rel->joininfo)//遍历连接条件{RestrictInfo*rinfo=(RestrictInfo*)lfirst(lc);//条件/*Checkifclausecanbemovedtothisrel*/if(!join_clause_is_movable_to(rinfo,rel))//条件是否可以下推到Rel中continue;//不可用,下一个条件clauselist=lappend(clauselist,rinfo);//可以,则添加到条件语句链表中}/**Addonanyequivalence-derivablejoinclauses.Computingthecorrect*relidsetsforgenerate_join_implied_equalitiesisslightlytricky*becausetherelcouldbeachildrelratherthanatruebaserel,andin*thatcasewemustremoveitsparents'relid(s)fromall_baserels.*/if(rel->reloptkind==RELOPT_OTHER_MEMBER_REL)otherrels=bms_difference(root->all_baserels,find_childrel_parents(root,rel));//elseotherrels=bms_difference(root->all_baserels,rel->relids);//获取与rel无关的其他relsif(!bms_is_empty(otherrels))clauselist=list_concat(clauselist,generate_join_implied_equalities(root,bms_union(rel->relids,otherrels),otherrels,rel));//添加到条件语句/**Normallyweremovequalsthatareimpliedbyapartialindex's*predicatefromindrestrictinfo,indicatingthattheyneednotbe*checkedexplicitlybyanindexscanplanusingthisindex.However,if*therelisatargetrelationofUPDATE/DELETE/SELECTFORUPDATE,we*cannotremovesuchqualsfromtheplan,becausetheyneedtobeinthe*plansothattheywillbeproperlyrecheckedbyEvalPlanQualtesting.*Somedaywemightwanttoremovesuchqualsfromthemainplananyway*andpassthemthroughtoEvalPlanQualviaasidechannel;butfornow,*wejustdon'tremoveimpliedqualsatallfortargetrelations.*/is_target_rel=(rel->relid==root->parse->resultRelation||get_plan_rowmark(root->rowMarks,rel->relid)!=NULL);/**Nowtrytoproveeachindexpredicatetrue,andcomputethe*indrestrictinfolistsforpartialindexes.Notethatwecomputethe*indrestrictinfolistevenfornon-predOKindexes;thismightseem*wasteful,butwemaybeabletousesuchindexesinORclauses,cf*generate_bitmap_or_paths().*/foreach(lc,rel->indexlist)//遍历index{IndexOptInfo*index=(IndexOptInfo*)lfirst(lc);ListCell*lcr;if(index->indpred==NIL)continue;/*ignorenon-partialindexeshere*/if(!index->predOK)/*don'trepeatworkifalreadyprovenOK*/index->predOK=predicate_implied_by(index->indpred,clauselist,false);//设置predOK参数/*Ifrelisanupdatetarget,leaveindrestrictinfoassetabove*/if(is_target_rel)continue;/*Elsecomputeindrestrictinfoasthenon-impliedquals*/index->indrestrictinfo=NIL;foreach(lcr,rel->baserestrictinfo){RestrictInfo*rinfo=(RestrictInfo*)lfirst(lcr);/*predicate_implied_by()assumesfirstargisimmutable*/if(contain_mutable_functions((Node*)rinfo->clause)||!predicate_implied_by(list_make1(rinfo->clause),index->indpred,false))index->indrestrictinfo=lappend(index->indrestrictinfo,rinfo);//}}}

set_plain_rel_size->set_baserel_size_estimates
函数注释:

//-----------------------------------------set_baserel_size_estimates/**set_baserel_size_estimates*Setthesizeestimatesforthegivenbaserelation.*估算baserel的估算大小**Therel'stargetlistandrestrictinfolistmusthavebeenconstructed*already,andrel->tuplesmustbeset.*rel的targetlist和限制条件链表已构建,并且tuples已获取.**Wesetthefollowingfieldsoftherelnode:*通过该方法,设置以下3个变量*rows:theestimatednumberofoutputtuples(afterapplying*restrictionclauses).*应用限制条件后,估算得出的输出元组数目*width:theestimatedaverageoutputtuplewidthinbytes.*以字节为单位输出估算的平均元组大小*baserestrictcost:estimatedcostofevaluatingbaserestrictinfoclauses.*解析限制条件的估算成本*/

源代码:

voidset_baserel_size_estimates(PlannerInfo*root,RelOptInfo*rel){doublenrows;/*Shouldonlybeappliedtobaserelations*/Assert(rel->relid>0);nrows=rel->tuples*clauselist_selectivity(root,rel->baserestrictinfo,0,JOIN_INNER,NULL);//元组总数*选择率rel->rows=clamp_row_est(nrows);cost_qual_eval(&rel->baserestrictcost,rel->baserestrictinfo,root);set_rel_width(root,rel);}//-----------------------------------------clauselist_selectivity/**clauselist_selectivity-*Computetheselectivityofanimplicitly-ANDedlistofboolean*expressionclauses.Thelistcanbeempty,inwhichcase1.0*mustbereturned.ListelementsmaybeeitherRestrictInfos*orbareexpressionclauses---theformerispreferredsince*itallowscachingofresults.**计算布尔表达式条件(隐式AND条件语句链表保存)的选择性.如果链表为空,*则返回1.0.链表中的元素可以是RestrictInfo结构体,也可以是裸条件表达式*(前者因为允许缓存,因此是首选)**Seeclause_selectivity()forthemeaningoftheadditionalparameters.**Ourbasicapproachistotaketheproductoftheselectivitiesofthe*subclauses.However,that'sonlyrightifthesubclauseshaveindependent*probabilities,andinrealitytheyareoftenNOTindependent.So,*wewanttobesmarterwherewecan.**采取的基本方法是subclauses的选择性乘积。采取这种做法的前台是子项具有独立的概率.*但实际上它们往往不是独立的,因此,需要在能做到的地方变得更好。**Iftheclausestakentogetherrefertojustonerelation,we'lltryto*applyselectivityestimatesusinganyextendedstatisticsforthatrel.*Currentlyweonlyhave(soft)functionaldependencies,soapplytheseinas*manycasesaspossible,andfallbackonnormalestimatesforremaining*clauses.**如果在一起的子句只涉及一个关系，将尝试应用关系上的扩展统计进行选择性的估算。**Wealsorecognize"rangequeries",suchas"x>34ANDx<42".Clauses*arerecognizedaspossiblerangequerycomponentsiftheyarerestriction*opclauseswhoseoperatorshavescalarltselorarelatedfunctionastheir*restrictionselectivityestimator.Wepairupclausesofthisformthat*refertothesamevariable.Anunpairableclauseofthiskindissimply*multipliedintotheselectivityproductinthenormalway.Butwhenwe*findapair,weknowthattheselectivitiesrepresenttherelative*positionsofthelowandhighboundswithinthecolumn'srange,soinstead*offiguringtheselectivityashisel*losel,wecanfigureitashisel+*losel-1.(Tovisualizethis,seethathiselisthefractionoftherange*belowthehighbound,whileloselisthefractionabovethelowbound;so*hiselcanbeinterpreteddirectlyasa0..1valuebutweneedtoconvert*loselto1-loselbeforeinterpretingitasavalue.Thentheavailable*rangeis1-loseltohisel.However,thiscalculationdouble-excludes*nulls,soreallyweneedhisel+losel+null_frac-1.)**优化器还可以识别范围查询,比如x>34ANDx<42,这类范围查询不能简单的把x>34*的选择率乘以x<42的选择率,为方便起见,假定x<42的选择率为hisel,x<34的选择率为losel,*那么计算公式应该为hisel-(1-losel),即hisel+losel-1,考虑NULL值,则范围查询的选择率*为hisel+losel+null_frac-1**IfeitherselectivityisexactlyDEFAULT_INEQ_SEL,weforgetthisequation*andinsteaduseDEFAULT_RANGE_INEQ_SEL.Thesameappliesiftheequation*yieldsanimpossible(negative)result.**如果任意一个选择性都恰好是DEFAULT_INEQ_SEL，那么我们将忘记这个等式，*而使用DEFAULT_RANGE_INEQ_SEL。这种情况同样适用于如果等式产生了一个不可能的(负的)结果。**Afreeside-effectisthatwecanrecognizeredundantinequalitiessuch*as"x<4ANDx<5";onlythetighterconstraintwillbecounted.**我们可以识别冗余的不等式,比如x<4ANDx<5,只有严格的约束条件才会计算在内**Ofcoursethisisallverydependentonthebehavioroftheinequality*selectivityfunctions;perhapssomedaywecangeneralizetheapproach.**这完全取决于不等选择性函数的行为,也许有一天我们可以推广这种方法.**/Selectivityclauselist_selectivity(PlannerInfo*root,List*clauses,intvarRelid,JoinTypejointype,SpecialJoinInfo*sjinfo){Selectivitys1=1.0;//默认返回值RelOptInfo*rel;//Bitmapset*estimatedclauses=NULL;//位图集合RangeQueryClause*rqlist=NULL;//范围查询语句ListCell*l;//临时变量intlistidx;/**Ifthere'sexactlyoneclause,justgodirectlyto*clause_selectivity().Noneofwhatwemightdobelowisrelevant.*/if(list_length(clauses)==1)returnclause_selectivity(root,(Node*)linitial(clauses),varRelid,jointype,sjinfo);//单个条件/**Determineiftheseclausesreferenceasinglerelation.Ifso,andif*ithasextendedstatistics,trytoapplythose.*///如果条件链表中的元素依赖的rel有且只有一个,则返回此relrel=find_single_rel_for_clauses(root,clauses);//应用dependencies_clauselist_selectivity中的可用的条件进行选择率估算if(rel&&rel->rtekind==RTE_RELATION&&rel->statlist!=NIL){/**Performselectivityestimationsonanyclausesfoundapplicableby*dependencies_clauselist_selectivity.'estimatedclauses'willbe*filledwiththe0-basedlistpositionsofclausesusedthatway,so*thatwecanignorethembelow.*/s1*=dependencies_clauselist_selectivity(root,clauses,varRelid,jointype,sjinfo,rel,&estimatedclauses);/**Thiswouldbetheplacetoapplyanyothertypesofextended*statisticsselectivityestimationsforremainingclauses.*/}/**Applynormalselectivityestimatesforremainingclauses.We'llbe*carefultoskipanyclauseswhichwerealreadyestimatedabove.*剩下的条件语句,应用常规的选择率估算.**Anythingthatdoesn'tlooklikeapotentialrangequeryclausegets*multipliedintos1andforgotten.Anythingthatdoesgetsinsertedinto*anrqlistentry.*非范围条件语句乘上s1后被丢弃,范围条件语句则加入到rqlist链表中.*/listidx=-1;foreach(l,clauses)//遍历{Node*clause=(Node*)lfirst(l);//链表中的元素RestrictInfo*rinfo;Selectivitys2;listidx++;/**Skipthisclauseifit'salreadybeenestimatedbysomeother*statisticsabove.*/if(bms_is_member(listidx,estimatedclauses))//跳过已处理的条件continue;/*Alwayscomputetheselectivityusingclause_selectivity*/s2=clause_selectivity(root,clause,varRelid,jointype,sjinfo);//获取条件选择率/**CheckforbeingpassedaRestrictInfo.**Ifit'sapseudoconstantRestrictInfo,thens2iseither1.0or*0.0;justusethatratherthanlookingforrangepairs.*/if(IsA(clause,RestrictInfo))//条件语句是RestrictInfo类型{rinfo=(RestrictInfo*)clause;if(rinfo->pseudoconstant)//常量{s1=s1*s2;//直接相乘continue;}clause=(Node*)rinfo->clause;//条件表达式}elserinfo=NULL;//不是RestrictInfo类型,rinfo设置为NULL/**Seeifitlookslikearestrictionclausewithapseudoconstanton*oneside.(Anythingmorecomplicatedthanthatmightnotbehavein*thesimplewayweareexpecting.)Mostofthetestsherecanbe*donemoreefficientlywithrinfothanwithout.*/if(is_opclause(clause)&&list_length(((OpExpr*)clause)->args)==2)//OpExpr{OpExpr*expr=(OpExpr*)clause;//条件语句boolvaronleft=true;boolok;if(rinfo)//rinfo中的条件语句{ok=(bms_membership(rinfo->clause_relids)==BMS_SINGLETON)&&(is_pseudo_constant_clause_relids(lsecond(expr->args),rinfo->right_relids)||(varonleft=false,is_pseudo_constant_clause_relids(linitial(expr->args),rinfo->left_relids)));}else//裸条件语句{ok=(NumRelids(clause)==1)&&(is_pseudo_constant_clause(lsecond(expr->args))||(varonleft=false,is_pseudo_constant_clause(linitial(expr->args))));}if(ok)//校验通过{/**Ifit'snota"<"/"<="/">"/">="operator,justmergethe*selectivityingenerically.Butifit'stherightoprrest,*addtheclausetorqlistforlaterprocessing.*/switch(get_oprrest(expr->opno)){caseF_SCALARLTSEL:caseF_SCALARLESEL:addRangeClause(&rqlist,clause,varonleft,true,s2);//范围条件break;caseF_SCALARGTSEL:caseF_SCALARGESEL:addRangeClause(&rqlist,clause,varonleft,false,s2);//范围条件break;default:/*Justmergetheselectivityingenerically*/s1=s1*s2;//直接相乘break;}continue;/*droptoloopbottom*/}}/*Nottherightform,sotreatitgenerically.*/s1=s1*s2;//直接相乘}/**Nowscantherangequerypairlist.*/while(rqlist!=NULL)//处理范围条件{RangeQueryClause*rqnext;if(rqlist->have_lobound&&rqlist->have_hibound)//存在上下限{/*Successfullymatchedapairofrangeclauses*/Selectivitys2;//选择率/**Exactequalitytothedefaultvalueprobablymeansthe*selectivityfunctionpunted.Thisisnotairtightbutshould*begoodenough.*/if(rqlist->hibound==DEFAULT_INEQ_SEL||rqlist->lobound==DEFAULT_INEQ_SEL)//默认值{s2=DEFAULT_RANGE_INEQ_SEL;//默认为DEFAULT_RANGE_INEQ_SEL}else{s2=rqlist->hibound+rqlist->lobound-1.0;//计算公式在注释已解释/*Adjustfordouble-exclusionofNULLs*/s2+=nulltestsel(root,IS_NULL,rqlist->var,varRelid,jointype,sjinfo);//NULL值/**Azeroorslightlynegatives2shouldbeconvertedintoa*smallpositivevalue;weprobablyaredealingwithavery*tightrangeandgotabogusresultduetoroundofferrors.*However,ifs2isverynegative,thenweprobablyhave*defaultselectivityestimatesononeorbothsidesofthe*rangethatwefailedtorecognizeaboveforsomereason.*/if(s2<=0.0)//小于0?{if(s2<-0.01){/**Nodataavailable---useadefaultestimatethat*issmall,butnotrealsmall.*/s2=DEFAULT_RANGE_INEQ_SEL;//小于﹣1%,默认值}else{/**It'sjustroundofferror;useasmallpositive*value*/s2=1.0e-10;,//否则设置为1的﹣10次方}}}/*Mergeintheselectivityofthepairofclauses*/s1*=s2;//直接相乘}else//只有其中一个限制{/*Onlyfoundoneofapair,mergeitingenerically*/if(rqlist->have_lobound)s1*=rqlist->lobound;//下限elses1*=rqlist->hibound;//上限}/*releasestorageandadvance*/rqnext=rqlist->next;//释放资源pfree(rqlist);rqlist=rqnext;}returns1;//返回选择率}/**clause_selectivity-*Computetheselectivityofageneralbooleanexpressionclause.*计算布尔表达式条件语句的选择率**TheclausecanbeeitheraRestrictInfooraplainexpression.Ifit's*aRestrictInfo,wetrytocachetheselectivityforpossiblere-use,*sopassingRestrictInfosispreferred.*clause可以是RestrictInfo结构体或者是常规的表达式.如果是RestrictInfo,*那么我们会尝试缓存结果已便于重用.**varRelidiseither0orarangetableindex.*varRelid是0或者是RTE编号**WhenvarRelidisnot0,onlyvariablesbelongingtothatrelationare*consideredincomputingselectivity;othervarsaretreatedasconstants*ofunknownvalues.Thisisappropriateforestimatingtheselectivityof*ajoinclausethatisbeingusedasarestrictionclauseinascanofa*nestloopjoin'sinnerrelation---varRelidshouldthenbetheIDofthe*innerrelation.*如果varRelid不为0,只有属于该Rel的变量才会考虑计算选择率,其他变量作为未知常量处理*这种情况常见于嵌套循环内表的选择率估算(varRelid是内部的RTE)**WhenvarRelidis0,allvariablesaretreatedasvariables.This*isappropriateforordinaryjoinclausesandrestrictionclauses.*如果varRelid为0,所有的变量都需要考虑,用于常规的连接条件和限制条件估算***jointypeisthejointype,iftheclauseisajoinclause.PassJOIN_INNER*iftheclauseisn'tajoinclause.*如果条件是连接条件,那么jointype是连接类型,如果不是连接调整,则该参数为JOIN_INNER**sjinfoisNULLforanon-joinclause,otherwiseitprovidesadditional*contextinformationaboutthejoinbeingperformed.Therearesome*specialcases:*1.Foraspecial(notINNER)join,sjinfoisalwaysamemberof*root->join_info_list.非INNER_JOIN,sjinfo通常是oot->join_info_list的一个元素*2.ForanINNERjoin,sjinfoisjustatransientstruct,andonlythe*relidsandjointypefieldsinitcanbetrusted.INNER_JOIN,sjinfo通常是临时的结构*Itispossibleforjointypetobedifferentfromsjinfo->jointype.//jointype可能跟sjinfo中的jointype不同*Thisindicatesweareconsideringavariantjoin:eitherwith*theLHSandRHSswitched,orwithoneinputunique-ified.这意味着连接有可能会变换**Note:whenpassingnonzerovarRelid,it'snormallyappropriatetoset*jointype==JOIN_INNER,sjinfo==NULL,eveniftheclauseisreallya*joinclause;becausewearen'ttreatingitasajoinclause.*/Selectivityclause_selectivity(PlannerInfo*root,Node*clause,intvarRelid,JoinTypejointype,SpecialJoinInfo*sjinfo){Selectivitys1=0.5;/*默认值,defaultforanyunhandledclausetype*/RestrictInfo*rinfo=NULL;boolcacheable=false;if(clause==NULL)/*canthisstillhappen?*/returns1;if(IsA(clause,RestrictInfo))//RestrictInfo{rinfo=(RestrictInfo*)clause;/**Iftheclauseismarkedpseudoconstant,thenitwillbeusedasa*gatingqualandshouldnotaffectselectivityestimates;hence*return1.0.TheonlyexceptionisthataconstantFALSEmaybe*takenashavingselectivity0.0,sinceitwillsurelymeannorows*outoftheplan.Thiscaseissimpleenoughthatweneednot*bothercachingtheresult.*/if(rinfo->pseudoconstant)//pseudoconstant,不影响选择率{if(!IsA(rinfo->clause,Const))return(Selectivity)1.0;//返回1.0}/**Iftheclauseismarkedredundant,alwaysreturn1.0.*/if(rinfo->norm_selec>1)return(Selectivity)1.0;//返回1.0/**Ifpossible,cachetheresultoftheselectivitycalculationfor*theclause.WecancacheifvarRelidiszeroortheclause*containsonlyvarsofthatrelid---otherwisevarRelidwillaffect*theresult,somustn'tcache.Outerjoinqualsmightbeexamined*witheithertheirjoin'sactualjointypeorJOIN_INNER,soweneed*twocachevariablestorememberbothcases.Note:weassumethe*resultwon'tchangeifweareswitchingtheinputrelationsor*consideringaunique-ifiedcase,soweonlyneedonecachevariable*forallnon-JOIN_INNERcases.*/if(varRelid==0||bms_is_subset_singleton(rinfo->clause_relids,varRelid))//varRelid为0{/*Cacheable---dowealreadyhavetheresult?*/if(jointype==JOIN_INNER){if(rinfo->norm_selec>=0)returnrinfo->norm_selec;}else{if(rinfo->outer_selec>=0)returnrinfo->outer_selec;}cacheable=true;}/**Proceedwithexaminationofcontainedclause.Iftheclauseisan*OR-clause,wewanttolookatthevariantwithsub-RestrictInfos,*sothatper-subclauseselectivitiescanbecached.*/if(rinfo->orclause)clause=(Node*)rinfo->orclause;elseclause=(Node*)rinfo->clause;}if(IsA(clause,Var))//Var{Var*var=(Var*)clause;/**Weprobablyshouldn'teverseeanuplevelVarhere,butifwedo,*returnthedefaultselectivity...*/if(var->varlevelsup==0&&(varRelid==0||varRelid==(int)var->varno)){/*UsetherestrictionselectivityfunctionforaboolVar*/s1=boolvarsel(root,(Node*)var,varRelid);//boolVar选择率}}elseif(IsA(clause,Const))//常量{/*boolconstantisprettyeasy...*/Const*con=(Const*)clause;s1=con->constisnull?0.0:DatumGetBool(con->constvalue)?1.0:0.0;//常量有效则为1.0,否则为0.0}elseif(IsA(clause,Param))//参数{/*seeifwecanreplacetheParam*/Node*subst=estimate_expression_value(root,clause);if(IsA(subst,Const)){/*boolconstantisprettyeasy...*/Const*con=(Const*)subst;s1=con->constisnull?0.0:DatumGetBool(con->constvalue)?1.0:0.0;}else{/*XXXanywaytodobetterthandefault?*/}}elseif(not_clause(clause))//反选{/*inverseoftheselectivityoftheunderlyingclause*/s1=1.0-clause_selectivity(root,(Node*)get_notclausearg((Expr*)clause),varRelid,jointype,sjinfo);}elseif(and_clause(clause))//AND语句{/*sharecodewithclauselist_selectivity()*/s1=clauselist_selectivity(root,((BoolExpr*)clause)->args,varRelid,jointype,sjinfo);//递归调用}elseif(or_clause(clause))//OR语句{/**SelectivitiesforanORclausearecomputedass1+s2-s1*s2to*accountfortheprobableoverlapofselectedtuplesets.**XXXisthistooconservative?*/ListCell*arg;s1=0.0;foreach(arg,((BoolExpr*)clause)->args){Selectivitys2=clause_selectivity(root,(Node*)lfirst(arg),varRelid,jointype,sjinfo);//递归调用s1=s1+s2-s1*s2;}}elseif(is_opclause(clause)||IsA(clause,DistinctExpr)){OpExpr*opclause=(OpExpr*)clause;Oidopno=opclause->opno;if(treat_as_join_clause(clause,rinfo,varRelid,sjinfo)){/*Estimateselectivityforajoinclause.*/s1=join_selectivity(root,opno,opclause->args,opclause->inputcollid,jointype,sjinfo);//连接条件}else{/*Estimateselectivityforarestrictionclause.*/s1=restriction_selectivity(root,opno,opclause->args,opclause->inputcollid,varRelid);//限制条件}/**DistinctExprhasthesamerepresentationasOpExpr,butthe*containedoperatoris"="not"<>",sowemustnegatetheresult.*Thisestimationmethoddoesn'tgivetherightbehaviorfornulls,*butit'sbetterthandoingnothing.*/if(IsA(clause,DistinctExpr))s1=1.0-s1;//Distinct?}elseif(IsA(clause,ScalarArrayOpExpr))//数组{/*Usenodespecificselectivitycalculationfunction*/s1=scalararraysel(root,(ScalarArrayOpExpr*)clause,treat_as_join_clause(clause,rinfo,varRelid,sjinfo),varRelid,jointype,sjinfo);}elseif(IsA(clause,RowCompareExpr))//行对比{/*Usenodespecificselectivitycalculationfunction*/s1=rowcomparesel(root,(RowCompareExpr*)clause,varRelid,jointype,sjinfo);}elseif(IsA(clause,NullTest))//NullTest{/*Usenodespecificselectivitycalculationfunction*/s1=nulltestsel(root,((NullTest*)clause)->nulltesttype,(Node*)((NullTest*)clause)->arg,varRelid,jointype,sjinfo);}elseif(IsA(clause,BooleanTest))//BooleanTest{/*Usenodespecificselectivitycalculationfunction*/s1=booltestsel(root,((BooleanTest*)clause)->booltesttype,(Node*)((BooleanTest*)clause)->arg,varRelid,jointype,sjinfo);}elseif(IsA(clause,CurrentOfExpr))//CurrentOfExpr{/*CURRENTOFselectsatmostonerowofitstable*/CurrentOfExpr*cexpr=(CurrentOfExpr*)clause;RelOptInfo*crel=find_base_rel(root,cexpr->cvarno);if(crel->tuples>0)s1=1.0/crel->tuples;}elseif(IsA(clause,RelabelType))//RelabelType{/*Notsurethiscaseisneeded,butitcan'thurt*/s1=clause_selectivity(root,(Node*)((RelabelType*)clause)->arg,varRelid,jointype,sjinfo);}elseif(IsA(clause,CoerceToDomain))//CoerceToDomain{/*Notsurethiscaseisneeded,butitcan'thurt*/s1=clause_selectivity(root,(Node*)((CoerceToDomain*)clause)->arg,varRelid,jointype,sjinfo);}else{/**Foranythingelse,seeifwecanconsideritasabooleanvariable.*Thisonlyworksifit'sanimmutableexpressioninVarsofasingle*relation;butthere'snopointinuscheckingthatherebecause*boolvarsel()willdoitinternally,andreturnasuitabledefault*selectivityifnot.*/s1=boolvarsel(root,clause,varRelid);//默认为boolVar}/*Cachetheresultifpossible*/if(cacheable)//缓存?{if(jointype==JOIN_INNER)rinfo->norm_selec=s1;elserinfo->outer_selec=s1;}#ifdefSELECTIVITY_DEBUGelog(DEBUG4,"clause_selectivity:s1%f",s1);#endif/*SELECTIVITY_DEBUG*/returns1;}/**find_single_rel_for_clauses*Examineeachclausein'clauses'anddetermineifallclauses*referenceonlyasinglerelation.Ifsoreturnthatrelation,*otherwisereturnNULL.**如果条件链表中的元素依赖的rel有且只有一个,则返回此rel*/staticRelOptInfo*find_single_rel_for_clauses(PlannerInfo*root,List*clauses){intlastrelid=0;ListCell*l;foreach(l,clauses){RestrictInfo*rinfo=(RestrictInfo*)lfirst(l);intrelid;/**IfwehavealistofbareclausesratherthanRestrictInfos,we*couldpullouttheirrelidsthehardwaywithpull_varnos().*However,currentlytheextended-statsmachinerywon'tdoanything*withnon-RestrictInfoclausesanyway,sothere'snopointin*spendingextracycles;justfailifthat'swhatwehave.*/if(!IsA(rinfo,RestrictInfo))returnNULL;if(bms_is_empty(rinfo->clause_relids))continue;/*wecanignorevariable-freeclauses*/if(!bms_get_singleton_member(rinfo->clause_relids,&relid))returnNULL;/*multiplerelationsinthisclause*/if(lastrelid==0)lastrelid=relid;/*firstclausereferencingarelation*/elseif(relid!=lastrelid)returnNULL;/*relationnotsameaslastone*/}if(lastrelid!=0)returnfind_base_rel(root,lastrelid);returnNULL;/*noclauses*/}//----------------------------------------------clamp_row_est/**clamp_row_est*Forcearow-countestimatetoasanevalue.*返回合法值*/doubleclamp_row_est(doublenrows){/**Forceestimatetobeatleastonerow,tomakeexplainoutputlook*betterandtoavoidpossibledivide-by-zerowheninterpolatingcosts.*Makeitaninteger,too.*/if(nrows<=1.0)nrows=1.0;//小于1,返回1elsenrows=rint(nrows);//整型returnnrows;}//----------------------------------------------cost_qual_eval/**cost_qual_eval*EstimatetheCPUcostsofevaluatingaWHEREclause.*Theinputcanbeeitheranimplicitly-ANDedlistofboolean*expressions,oralistofRestrictInfonodes.(Thelatteris*preferredsinceitallowscachingoftheresults.)*Theresultincludesbothaone-time(startup)component,*andaper-evaluationcomponent.*/voidcost_qual_eval(QualCost*cost,List*quals,PlannerInfo*root){cost_qual_eval_contextcontext;ListCell*l;context.root=root;context.total.startup=0;context.total.per_tuple=0;/*Wedon'tchargeanycostfortheimplicitANDingattoplevel...*/foreach(l,quals){Node*qual=(Node*)lfirst(l);cost_qual_eval_walker(qual,&context);}*cost=context.total;}//----------------------------------------------set_rel_width/**set_rel_width*Settheestimatedoutputwidthofabaserelation.**Theestimatedoutputwidthisthesumoftheper-attributewidthestimates*fortheactually-referencedcolumns,plusanyPHVsorotherexpressions*thathavetobecalculatedatthisrelation.Thisistheamountofdata*we'dneedtopassupwardsincaseofasort,hash,etc.**Thisfunctionalsosetsreltarget->cost,soit'sabitmisnamednow.**NB:thisworksbestonplainrelationsbecauseitpreferstolookat*realVars.Forsubqueries,set_subquery_size_estimateswillalreadyhave*copiedupwhateverper-columnestimatesweremadewithinthesubquery,*andforothertypesofrelsthereisn'tmuchwecandoanyway.Wefall*backon(fairlystupid)datatype-basedwidthestimatesifwecan'tget*anybetternumber.**Theper-attributewidthestimatesarecachedforpossiblere-usewhile*buildingjoinrelationsorpost-scan/joinpathtargets.*/staticvoidset_rel_width(PlannerInfo*root,RelOptInfo*rel){Oidreloid=planner_rt_fetch(rel->relid,root)->relid;int32tuple_width=0;boolhave_wholerow_var=false;ListCell*lc;/*Varsareassumedtohavecostzero,butotherexprsdonot*/rel->reltarget->cost.startup=0;rel->reltarget->cost.per_tuple=0;foreach(lc,rel->reltarget->exprs){Node*node=(Node*)lfirst(lc);/**Ordinarily,aVarinarel'stargetlistmustbelongtothatrel;*buttherearecornercasesinvolvingLATERALreferenceswherethat*isn'tso.IftheVarhasthewrongvarno,fallthroughtothe*genericcase(itdoesn'tseemworththetroubletobeanysmarter).*/if(IsA(node,Var)&&((Var*)node)->varno==rel->relid){Var*var=(Var*)node;intndx;int32item_width;Assert(var->varattno>=rel->min_attr);Assert(var->varattno<=rel->max_attr);ndx=var->varattno-rel->min_attr;/**Ifit'sawhole-rowVar,we'lldealwithitbelowafterwehave*alreadycachedasmanyattrwidthsaspossible.*/if(var->varattno==0){have_wholerow_var=true;continue;}/**Thewidthmayhavebeencachedalready(especiallyifit'sa*subquery),sodon'tduplicateeffort.*/if(rel->attr_widths[ndx]>0){tuple_width+=rel->attr_widths[ndx];continue;}/*Trytogetcolumnwidthfromstatistics*/if(reloid!=InvalidOid&&var->varattno>0){item_width=get_attavgwidth(reloid,var->varattno);if(item_width>0){rel->attr_widths[ndx]=item_width;tuple_width+=item_width;continue;}}/**Notaplainrelation,orcan'tfindstatisticsforit.Estimate*usingjustthetypeinfo.*/item_width=get_typavgwidth(var->vartype,var->vartypmod);Assert(item_width>0);rel->attr_widths[ndx]=item_width;tuple_width+=item_width;}elseif(IsA(node,PlaceHolderVar)){/**WewillneedtoevaluatethePHV'scontainedexpressionwhile*scanningthisrel,sobesuretoincludeitinreltarget->cost.*/PlaceHolderVar*phv=(PlaceHolderVar*)node;PlaceHolderInfo*phinfo=find_placeholder_info(root,phv,false);QualCostcost;tuple_width+=phinfo->ph_width;cost_qual_eval_node(&cost,(Node*)phv->phexpr,root);rel->reltarget->cost.startup+=cost.startup;rel->reltarget->cost.per_tuple+=cost.per_tuple;}else{/**Wecouldbelookingatanexpressionpulledupfromasubquery,*oraROW()representingawhole-rowchildVar,etc.Dowhatwe*canusingtheexpressiontypeinformation.*/int32item_width;QualCostcost;item_width=get_typavgwidth(exprType(node),exprTypmod(node));Assert(item_width>0);tuple_width+=item_width;/*Notentirelyclearifweneedtoaccountforcost,butdoso*/cost_qual_eval_node(&cost,node,root);rel->reltarget->cost.startup+=cost.startup;rel->reltarget->cost.per_tuple+=cost.per_tuple;}}/**Ifwehaveawhole-rowreference,estimateitswidthasthesumof*per-columnwidthsplusheaptupleheaderoverhead.*/if(have_wholerow_var){int32wholerow_width=MAXALIGN(SizeofHeapTupleHeader);if(reloid!=InvalidOid){/*Realrelation,soestimatetruetuplewidth*/wholerow_width+=get_relation_data_width(reloid,rel->attr_widths-rel->min_attr);}else{/*Dowhatwecanwithinfoforaphonyrel*/AttrNumberi;for(i=1;i<=rel->max_attr;i++)wholerow_width+=rel->attr_widths[i-rel->min_attr];}rel->attr_widths[0-rel->min_attr]=wholerow_width;/**Includethewhole-rowVaraspartoftheoutputtuple.Yes,that*reallyiswhathappensatruntime.*/tuple_width+=wholerow_width;}Assert(tuple_width>=0);rel->reltarget->width=tuple_width;}三、跟踪分析

测试脚本:
单位信息表,插入100,000行数据,dwbh为主键,同时创建函数索引和部分索引

droptableifexistst_dwxx;createtablet_dwxx(dwmcvarchar(100),dwbhvarchar(20),dwdzvarchar(100));altertablet_dwxxaddprimarykey(dwbh);createindexidx_dwxx_expront_dwxx(trim(dwmc));createindexidx_dwxx_predicate_dwbhont_dwxx(dwbh)wheredwbh>'50000';truncatetablet_dwxx;insertintot_dwxx(dwmc,dwbh,dwdz)select'DWMC'||generate_series(1,100000),generate_series(1,100000)||'','DWDZ'||generate_series(1,100000);

个人信息表,插入5,000,000行数据,在grbh和dwbh上创建索引

droptableifexistst_grxx;createtablet_grxx(dwbhvarchar(10),grbhvarchar(10),xmvarchar(20),xbvarchar(10),nlint);insertintot_grxx(dwbh,grbh,xm,xb,nl)selectgenerate_series(1,5000000)/50||'',generate_series(1,5000000),'XM'||generate_series(1,5000000),(casewhen(floor(random()*2)=0)then'男'else'女'end),floor(random()*100+1)::int;createindexidx_t_grxx_grbhont_grxx(grbh);createindexidx_t_dwxx_grbhont_grxx(dwbh);

个人缴费信息表,在grbh上创建索引,多次插入5,000,000行数据

droptableifexistst_jfxx;createtablet_jfxx(grbhvarchar(10),nyvarchar(10),jefloat);--根据实际情况,多次执行以下SQLinsertintot_jfxx(grbh,ny,je)selectgenerate_series(1,5000000),to_char(now()::timestamp-(floor(random()*240+1)||'month')::interval,'yyyymm'),floor(random()*10000+1);createindexidx_t_jfxx_grbhont_jfxx(grbh);

执行简单的查询SQL:

selectt1.*fromt_dwxxt1wheredwbh>'60000'anddwbh<'70000'anddwbh<'65000';

执行计划如下:

testdb=#explain(analyzetrue,verbose)selectt1.*fromt_dwxxt1wheredwbh>'60000'anddwbh<'70000'anddwbh<'65000';QUERYPLAN----------------------------------------------------------------------------------------------------------------------------------------BitmapHeapScanonpublic.t_dwxxt1(cost=134.19..956.12rows=5482width=23)(actualtime=1.484..2.744rows=5554loops=1)Output:dwmc,dwbh,dwdzRecheckCond:(((t1.dwbh)::text>'60000'::text)AND((t1.dwbh)::text<'70000'::text)AND((t1.dwbh)::text<'65000'::text))HeapBlocks:exact=45->BitmapIndexScanonidx_dwxx_predicate_dwbh(cost=0.00..132.81rows=5482width=0)(actualtime=1.467..1.467rows=5554loops=1)IndexCond:(((t1.dwbh)::text>'60000'::text)AND((t1.dwbh)::text<'70000'::text)AND((t1.dwbh)::text<'65000'::text))PlanningTime:0.204msExecutionTime:3.288ms

启动gdb跟踪分析:

(gdb)bset_baserel_size_estimatesBreakpoint1at0x747bf5:filecostsize.c,line4302.(gdb)cContinuing.Breakpoint1,set_baserel_size_estimates(root=0x2686fa8,rel=0x26873b8)atcostsize.c:43024302nrows=rel->tuples*

进入函数clauselist_selectivity:

(gdb)stepclauselist_selectivity(root=0x2686fa8,clauses=0x271f600,varRelid=0,jointype=JOIN_INNER,sjinfo=0x0)atclausesel.c:105105Selectivitys1=1.0;...124rel=find_single_rel_for_clauses(root,clauses);(gdb)125if(rel&&rel->rtekind==RTE_RELATION&&rel->statlist!=NIL)#与限制条件相关的rel(t_dwxx)(gdb)p*rel$1={type=T_RelOptInfo,reloptkind=RELOPT_BASEREL,relids=0x2687728,rows=0,consider_startup=false,consider_param_startup=false,consider_parallel=true,reltarget=0x271e228,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=0x271e278,attr_widths=0x271e308,lateral_vars=0x0,lateral_referencers=0x0,indexlist=0x271e700,statlist=0x0,pages=726,tuples=100000,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=0x271f600,baserestrictcost={startup=0,per_tuple=0},baserestrict_min_security=0,joininfo=0x0,has_eclass_joins=false,top_parent_relids=0x0,part_scheme=0x0,nparts=0,boundinfo=0x0,partition_qual=0x0,part_rels=0x0,partexprs=0x0,nullable_partexprs=0x0,partitioned_child_rels=0x0}

开始循环处理:

152foreach(l,clauses)...

第一个条件语句,调用clause_selectivity后,选择率为0.44...

168s2=clause_selectivity(root,clause,varRelid,jointype,sjinfo);(gdb)176if(IsA(clause,RestrictInfo))(gdb)ps2$2=0.44045086705202319

添加到范围条件语句中:

...225switch(get_oprrest(expr->opno))(gdb)234addRangeClause(&rqlist,clause,(gdb)236break;

第二个条件语句,调用clause_selectivity后,选择率为0.66...,,同样会添加到范围条件语句中:

168s2=clause_selectivity(root,clause,varRelid,jointype,sjinfo);(gdb)176if(IsA(clause,RestrictInfo))(gdb)ps2$3=0.66904390539053915...225switch(get_oprrest(expr->opno))(gdb)229addRangeClause(&rqlist,clause,

第三个条件语句,调用clause_selectivity后,选择率为0.61...,,同样会添加到范围条件语句中:

168s2=clause_selectivity(root,clause,varRelid,jointype,sjinfo);(gdb)176if(IsA(clause,RestrictInfo))(gdb)ps2$4=0.61437297872340435...225switch(get_oprrest(expr->opno))(gdb)229addRangeClause(&rqlist,clause,

结束循环,开始处理范围条件语句:

253while(rqlist!=NULL)(gdb)n#既有上限,也有下限(gdb)p*rqlist$7={next=0x0,var=0x271dba8,have_lobound=true,have_hibound=true,lobound=0.44045086705202319,hibound=0.61437297872340435}...#计算公式注释已作介绍(gdb)n274s2=rqlist->hibound+rqlist->lobound-1.0;(gdb)277s2+=nulltestsel(root,IS_NULL,rqlist->var,#最终结果(gdb)325returns1;(gdb)ps1$11=0.054823845775427538...

回到主函数:

(gdb)set_baserel_size_estimates(root=0x2686fa8,rel=0x26873b8)atcostsize.c:43024302nrows=rel->tuples*(gdb)4309rel->rows=clamp_row_est(nrows);(gdb)4311cost_qual_eval(&rel->baserestrictcost,rel->baserestrictinfo,root);(gdb)4313set_rel_width(root,rel);(gdb)prel->rows$12=5482

结果为5482,执行计划中的rows=5482就是这么来的.

“PostgreSQL中set_base_rel_sizes函数及其子函数案例分析”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！