PostgreSQL中set_base_rel_pathlists函数有什么作用

这篇文章主要介绍“postgresql中set_base_rel_pathlists函数有什么作用”，在日常操作中，相信很多人在Postgresql中set_base_rel_pathlists函数有什么作用问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL中set_base_rel_pathlists函数有什么作用”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

set_base_rel_pathlists函数的目的是为每一个base rel找出所有可用的访问路径(包括顺序扫描和所有可用的索引)，每一个可用的路径都会添加到pathlist链表中。

一、数据结构

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;   
 
     
     //FDW相关信息
     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;   
 
     
       //是否尝试partitionwise连接,这是PG 11的一个新特性.
     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;

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

 typedef struct QualCost
 {
     Cost        startup;        
     Cost        per_tuple;      
 } QualCost;
 
typedef double Cost; 

 
 
 
 
 #define DEFAULT_SEQ_PAGE_COST  1.0       //顺序扫描page的成本
 #define DEFAULT_RANDOM_PAGE_COST  4.0      //随机扫描page的成本
 #define DEFAULT_CPU_TUPLE_COST  0.01     //处理一个元组的CPU成本
 #define DEFAULT_CPU_INDEX_TUPLE_COST 0.005   //处理一个索引元组的CPU成本
 #define DEFAULT_CPU_OPERATOR_COST  0.0025    //执行一次操作或函数的CPU成本
 #define DEFAULT_PARALLEL_TUPLE_COST 0.1    //并行执行,从一个worker传输一个元组到另一个worker的成本
 #define DEFAULT_PARALLEL_SETUP_COST  1000.0  //构建并行执行环境的成本
 
 #define DEFAULT_EFFECTIVE_CACHE_SIZE  524288    

 double      seq_page_cost = DEFAULT_SEQ_PAGE_COST;
 double      random_page_cost = DEFAULT_RANDOM_PAGE_COST;
 double      cpu_tuple_cost = DEFAULT_CPU_TUPLE_COST;
 double      cpu_index_tuple_cost = DEFAULT_CPU_INDEX_TUPLE_COST;
 double      cpu_operator_cost = DEFAULT_CPU_OPERATOR_COST;
 double      parallel_tuple_cost = DEFAULT_PARALLEL_TUPLE_COST;
 double      parallel_setup_cost = DEFAULT_PARALLEL_SETUP_COST;
 
 int         effective_cache_size = DEFAULT_EFFECTIVE_CACHE_SIZE;
 
 Cost        disable_cost = 1.0e10;//1后面10个0,通过设置一个巨大的成本,让优化器自动放弃此路径
 
 int         max_parallel_workers_per_gather = 2;//每次gather使用的worker数

IndexClauseSet
用于收集匹配索引的的条件语句

 
 typedef struct
 {
     bool        nonempty;       
     
     List       *indexclauses[INDEX_MAX_KEYS];
 } IndexClauseSet;

二、源码解读

set_base_rel_pathlists函数遍历RelOptInfo数组,为每一个Rel构造访问路径,先前已介绍了顺序扫描的成本估算,本节介绍索引扫描的成本估算(函数:create_index_paths),通过调用set_plain_rel_pathlist->create_index_paths函数实现.

 
 static void
 set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
 {
     Relids      required_outer;
 
     //...
 
     
     create_index_paths(root, rel);
 
     
     create_tidscan_paths(root, rel);
 }

create_index_paths
create_index_paths函数生成Relation所有可能被选中的索引访问路径,详见源码注释.

 
 void
 create_index_paths(PlannerInfo *root, RelOptInfo *rel)
 {
     List       *indexpaths;//索引访问路径链表
     List       *bitindexpaths;//
     List       *bitjoinpaths;
     List       *joinorclauses;
     IndexClauseSet rclauseset;
     IndexClauseSet jclauseset;
     IndexClauseSet eclauseset;
     ListCell   *lc;
 
     
     if (rel->indexlist == NIL)//不存在索引,退出
         return;
 
     
     bitindexpaths = bitjoinpaths = joinorclauses = NIL;//初始赋值
 
     
     foreach(lc, rel->indexlist)//遍历索引链表
     {
         IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);//索引信息
 
         
         Assert(index->ncolumns <= INDEX_MAX_KEYS);
 
         
         if (index->indpred != NIL && !index->predOK)//部分索引,而且不能使用,不使用此索引
             continue;
 
         
         MemSet(&rclauseset, 0, sizeof(rclauseset));
         match_restriction_clauses_to_index(rel, index, &rclauseset);
 
         
         get_index_paths(root, rel, index, &rclauseset,
                         &bitindexpaths);
 
         
         MemSet(&jclauseset, 0, sizeof(jclauseset));
         match_join_clauses_to_index(root, rel, index,
                                     &jclauseset, &joinorclauses);
 
         
         MemSet(&eclauseset, 0, sizeof(eclauseset));
         match_eclass_clauses_to_index(root, index,
                                       &eclauseset);
 
         
         if (jclauseset.nonempty || eclauseset.nonempty)
             consider_index_join_clauses(root, rel, index,
                                         &rclauseset,
                                         &jclauseset,
                                         &eclauseset,
                                         &bitjoinpaths);
     }
 
     
     indexpaths = generate_bitmap_or_paths(root, rel,
                                           rel->baserestrictinfo, NIL);
     bitindexpaths = list_concat(bitindexpaths, indexpaths);//合并到bitindexpaths链表中
 
     
     indexpaths = generate_bitmap_or_paths(root, rel,
                                           joinorclauses, rel->baserestrictinfo);
     bitjoinpaths = list_concat(bitjoinpaths, indexpaths);//合并到bitjoinpaths链表中
 
     
     if (bitindexpaths != NIL)//存在位图索引访问路径
     {
         Path       *bitmapqual;//访问路径
         BitmapHeapPath *bpath;//BitmapHeapPath访问路径
 
         bitmapqual = choose_bitmap_and(root, rel, bitindexpaths);//位图表达式路径
         bpath = create_bitmap_heap_path(root, rel, bitmapqual,
                                         rel->lateral_relids, 1.0, 0);//BitmapHeapPath访问路径
         add_path(rel, (Path *) bpath);//添加到RelOptInfo中
 
         
         if (rel->consider_parallel && rel->lateral_relids == NULL)
             create_partial_bitmap_paths(root, rel, bitmapqual);//创建并行访问路径
     }
 
     
     if (bitjoinpaths != NIL)//bitjoinpaths位图连接访问路径
     {
         List       *path_outer;//依赖的外部Relids链表
         List       *all_path_outers;//依赖的外部路径Relids链表
         ListCell   *lc;//临时变量
 
         
         path_outer = all_path_outers = NIL;//初始化变量
         foreach(lc, bitjoinpaths)//遍历bitjoinpaths
         {
             Path       *path = (Path *) lfirst(lc);//访问路径
             Relids      required_outer;//依赖的外部Relids
 
             required_outer = get_bitmap_tree_required_outer(path);//
             path_outer = lappend(path_outer, required_outer);//添加到链表中
             if (!bms_equal_any(required_outer, all_path_outers))//不等,则添加到all_path_outers中
                 all_path_outers = lappend(all_path_outers, required_outer);
         }
 
         
         //对每一个唯一的参数化集合进行处理
         foreach(lc, all_path_outers)//遍历all_path_outers
         {
             Relids      max_outers = (Relids) lfirst(lc);
             List       *this_path_set;
             Path       *bitmapqual;
             Relids      required_outer;
             double      loop_count;
             BitmapHeapPath *bpath;
             ListCell   *lcp;
             ListCell   *lco;
 
             
             this_path_set = NIL;
             forboth(lcp, bitjoinpaths, lco, path_outer)//遍历
             {
                 Path       *path = (Path *) lfirst(lcp);
                 Relids      p_outers = (Relids) lfirst(lco);
 
                 if (bms_is_subset(p_outers, max_outers))//无需依赖其他Relids,添加到this_path_set中
                     this_path_set = lappend(this_path_set, path);
             }
 
             
             this_path_set = list_concat(this_path_set, bitindexpaths);//合并bitindexpaths访问路径
 
             
             bitmapqual = choose_bitmap_and(root, rel, this_path_set);//为此参数化处理选择最好的AND组合
 
             
             required_outer = get_bitmap_tree_required_outer(bitmapqual);
             loop_count = get_loop_count(root, rel->relid, required_outer);
             bpath = create_bitmap_heap_path(root, rel, bitmapqual,
                                             required_outer, loop_count, 0);//创建索引访问路径
             add_path(rel, (Path *) bpath);
         }
     }
 }

match_XXX
match_restriction_clauses_to_index函数验证限制条件是否与Index匹配,匹配的条件添加到clauseset中.
match_join_clauses_to_index函数验证连接条件是否与Index匹配,同样的,匹配的条件添加到clauseset中.
match_eclass_clauses_to_index函数验证EC连接条件是否与Index匹配,匹配的条件添加到clauseset中.

//--------------------------------------------------- match_restriction_clauses_to_index
 
 static void
 match_restriction_clauses_to_index(RelOptInfo *rel, IndexOptInfo *index,
                                    IndexClauseSet *clauseset)
 {
     
     //忽略部分(条件)索引,直接调用match_clauses_to_index
     match_clauses_to_index(index, index->indrestrictinfo, clauseset);
 }
 
//------------------------------- match_clauses_to_index

 
 static void
 match_clauses_to_index(IndexOptInfo *index,
                        List *clauses,
                        IndexClauseSet *clauseset)
 {
     ListCell   *lc;//临时变量
 
     foreach(lc, clauses)//遍历限制条件
     {
         RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
 
         match_clause_to_index(index, rinfo, clauseset);
     }
 }

//--------------------------------------------------- match_join_clauses_to_index

 
 static void
 match_join_clauses_to_index(PlannerInfo *root,
                             RelOptInfo *rel, IndexOptInfo *index,
                             IndexClauseSet *clauseset,
                             List **joinorclauses)
 {
     ListCell   *lc;//临时变量
 
     
     foreach(lc, rel->joininfo)//遍历连接条件
     {
         RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);
 
         
         if (!join_clause_is_movable_to(rinfo, rel))
             continue;
 
         
         if (restriction_is_or_clause(rinfo))
             *joinorclauses = lappend(*joinorclauses, rinfo);
         else
             match_clause_to_index(index, rinfo, clauseset);
     }
 }


//--------------------------------------------------- match_eclass_clauses_to_index

 
 static void
 match_eclass_clauses_to_index(PlannerInfo *root, IndexOptInfo *index,
                               IndexClauseSet *clauseset)
 {
     int         indexcol;
 
     
     if (!index->rel->has_eclass_joins)//没有ECs,返回
         return;
 
     for (indexcol = 0; indexcol < index->nkeycolumns; indexcol++)//遍历索引列
     {
         ec_member_matches_arg arg;
         List       *clauses;
 
         
         //生成条件子句链表
         arg.index = index;
         arg.indexcol = indexcol;
         clauses = generate_implied_equalities_for_column(root,
                                                          index->rel,
                                                          ec_member_matches_indexcol,
                                                          (void *) &arg,
                                                          index->rel->lateral_referencers);
 
         
         match_clauses_to_index(index, clauses, clauseset);
     }
 }

//---------------------------- generate_implied_equalities_for_column

 
 List *
 generate_implied_equalities_for_column(PlannerInfo *root,
                                        RelOptInfo *rel,
                                        ec_matches_callback_type callback,
                                        void *callback_arg,
                                        Relids prohibited_rels)
 {
     List       *result = NIL;//结果链表
     bool        is_child_rel = (rel->reloptkind == RELOPT_OTHER_MEMBER_REL);//是否子Relation
     Relids      parent_relids;//父Relids
     ListCell   *lc1;//变量
 
     
     Assert(IS_SIMPLE_REL(rel));
 
     
     if (is_child_rel)
         parent_relids = find_childrel_parents(root, rel);
     else
         parent_relids = NULL;   
 
     foreach(lc1, root->eq_classes)//遍历EC
     {
         EquivalenceClass *cur_ec = (EquivalenceClass *) lfirst(lc1);//当前的EC
         EquivalenceMember *cur_em;//EC成员
         ListCell   *lc2;//链表成员
 
         
         if (cur_ec->ec_has_const || list_length(cur_ec->ec_members) <= 1)
             continue;
 
         
         if (!is_child_rel &&
             !bms_is_subset(rel->relids, cur_ec->ec_relids))
             continue;
 
         
         cur_em = NULL;
         foreach(lc2, cur_ec->ec_members)//遍历EC的成员
         {
             cur_em = (EquivalenceMember *) lfirst(lc2);//当前成员
             if (bms_equal(cur_em->em_relids, rel->relids) &&
                 callback(root, rel, cur_ec, cur_em, callback_arg))//调用ec_member_matches_indexcol函数
                 break;//找到匹配的成员,跳出
             cur_em = NULL;
         }
 
         if (!cur_em)
             continue;
 
         
         foreach(lc2, cur_ec->ec_members)
         {
             EquivalenceMember *other_em = (EquivalenceMember *) lfirst(lc2);
             Oid         eq_op;
             RestrictInfo *rinfo;
 
             if (other_em->em_is_child)//
                 continue;       
 
             
             if (other_em == cur_em ||
                 bms_overlap(other_em->em_relids, rel->relids))//过滤cur_em
                 continue;
 
             
             if (bms_overlap(other_em->em_relids, prohibited_rels))
                 continue;
 
             
             if (is_child_rel &&
                 bms_overlap(parent_relids, other_em->em_relids))
                 continue;
 
             eq_op = select_equality_operator(cur_ec,
                                              cur_em->em_datatype,
                                              other_em->em_datatype);
             if (!OidIsValid(eq_op))
                 continue;
 
             
             rinfo = create_join_clause(root, cur_ec, eq_op,
                                        cur_em, other_em,
                                        cur_ec);//创建连接条件语句
 
             result = lappend(result, rinfo);
         }
 
         
         if (result)
             break;
     }
 
     return result;
 }

//---------------------------- match_clause_to_index

 
 static void
 match_clause_to_index(IndexOptInfo *index,
                       RestrictInfo *rinfo,
                       IndexClauseSet *clauseset)
 {
     int         indexcol;
 
     
     if (rinfo->pseudoconstant)
         return;
 
     
     if (!restriction_is_securely_promotable(rinfo, index->rel))
         return;
 
     
     for (indexcol = 0; indexcol < index->nkeycolumns; indexcol++)
     {
         if (match_clause_to_indexcol(index,
                                      indexcol,
                                      rinfo))
         {
             clauseset->indexclauses[indexcol] =
                 list_append_unique_ptr(clauseset->indexclauses[indexcol],
                                        rinfo);//赋值
             clauseset->nonempty = true;//设置标记
             return;
         }
     }
 }

//------------------- match_clause_to_indexcol

 
 static bool
 match_clause_to_indexcol(IndexOptInfo *index,
                          int indexcol,
                          RestrictInfo *rinfo)
 {
     Expr       *clause = rinfo->clause;//条件语句
     Index       index_relid = index->rel->relid;//Index的Relid
     Oid         opfamily;//操作符种类
     Oid         idxcollation;//索引排序规则
     Node       *leftop,//左节点
                *rightop;//右节点
     Relids      left_relids;//左节点相关Relids
     Relids      right_relids;//右节点相关Relids
     Oid         expr_op;//表达式操作符的Oid
     Oid         expr_coll;//表达式Collation的Oid
     bool        plain_op;//是否Plain操作符
 
     Assert(indexcol < index->nkeycolumns);
 
     opfamily = index->opfamily[indexcol];//获取操作符种类
     idxcollation = index->indexcollations[indexcol];//获取索引排序规则
 
     
     if (IsBooleanOpfamily(opfamily))//是否布尔类
     {
         if (match_boolean_index_clause((Node *) clause, indexcol, index))//是否匹配
             return true;//如匹配,返回T
     }
 
     
     if (is_opclause(clause))//OpExpr
     {
         leftop = get_leftop(clause);
         rightop = get_rightop(clause);
         if (!leftop || !rightop)
             return false;
         left_relids = rinfo->left_relids;
         right_relids = rinfo->right_relids;
         expr_op = ((OpExpr *) clause)->opno;
         expr_coll = ((OpExpr *) clause)->inputcollid;
         plain_op = true;
     }
     else if (clause && IsA(clause, ScalarArrayOpExpr))//ScalarArrayOpExpr
     {
         ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *) clause;
 
         
         if (!saop->useOr)
             return false;
         leftop = (Node *) linitial(saop->args);
         rightop = (Node *) lsecond(saop->args);
         left_relids = NULL;     
         right_relids = pull_varnos(rightop);
         expr_op = saop->opno;
         expr_coll = saop->inputcollid;
         plain_op = false;
     }
     else if (clause && IsA(clause, RowCompareExpr))//RowCompareExpr
     {
         return match_rowcompare_to_indexcol(index, indexcol,
                                             opfamily, idxcollation,
                                             (RowCompareExpr *) clause);
     }
     else if (index->amsearchnulls && IsA(clause, NullTest))//NullTest
     {
         NullTest   *nt = (NullTest *) clause;
 
         if (!nt->arGISrow &&
             match_index_to_operand((Node *) nt->arg, indexcol, index))
             return true;
         return false;
     }
     else
         return false;
 
     
     //处理:(indexkey operator constant)
     if (match_index_to_operand(leftop, indexcol, index) &&
         !bms_is_member(index_relid, right_relids) &&
         !contain_volatile_functions(rightop))
     {
         if (IndexCollMatchesExprColl(idxcollation, expr_coll) &&
             is_indexable_operator(expr_op, opfamily, true))//排序规则&操作符种类匹配
             return true;//返回T
 
         
         if (plain_op &&
             match_special_index_operator(clause, opfamily,
                                          idxcollation, true))//Plain操作&特殊操作符,返回T
             return true;
         return false;//否则,返回F
     }
 
     //处理(constant operator indexkey)
     if (plain_op &&
         match_index_to_operand(rightop, indexcol, index) &&
         !bms_is_member(index_relid, left_relids) &&
         !contain_volatile_functions(leftop))
     {
         if (IndexCollMatchesExprColl(idxcollation, expr_coll) &&
             is_indexable_operator(expr_op, opfamily, false))
             return true;
 
         
         if (match_special_index_operator(clause, opfamily,
                                          idxcollation, false))
             return true;
         return false;
     }
 
     return false;
 }

三、跟踪分析

测试脚本如下

select a.*,b.grbh,b.je 
from t_dwxx a,
    lateral (select t1.dwbh,t1.grbh,t2.je 
     from t_grxx t1 
          inner join t_jfxx t2 on t1.dwbh = a.dwbh and t1.grbh = t2.grbh) b
where a.dwbh = '1001'
order by b.dwbh;

注意:按先前的分析,SQL语句存在等价类{t_dwxx.dwbh t_grxx.dwbh '1001'}和{t_grxx.grbh t_jfxx.grbh},在构造t_grxx的索引访问路径时,使用等价类构造.

启动gdb,第一个RelOptInfo(对应t_dwxx)有3个Index,第二个RelOptInfo(对应t_grxx)有2个Index(分别是在dwbh和grbh上的索引),第三个RelOptInfo(对应t_jfxx)有1个Index(grbh上的索引),本节以t_jfxx和t_grxx为例进行跟踪分析

...
(gdb) c
Continuing.

Breakpoint 1, create_index_paths (root=0x2714c50, rel=0x2729530) at indxpath.c:242
242   if (rel->indexlist == NIL)
(gdb) p *(IndexOptInfo *)rel->indexlist->head->data.ptr_value
$38 = {type = T_IndexOptInfo, indexoid = 16750, reltablespace = 0, rel = 0x2729530, pages = 276, tuples = 100000, 
  tree_height = 1, ncolumns = 1, nkeycolumns = 1, indexkeys = 0x2729998, indexcollations = 0x27299b0, opfamily = 0x27299c8, 
  opcintype = 0x27299e0, sortopfamily = 0x27299c8, reverse_sort = 0x2729a10, nulls_first = 0x2729a28, 
  canreturn = 0x27299f8, relam = 403, indexprs = 0x0, indpred = 0x0, indextlist = 0x2729ae0, indrestrictinfo = 0x0, 
  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>}

输入信息是已熟知的root(PlannerInfo)和rel(RelOptInfo).首先进行索引遍历循环

(gdb) c
Continuing.

Breakpoint 1, create_index_paths (root=0x2714c50, rel=0x2729530) at indxpath.c:242
242   if (rel->indexlist == NIL)
(gdb) p *(IndexOptInfo *)rel->indexlist->head->data.ptr_value
$38 = {type = T_IndexOptInfo, indexoid = 16750, reltablespace = 0, rel = 0x2729530, pages = 276, tuples = 100000, 
  tree_height = 1, ncolumns = 1, nkeycolumns = 1, indexkeys = 0x2729998, indexcollations = 0x27299b0, opfamily = 0x27299c8, 
  opcintype = 0x27299e0, sortopfamily = 0x27299c8, reverse_sort = 0x2729a10, nulls_first = 0x2729a28, 
  canreturn = 0x27299f8, relam = 403, indexprs = 0x0, indpred = 0x0, indextlist = 0x2729ae0, indrestrictinfo = 0x0, 
  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>}

查询数据字典pg_class,oid=16750相应的索引是idx_t_jfxx_grbh

testdb=# select relname from pg_class where oid=16750;
     relname     
-----------------
 idx_t_jfxx_grbh
(1 row)

调用match_restriction_clauses_to_index和match_join_clauses_to_index,子句集合均为NULL

(gdb) 
match_restriction_clauses_to_index (rel=0x2729530, index=0x2729888, clauseset=0x7fff69cf0890) at indxpath.c:2117
2117  }
(gdb) 
create_index_paths (root=0x2714c50, rel=0x2729530) at indxpath.c:275
275     get_index_paths(root, rel, index, &rclauseset,
(gdb) 
284     MemSet(&jclauseset, 0, sizeof(jclauseset));
(gdb) 
285     match_join_clauses_to_index(root, rel, index,
(gdb) 
292     MemSet(&eclauseset, 0, sizeof(eclauseset));
(gdb) 
293     match_eclass_clauses_to_index(root, index,
(gdb) p rclauseset
$2 = {nonempty = false, indexclauses = {0x0 <repeats 32 times>}}
(gdb) p joinorclauses
$3 = (List *) 0x0
(gdb) p jclauseset
$4 = {nonempty = false, indexclauses = {0x0 <repeats 32 times>}}

进入match_eclass_clauses_to_index

...
268     match_restriction_clauses_to_index(rel, index, &rclauseset);
(gdb) step
match_restriction_clauses_to_index (rel=0x2724c88, index=0x27254d8, clauseset=0x7fff69cf0890) at indxpath.c:2116
2116    match_clauses_to_index(index, index->indrestrictinfo, clauseset);

进入generate_implied_equalities_for_column

...
(gdb) step
generate_implied_equalities_for_column (root=0x2714c50, rel=0x2729530, callback=0x7509b0 <ec_member_matches_indexcol>, 
    callback_arg=0x7fff69cf0620, prohibited_rels=0x0) at equivclass.c:2219
2219    List     *result = NIL;

等价类信息

...
2235      EquivalenceClass *cur_ec = (EquivalenceClass *) lfirst(lc1);
(gdb) 
2243      if (cur_ec->ec_has_const || list_length(cur_ec->ec_members) <= 1)
(gdb) p *cur_ec
$6 = {type = T_EquivalenceClass, ec_opfamilies = 0x272a268, ec_collation = 100, ec_members = 0x272a4a8, 
  ec_sources = 0x272a3f0, ec_derives = 0x272d2f0, ec_relids = 0x272a470, ec_has_const = false, ec_has_volatile = false, 
  ec_below_outer_join = false, ec_broken = false, ec_sortref = 0, ec_min_security = 0, ec_max_security = 0, ec_merged = 0x0}

遍历EC的成员后,cur_em不为NULL,查看cur_em内存结构(匹配的成员,即t_jfxx.grbh)

2281      foreach(lc2, cur_ec->ec_members)
(gdb) p *cur_em
$7 = {type = T_EquivalenceMember, em_expr = 0x2722890, em_relids = 0x272a238, em_nullable_relids = 0x0, 
  em_is_const = false, em_is_child = false, em_datatype = 25}
(gdb) p *cur_em->em_expr
$8 = {type = T_RelabelType}
(gdb) p *(RelabelType *)cur_em->em_expr
$9 = {xpr = {type = T_RelabelType}, arg = 0x2722840, resulttype = 25, resulttypmod = -1, resultcollid = 100, 
  relabelfORMat = COERCE_IMPLICIT_CAST, location = -1}
(gdb) p *((RelabelType *)cur_em->em_expr)->arg
$10 = {type = T_Var}
(gdb) p *(Var *)((RelabelType *)cur_em->em_expr)->arg
$11 = {xpr = {type = T_Var}, varno = 4, varattno = 1, vartype = 1043, vartypmod = 14, varcollid = 100, varlevelsup = 0, 
  varnoold = 4, varoattno = 1, location = 168}

再次遍历等价类的成员,得到第一个约束条件(t_jfxx.grbh=t_grxx.grbh)

(gdb) n
2314        rinfo = create_join_clause(root, cur_ec, eq_op,
(gdb) 
2318        result = lappend(result, rinfo);
(gdb) p *rinfo
$18 = {type = T_RestrictInfo, clause = 0x272d910, is_pushed_down = true, outerjoin_delayed = false, can_join = true, 
  pseudoconstant = false, leakproof = false, security_level = 0, clause_relids = 0x272db10, required_relids = 0x272d5f0, 
  outer_relids = 0x0, nullable_relids = 0x0, left_relids = 0x272dae0, right_relids = 0x272daf8, orclause = 0x0, 
  parent_ec = 0x272a340, eval_cost = {startup = 0, per_tuple = 0.0025000000000000001}, norm_selec = -1, outer_selec = -1, 
  mergeopfamilies = 0x272db48, left_ec = 0x272a340, right_ec = 0x272a340, left_em = 0x272a4d8, right_em = 0x272a420, 
  scansel_cache = 0x0, outer_is_left = false, hashjoinoperator = 98, left_bucketsize = -1, right_bucketsize = -1, 
  left_mcvfreq = -1, right_mcvfreq = -1}
(gdb) set $tmp1=(RelabelType *)((OpExpr *)rinfo->clause)->args->head->data.ptr_value
(gdb) set $tmp2=(RelabelType *)((OpExpr *)rinfo->clause)->args->head->next->data.ptr_value
(gdb) p *(Var *)$tmp1->arg
$31 = {xpr = {type = T_Var}, varno = 4, varattno = 1, vartype = 1043, vartypmod = 14, varcollid = 100, varlevelsup = 0, 
  varnoold = 4, varoattno = 1, location = 168}
(gdb) p *(Var *)$tmp2->arg
$32 = {xpr = {type = T_Var}, varno = 3, varattno = 2, vartype = 1043, vartypmod = 14, varcollid = 100, varlevelsup = 0, 
  varnoold = 3, varoattno = 2, location = 158}

获得了结果,返回到match_eclass_clauses_to_index

2281      foreach(lc2, cur_ec->ec_members)
(gdb) 
2326      if (result)
(gdb) 
2327        break;
(gdb) 
2330    return result;
(gdb) 
2331  }
(gdb) 
match_eclass_clauses_to_index (root=0x2714c50, index=0x2729888, clauseset=0x7fff69cf0670) at indxpath.c:2184
2184      match_clauses_to_index(index, clauses, clauseset);
...

下面再考察t_grxx.dwbh上的索引为例,分析match_clause_to_index

(gdb) c
Continuing.

Breakpoint 1, create_index_paths (root=0x2714c50, rel=0x2728c38) at indxpath.c:242
242   if (rel->indexlist == NIL)
(gdb) p *(IndexOptInfo *)rel->indexlist->head->data.ptr_value
$39 = {type = T_IndexOptInfo, indexoid = 16752, reltablespace = 0, rel = 0x2728c38, pages = 276, tuples = 100000, 
  tree_height = 1, ncolumns = 1, nkeycolumns = 1, indexkeys = 0x2729378, indexcollations = 0x2729390, opfamily = 0x27293a8, 
  opcintype = 0x27293c0, sortopfamily = 0x27293a8, reverse_sort = 0x27293f0, nulls_first = 0x2729408, 
  canreturn = 0x27293D8, relam = 403, indexprs = 0x0, indpred = 0x0, indextlist = 0x27294e0, indrestrictinfo = 0x272b040, 
  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>}

oid=16752,对应的object为idx_t_grxx_dwbh

testdb=# select relname from pg_class where oid=16752;
     relname     
-----------------
 idx_t_grxx_dwbh
(1 row)

进入IndexOptInfo循环,第一个元素对应的IndexOptInfo为idx_t_grxx_dwbh

249   foreach(lc, rel->indexlist)
(gdb) p *rel->indexlist
$40 = {type = T_List, length = 2, head = 0x2729510, tail = 0x2729218}
(gdb) p *(IndexOptInfo *)rel->indexlist->head->data->ptr_value
$42 = {type = T_IndexOptInfo, indexoid = 16752, reltablespace = 0, rel = 0x2728c38, pages = 276, tuples = 100000, 
  tree_height = 1, ncolumns = 1, nkeycolumns = 1, indexkeys = 0x2729378, indexcollations = 0x2729390, opfamily = 0x27293a8, 
  opcintype = 0x27293c0, sortopfamily = 0x27293a8, reverse_sort = 0x27293f0, nulls_first = 0x2729408, 
  canreturn = 0x27293d8, relam = 403, indexprs = 0x0, indpred = 0x0, indextlist = 0x27294e0, indrestrictinfo = 0x272b040, 
  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>}

一路小跑,进入match_clause_to_indexcol

...
(gdb) step
match_clause_to_indexcol (index=0x2729268, indexcol=0, rinfo=0x272ae58) at indxpath.c:2330
2330    Expr     *clause = rinfo->clause;
(gdb) n
2331    Index   index_relid = index->rel->relid;
(gdb) n
2344    opfamily = index->opfamily[indexcol];
(gdb) 
2345    idxcollation = index->indexcollations[indexcol];
(gdb) p index_relid
$47 = 3
(gdb) p opfamily
$48 = 1994
(gdb)

根据opfamily查询数据字典

testdb=# select * from pg_opfamily where oid=1994;
 opfmethod | opfname  | opfnamespace | opfowner 
-----------+----------+--------------+----------
       403 | text_ops |           11 |       10
(1 row)
-- 索引访问方法(btree)
testdb=# select * from pg_am where oid=403;
 amname | amhandler | amtype 
--------+-----------+--------
 btree  | bthandler | i
(1 row)

下面进入is_opclause判断分支

(gdb) p idxcollation
$49 = 100
(gdb) n
2360    if (is_opclause(clause))
(gdb) 
2362      leftop = get_leftop(clause);
(gdb) 
2363      rightop = get_rightop(clause);
(gdb) 
2364      if (!leftop || !rightop)
(gdb) p *leftop
$50 = {type = T_RelabelType}
(gdb) p *rightop
$51 = {type = T_Const}

限制条件下推后,形成限制条件t_grxx.dwbh = '1001'

#Var:t_grxx.dwbh
(gdb) p *(RelabelType *)leftop
$56 = {xpr = {type = T_RelabelType}, arg = 0x272ad80, resulttype = 25, resulttypmod = -1, resultcollid = 100, 
  relabelformat = COERCE_IMPLICIT_CAST, location = -1}
#常量:'1001'
(gdb) p *(Const *)rightop
$57 = {xpr = {type = T_Const}, consttype = 25, consttypmod = -1, constcollid = 100, constlen = -1, constvalue = 41069848, 
  constisnull = false, constbyval = false, location = 194}

执行相关判断,返回T

(gdb) n
2366      left_relids = rinfo->left_relids;
(gdb) 
2367      right_relids = rinfo->right_relids;
(gdb) 
2368      expr_op = ((OpExpr *) clause)->opno;
(gdb) 
2369      expr_coll = ((OpExpr *) clause)->inputcollid;
(gdb) 
2370      plain_op = true;
(gdb) 
2409    if (match_index_to_operand(leftop, indexcol, index) &&
(gdb) 
2410      !bms_is_member(index_relid, right_relids) &&
(gdb) 
2409    if (match_index_to_operand(leftop, indexcol, index) &&
(gdb) 
2411      !contain_volatile_functions(rightop))
(gdb) 
2410      !bms_is_member(index_relid, right_relids) &&
(gdb) 
2413      if (IndexCollMatchesExprColl(idxcollation, expr_coll) &&
(gdb) 
2414        is_indexable_operator(expr_op, opfamily, true))
(gdb) 
2413      if (IndexCollMatchesExprColl(idxcollation, expr_coll) &&
(gdb) 
2415        return true;

给clauseset变量赋值

(gdb) 
match_clause_to_index (index=0x2729268, rinfo=0x272ae58, clauseset=0x7fff69cf0890) at indxpath.c:2255
2255          list_append_unique_ptr(clauseset->indexclauses[indexcol],
(gdb) 
2254        clauseset->indexclauses[indexcol] =
(gdb) 
2257        clauseset->nonempty = true;
(gdb) 
2258        return;
(gdb) 
2261  }

返回到match_clauses_to_index

(gdb) 
match_clauses_to_index (index=0x2729268, clauses=0x272b040, clauseset=0x7fff69cf0890) at indxpath.c:2200
2200    foreach(lc, clauses)

到此，关于“PostgreSQL中set_base_rel_pathlists函数有什么作用”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注编程网网站，小编会继续努力为大家带来更多实用的文章！