PostgreSQL物理优化中的create_index_paths->choose_bitmap_and函数分析

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

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

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

testdb=# explain verbose select t1.* 
testdb-# from t_dwxx t1 
testdb-# where (dwbh > '10000' and dwbh < '15000') AND (dwdz between 'DWDZ10000' and 'DWDZ15000');
QUERY PLAN                                
                                                    
----------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.t_dwxx t1  (cost=32.33..88.38 rows=33 width=20)
   Output: dwmc, dwbh, dwdz
   Recheck Cond: (((t1.dwbh)::text > '10000'::text) AND ((t1.dwbh)::text < '15000'::text) AND ((t1.dwdz)::text >= 'DWDZ10000'
::text) AND ((t1.dwdz)::text <= 'DWDZ15000'::text))
   ->  BitmapAnd  (cost=32.33..32.33 rows=33 width=0)  -->BitmapAnd
         ->  Bitmap Index Scan on t_dwxx_pkey  (cost=0.00..13.86 rows=557 width=0)
               Index Cond: (((t1.dwbh)::text > '10000'::text) AND ((t1.dwbh)::text < '15000'::text))
         ->  Bitmap Index Scan on idx_dwxx_dwdz  (cost=0.00..18.21 rows=592 width=0)
               Index Cond: (((t1.dwdz)::text >= 'DWDZ10000'::text) AND ((t1.dwdz)::text <= 'DWDZ15000'::text))
(8 rows)

一、数据结构

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

 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数

PathClauseUsage

 
 typedef struct
 {
     Path       *path;           
     List       *quals;          
     List       *preds;          
     Bitmapset  *clauseids;      
 } PathClauseUsage;

二、源码解读

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

 
 static Path *
 choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
 {
     int         npaths = list_length(paths);
     PathClauseUsage **pathinfoarray;
     PathClauseUsage *pathinfo;
     List       *clauselist;
     List       *bestpaths = NIL;
     Cost        bestcost = 0;
     int         i,
                 j;
     ListCell   *l;
 
     Assert(npaths > 0);         
     if (npaths == 1)
         return (Path *) linitial(paths);    
 
     
 
     
     pathinfoarray = (PathClauseUsage **)
         palloc(npaths * sizeof(PathClauseUsage *));//数组
     clauselist = NIL;
     npaths = 0;
     foreach(l, paths)//遍历paths
     {
         Path       *ipath = (Path *) lfirst(l);
 
         pathinfo = classify_index_clause_usage(ipath, &clauselist);//归类路径信息
         for (i = 0; i < npaths; i++)
         {
             if (bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
                 break;//只要发现子句集一样,就继续执行
         }
         if (i < npaths)//发现相同的
         {
             
             //相同的约束条件,只保留成本最低的
             Cost        ncost;
             Cost        ocost;
             Selectivity nselec;
             Selectivity oselec;
 
             cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);//计算成本
             cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
             if (ncost < ocost)
                 pathinfoarray[i] = pathinfo;
         }
         else//没有发现条件一样的,添加到数组中
         {
             
             pathinfoarray[npaths++] = pathinfo;
         }
     }
 
     
     if (npaths == 1)//结果只有一条,则返回之
         return pathinfoarray[0]->path;
 
     
     qsort(pathinfoarray, npaths, sizeof(PathClauseUsage *),
           path_usage_comparator);//以索引访问成本排序现存路径
 
     
     for (i = 0; i < npaths; i++)//遍历这些路径
     {
         Cost        costsofar;
         List       *qualsofar;
         Bitmapset  *clauseidsofar;
         ListCell   *lastcell;
 
         pathinfo = pathinfoarray[i];//PathClauseUsage结构体
         paths = list_make1(pathinfo->path);//路径链表
         costsofar = bitmap_scan_cost_est(root, rel, pathinfo->path);//当前的成本
         qualsofar = list_concat(list_copy(pathinfo->quals),
                                 list_copy(pathinfo->preds));
         clauseidsofar = bms_copy(pathinfo->clauseids);
         lastcell = list_head(paths);    
 
         for (j = i + 1; j < npaths; j++)//扫描后续的路径
         {
             Cost        newcost;
 
             pathinfo = pathinfoarray[j];
             
             if (bms_overlap(pathinfo->clauseids, clauseidsofar))
                 continue;       
             if (pathinfo->preds)//部分索引?
             {
                 bool        redundant = false;
 
                 
                 //单独检查每一个谓词
                 foreach(l, pathinfo->preds)
                 {
                     Node       *np = (Node *) lfirst(l);
 
                     if (predicate_implied_by(list_make1(np), qualsofar, false))
                     {
                         redundant = true;
                         break;  
                     }
                 }
                 if (redundant)
                     continue;
             }
             
             //尝试在路径中添加新路径，这样我们就可以估算成本
             paths = lappend(paths, pathinfo->path);
             newcost = bitmap_and_cost_est(root, rel, paths);//估算成本
             if (newcost < costsofar)//新成本更低
             {
                 
                 costsofar = newcost;
                 qualsofar = list_concat(qualsofar,
                                         list_copy(pathinfo->quals));//添加此条件
                 qualsofar = list_concat(qualsofar,
                                         list_copy(pathinfo->preds));//添加此谓词
                 clauseidsofar = bms_add_members(clauseidsofar,
                                                 pathinfo->clauseids);//添加此子句ID
                 lastcell = lnext(lastcell);
             }
             else
             {
                 
                 paths = list_delete_cell(paths, lnext(lastcell), lastcell);//去掉新路径
             }
             Assert(lnext(lastcell) == NULL);
         }
 
         
         if (i == 0 || costsofar < bestcost)//单条路径或者取得最小的成本
         {
             bestpaths = paths;
             bestcost = costsofar;
         }
 
         
         list_free(qualsofar);
     }
 
     if (list_length(bestpaths) == 1)
         return (Path *) linitial(bestpaths);    
     return (Path *) create_bitmap_and_path(root, rel, bestpaths);//生成BitmapAndPath
 }
 
//-------------------------------------------------------------------------- bitmap_scan_cost_est
 
 static Cost
 bitmap_scan_cost_est(PlannerInfo *root, RelOptInfo *rel, Path *ipath)
 {
     BitmapHeapPath bpath;
     Relids      required_outer;
 
     
     required_outer = get_bitmap_tree_required_outer(ipath);
 
     
     bpath.path.type = T_BitmapHeapPath;
     bpath.path.pathtype = T_BitmapHeapScan;
     bpath.path.parent = rel;
     bpath.path.pathtarget = rel->reltarget;
     bpath.path.param_info = get_baserel_parampathinfo(root, rel,
                                                       required_outer);
     bpath.path.pathkeys = NIL;
     bpath.bitmapqual = ipath;
 
     
     bpath.path.parallel_workers = 0;
     cost_bitmap_heap_scan(&bpath.path, root, rel,
                           bpath.path.param_info,
                           ipath,
                           get_loop_count(root, rel->relid, required_outer));//BitmapHeapPath计算成本
 
     return bpath.path.total_cost;
 }
 
//-------------------------------------------------------------------------- bitmap_and_cost_est

 
 static Cost
 bitmap_and_cost_est(PlannerInfo *root, RelOptInfo *rel, List *paths)
 {
     BitmapAndPath apath;
     BitmapHeapPath bpath;
     Relids      required_outer;
 
     
     apath.path.type = T_BitmapAndPath;
     apath.path.pathtype = T_BitmapAnd;
     apath.path.parent = rel;
     apath.path.pathtarget = rel->reltarget;
     apath.path.param_info = NULL;   
     apath.path.pathkeys = NIL;
     apath.bitmapquals = paths;
     cost_bitmap_and_node(&apath, root);
 
     
     required_outer = get_bitmap_tree_required_outer((Path *) &apath);
 
     
     bpath.path.type = T_BitmapHeapPath;
     bpath.path.pathtype = T_BitmapHeapScan;
     bpath.path.parent = rel;
     bpath.path.pathtarget = rel->reltarget;
     bpath.path.param_info = get_baserel_parampathinfo(root, rel,
                                                       required_outer);
     bpath.path.pathkeys = NIL;
     bpath.bitmapqual = (Path *) &apath;
 
     
     bpath.path.parallel_workers = 0;
 
     
     cost_bitmap_heap_scan(&bpath.path, root, rel,
                           bpath.path.param_info,
                           (Path *) &apath,
                           get_loop_count(root, rel->relid, required_outer));//BitmapHeapPath计算成本
 
     return bpath.path.total_cost;
 }
 

//-------------------------------------------------------------------------- create_bitmap_and_path

 
 BitmapAndPath *
 create_bitmap_and_path(PlannerInfo *root,
                        RelOptInfo *rel,
                        List *bitmapquals)
 {
     BitmapAndPath *pathnode = makeNode(BitmapAndPath);
 
     pathnode->path.pathtype = T_BitmapAnd;
     pathnode->path.parent = rel;
     pathnode->path.pathtarget = rel->reltarget;
     pathnode->path.param_info = NULL;   
 
     
     pathnode->path.parallel_aware = false;
     pathnode->path.parallel_safe = rel->consider_parallel;
     pathnode->path.parallel_workers = 0;
 
     pathnode->path.pathkeys = NIL;  
 
     pathnode->bitmapquals = bitmapquals;
 
     
     cost_bitmap_and_node(pathnode, root);//计算成本
 
     return pathnode;
 }

//----------------------------------------------------- cost_bitmap_and_node
 
 void
 cost_bitmap_and_node(BitmapAndPath *path, PlannerInfo *root)
 {
     Cost        totalCost;
     Selectivity selec;
     ListCell   *l;
 
     
     totalCost = 0.0;
     selec = 1.0;
     foreach(l, path->bitmapquals)
     {
         Path       *subpath = (Path *) lfirst(l);
         Cost        subCost;
         Selectivity subselec;
 
         cost_bitmap_tree_node(subpath, &subCost, &subselec);
 
         selec *= subselec;
 
         totalCost += subCost;
         if (l != list_head(path->bitmapquals))
             totalCost += 100.0 * cpu_operator_cost;
     }
     path->bitmapselectivity = selec;
     path->path.rows = 0;        
     path->path.startup_cost = totalCost;
     path->path.total_cost = totalCost;
 }

三、跟踪分析

测试脚本如下

select t1.* 
from t_dwxx t1 
where (dwbh > '10000' and dwbh < '15000') AND (dwdz between 'DWDZ10000' and 'DWDZ15000');

启动gdb跟踪

(gdb) b choose_bitmap_and
Breakpoint 1 at 0x74e8c2: file indxpath.c, line 1372.
(gdb) c
Continuing.

Breakpoint 1, choose_bitmap_and (root=0x1666638, rel=0x1666a48, paths=0x166fdf0) at indxpath.c:1372
1372    int     npaths = list_length(paths);

输入参数

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

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

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

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

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

收集信息到PathClauseUsage数组中

...
(gdb) n
1471        pathinfoarray[npaths++] = pathinfo;
(gdb) 
1445    foreach(l, paths)
(gdb) 
1476    if (npaths == 1)
(gdb) p npaths
$26 = 2
(gdb)

按成本排序

(gdb) n
1480    qsort(pathinfoarray, npaths, sizeof(PathClauseUsage *),

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

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

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

(gdb) p costsofar
$27 = 89.003250000000008
(gdb) n
1498      qualsofar = list_concat(list_copy(pathinfo->quals),

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

(gdb) n
1531        newcost = bitmap_and_cost_est(root, rel, paths);
(gdb) 
1532        if (newcost < costsofar)
(gdb) p newcost
$30 = 88.375456720095343
(gdb) n
1535          costsofar = newcost;
(gdb) n
1537                      list_copy(pathinfo->quals));
(gdb) 
1536          qualsofar = list_concat(qualsofar,
(gdb) 
1539                      list_copy(pathinfo->preds));

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

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

构建BitmapAndPath,返回

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

DONE!

(gdb) n
create_index_paths (root=0x1666638, rel=0x1666a48) at indxpath.c:337
337     bpath = create_bitmap_heap_path(root, rel, bitmapqual,

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