PostgreSQL 原始碼解讀（61）- 查詢語句#46（make_one_rel函式#11-...

本節繼續介紹make_one_rel函式中的set_base_rel_pathlists->create_tidscan_paths函式，該函式建立相應的TID掃描路徑。

一、資料結構

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數

二、原始碼解讀

set_base_rel_pathlists->create_tidscan_paths函式建立相應的TID掃描路徑。

/*
 * create_tidscan_paths
 *    Create paths corresponding to direct TID scans of the given rel.
 *    建立相應的TID掃描路徑
 *
 *    Candidate paths are added to the rel's pathlist (using add_path).
 *    候選路徑會新增到關係的pathlist連結串列中
 */
void
create_tidscan_paths(PlannerInfo *root, RelOptInfo *rel)
{
    Relids      required_outer;
    List       *tidquals;

    /*
     * We don't support pushing join clauses into the quals of a tidscan, but
     * it could still have required parameterization due to LATERAL refs in
     * its tlist.
     */
    required_outer = rel->lateral_relids;//需依賴的外部relids

    tidquals = TidQualFromBaseRestrictinfo(rel);//tid條件子句

    if (tidquals)
        add_path(rel, (Path *) create_tidscan_path(root, rel, tidquals,
                                                   required_outer));//新增tid路徑(如有)
}

//-------------------------------------------------------------------------- TidQualFromBaseRestrictinfo
/*
 *  Extract a set of CTID conditions from the rel's baserestrictinfo list
 *  在關係的約束條件連結串列中抽取CTID條件集合
 */
static List *
TidQualFromBaseRestrictinfo(RelOptInfo *rel)
{
    List       *rlst = NIL;
    ListCell   *l;

    foreach(l, rel->baserestrictinfo)//迴圈遍歷約束條件
    {
        RestrictInfo *rinfo = (RestrictInfo *) lfirst(l);//約束條件

        /*
         * If clause must wait till after some lower-security-level
         * restriction clause, reject it.
         */
        if (!restriction_is_securely_promotable(rinfo, rel))
            continue;

        rlst = TidQualFromExpr((Node *) rinfo->clause, rel->relid);//獲取結果連結串列
        if (rlst)
            break;//如有,則退出
    }
    return rlst;
}

//------------------------------------------------------ TidQualFromExpr

 /*
  *  Extract a set of CTID conditions from the given qual expression
  *  給定條件表示式,獲取CTID條件集合
  *
  *  Returns a List of CTID qual expressions (with implicit OR semantics
  *  across the list), or NIL if there are no usable conditions.
  *  返回CTID條件表示式連結串列,如無則返回NIL
  *
  *  If the expression is an AND clause, we can use a CTID condition
  *  from any sub-clause.  If it is an OR clause, we must be able to
  *  extract a CTID condition from every sub-clause, or we can't use it.
  *  如為AND子句,從任意一個sub-clause中獲取;如為OR則從每一個sub-clause中獲取
  *
  *  In theory, in the AND case we could get CTID conditions from different
  *  sub-clauses, in which case we could try to pick the most efficient one.
  *  In practice, such usage seems very unlikely, so we don't bother; we
  *  just exit as soon as we find the first candidate.
  */
 static List *
 TidQualFromExpr(Node *expr, int varno)
 {
     List       *rlst = NIL;
     ListCell   *l;
 
     if (is_opclause(expr))//常規的表示式
     {
         /* base case: check for tideq opclause */
         if (IsTidEqualClause((OpExpr *) expr, varno))
             rlst = list_make1(expr);
     }
     else if (expr && IsA(expr, ScalarArrayOpExpr))//ScalarArrayOpExpr
     {
         /* another base case: check for tid = ANY clause */
         if (IsTidEqualAnyClause((ScalarArrayOpExpr *) expr, varno))
             rlst = list_make1(expr);
     }
     else if (expr && IsA(expr, CurrentOfExpr))//CurrentOfExpr
     {
         /* another base case: check for CURRENT OF on this rel */
         if (((CurrentOfExpr *) expr)->cvarno == varno)
             rlst = list_make1(expr);
     }
     else if (and_clause(expr))//AND
     {
         foreach(l, ((BoolExpr *) expr)->args)
         {
             rlst = TidQualFromExpr((Node *) lfirst(l), varno);
             if (rlst)
                 break;
         }
     }
     else if (or_clause(expr))//OR
     {
         foreach(l, ((BoolExpr *) expr)->args)
         {
             List       *frtn = TidQualFromExpr((Node *) lfirst(l), varno);
 
             if (frtn)
                 rlst = list_concat(rlst, frtn);
             else
             {
                 if (rlst)
                     list_free(rlst);
                 rlst = NIL;
                 break;
             }
         }
     }
     return rlst;
 }


//-------------------------------------------------------------------------- create_tidscan_path

/*
 * create_tidscan_path
 *    Creates a path corresponding to a scan by TID, returning the pathnode.
 *    建立訪問路徑,返回TidPath
 */
TidPath *
create_tidscan_path(PlannerInfo *root, RelOptInfo *rel, List *tidquals,
                    Relids required_outer)
{
    TidPath    *pathnode = makeNode(TidPath);

    pathnode->path.pathtype = T_TidScan;
    pathnode->path.parent = rel;
    pathnode->path.pathtarget = rel->reltarget;
    pathnode->path.param_info = get_baserel_parampathinfo(root, rel,
                                                          required_outer);
    pathnode->path.parallel_aware = false;
    pathnode->path.parallel_safe = rel->consider_parallel;
    pathnode->path.parallel_workers = 0;
    pathnode->path.pathkeys = NIL;  /* always unordered */

    pathnode->tidquals = tidquals;

    cost_tidscan(&pathnode->path, root, rel, tidquals,
                 pathnode->path.param_info);//計算成本

    return pathnode;
}

//-------------------------------------------------------- cost_tidscan

 /*
  * cost_tidscan
  *    Determines and returns the cost of scanning a relation using TIDs.
  *    計算並返回使用TIDs掃描的成本
  *
  * 'baserel' is the relation to be scanned
  * baserel-基礎關係
  * 'tidquals' is the list of TID-checkable quals
  * tidquals-TID條件表示式
  * 'param_info' is the ParamPathInfo if this is a parameterized path, else NULL
  * param_info-引數化路徑,如無則為NULL
  */
 void
 cost_tidscan(Path *path, PlannerInfo *root,
              RelOptInfo *baserel, List *tidquals, ParamPathInfo *param_info)
 {
     Cost        startup_cost = 0;
     Cost        run_cost = 0;
     bool        isCurrentOf = false;
     QualCost    qpqual_cost;
     Cost        cpu_per_tuple;
     QualCost    tid_qual_cost;
     int         ntuples;
     ListCell   *l;
     double      spc_random_page_cost;
 
     /* Should only be applied to base relations */
     Assert(baserel->relid > 0);
     Assert(baserel->rtekind == RTE_RELATION);
 
     /* Mark the path with the correct row estimate */
     if (param_info)
         path->rows = param_info->ppi_rows;
     else
         path->rows = baserel->rows;//行數
 
     /* Count how many tuples we expect to retrieve */
     ntuples = 0;
     foreach(l, tidquals)//遍歷條件表示式
     {
         if (IsA(lfirst(l), ScalarArrayOpExpr))//ScalarArrayOpExpr
         {
             /* Each element of the array yields 1 tuple */
             ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *) lfirst(l);
             Node       *arraynode = (Node *) lsecond(saop->args);
 
             ntuples += estimate_array_length(arraynode);
         }
         else if (IsA(lfirst(l), CurrentOfExpr))//CurrentOfExpr
         {
             /* CURRENT OF yields 1 tuple */
             isCurrentOf = true;
             ntuples++;
         }
         else
         {
             /* It's just CTID = something, count 1 tuple */
             ntuples++;//計數+1
         }
     }
 
     /*
      * We must force TID scan for WHERE CURRENT OF, because only nodeTidscan.c
      * understands how to do it correctly.  Therefore, honor enable_tidscan
      * only when CURRENT OF isn't present.  Also note that cost_qual_eval
      * counts a CurrentOfExpr as having startup cost disable_cost, which we
      * subtract off here; that's to prevent other plan types such as seqscan
      * from winning.
      */
     if (isCurrentOf)//CurrentOfExpr
     {
         Assert(baserel->baserestrictcost.startup >= disable_cost);
         startup_cost -= disable_cost;
     }
     else if (!enable_tidscan)//如禁用tidscan
         startup_cost += disable_cost;//設定為高成本
 
     /*
      * The TID qual expressions will be computed once, any other baserestrict
      * quals once per retrieved tuple.
      * TID條件表示式每計算一次，其他基本型別的表示式亦計算一次
      */
     cost_qual_eval(&tid_qual_cost, tidquals, root);
 
     /* fetch estimated page cost for tablespace containing table */
     get_tablespace_page_costs(baserel->reltablespace,
                               &spc_random_page_cost,
                               NULL);//表空間page訪問成本
 
     /* IO成本,假定每個元組都在不同的page中.disk costs --- assume each tuple on a different page */
     run_cost += spc_random_page_cost * ntuples;//執行成本
 
     /* CPU成本,Add scanning CPU costs */
     get_restriction_qual_cost(root, baserel, param_info, &qpqual_cost);//CPU掃描成本
 
     /* XXX currently we assume TID quals are a subset of qpquals */
     startup_cost += qpqual_cost.startup + tid_qual_cost.per_tuple;
     cpu_per_tuple = cpu_tuple_cost + qpqual_cost.per_tuple -
         tid_qual_cost.per_tuple;
     run_cost += cpu_per_tuple * ntuples;
 
     /* tlist eval costs are paid per output row, not per tuple scanned */
     startup_cost += path->pathtarget->cost.startup;
     run_cost += path->pathtarget->cost.per_tuple * path->rows;
 
     path->startup_cost = startup_cost;
     path->total_cost = startup_cost + run_cost;
 }
 

三、跟蹤分析

測試指令碼如下

select a.ctid,a.dwbh,a.dwmc,b.grbh,b.xm,b.xb,b.nl 
from t_dwxx a,t_grxx b 
where a.ctid = '(2,10)'::tid 
      and a.dwbh = b.dwbh;

啟動gdb,設定斷點

(gdb) b create_tidscan_paths
Breakpoint 2 at 0x759b06: file tidpath.c, line 263.
(gdb) c
Continuing.

Breakpoint 2, create_tidscan_paths (root=0x2869588, rel=0x2869998) at tidpath.c:263
263     required_outer = rel->lateral_relids;

進入create_tidscan_paths->TidQualFromBaseRestrictinfo函式

(gdb) n
265     tidquals = TidQualFromBaseRestrictinfo(rel);
(gdb) step
TidQualFromBaseRestrictinfo (rel=0x2869998) at tidpath.c:225
225     List       *rlst = NIL;

獲取TID條件表示式,對應的是:a.ctid = '(2,10)'::tid

...
(gdb) p *(Var *)$tmp->args->head->data.ptr_value
$11 = {xpr = {type = T_Var}, varno = 1, varattno = -1, vartype = 27, vartypmod = -1, varcollid = 0, varlevelsup = 0, 
  varnoold = 1, varoattno = -1, location = 81}
(gdb) p *(Const *)$tmp->args->head->next->data.ptr_value
$12 = {xpr = {type = T_Const}, consttype = 27, consttypmod = -1, constcollid = 0, constlen = 6, constvalue = 41705832, 
  constisnull = false, constbyval = false, location = 90}

進入create_tidscan_path函式

(gdb) 
create_tidscan_paths (root=0x2869588, rel=0x2869998) at tidpath.c:267
267     if (tidquals)
(gdb) n
268         add_path(rel, (Path *) create_tidscan_path(root, rel, tidquals,
(gdb) step
create_tidscan_path (root=0x2869588, rel=0x2869998, tidquals=0x287ef90, required_outer=0x0) at pathnode.c:1191
1191        TidPath    *pathnode = makeNode(TidPath);

進入cost_tidscan

(gdb) step
cost_tidscan (path=0x287eee0, root=0x2869588, baserel=0x2869998, tidquals=0x287ef90, param_info=0x0) at costsize.c:1184
1184        Cost        startup_cost = 0;
#解析表示式的CPU成本
(gdb) 
1249        cost_qual_eval(&tid_qual_cost, tidquals, root);
(gdb) 
1252        get_tablespace_page_costs(baserel->reltablespace,
(gdb) p tid_qual_cost
$14 = {startup = 0, per_tuple = 0.0025000000000000001}

計算完畢,返回結果

...
(gdb) 
1272        path->startup_cost = startup_cost;
(gdb) 
1273        path->total_cost = startup_cost + run_cost;
(gdb) 
1274    }
(gdb) 
(gdb) p *path
$17 = {type = T_TidPath, pathtype = T_TidScan, parent = 0x2869998, pathtarget = 0x287ac38, param_info = 0x0, 
  parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 1, startup_cost = 0.0025000000000000001, 
  total_cost = 4.0125000000000002, pathkeys = 0x0}

結束create_tidscan_paths函式呼叫

(gdb) n
create_tidscan_path (root=0x2869588, rel=0x2869998, tidquals=0x287ef90, required_outer=0x0) at pathnode.c:1208
1208        return pathnode;
(gdb) 
1209    }
(gdb) 
create_tidscan_paths (root=0x2869588, rel=0x2869998) at tidpath.c:270
270 }
(gdb) 
set_plain_rel_pathlist (root=0x2869588, rel=0x2869998, rte=0x27c5318) at allpaths.c:718
718 }

四、參考資料

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