PostgreSQL 原始碼解讀(59)- 查詢語句#44(make_one_rel函式#9-B...

husthxd發表於2018-09-29

本小節繼續介紹查詢物理最佳化中的create_index_paths->generate_bitmap_or_paths函式,該函式從條件子句連結串列中尋找OR子句,如找到並且可以處理則生成BitmapOrPath。
關於Bitmap Scan的相關知識,請參照PostgreSQL DBA(6) - SeqScan vs IndexScan vs BitmapHeapScan這篇文章。

下面是BitmapOrPath訪問路徑樣例:

testdb=# explain verbose select t1.* from t_dwxx t1 where (dwbh > '10000' and dwbh < '30000') OR (dwdz between 'DWDZ10000' and 'DWDZ20000');
QUERY PLAN                              
                                                       
---------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.t_dwxx t1  (cost=84.38..216.82 rows=3156 width=20)
   Output: dwmc, dwbh, dwdz
   Recheck Cond: ((((t1.dwbh)::text > '10000'::text) AND ((t1.dwbh)::text < '30000'::text)) OR (((t1.dwdz)::text >= 'DWDZ1000
0'::text) AND ((t1.dwdz)::text <= 'DWDZ20000'::text)))
   ->  BitmapOr  (cost=84.38..84.38 rows=3422 width=0)  -->BitmapOr
         ->  Bitmap Index Scan on t_dwxx_pkey  (cost=0.00..50.52 rows=2223 width=0)
               Index Cond: (((t1.dwbh)::text > '10000'::text) AND ((t1.dwbh)::text < '30000'::text))
         ->  Bitmap Index Scan on idx_dwxx_dwdz  (cost=0.00..32.28 rows=1200 width=0)
               Index Cond: (((t1.dwdz)::text >= 'DWDZ10000'::text) AND ((t1.dwdz)::text <= 'DWDZ20000'::text))
(8 rows)

一、資料結構

Cost相關
注意:實際使用的引數值透過系統配置檔案定義,而不是這裡的常量定義!

 typedef double Cost; /* execution cost (in page-access units) */

 /* defaults for costsize.c's Cost parameters */
 /* NB: cost-estimation code should use the variables, not these constants! */
 /* 注意:實際值透過系統配置檔案定義,而不是這裡的常量定義! */
 /* If you change these, update backend/utils/misc/postgresql.sample.conf */
 #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    /*先前已有介紹, measured in pages */

 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數

二、原始碼解讀

generate_bitmap_or_paths函式
create_index_paths->generate_bitmap_or_paths函式從條件子句連結串列中尋找OR子句,如找到並且可以處理則生成BitmapOrPath.函式返回生成的連結串列BitmapOrPaths.

 /*
  * generate_bitmap_or_paths
  *      Look through the list of clauses to find OR clauses, and generate
  *      a BitmapOrPath for each one we can handle that way.  Return a list
  *      of the generated BitmapOrPaths.
  *   從條件子句連結串列中尋找OR子句,如找到並且可以處理則生成BitmapOrPath.
  *     函式返回生成的連結串列BitmapOrPaths 
  *
  * other_clauses is a list of additional clauses that can be assumed true
  * for the purpose of generating indexquals, but are not to be searched for
  * ORs.  (See build_paths_for_OR() for motivation.)
  * other_clauses是一個附加子句連結串列,
  * 為了生成索引條件,可以假定為true,但不能用於搜尋OR子句。
  */
 static List *
 generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
                          List *clauses, List *other_clauses)
 {
     List       *result = NIL;
     List       *all_clauses;
     ListCell   *lc;
 
     /*
      * We can use both the current and other clauses as context for
      * build_paths_for_OR; no need to remove ORs from the lists.
      * 使用當前和其他子句作為build_paths_for_OR函式的輸入引數
      * 從而不需要從列表中刪除OR子句。
      */
     all_clauses = list_concat(list_copy(clauses), other_clauses);//合併到連結串列中
 
     foreach(lc, clauses)//遍歷子句連結串列
     {
         RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);//約束條件
         List       *pathlist;//路徑連結串列
         Path       *bitmapqual;//
         ListCell   *j;
 
         /* Ignore RestrictInfos that aren't ORs */
         if (!restriction_is_or_clause(rinfo))//不是OR子句,處理下一個子句
             continue;
 
         /*
          * We must be able to match at least one index to each of the arms of
          * the OR, else we can't use it.
          * 必須能夠將至少一個索引匹配到OR的某個分支,否則無法使用索引。
          */
         pathlist = NIL;
         foreach(j, ((BoolExpr *) rinfo->orclause)->args)//遍歷OR子句引數
         {
             Node       *orarg = (Node *) lfirst(j);//引數節點
             List       *indlist;
 
             /* OR arguments should be ANDs or sub-RestrictInfos */
             //OR子句的引數必須是AND子句或者是子約束條件
             if (and_clause(orarg))//如為AND子句
             {
                 List       *andargs = ((BoolExpr *) orarg)->args;//獲取AND子句的引數
 
                 indlist = build_paths_for_OR(root, rel,
                                              andargs,
                                              all_clauses);//構建路徑
 
                 /* Recurse in case there are sub-ORs */
                 //遞迴呼叫generate_bitmap_or_paths,並新增到訪問路徑連結串列中
                 indlist = list_concat(indlist,
                                       generate_bitmap_or_paths(root, rel,
                                                                andargs,
                                                                all_clauses));
             }
             else
             {
                 RestrictInfo *rinfo = castNode(RestrictInfo, orarg);//不是AND,則為約束條件
                 List       *orargs;
 
                 Assert(!restriction_is_or_clause(rinfo));
                 orargs = list_make1(rinfo);
 
                 indlist = build_paths_for_OR(root, rel,
                                              orargs,
                                              all_clauses);//構建訪問路徑
             }
 
             /*
              * If nothing matched this arm, we can't do anything with this OR
              * clause.
              */
             if (indlist == NIL)
             {
                 pathlist = NIL;
                 break;
             }
 
             /*
              * OK, pick the most promising AND combination, and add it to
              * pathlist.
              * 選擇最有希望的組合,並將其新增到路徑列表中。
              */
             bitmapqual = choose_bitmap_and(root, rel, indlist);
             pathlist = lappend(pathlist, bitmapqual);
         }
 
         /*
          * If we have a match for every arm, then turn them into a
          * BitmapOrPath, and add to result list.
          * 如果左右兩邊都匹配,那麼將它們轉換為BitmapOrPath,並新增到結果列表中。
          */
         if (pathlist != NIL)
         {
       //建立BitmapOrPath
             bitmapqual = (Path *) create_bitmap_or_path(root, rel, pathlist);
             result = lappend(result, bitmapqual);
         }
     }
 
     return result;
 }
 
//------------------------------------------------------ build_paths_for_OR
 /*
  * build_paths_for_OR
  *    Given a list of restriction clauses from one arm of an OR clause,
  *    construct all matching IndexPaths for the relation.
  *    給定OR子句的約束條件子句,構建該Relation所有匹配的索引訪問路徑.
  *
  * Here we must scan all indexes of the relation, since a bitmap OR tree
  * can use multiple indexes.
  * BitmapOr可能會使用多個索引,因此需要訪問該Relation的所有索引.
  *
  * The caller actually supplies two lists of restriction clauses: some
  * "current" ones and some "other" ones.  Both lists can be used freely
  * to match keys of the index, but an index must use at least one of the
  * "current" clauses to be considered usable.  The motivation for this is
  * examples like
  *      WHERE (x = 42) AND (... OR (y = 52 AND z = 77) OR ....)
  * While we are considering the y/z subclause of the OR, we can use "x = 42"
  * as one of the available index conditions; but we shouldn't match the
  * subclause to any index on x alone, because such a Path would already have
  * been generated at the upper level.  So we could use an index on x,y,z
  * or an index on x,y for the OR subclause, but not an index on just x.
  * When dealing with a partial index, a match of the index predicate to
  * one of the "current" clauses also makes the index usable.
  * 函式呼叫方提供了2個約束條件子句連結串列,一個是"current",另外一個是"other".
  * 這兩個連結串列都可以用於資源匹配索引鍵,但是索引必須使用至少一個存在於"current"中的子句. 
  * 舉個例子,有下面的條件語句:
  *     WHERE (x = 42) AND (... OR (y = 52 AND z = 77) OR ....)
  * 在考察OR中的x/z子句時,可以使用"x = 42"作為可用的索引條件,但
  * 但不應該將子句單獨與x上的任何索引進行匹配,因為這樣的訪問路徑已經在上層生成。
  * 因此可以在OR子句上使用x,y,z上索引或x,y上的索引,但不能是x上的索引.
  * 在處理部分索引時,與索引謂詞匹配的"current"子句同樣可以使用此索引.
  *
  * 'rel' is the relation for which we want to generate index paths
  * 'clauses' is the current list of clauses (RestrictInfo nodes)
  * 'other_clauses' is the list of additional upper-level clauses
  * 輸入引數:
  * rel-需要生成訪問路徑的Relation 
  * clauses-"current"子句,節點型別為RestrictInfo
  * other_clauses-"other"子句,上層子句已處理
  */
 static List *
 build_paths_for_OR(PlannerInfo *root, RelOptInfo *rel,
                    List *clauses, List *other_clauses)
 {
     List       *result = NIL;//返回結果
     List       *all_clauses = NIL;  /* not computed till needed */
     ListCell   *lc;//臨時變數
 
     foreach(lc, rel->indexlist)//遍歷RelOptInfo上的Index
     {
         IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);//IndexOptInfo
         IndexClauseSet clauseset;//
         List       *indexpaths;
         bool        useful_predicate;
 
         /* Ignore index if it doesn't support bitmap scans */
         if (!index->amhasgetbitmap)//索引不支援BitmapIndexScan
             continue;
 
         /*
          * Ignore partial indexes that do not match the query.  If a partial
          * index is marked predOK then we know it's OK.  Otherwise, we have to
          * test whether the added clauses are sufficient to imply the
          * predicate. If so, we can use the index in the current context.
          * 忽略不匹配查詢的部分索引。如predOK標記為T,則可考慮使用此索引.
          * 否則,必須測試新增的子句是否足以包含索引謂詞.
          * 在這種情況下才可以在當前上下文中使用索引。
          *
          * We set useful_predicate to true iff the predicate was proven using
          * the current set of clauses.  This is needed to prevent matching a
          * predOK index to an arm of an OR, which would be a legal but
          * pointlessly inefficient plan.  (A better plan will be generated by
          * just scanning the predOK index alone, no OR.)
          * 如驗證透過,則將useful_predicate設定為T。
      * 這是為了避免predOK索引與OR的某個分支相匹配,這是一個合法但無意義的低效計劃。
      * (只需要掃描部分索引就可以產生一個更好的計劃,但不是OR子句)
          */
         useful_predicate = false;
         if (index->indpred != NIL)
         {
             if (index->predOK)
             {
                 /* Usable, but don't set useful_predicate */
             }
             else
             {
                 /* Form all_clauses if not done already */
                 if (all_clauses == NIL)
                     all_clauses = list_concat(list_copy(clauses),
                                               other_clauses);
 
                 if (!predicate_implied_by(index->indpred, all_clauses, false))
                     continue;   /* can't use it at all */
 
                 if (!predicate_implied_by(index->indpred, other_clauses, false))
                     useful_predicate = true;
             }
         }
 
         /*
          * Identify the restriction clauses that can match the index.
          * 標記與索引匹配的約束條件子句
          */
         MemSet(&clauseset, 0, sizeof(clauseset));
         match_clauses_to_index(index, clauses, &clauseset);
 
         /*
          * If no matches so far, and the index predicate isn't useful, we
          * don't want it.
          */
         if (!clauseset.nonempty && !useful_predicate)//沒有合適的,繼續下一個索引
             continue;
 
         /*
          * Add "other" restriction clauses to the clauseset.
          */
         match_clauses_to_index(index, other_clauses, &clauseset);//新增到clauseset中
 
         /*
          * Construct paths if possible.
          */
         indexpaths = build_index_paths(root, rel,
                                        index, &clauseset,
                                        useful_predicate,
                                        ST_BITMAPSCAN,
                                        NULL,
                                        NULL);//構建索引訪問路徑
         result = list_concat(result, indexpaths);
     }
 
     return result;
 }
 

//------------------------------------------------------ create_bitmap_or_path

 /*
  * create_bitmap_or_path
  *    Creates a path node representing a BitmapOr.
  *    建立BitmapOr路徑節點
  */
 BitmapOrPath *
 create_bitmap_or_path(PlannerInfo *root,
                       RelOptInfo *rel,
                       List *bitmapquals)
 {
     BitmapOrPath *pathnode = makeNode(BitmapOrPath);
 
     pathnode->path.pathtype = T_BitmapOr;
     pathnode->path.parent = rel;
     pathnode->path.pathtarget = rel->reltarget;
     pathnode->path.param_info = NULL;   /* not used in bitmap trees */
 
     /*
      * Currently, a BitmapHeapPath, BitmapAndPath, or BitmapOrPath will be
      * parallel-safe if and only if rel->consider_parallel is set.  So, we can
      * set the flag for this path based only on the relation-level flag,
      * without actually iterating over the list of children.
      */
     pathnode->path.parallel_aware = false;
     pathnode->path.parallel_safe = rel->consider_parallel;
     pathnode->path.parallel_workers = 0;
 
     pathnode->path.pathkeys = NIL;  /* always unordered */
 
     pathnode->bitmapquals = bitmapquals;
 
     /* this sets bitmapselectivity as well as the regular cost fields: */
     cost_bitmap_or_node(pathnode, root);//計算成本
 
     return pathnode;
 }

//------------------------------------ create_bitmap_or_path

 /*
  * cost_bitmap_or_node
  *      Estimate the cost of a BitmapOr node
  *      估算BitmapOr成本
  *
  * See comments for cost_bitmap_and_node.
  */
 void
 cost_bitmap_or_node(BitmapOrPath *path, PlannerInfo *root)
 {
     Cost        totalCost;
     Selectivity selec;
     ListCell   *l;
 
     /*
      * We estimate OR selectivity on the assumption that the inputs are
      * non-overlapping, since that's often the case in "x IN (list)" type
      * situations.  Of course, we clamp to 1.0 at the end.
      * 我們估算或計算選擇率的前提是輸入不重疊,因為存在“x in (list)”這樣的情況。
      * 當然,我們在最後調整為1.0。
    *
      * The runtime cost of the BitmapOr itself is estimated at 100x
      * cpu_operator_cost for each tbm_union needed.  Probably too small,
      * definitely too simplistic?  We are aware that the tbm_unions are
      * optimized out when the inputs are BitmapIndexScans.
      * 對於所需的每個tbm_union操作, 
      * BitmapOr本身的執行時成本估計為100 x cpu_operator_cost。
      * 這個估值是否太小,太簡單了?其實,當輸入是點陣圖索引掃描時,tbm_unions已被最佳化。
      */
     totalCost = 0.0;//成本
     selec = 0.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) &&
             !IsA(subpath, IndexPath))
             totalCost += 100.0 * cpu_operator_cost;//非單個條件而且不是索引訪問路徑,則新增執行期成本
     }
     path->bitmapselectivity = Min(selec, 1.0);//選擇率
     path->path.rows = 0;        /* per above, not used */
     path->path.startup_cost = totalCost;
     path->path.total_cost = totalCost;
 }
 

三、跟蹤分析

測試指令碼如下

select t1.* 
from t_dwxx t1 
where (dwbh > '10000' and dwbh < '30000') 
      OR (dwdz between 'DWDZ10000' and 'DWDZ20000');

啟動gdb跟蹤

(gdb) b generate_bitmap_or_paths
Breakpoint 1 at 0x74e6c1: file indxpath.c, line 1266.
(gdb) c
Continuing.

Breakpoint 1, generate_bitmap_or_paths (root=0x2aa6248, rel=0x2aa6658, clauses=0x2aaf138, other_clauses=0x0)
    at indxpath.c:1266
1266    List     *result = NIL;

檢視輸入引數,clauses是連結串列,只有一個元素(BoolExpr型別,即OR子句);other_clauses為NULL

(gdb) p *clauses
$1 = {type = T_List, length = 1, head = 0x2aaf118, tail = 0x2aaf118}
(gdb) p *(Node *)clauses->head->data.ptr_value
$2 = {type = T_RestrictInfo}
(gdb) p *(RestrictInfo *)clauses->head->data.ptr_value
$3 = {type = T_RestrictInfo, clause = 0x2aad818, is_pushed_down = true, outerjoin_delayed = false, can_join = false, 
  pseudoconstant = false, leakproof = false, security_level = 0, clause_relids = 0x2aaf100, required_relids = 0x2aae938, 
  outer_relids = 0x0, nullable_relids = 0x0, left_relids = 0x0, right_relids = 0x0, orclause = 0x2aaefc0, parent_ec = 0x0, 
  eval_cost = {startup = 0, per_tuple = 0.01}, norm_selec = 0.31556115090433856, outer_selec = -1, mergeopfamilies = 0x0, 
  left_ec = 0x0, right_ec = 0x0, left_em = 0x0, right_em = 0x0, scansel_cache = 0x0, outer_is_left = false, 
  hashjoinoperator = 0, left_bucketsize = -1, right_bucketsize = -1, left_mcvfreq = -1, right_mcvfreq = -1}
(gdb) p *((RestrictInfo *)clauses->head->data.ptr_value)->clause
$4 = {type = T_BoolExpr}
(gdb) set $clause=((RestrictInfo *)clauses->head->data.ptr_value)->clause
(gdb) p *(BoolExpr *)$clause
$6 = {xpr = {type = T_BoolExpr}, boolop = OR_EXPR, args = 0x2aad758, location = -1}

遍歷clausees子句,rinfo->clause即BoolExpr(OR子句)

...
1276    foreach(lc, clauses)
(gdb) 
1278      RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
(gdb) 
1284      if (!restriction_is_or_clause(rinfo))

遍歷OR子句的引數

(gdb) 
1292      foreach(j, ((BoolExpr *) rinfo->orclause)->args)

引數的第一個元素,BoolExpr,boolop運算子為AND_EXPR

(gdb) n
1294        Node     *orarg = (Node *) lfirst(j);
(gdb) 
1298        if (and_clause(orarg))
(gdb) p *orarg
$10 = {type = T_BoolExpr}
(gdb) p *(BoolExpr *)orarg
$11 = {xpr = {type = T_BoolExpr}, boolop = AND_EXPR, args = 0x2aaea90, location = -1}

AND子句的引數

(gdb) n
1300          List     *andargs = ((BoolExpr *) orarg)->args;
(gdb) 
1302          indlist = build_paths_for_OR(root, rel,
(gdb) p *andargs
$12 = {type = T_List, length = 2, head = 0x2aada78, tail = 0x2aada98}
(gdb) p *(Node *)andargs->head->data.ptr_value
$13 = {type = T_RestrictInfo}
(gdb) p *(RestrictInfo *)andargs->head->data.ptr_value
$14 = {type = T_RestrictInfo, clause = 0x2aace08, is_pushed_down = true, outerjoin_delayed = false, can_join = false, 
  pseudoconstant = false, leakproof = false, security_level = 0, clause_relids = 0x2aaea78, required_relids = 0x2aaea78, 
  outer_relids = 0x0, nullable_relids = 0x0, left_relids = 0x2aaea60, right_relids = 0x0, orclause = 0x0, parent_ec = 0x0, 
  eval_cost = {startup = 0, per_tuple = 0.0025000000000000001}, norm_selec = 0.99990000000000001, outer_selec = -1, 
  mergeopfamilies = 0x0, left_ec = 0x0, right_ec = 0x0, left_em = 0x0, right_em = 0x0, scansel_cache = 0x0, 
  outer_is_left = false, hashjoinoperator = 0, left_bucketsize = -1, right_bucketsize = -1, left_mcvfreq = -1, 
  right_mcvfreq = -1}
(gdb) p *((RestrictInfo *)andargs->head->data.ptr_value)->clause
$15 = {type = T_OpExpr}
(gdb) set $tmp=((RestrictInfo *)andargs->head->data.ptr_value)->clause
(gdb) p *(OpExpr *)$tmp
$16 = {xpr = {type = T_OpExpr}, opno = 666, opfuncid = 742, opresulttype = 16, opretset = false, opcollid = 0, 
  inputcollid = 100, args = 0x2aacd68, location = 39}
(gdb) set $tmp2=((RestrictInfo *)andargs->head->next->data.ptr_value)->clause
(gdb) p *(OpExpr *)$tmp2
$17 = {xpr = {type = T_OpExpr}, opno = 664, opfuncid = 740, opresulttype = 16, opretset = false, opcollid = 0, 
  inputcollid = 100, args = 0x2aacc78, location = 58}
(gdb) 

進入build_paths_for_OR函式

(gdb) step
build_paths_for_OR (root=0x2aa6248, rel=0x2aa6658, clauses=0x2aaea90, other_clauses=0x2aaf598) at indxpath.c:1170
1170    List     *result = NIL;

遍歷索引,第一個索引是idx_dwxx_dwdz

1174    foreach(lc, rel->indexlist)
(gdb) 
1176      IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);
(gdb) 
1182      if (!index->amhasgetbitmap)
(gdb) p *index
$18 = {type = T_IndexOptInfo, indexoid = 16753, reltablespace = 0, rel = 0x2aa6658, pages = 40, tuples = 10000, 
  tree_height = 1, ncolumns = 1, nkeycolumns = 1, indexkeys = 0x2aae590, indexcollations = 0x2aae5a8, opfamily = 0x2aae5c0, 
  opcintype = 0x2aae5d8, sortopfamily = 0x2aae5c0, reverse_sort = 0x2aae608, nulls_first = 0x2aae620, 
  canreturn = 0x2aae5f0, relam = 403, indexprs = 0x0, indpred = 0x0, indextlist = 0x2aae6f8, indrestrictinfo = 0x2aaf138, 
  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>}
--
testdb=# select relname from pg_class where oid=16753;
    relname    
---------------
 idx_dwxx_dwdz
(1 row)
--  

與約束條件不匹配((dwbh > '10000' and dwbh < '30000')),繼續下一個索引

1229      if (!clauseset.nonempty && !useful_predicate)
(gdb) p clauseset
$20 = {nonempty = false, indexclauses = {0x0 <repeats 32 times>}}
(gdb) n
1230        continue;

下一個索引是idx_dwxx_predicate_dwmc/idx_dwxx_expr,同樣不匹配,繼續尋找索引,直至索引t_dwxx_pkey

(gdb) p *index
$23 = {type = T_IndexOptInfo, indexoid = 16738,...
1223      match_clauses_to_index(index, clauses, &clauseset);
(gdb) 
1229      if (!clauseset.nonempty && !useful_predicate)
(gdb) p clauseset
$24 = {nonempty = true, indexclauses = {0x2aaf638, 0x0 <repeats 31 times>}}

構建索引訪問路徑

(gdb) 
1246      result = list_concat(result, indexpaths);
(gdb) p *indexpaths
$25 = {type = T_List, length = 1, head = 0x2aafb48, tail = 0x2aafb48}
(gdb) p *(Node *)indexpaths->head->data.ptr_value
$26 = {type = T_IndexPath}
(gdb) p *(IndexPath *)indexpaths->head->data.ptr_value
$27 = {path = {type = T_IndexPath, pathtype = T_IndexScan, parent = 0x2aa6658, pathtarget = 0x2aad8d8, param_info = 0x0, 
    parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 3156, startup_cost = 0.28500000000000003, 
    total_cost = 191.46871600907946, pathkeys = 0x0}, indexinfo = 0x2aa6868, indexclauses = 0x2aaf6a8, 
  indexquals = 0x2aaf898, indexqualcols = 0x2aaf8e8, indexorderbys = 0x0, indexorderbycols = 0x0, 
  indexscandir = ForwardScanDirection, indextotalcost = 50.515000000000001, indexselectivity = 0.22227191011235958}

回到generate_bitmap_or_paths函式

1250  }
(gdb) 
generate_bitmap_or_paths (root=0x2aa6248, rel=0x2aa6658, clauses=0x2aaf138, other_clauses=0x0) at indxpath.c:1307
1307          indlist = list_concat(indlist,

遞迴進入generate_bitmap_or_paths

(gdb) n

Breakpoint 1, generate_bitmap_or_paths (root=0x2aa6248, rel=0x2aa6658, clauses=0x2aaea90, other_clauses=0x2aaf598)
    at indxpath.c:1266
1266    List     *result = NIL;
#直接結束
(gdb) finish
Run till exit from #0  generate_bitmap_or_paths (root=0x2aa6248, rel=0x2aa6658, clauses=0x2aaea90, other_clauses=0x2aaf598)
    at indxpath.c:1266
0x000000000074e7a0 in generate_bitmap_or_paths (root=0x2aa6248, rel=0x2aa6658, clauses=0x2aaf138, other_clauses=0x0)
    at indxpath.c:1307
1307          indlist = list_concat(indlist,
Value returned is $28 = (List *) 0x0

完成第一輪迴圈

(gdb) n
1329        if (indlist == NIL)
(gdb) n
1339        bitmapqual = choose_bitmap_and(root, rel, indlist);
(gdb) 
1340        pathlist = lappend(pathlist, bitmapqual);
(gdb) p *bitmapqual
$29 = {type = T_IndexPath, pathtype = T_IndexScan, parent = 0x2aa6658, pathtarget = 0x2aad8d8, param_info = 0x0, 
  parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 3156, startup_cost = 0.28500000000000003, 
  total_cost = 191.46871600907946, pathkeys = 0x0}

這是第二個AND子句

1292      foreach(j, ((BoolExpr *) rinfo->orclause)->args)
(gdb) 
1294        Node     *orarg = (Node *) lfirst(j);
(gdb) 
1298        if (and_clause(orarg))
(gdb) 
1300          List     *andargs = ((BoolExpr *) orarg)->args;

完成第二輪迴圈

(gdb) 
1339        bitmapqual = choose_bitmap_and(root, rel, indlist);
(gdb) 
1340        pathlist = lappend(pathlist, bitmapqual);
(gdb) p bitmapqual
$33 = (Path *) 0x2aafd78
(gdb) p *bitmapqual
$34 = {type = T_IndexPath, pathtype = T_IndexScan, parent = 0x2aa6658, pathtarget = 0x2aad8d8, param_info = 0x0, 
  parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 3156, startup_cost = 0.28500000000000003, 
  total_cost = 148.08735471522883, pathkeys = 0x0}

結束迴圈,構建BitmapOrPath

1347      if (pathlist != NIL)
(gdb) 
1349        bitmapqual = (Path *) create_bitmap_or_path(root, rel, pathlist);

進入create_bitmap_or_path,呼叫函式cost_bitmap_or_node計算成本

(gdb) step
create_bitmap_or_path (root=0x2aa6248, rel=0x2aa6658, bitmapquals=0x2aafbf8) at pathnode.c:1156
1156    BitmapOrPath *pathnode = makeNode(BitmapOrPath);
...
1178    cost_bitmap_or_node(pathnode, root);
(gdb) step
cost_bitmap_or_node (path=0x2ab0278, root=0x2aa6248) at costsize.c:1149
...

計算結果,與執行計劃中的資訊相匹配"BitmapOr (cost=84.38..84.38 rows=3422 width=0)"

(gdb) p *path
$37 = {path = {type = T_BitmapOrPath, pathtype = T_BitmapOr, parent = 0x2aa6658, pathtarget = 0x2aad8d8, param_info = 0x0, 
    parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 0, startup_cost = 84.378, 
    total_cost = 84.378, pathkeys = 0x0}, bitmapquals = 0x2aafbf8, bitmapselectivity = 0.34222288270157986}

回到generate_bitmap_or_paths

(gdb) n
create_bitmap_or_path (root=0x2aa6248, rel=0x2aa6658, bitmapquals=0x2aafbf8) at pathnode.c:1180
1180    return pathnode;
(gdb) 
1181  }
(gdb) 
generate_bitmap_or_paths (root=0x2aa6248, rel=0x2aa6658, clauses=0x2aaf138, other_clauses=0x0) at indxpath.c:1350
1350        result = lappend(result, bitmapqual);

完成,DONE!

(gdb) n
1276    foreach(lc, clauses)
(gdb) 
1354    return result;
(gdb) 
1355  }

四、參考資料

allpaths.c
cost.h
costsize.c
PG Document:Query Planning

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/6906/viewspace-2374845/,如需轉載,請註明出處,否則將追究法律責任。

相關文章