PostgreSQL中set_base_rel_sizes函数及其子函数案例分析

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

make_one_rel源代码:

 RelOptInfo *
 make_one_rel(PlannerInfo *root, List *joinlist)
 {
     RelOptInfo *rel;
     Index       rti;
 
     
     root->all_baserels = NULL;
     for (rti = 1; rti < root->simple_rel_array_size; rti++)//遍历RelOptInfo
     {
         RelOptInfo *brel = root->simple_rel_array[rti];
 
         
         if (brel == NULL)
             continue;
 
         Assert(brel->relid == rti); 
 
         
         if (brel->reloptkind != RELOPT_BASEREL)
             continue;
 
         root->all_baserels = bms_add_member(root->all_baserels, brel->relid);//添加到all_baserels遍历中
     }
 
     
     //设置RelOptInfo的consider_param_startup变量,是否考量fast-start plans
     set_base_rel_consider_startup(root);
 
     
     set_base_rel_sizes(root);//估算Relation的Size并且设置consider_parallel标记
     set_base_rel_pathlists(root);//生成Relation的扫描(访问)路径
 
     
     rel = make_rel_from_joinlist(root, joinlist);
 
     
     Assert(bms_equal(rel->relids, root->all_baserels));
   //返回最上层的RelOptInfo
     return rel;
 }

一、数据结构

RelOptInfo

 typedef struct RelOptInfo
 {
     NodeTag     type;//节点标识
 
     RelOptKind  reloptkind;//RelOpt类型
 
     
     Relids      relids;         
 
     
     double      rows;           
 
     
     bool        consider_startup;   
     bool        consider_param_startup; 
     bool        consider_parallel;  
 
     
     struct PathTarget *reltarget;   
 
     
     List       *pathlist;       
     List       *ppilist;        
     List       *partial_pathlist;   
     struct Path *cheapest_startup_path;//代价最低的启动路径
     struct Path *cheapest_total_path;//代价最低的整体路径
     struct Path *cheapest_unique_path;//代价最低的获取唯一值的路径
     List       *cheapest_parameterized_paths;//代价最低的参数化?路径链表
 
     
     
     Relids      direct_lateral_relids;  
     Relids      lateral_relids; 
 
     
     //reloptkind=RELOPT_BASEREL时使用的数据结构
     Index       relid;          
     Oid         reltablespace;  
     RTEKind     rtekind;        
     AttrNumber  min_attr;       
     AttrNumber  max_attr;       
     Relids     *attr_needed;    
     int32      *attr_widths;    
     List       *lateral_vars;   
     Relids      lateral_referencers;    
     List       *indexlist;      
     List       *statlist;       
     BlockNumber pages;          
     double      tuples;         
     double      allvisfrac;     
     PlannerInfo *subroot;       
     List       *subplan_params; 
     int         rel_parallel_workers;   
 
     
     //FWD相关信息
     Oid         serverid;       
     Oid         userid;         
     bool        useridiscurrent;    
     
     struct FdwRoutine *fdwroutine;
     void       *fdw_private;
 
     
     //已知的,可保证唯一的Relids链表
     List       *unique_for_rels;    
     List       *non_unique_for_rels;    
 
     
     List       *baserestrictinfo;   
     QualCost    baserestrictcost;   
     Index       baserestrict_min_security;  
     List       *joininfo;       
     bool        has_eclass_joins;   
 
     
     bool        consider_partitionwise_join;    
     Relids      top_parent_relids;  
 
     
     //分区表使用
     PartitionScheme part_scheme;    
     int         nparts;         
     struct PartitionBoundInfoData *boundinfo;   
     List       *partition_qual; 
     struct RelOptInfo **part_rels;  
     List      **partexprs;      
     List      **nullable_partexprs; 
     List       *partitioned_child_rels; 
 } RelOptInfo;

二、源码解读

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

 
 static void
 set_plain_rel_size(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
 {
     
     check_index_predicates(root, rel);//验证部分(条件)索引的可用性
 
     
     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
 

 void
 check_index_predicates(PlannerInfo *root, RelOptInfo *rel)
 {
     List       *clauselist;//条件链表
     bool        have_partial;//是否包含部分索引
     bool        is_target_rel;//目标rel?
     Relids      otherrels;//Relids
     ListCell   *lc;//临时变量
 
     
     Assert(IS_SIMPLE_REL(rel));//rel必须是基础关系
 
     
     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;
 
     
     clauselist = list_copy(rel->baserestrictinfo);//条件语句初始化
 
     
     foreach(lc, rel->joininfo)//遍历连接条件
     {
         RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);//条件
 
         
         if (!join_clause_is_movable_to(rinfo, rel))//条件是否可以下推到Rel中
             continue;//不可用,下一个条件
 
         clauselist = lappend(clauselist, rinfo);//可以,则添加到条件语句链表中
     }
 
     
     if (rel->reloptkind == RELOPT_OTHER_MEMBER_REL)
         otherrels = bms_difference(root->all_baserels,
                                    find_childrel_parents(root, rel));//
     else
         otherrels = bms_difference(root->all_baserels, rel->relids);//获取与rel无关的其他rels
 
     if (!bms_is_empty(otherrels))
         clauselist =
             list_concat(clauselist,
                         generate_join_implied_equalities(root,
                                                          bms_union(rel->relids,
                                                                    otherrels),
                                                          otherrels,
                                                          rel));//添加到条件语句
 
     
     is_target_rel = (rel->relid == root->parse->resultRelation ||
                      get_plan_rowmark(root->rowMarks, rel->relid) != NULL);
 
     
     foreach(lc, rel->indexlist)//遍历index
     {
         IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);
         ListCell   *lcr;
 
         if (index->indpred == NIL)
             continue;           
 
         if (!index->predOK)     
             index->predOK = predicate_implied_by(index->indpred, clauselist,
                                                  false);//设置predOK参数
 
         
         if (is_target_rel)
             continue;
 
         
         index->indrestrictinfo = NIL;
         foreach(lcr, rel->baserestrictinfo)
         {
             RestrictInfo *rinfo = (RestrictInfo *) lfirst(lcr);
 
             
             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
 

源代码:

 void
 set_baserel_size_estimates(PlannerInfo *root, RelOptInfo *rel)
 {
     double      nrows;
 
     
     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

 
 Selectivity
 clauselist_selectivity(PlannerInfo *root,
                        List *clauses,
                        int varRelid,
                        JoinType jointype,
                        SpecialJoinInfo *sjinfo)
 {
     Selectivity s1 = 1.0;//默认返回值
     RelOptInfo *rel;//
     Bitmapset  *estimatedclauses = NULL;//位图集合
     RangeQueryClause *rqlist = NULL;//范围查询语句
     ListCell   *l;//临时变量
     int         listidx;
 
     
     if (list_length(clauses) == 1)
         return clause_selectivity(root, (Node *) linitial(clauses),
                                   varRelid, jointype, sjinfo);//单个条件
 
     
     //如果条件链表中的元素依赖的rel有且只有一个,则返回此rel
     rel = find_single_rel_for_clauses(root, clauses);
     //应用dependencies_clauselist_selectivity中的可用的条件进行选择率估算
     if (rel && rel->rtekind == RTE_RELATION && rel->statlist != NIL)
     {
         
         s1 *= dependencies_clauselist_selectivity(root, clauses, varRelid,
                                                   jointype, sjinfo, rel,
                                                   &estimatedclauses);
 
         
     }
 
     
     listidx = -1;
     foreach(l, clauses)//遍历
     {
         Node       *clause = (Node *) lfirst(l);//链表中的元素
         RestrictInfo *rinfo;
         Selectivity s2;
 
         listidx++;
 
         
         if (bms_is_member(listidx, estimatedclauses))//跳过已处理的条件
             continue;
 
         
         s2 = clause_selectivity(root, clause, varRelid, jointype, sjinfo);//获取条件选择率
 
         
         if (IsA(clause, RestrictInfo))//条件语句是RestrictInfo类型
         {
             rinfo = (RestrictInfo *) clause;
             if (rinfo->pseudoconstant)//常量
             {
                 s1 = s1 * s2;//直接相乘
                 continue;
             }
             clause = (Node *) rinfo->clause;//条件表达式
         }
         else
             rinfo = NULL;//不是RestrictInfo类型,rinfo设置为NULL
 
         
         if (is_opclause(clause) && list_length(((OpExpr *) clause)->args) == 2)//OpExpr
         {
             OpExpr     *expr = (OpExpr *) clause;//条件语句
             bool        varonleft = true;
             bool        ok;
 
             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)//校验通过
             {
                 
                 switch (get_oprrest(expr->opno))
                 {
                     case F_SCALARLTSEL:
                     case F_SCALARLESEL:
                         addRangeClause(&rqlist, clause,
                                        varonleft, true, s2);//范围条件
                         break;
                     case F_SCALARGTSEL:
                     case F_SCALARGESEL:
                         addRangeClause(&rqlist, clause,
                                        varonleft, false, s2);//范围条件
                         break;
                     default:
                         
                         s1 = s1 * s2;//直接相乘
                         break;
                 }
                 continue;       
             }
         }
 
         
         s1 = s1 * s2;//直接相乘
     }
 
     
     while (rqlist != NULL)//处理范围条件
     {
         RangeQueryClause *rqnext;
 
         if (rqlist->have_lobound && rqlist->have_hibound)//存在上下限
         {
             
             Selectivity s2;//选择率
 
             
             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;//计算公式在注释已解释
 
                 
                 s2 += nulltestsel(root, IS_NULL, rqlist->var,
                                   varRelid, jointype, sjinfo);//NULL值
 
                 
                 if (s2 <= 0.0)//小于0?
                 {
                     if (s2 < -0.01)
                     {
                         
                         s2 = DEFAULT_RANGE_INEQ_SEL;//小于﹣1%,默认值
                     }
                     else
                     {
                         
                         s2 = 1.0e-10;,//否则设置为1的﹣10次方
                     }
                 }
             }
             
             s1 *= s2;//直接相乘
         }
         else//只有其中一个限制
         {
             
             if (rqlist->have_lobound)
                 s1 *= rqlist->lobound;//下限
             else
                 s1 *= rqlist->hibound;//上限
         }
         
         rqnext = rqlist->next;//释放资源
         pfree(rqlist);
         rqlist = rqnext;
     }
 
     return s1;//返回选择率
 }

 
 Selectivity
 clause_selectivity(PlannerInfo *root,
                    Node *clause,
                    int varRelid,
                    JoinType jointype,
                    SpecialJoinInfo *sjinfo)
 {
     Selectivity s1 = 0.5;       
     RestrictInfo *rinfo = NULL;
     bool        cacheable = false;
 
     if (clause == NULL)         
         return s1;
 
     if (IsA(clause, RestrictInfo))//RestrictInfo
     {
         rinfo = (RestrictInfo *) clause;
 
         
         if (rinfo->pseudoconstant)//pseudoconstant,不影响选择率
         {
             if (!IsA(rinfo->clause, Const))
                 return (Selectivity) 1.0;//返回1.0
         }
 
         
         if (rinfo->norm_selec > 1)
             return (Selectivity) 1.0;//返回1.0
 
         
         if (varRelid == 0 ||
             bms_is_subset_singleton(rinfo->clause_relids, varRelid))//varRelid为0
         {
             
             if (jointype == JOIN_INNER)
             {
                 if (rinfo->norm_selec >= 0)
                     return rinfo->norm_selec;
             }
             else
             {
                 if (rinfo->outer_selec >= 0)
                     return rinfo->outer_selec;
             }
             cacheable = true;
         }
 
         
         if (rinfo->orclause)
             clause = (Node *) rinfo->orclause;
         else
             clause = (Node *) rinfo->clause;
     }
 
     if (IsA(clause, Var))//Var
     {
         Var        *var = (Var *) clause;
 
         
         if (var->varlevelsup == 0 &&
             (varRelid == 0 || varRelid == (int) var->varno))
         {
             
             s1 = boolvarsel(root, (Node *) var, varRelid);//bool Var选择率
         }
     }
     else if (IsA(clause, Const))//常量
     {
         
         Const      *con = (Const *) clause;
 
         s1 = con->constisnull ? 0.0 :
             DatumGetBool(con->constvalue) ? 1.0 : 0.0;//常量有效则为1.0,否则为0.0
     }
     else if (IsA(clause, Param))//参数
     {
         
         Node       *subst = estimate_expression_value(root, clause);
 
         if (IsA(subst, Const))
         {
             
             Const      *con = (Const *) subst;
 
             s1 = con->constisnull ? 0.0 :
                 DatumGetBool(con->constvalue) ? 1.0 : 0.0;
         }
         else
         {
             
         }
     }
     else if (not_clause(clause))//反选
     {
         
         s1 = 1.0 - clause_selectivity(root,
                                       (Node *) get_notclausearg((Expr *) clause),
                                       varRelid,
                                       jointype,
                                       sjinfo);
     }
     else if (and_clause(clause))//AND语句
     {
         
         s1 = clauselist_selectivity(root,
                                     ((BoolExpr *) clause)->args,
                                     varRelid,
                                     jointype,
                                     sjinfo);//递归调用
     }
     else if (or_clause(clause))//OR语句
     {
         
         ListCell   *arg;
 
         s1 = 0.0;
         foreach(arg, ((BoolExpr *) clause)->args)
         {
             Selectivity s2 = clause_selectivity(root,
                                                 (Node *) lfirst(arg),
                                                 varRelid,
                                                 jointype,
                                                 sjinfo);//递归调用
 
             s1 = s1 + s2 - s1 * s2;
         }
     }
     else if (is_opclause(clause) || IsA(clause, DistinctExpr))
     {
         OpExpr     *opclause = (OpExpr *) clause;
         Oid         opno = opclause->opno;
 
         if (treat_as_join_clause(clause, rinfo, varRelid, sjinfo))
         {
             
             s1 = join_selectivity(root, opno,
                                   opclause->args,
                                   opclause->inputcollid,
                                   jointype,
                                   sjinfo);//连接条件
         }
         else
         {
             
             s1 = restriction_selectivity(root, opno,
                                          opclause->args,
                                          opclause->inputcollid,
                                          varRelid);//限制条件
         }
 
         
         if (IsA(clause, DistinctExpr))
             s1 = 1.0 - s1;//Distinct?
     }
     else if (IsA(clause, ScalarArrayOpExpr))//数组
     {
         
         s1 = scalararraysel(root,
                             (ScalarArrayOpExpr *) clause,
                             treat_as_join_clause(clause, rinfo,
                                                  varRelid, sjinfo),
                             varRelid,
                             jointype,
                             sjinfo);
     }
     else if (IsA(clause, RowCompareExpr))//行对比
     {
         
         s1 = rowcomparesel(root,
                            (RowCompareExpr *) clause,
                            varRelid,
                            jointype,
                            sjinfo);
     }
     else if (IsA(clause, NullTest))//NullTest
     {
         
         s1 = nulltestsel(root,
                          ((NullTest *) clause)->nulltesttype,
                          (Node *) ((NullTest *) clause)->arg,
                          varRelid,
                          jointype,
                          sjinfo);
     }
     else if (IsA(clause, BooleanTest))//BooleanTest
     {
         
         s1 = booltestsel(root,
                          ((BooleanTest *) clause)->booltesttype,
                          (Node *) ((BooleanTest *) clause)->arg,
                          varRelid,
                          jointype,
                          sjinfo);
     }
     else if (IsA(clause, CurrentOfExpr))//CurrentOfExpr
     {
         
         CurrentOfExpr *cexpr = (CurrentOfExpr *) clause;
         RelOptInfo *crel = find_base_rel(root, cexpr->cvarno);
 
         if (crel->tuples > 0)
             s1 = 1.0 / crel->tuples;
     }
     else if (IsA(clause, RelabelType))//RelabelType
     {
         
         s1 = clause_selectivity(root,
                                 (Node *) ((RelabelType *) clause)->arg,
                                 varRelid,
                                 jointype,
                                 sjinfo);
     }
     else if (IsA(clause, CoerceToDomain))//CoerceToDomain
     {
         
         s1 = clause_selectivity(root,
                                 (Node *) ((CoerceToDomain *) clause)->arg,
                                 varRelid,
                                 jointype,
                                 sjinfo);
     }
     else
     {
         
         s1 = boolvarsel(root, clause, varRelid);//默认为bool Var
     }
 
     
     if (cacheable)//缓存?
     {
         if (jointype == JOIN_INNER)
             rinfo->norm_selec = s1;
         else
             rinfo->outer_selec = s1;
     }
 
 #ifdef SELECTIVITY_DEBUG
     elog(DEBUG4, "clause_selectivity: s1 %f", s1);
 #endif                          
 
     return s1;
 }

 
 static RelOptInfo *
 find_single_rel_for_clauses(PlannerInfo *root, List *clauses)
 {
     int         lastrelid = 0;
     ListCell   *l;
 
     foreach(l, clauses)
     {
         RestrictInfo *rinfo = (RestrictInfo *) lfirst(l);
         int         relid;
 
         
         if (!IsA(rinfo, RestrictInfo))
             return NULL;
 
         if (bms_is_empty(rinfo->clause_relids))
             continue;           
         if (!bms_get_singleton_member(rinfo->clause_relids, &relid))
             return NULL;        
         if (lastrelid == 0)
             lastrelid = relid;  
         else if (relid != lastrelid)
             return NULL;        
     }
 
     if (lastrelid != 0)
         return find_base_rel(root, lastrelid);
 
     return NULL;                
 }

//---------------------------------------------- clamp_row_est
 
 double
 clamp_row_est(double nrows)
 {
     
     if (nrows <= 1.0)
         nrows = 1.0;//小于1,返回1
     else
         nrows = rint(nrows);//整型
 
     return nrows;
 }
 
//---------------------------------------------- cost_qual_eval

 
 void
 cost_qual_eval(QualCost *cost, List *quals, PlannerInfo *root)
 {
     cost_qual_eval_context context;
     ListCell   *l;
 
     context.root = root;
     context.total.startup = 0;
     context.total.per_tuple = 0;
 
     
 
     foreach(l, quals)
     {
         Node       *qual = (Node *) lfirst(l);
 
         cost_qual_eval_walker(qual, &context);
     }
 
     *cost = context.total;
 }


//---------------------------------------------- set_rel_width

 
 static void
 set_rel_width(PlannerInfo *root, RelOptInfo *rel)
 {
     Oid         reloid = planner_rt_fetch(rel->relid, root)->relid;
     int32       tuple_width = 0;
     bool        have_wholerow_var = false;
     ListCell   *lc;
 
     
     rel->reltarget->cost.startup = 0;
     rel->reltarget->cost.per_tuple = 0;
 
     foreach(lc, rel->reltarget->exprs)
     {
         Node       *node = (Node *) lfirst(lc);
 
         
         if (IsA(node, Var) &&
             ((Var *) node)->varno == rel->relid)
         {
             Var        *var = (Var *) node;
             int         ndx;
             int32       item_width;
 
             Assert(var->varattno >= rel->min_attr);
             Assert(var->varattno <= rel->max_attr);
 
             ndx = var->varattno - rel->min_attr;
 
             
             if (var->varattno == 0)
             {
                 have_wholerow_var = true;
                 continue;
             }
 
             
             if (rel->attr_widths[ndx] > 0)
             {
                 tuple_width += rel->attr_widths[ndx];
                 continue;
             }
 
             
             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;
                 }
             }
 
             
             item_width = get_typavgwidth(var->vartype, var->vartypmod);
             Assert(item_width > 0);
             rel->attr_widths[ndx] = item_width;
             tuple_width += item_width;
         }
         else if (IsA(node, PlaceHolderVar))
         {
             
             PlaceHolderVar *phv = (PlaceHolderVar *) node;
             PlaceHolderInfo *phinfo = find_placeholder_info(root, phv, false);
             QualCost    cost;
 
             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
         {
             
             int32       item_width;
             QualCost    cost;
 
             item_width = get_typavgwidth(exprType(node), exprTypmod(node));
             Assert(item_width > 0);
             tuple_width += item_width;
             
             cost_qual_eval_node(&cost, node, root);
             rel->reltarget->cost.startup += cost.startup;
             rel->reltarget->cost.per_tuple += cost.per_tuple;
         }
     }
 
     
     if (have_wholerow_var)
     {
         int32       wholerow_width = MAXALIGN(SizeofHeapTupleHeader);
 
         if (reloid != InvalidOid)
         {
             
             wholerow_width += get_relation_data_width(reloid,
                                                       rel->attr_widths - rel->min_attr);
         }
         else
         {
             
             AttrNumber  i;
 
             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;
 
         
         tuple_width += wholerow_width;
     }
 
     Assert(tuple_width >= 0);
     rel->reltarget->width = tuple_width;
 }

三、跟踪分析

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

drop table if exists t_dwxx;
create table t_dwxx(dwmc varchar(100),dwbh varchar(20),dwdz varchar(100));
alter table t_dwxx add primary key(dwbh);
create index idx_dwxx_expr on t_dwxx(trim(dwmc));
create index idx_dwxx_predicate_dwbh on t_dwxx(dwbh) where dwbh > '50000';

truncate table t_dwxx;
insert into t_dwxx(dwmc,dwbh,dwdz) 
select 'DWMC'||generate_series(1,100000),generate_series(1,100000)||'','DWDZ'||generate_series(1,100000);

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

drop table if exists t_grxx;
create table t_grxx(dwbh varchar(10),grbh varchar(10),xm varchar(20),xb varchar(10),nl int);

insert into t_grxx(dwbh,grbh,xm,xb,nl) 
select generate_series(1,5000000)/50||'',generate_series(1,5000000),'XM'||generate_series(1,5000000),
(case when (floor(random()*2)=0) then '男' else '女' end),floor(random() * 100 + 1)::int;

create index idx_t_grxx_grbh on t_grxx(grbh);
create index idx_t_dwxx_grbh on t_grxx(dwbh);

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

drop table if exists t_jfxx;
create table t_jfxx(grbh varchar(10),ny varchar(10),je float);
-- 根据实际情况,多次执行以下SQL
insert into t_jfxx(grbh,ny,je) 
select generate_series(1,5000000),
       to_char(now()::timestamp - (floor(random()*240+1)||' month')::interval,'yyyymm'),
       floor(random()*10000+1);

create index idx_t_jfxx_grbh on t_jfxx(grbh);

执行简单的查询SQL:

select t1.* from t_dwxx t1 where dwbh > '60000' and dwbh < '70000' and dwbh < '65000';

执行计划如下:

testdb=# explain (analyze true,verbose) select t1.* from t_dwxx t1 where dwbh > '60000' and dwbh < '70000' and dwbh < '65000';
                                                               QUERY PLAN                                                    
           
-----------------------------------------------------------------------------------------------------------------------------
-----------
 Bitmap Heap Scan on public.t_dwxx t1  (cost=134.19..956.12 rows=5482 width=23) (actual time=1.484..2.744 rows=5554 loops=1)
   Output: dwmc, dwbh, dwdz
   Recheck Cond: (((t1.dwbh)::text > '60000'::text) AND ((t1.dwbh)::text < '70000'::text) AND ((t1.dwbh)::text < '65000'::tex
t))
   Heap Blocks: exact=45
   ->  Bitmap Index Scan on idx_dwxx_predicate_dwbh  (cost=0.00..132.81 rows=5482 width=0) (actual time=1.467..1.467 rows=555
4 loops=1)
         Index Cond: (((t1.dwbh)::text > '60000'::text) AND ((t1.dwbh)::text < '70000'::text) AND ((t1.dwbh)::text < '65000':
:text))
 Planning Time: 0.204 ms
 Execution Time: 3.288 ms

启动gdb跟踪分析:

(gdb) b set_baserel_size_estimates
Breakpoint 1 at 0x747bf5: file costsize.c, line 4302.
(gdb) c
Continuing.

Breakpoint 1, set_baserel_size_estimates (root=0x2686fa8, rel=0x26873b8) at costsize.c:4302
4302    nrows = rel->tuples *

进入函数clauselist_selectivity:

(gdb) step
clauselist_selectivity (root=0x2686fa8, clauses=0x271f600, varRelid=0, jointype=JOIN_INNER, sjinfo=0x0) at clausesel.c:105
105   Selectivity s1 = 1.0;
...
124   rel = find_single_rel_for_clauses(root, clauses);
(gdb) 
125   if (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}

开始循环处理:

152   foreach(l, clauses)
...

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

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

添加到范围条件语句中:

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

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

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

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

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

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

253   while (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) n
274         s2 = rqlist->hibound + rqlist->lobound - 1.0;
(gdb) 
277         s2 += nulltestsel(root, IS_NULL, rqlist->var,
#最终结果  
(gdb) 
325   return s1;
(gdb) p s1
$11 = 0.054823845775427538
...

回到主函数:

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

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

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