PostgreSQL 原始碼解讀(54)- 查詢語句#39(make_one_rel函式#4-生...

husthxd發表於2018-09-25

本節繼續介紹make_one_rel函式中的set_base_rel_sizes及其子函式。在set_base_rel_sizes函式呼叫過程中,如RTE為子查詢,則生成子查詢的訪問路徑,透過呼叫函式set_subquery_pathlist實現。

make_one_rel原始碼:

 RelOptInfo *
 make_one_rel(PlannerInfo *root, List *joinlist)
 {
     //...

     /*
      * Compute size estimates and consider_parallel flags for each base rel,
      * then generate access paths.
      */
     set_base_rel_sizes(root);//估算Relation的Size並且設定consider_parallel標記
     //...
 }

一、資料結構

RelOptInfo
如前所述,RelOptInfo資料結構貫穿整個最佳化過程.

 typedef struct RelOptInfo
 {
     NodeTag     type;//節點標識
 
     RelOptKind  reloptkind;//RelOpt型別
 
     /* all relations included in this RelOptInfo */
     Relids      relids;         /*Relids(rtindex)集合 set of base relids (rangetable indexes) */
 
     /* size estimates generated by planner */
     double      rows;           /*結果元組的估算數量 estimated number of result tuples */
 
     /* per-relation planner control flags */
     bool        consider_startup;   /*是否考慮啟動成本?是,需要保留啟動成本低的路徑 keep cheap-startup-cost paths? */
     bool        consider_param_startup; /*是否考慮引數化?的路徑 ditto, for parameterized paths? */
     bool        consider_parallel;  /*是否考慮並行處理路徑 consider parallel paths? */
 
     /* default result targetlist for Paths scanning this relation */
     struct PathTarget *reltarget;   /*掃描該Relation時預設的結果 list of Vars/Exprs, cost, width */
 
     /* materialization information */
     List       *pathlist;       /*訪問路徑連結串列 Path structures */
     List       *ppilist;        /*路徑連結串列中使用引數化路徑進行 ParamPathInfos used in pathlist */
     List       *partial_pathlist;   /* partial Paths */
     struct Path *cheapest_startup_path;//代價最低的啟動路徑
     struct Path *cheapest_total_path;//代價最低的整體路徑
     struct Path *cheapest_unique_path;//代價最低的獲取唯一值的路徑
     List       *cheapest_parameterized_paths;//代價最低的引數化路徑連結串列
 
     /* parameterization information needed for both base rels and join rels */
     /* (see also lateral_vars and lateral_referencers) */
     Relids      direct_lateral_relids;  /*使用lateral語法,需依賴的Relids rels directly laterally referenced */
     Relids      lateral_relids; /* minimum parameterization of rel */
 
     /* information about a base rel (not set for join rels!) */
     //reloptkind=RELOPT_BASEREL時使用的資料結構
     Index       relid;          /* Relation ID */
     Oid         reltablespace;  /* 表空間 containing tablespace */
     RTEKind     rtekind;        /* 基表?子查詢?還是函式等等?RELATION, SUBQUERY, FUNCTION, etc */
     AttrNumber  min_attr;       /* 最小的屬性編號 smallest attrno of rel (often <0) */
     AttrNumber  max_attr;       /* 最大的屬性編號 largest attrno of rel */
     Relids     *attr_needed;    /* 陣列 array indexed [min_attr .. max_attr] */
     int32      *attr_widths;    /* 屬性寬度 array indexed [min_attr .. max_attr] */
     List       *lateral_vars;   /* 關係依賴的Vars/PHVs LATERAL Vars and PHVs referenced by rel */
     Relids      lateral_referencers;    /*依賴該關係的Relids rels that reference me laterally */
     List       *indexlist;      /* 該關係的IndexOptInfo連結串列 list of IndexOptInfo */
     List       *statlist;       /* 統計資訊連結串列 list of StatisticExtInfo */
     BlockNumber pages;          /* 塊數 size estimates derived from pg_class */
     double      tuples;         /* 元組數 */
     double      allvisfrac;     /* ? */
     PlannerInfo *subroot;       /* 如為子查詢,儲存子查詢的root if subquery */
     List       *subplan_params; /* 如為子查詢,儲存子查詢的引數 if subquery */
     int         rel_parallel_workers;   /* 並行執行,需要多少個workers? wanted number of parallel workers */
 
     /* Information about foreign tables and foreign joins */
     //FDW相關資訊
     Oid         serverid;       /* identifies server for the table or join */
     Oid         userid;         /* identifies user to check access as */
     bool        useridiscurrent;    /* join is only valid for current user */
     /* use "struct FdwRoutine" to avoid including fdwapi.h here */
     struct FdwRoutine *fdwroutine;
     void       *fdw_private;
 
     /* cache space for remembering if we have proven this relation unique */
     //已知的,可保證唯一元組返回的Relids連結串列
     List       *unique_for_rels;    /* known unique for these other relid
                                      * set(s) */
     List       *non_unique_for_rels;    /* 已知的,返回的資料不唯一的Relids連結串列 known not unique for these set(s) */
 
     /* used by various scans and joins: */
     List       *baserestrictinfo;   /* 如為基本關係,則儲存約束條件 RestrictInfo structures (if base rel) */
     QualCost    baserestrictcost;   /* 解析約束表示式的成本? cost of evaluating the above */
     Index       baserestrict_min_security;  /* 最低安全等級 min security_level found in
                                              * baserestrictinfo */
     List       *joininfo;       /* 連線語句的約束條件資訊 RestrictInfo structures for join clauses
                                  * involving this rel */
     bool        has_eclass_joins;   /* 是否存在等價類連線? True意味著joininfo並不完整,,T means joininfo is incomplete */
 
     /* used by partitionwise joins: */
       //是否嘗試partitionwise連線,這是PG 11的一個新特性.
     bool        consider_partitionwise_join;    /* consider partitionwise
                                                  * join paths? (if
                                                  * partitioned rel) */
     Relids      top_parent_relids;  /* Relids of topmost parents (if "other"
                                      * rel) */
 
     /* used for partitioned relations */
     //分割槽表使用
     PartitionScheme part_scheme;    /* 分割槽的schema Partitioning scheme. */
     int         nparts;         /* 分割槽數 number of partitions */
     struct PartitionBoundInfoData *boundinfo;   /* 分割槽邊界資訊 Partition bounds */
     List       *partition_qual; /* 分割槽約束 partition constraint */
     struct RelOptInfo **part_rels;  /* 分割槽的RelOptInfo陣列 Array of RelOptInfos of partitions,
                                      * stored in the same order of bounds */
     List      **partexprs;      /* 非空分割槽鍵表示式 Non-nullable partition key expressions. */
     List      **nullable_partexprs; /* 可為空的分割槽鍵表示式 Nullable partition key expressions. */
     List       *partitioned_child_rels; /* RT Indexes連結串列 List of RT indexes. */
 } RelOptInfo;

二、原始碼解讀

set_rel_size
如前所述,set_rel_size函式估算關係的大小,如RTE為子查詢,則呼叫set_subquery_pathlist方法生成子查詢訪問路徑,相關程式碼如下:

 /*
  * set_rel_size
  *    Set size estimates for a base relation
  */
 static void
 set_rel_size(PlannerInfo *root, RelOptInfo *rel,
              Index rti, RangeTblEntry *rte)
 {
     if (rel->reloptkind == RELOPT_BASEREL &&
         relation_excluded_by_constraints(root, rel, rte))
     {
        //...
     }
     else
     {
         switch (rel->rtekind)
         {
             case //...
                 //...
             case RTE_SUBQUERY://子查詢
 
                 /*
                  * Subqueries don't support making a choice between
                  * parameterized and unparameterized paths, so just go ahead
                  * and build their paths immediately.
                  */
                 set_subquery_pathlist(root, rel, rti, rte);//生成子查詢訪問路徑
                 break;
             case ...
          }
        //...
      }
      //...
}

set_subquery_pathlist
生成子查詢的掃描路徑,在生成過程中嘗試下推外層的限制條件(減少參與運算的元組數量),並呼叫subquery_planner生成執行計劃.

 /*
  * set_subquery_pathlist
  *      Generate SubqueryScan access paths for a subquery RTE
  *    生成子查詢的掃描路徑
  *
  * We don't currently support generating parameterized paths for subqueries
  * by pushing join clauses down into them; it seems too expensive to re-plan
  * the subquery multiple times to consider different alternatives.
  * (XXX that could stand to be reconsidered, now that we use Paths.)
  * So the paths made here will be parameterized if the subquery contains
  * LATERAL references, otherwise not.  As long as that's true, there's no need
  * for a separate set_subquery_size phase: just make the paths right away.
  */
 static void
 set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
                       Index rti, RangeTblEntry *rte)
 {
     Query      *parse = root->parse;
     Query      *subquery = rte->subquery;
     Relids      required_outer;
     pushdown_safety_info safetyInfo;
     double      tuple_fraction;
     RelOptInfo *sub_final_rel;
     ListCell   *lc;
 
     /*
      * Must copy the Query so that planning doesn't mess up the RTE contents
      * (really really need to fix the planner to not scribble on its input,
      * someday ... but see remove_unused_subquery_outputs to start with).
      */
     subquery = copyObject(subquery);//複製
 
     /*
      * If it's a LATERAL subquery, it might contain some Vars of the current
      * query level, requiring it to be treated as parameterized, even though
      * we don't support pushing down join quals into subqueries.
      */
     required_outer = rel->lateral_relids;//外層的Relids
 
     /*
      * Zero out result area for subquery_is_pushdown_safe, so that it can set
      * flags as needed while recursing.  In particular, we need a workspace
      * for keeping track of unsafe-to-reference columns.  unsafeColumns[i]
      * will be set true if we find that output column i of the subquery is
      * unsafe to use in a pushed-down qual.
      */
     memset(&safetyInfo, 0, sizeof(safetyInfo));
     safetyInfo.unsafeColumns = (bool *)
         palloc0((list_length(subquery->targetList) + 1) * sizeof(bool));
 
     /*
      * If the subquery has the "security_barrier" flag, it means the subquery
      * originated from a view that must enforce row level security.  Then we
      * must not push down quals that contain leaky functions.  (Ideally this
      * would be checked inside subquery_is_pushdown_safe, but since we don't
      * currently pass the RTE to that function, we must do it here.)
      */
     safetyInfo.unsafeLeaky = rte->security_barrier;
 
     /*
      * If there are any restriction clauses that have been attached to the
      * subquery relation, consider pushing them down to become WHERE or HAVING
      * quals of the subquery itself.  This transformation is useful because it
      * may allow us to generate a better plan for the subquery than evaluating
      * all the subquery output rows and then filtering them.
      *    限制條件是否可以下推到子查詢中?如可以,最佳化器有可能生成更好的執行計劃
      *
      * There are several cases where we cannot push down clauses. Restrictions
      * involving the subquery are checked by subquery_is_pushdown_safe().
      * Restrictions on individual clauses are checked by
      * qual_is_pushdown_safe().  Also, we don't want to push down
      * pseudoconstant clauses; better to have the gating node above the
      * subquery.
      *
      * Non-pushed-down clauses will get evaluated as qpquals of the
      * SubqueryScan node.
      *
      * XXX Are there any cases where we want to make a policy decision not to
      * push down a pushable qual, because it'd result in a worse plan?
      */
     if (rel->baserestrictinfo != NIL &&
         subquery_is_pushdown_safe(subquery, subquery, &safetyInfo))
     {
     //可以下推限制條件
         /* OK to consider pushing down individual quals */
         List       *upperrestrictlist = NIL;
         ListCell   *l;
 
         foreach(l, rel->baserestrictinfo)//遍歷子查詢上的限制條件
         {
             RestrictInfo *rinfo = (RestrictInfo *) lfirst(l);
             Node       *clause = (Node *) rinfo->clause;
 
             if (!rinfo->pseudoconstant &&
                 qual_is_pushdown_safe(subquery, rti, clause, &safetyInfo))
             {
                 /* Push it down */
                 subquery_push_qual(subquery, rte, rti, clause);//下推限制條件
             }
             else
             {
                 /* Keep it in the upper query */
                 upperrestrictlist = lappend(upperrestrictlist, rinfo);//保留在上層中
             }
         }
         rel->baserestrictinfo = upperrestrictlist;
         /* We don't bother recomputing baserestrict_min_security */
     }
 
     pfree(safetyInfo.unsafeColumns);
 
     /*
      * The upper query might not use all the subquery's output columns; if
      * not, we can simplify.
      */
     remove_unused_subquery_outputs(subquery, rel);
 
     /*
      * We can safely pass the outer tuple_fraction down to the subquery if the
      * outer level has no joining, aggregation, or sorting to do. Otherwise
      * we'd better tell the subquery to plan for full retrieval. (XXX This
      * could probably be made more intelligent ...)
      */
     if (parse->hasAggs ||
         parse->groupClause ||
         parse->groupingSets ||
         parse->havingQual ||
         parse->distinctClause ||
         parse->sortClause ||
         has_multiple_baserels(root))
         tuple_fraction = 0.0;   /* default case */
     else
         tuple_fraction = root->tuple_fraction;
 
     /* plan_params should not be in use in current query level */
     Assert(root->plan_params == NIL);
 
     /* Generate a subroot and Paths for the subquery */
     rel->subroot = subquery_planner(root->glob, subquery,
                                     root,
                                     false, tuple_fraction);//呼叫subquery_planner獲取子查詢的執行計劃
 
     /* Isolate the params needed by this specific subplan */
     rel->subplan_params = root->plan_params;
     root->plan_params = NIL;
 
     /*
      * It's possible that constraint exclusion proved the subquery empty. If
      * so, it's desirable to produce an unadorned dummy path so that we will
      * recognize appropriate optimizations at this query level.
      */
     sub_final_rel = fetch_upper_rel(rel->subroot, UPPERREL_FINAL, NULL);//子查詢返回的最終關係
 
     if (IS_DUMMY_REL(sub_final_rel))
     {
         set_dummy_rel_pathlist(rel);
         return;
     }
 
     /*
      * Mark rel with estimated output rows, width, etc.  Note that we have to
      * do this before generating outer-query paths, else cost_subqueryscan is
      * not happy.
      */
     set_subquery_size_estimates(root, rel);//設定子查詢的估算資訊
 
     /*
      * For each Path that subquery_planner produced, make a SubqueryScanPath
      * in the outer query.
      */
     foreach(lc, sub_final_rel->pathlist)//遍歷最終關係的訪問路徑
     {
         Path       *subpath = (Path *) lfirst(lc);
         List       *pathkeys;
 
         /* Convert subpath's pathkeys to outer representation */
     //轉換pathkeys為外層的表示法
         pathkeys = convert_subquery_pathkeys(root,
                                              rel,
                                              subpath->pathkeys,
                                              make_tlist_from_pathtarget(subpath->pathtarget));
 
         /* Generate outer path using this subpath */
         add_path(rel, (Path *)
                  create_subqueryscan_path(root, rel, subpath,
                                           pathkeys, required_outer));//透過子查詢路徑生成外層訪問路徑
     }
 
     /* If outer rel allows parallelism, do same for partial paths. */
     if (rel->consider_parallel && bms_is_empty(required_outer))//是否可以並行處理
     {
         /* If consider_parallel is false, there should be no partial paths. */
         Assert(sub_final_rel->consider_parallel ||
                sub_final_rel->partial_pathlist == NIL);
 
         /* Same for partial paths. */
         foreach(lc, sub_final_rel->partial_pathlist)
         {
             Path       *subpath = (Path *) lfirst(lc);
             List       *pathkeys;
 
             /* Convert subpath's pathkeys to outer representation */
             pathkeys = convert_subquery_pathkeys(root,
                                                  rel,
                                                  subpath->pathkeys,
                                                  make_tlist_from_pathtarget(subpath->pathtarget));
 
             /* Generate outer path using this subpath */
             add_partial_path(rel, (Path *)
                              create_subqueryscan_path(root, rel, subpath,
                                                       pathkeys,
                                                       required_outer));
         }
     }
 }

//-------------------------------------------------------- create_subqueryscan_path
 /*
  * create_subqueryscan_path
  *    Creates a path corresponding to a scan of a subquery,
  *    returning the pathnode.
  */
 SubqueryScanPath *
 create_subqueryscan_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
                          List *pathkeys, Relids required_outer)
 {
     SubqueryScanPath *pathnode = makeNode(SubqueryScanPath);
 
     pathnode->path.pathtype = T_SubqueryScan;//路徑型別:子查詢掃描
     pathnode->path.parent = rel;//父RelOptInfo
     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 &&
         subpath->parallel_safe;
     pathnode->path.parallel_workers = subpath->parallel_workers;
     pathnode->path.pathkeys = pathkeys;//排序鍵
     pathnode->subpath = subpath;//子訪問路徑
 
     cost_subqueryscan(pathnode, root, rel, pathnode->path.param_info);//子查詢的成本
 
     return pathnode;
 }
 
//-------------------------------------------------------- cost_subqueryscan

 /*
  * cost_subqueryscan
  *    Determines and returns the cost of scanning a subquery RTE.
  *
  * 'baserel' is the relation to be scanned
  * 'param_info' is the ParamPathInfo if this is a parameterized path, else NULL
  */
 void
 cost_subqueryscan(SubqueryScanPath *path, PlannerInfo *root,
                   RelOptInfo *baserel, ParamPathInfo *param_info)
 {
     Cost        startup_cost;
     Cost        run_cost;
     QualCost    qpqual_cost;
     Cost        cpu_per_tuple;
 
     /* Should only be applied to base relations that are subqueries */
     Assert(baserel->relid > 0);
     Assert(baserel->rtekind == RTE_SUBQUERY);
 
     /* Mark the path with the correct row estimate */
     if (param_info)
         path->path.rows = param_info->ppi_rows;
     else
         path->path.rows = baserel->rows;
 
     /*
      * Cost of path is cost of evaluating the subplan, plus cost of evaluating
      * any restriction clauses and tlist that will be attached to the
      * SubqueryScan node, plus cpu_tuple_cost to account for selection and
      * projection overhead.
      */
     path->path.startup_cost = path->subpath->startup_cost;
     path->path.total_cost = path->subpath->total_cost;
 
     get_restriction_qual_cost(root, baserel, param_info, &qpqual_cost);
 
     startup_cost = qpqual_cost.startup;
     cpu_per_tuple = cpu_tuple_cost + qpqual_cost.per_tuple;
     run_cost = cpu_per_tuple * baserel->tuples;
 
     /* tlist eval costs are paid per output row, not per tuple scanned */
     startup_cost += path->path.pathtarget->cost.startup;
     run_cost += path->path.pathtarget->cost.per_tuple * path->path.rows;
 
     path->path.startup_cost += startup_cost;
     path->path.total_cost += startup_cost + run_cost;
 }
 

三、跟蹤分析

測試指令碼如下:

select t1.*,t2.dwbh,t2.counter 
from t_dwxx t1,
    (select dwbh,count(*) as counter from t_grxx group by dwbh) t2 
where t1.dwbh = t2.dwbh and t1.dwbh = '1001';

啟動gdb:

(gdb) c
Continuing.

Breakpoint 1, set_subquery_pathlist (root=0x2d749b0, rel=0x2d34dd0, rti=2, rte=0x2d341a0) at allpaths.c:2082
2082    Query    *parse = root->parse;

進入函式set_subquery_pathlist,輸入引數中的root->simple_rel_array[2],rtekind為RTE_SUBQUERY子查詢

(gdb) p *root->simple_rel_array[2]
$13 = {type = T_RelOptInfo, reloptkind = RELOPT_BASEREL, relids = 0x2d6a428, rows = 0, consider_startup = false, 
  consider_param_startup = false, consider_parallel = true, reltarget = 0x2d6a440, 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 = 2, reltablespace = 0, 
  rtekind = RTE_SUBQUERY, min_attr = 0, max_attr = 2, attr_needed = 0x2d69b00, attr_widths = 0x2d69b50, lateral_vars = 0x0, 
  lateral_referencers = 0x0, indexlist = 0x0, statlist = 0x0, pages = 0, tuples = 0, 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 = 0x2d6b648, baserestrictcost = {
    startup = 0, per_tuple = 0}, baserestrict_min_security = 0, joininfo = 0x0, has_eclass_joins = true, 
  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}
(gdb) p *rte
$10 = {type = T_RangeTblEntry, rtekind = RTE_SUBQUERY, relid = 0, relkind = 0 '\000', tablesample = 0x0, 
  subquery = 0x2d342b0, security_barrier = false, jointype = JOIN_INNER, joinaliasvars = 0x0, functions = 0x0, 
  funcordinality = false, tablefunc = 0x0, values_lists = 0x0, ctename = 0x0, ctelevelsup = 0, self_reference = false, 
  coltypes = 0x0, coltypmods = 0x0, colcollations = 0x0, enrname = 0x0, enrtuples = 0, alias = 0x2c82728, eref = 0x2d35328, 
  lateral = false, inh = false, inFromCl = true, requiredPerms = 0, checkAsUser = 0, selectedCols = 0x0, 
  insertedCols = 0x0, updatedCols = 0x0, securityQuals = 0x0}  

下推限制條件:

...
(gdb) n
2157          qual_is_pushdown_safe(subquery, rti, clause, &safetyInfo))
(gdb) 
2156        if (!rinfo->pseudoconstant &&
(gdb) 
2160          subquery_push_qual(subquery, rte, rti, clause);

tuple_fraction設定為0.0

...
(gdb) 
2193      tuple_fraction = 0.0; /* default case */

呼叫subquery_planner獲取執行計劃:

(gdb) n
2201    rel->subroot = subquery_planner(root->glob, subquery,

獲取子查詢生成的最終關係,reloptkind為RELOPT_UPPER_REL

...
(gdb) 
2214    sub_final_rel = fetch_upper_rel(rel->subroot, UPPERREL_FINAL, NULL);
(gdb) 
2216    if (IS_DUMMY_REL(sub_final_rel))
(gdb) p *sub_final_rel
$16 = {type = T_RelOptInfo, reloptkind = RELOPT_UPPER_REL, relids = 0x0, rows = 0, consider_startup = false, 
  consider_param_startup = false, consider_parallel = true, reltarget = 0x2d7bd50, pathlist = 0x2d7be10, ppilist = 0x0, 
  partial_pathlist = 0x0, cheapest_startup_path = 0x2d7aaa8, cheapest_total_path = 0x2d7aaa8, cheapest_unique_path = 0x0, 
  cheapest_parameterized_paths = 0x2d7be60, direct_lateral_relids = 0x0, lateral_relids = 0x0, relid = 0, 
  reltablespace = 0, rtekind = RTE_RELATION, min_attr = 0, max_attr = 0, attr_needed = 0x0, attr_widths = 0x0, 
  lateral_vars = 0x0, lateral_referencers = 0x0, indexlist = 0x0, statlist = 0x0, pages = 0, tuples = 0, allvisfrac = 0, 
  subroot = 0x0, subplan_params = 0x0, rel_parallel_workers = 0, serverid = 0, userid = 0, useridiscurrent = false, 
  fdwroutine = 0x0, fdw_private = 0x0, unique_for_rels = 0x0, non_unique_for_rels = 0x0, baserestrictinfo = 0x0, 
  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}

成本最低的路徑

(gdb) p *sub_final_rel->cheapest_total_path
$17 = {type = T_AggPath, pathtype = T_Agg, parent = 0x2d7b6d0, pathtarget = 0x2d7adc8, param_info = 0x0, 
  parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 10, startup_cost = 0.29249999999999998, 
  total_cost = 20.143376803383145, pathkeys = 0x0}

透過子查詢訪問路徑生成外層的訪問路徑

(gdb) n
2227    set_subquery_size_estimates(root, rel);
(gdb) 
2233    foreach(lc, sub_final_rel->pathlist)
(gdb) 
2235      Path     *subpath = (Path *) lfirst(lc);
(gdb) 
2239      pathkeys = convert_subquery_pathkeys(root,
(gdb) 
2246           create_subqueryscan_path(root, rel, subpath,
(gdb) 
2245      add_path(rel, (Path *)
(gdb) 
2233    foreach(lc, sub_final_rel->pathlist)

完成函式呼叫,結束處理

(gdb) 
set_rel_size (root=0x2d749b0, rel=0x2d34dd0, rti=2, rte=0x2d341a0) at allpaths.c:380
380         break;

執行計劃如下:

testdb=# explain verbose select t1.*,t2.dwbh,t2.counter from t_dwxx t1,(select dwbh,count(*) as counter from t_grxx group by dwbh) t2                                           
where t1.dwbh = t2.dwbh and t1.dwbh = '1001';
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.58..28.65 rows=10 width=32)
   Output: t1.dwmc, t1.dwbh, t1.dwdz, t_grxx.dwbh, (count(*))
   ->  Index Scan using t_dwxx_pkey on public.t_dwxx t1  (cost=0.29..8.30 rows=1 width=20)
         Output: t1.dwmc, t1.dwbh, t1.dwdz
         Index Cond: ((t1.dwbh)::text = '1001'::text)
   ->  GroupAggregate  (cost=0.29..20.14 rows=10 width=12)
         Output: t_grxx.dwbh, count(*)
         Group Key: t_grxx.dwbh
         ->  Index Only Scan using idx_t_dwxx_grbh on public.t_grxx  (cost=0.29..19.99 rows=10 width=4)
               Output: t_grxx.dwbh
               Index Cond: (t_grxx.dwbh = '1001'::text)
(11 rows)

四、參考資料

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

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

相關文章