PostgreSQL 原始碼解讀(57)- 查詢語句#42(make_one_rel函式#7-索...

husthxd發表於2018-09-27

這一小節主要介紹函式build_index_paths中的子函式create_index_path,該函式實現了索引掃描成本的估算主邏輯。

一、資料結構

IndexOptInfo
回顧IndexOptInfo索引資訊結構體

 typedef struct IndexOptInfo
 {
     NodeTag     type;
 
     Oid         indexoid;       /* Index的OID,OID of the index relation */
     Oid         reltablespace;  /* Index的表空間,tablespace of index (not table) */
     RelOptInfo *rel;            /* 指向Relation的指標,back-link to index's table */
 
     /* index-size statistics (from pg_class and elsewhere) */
     BlockNumber pages;          /* Index的pages,number of disk pages in index */
     double      tuples;         /* Index的元組數,number of index tuples in index */
     int         tree_height;    /* 索引高度,index tree height, or -1 if unknown */
 
     /* index descriptor information */
     int         ncolumns;       /* 索引的列數,number of columns in index */
     int         nkeycolumns;    /* 索引的關鍵列數,number of key columns in index */
     int        *indexkeys;      /* column numbers of index's attributes both
                                  * key and included columns, or 0 */
     Oid        *indexcollations;    /* OIDs of collations of index columns */
     Oid        *opfamily;       /* OIDs of operator families for columns */
     Oid        *opcintype;      /* OIDs of opclass declared input data types */
     Oid        *sortopfamily;   /* OIDs of btree opfamilies, if orderable */
     bool       *reverse_sort;   /* 倒序?is sort order descending? */
     bool       *nulls_first;    /* NULLs值優先?do NULLs come first in the sort order? */
     bool       *canreturn;      /* 索引列可透過Index-Only Scan返回?which index cols can be returned in an
                                  * index-only scan? */
     Oid         relam;          /* 訪問方法OID,OID of the access method (in pg_am) */
 
     List       *indexprs;       /* 非簡單索引列表示式連結串列,如函式索引,expressions for non-simple index columns */
     List       *indpred;        /* 部分索引的謂詞連結串列,predicate if a partial index, else NIL */
 
     List       *indextlist;     /* 索引列(TargetEntry結構體連結串列),targetlist representing index columns */
 
     List       *indrestrictinfo;    /* 父關係的baserestrictinfo列表,
                                      * 不包含索引謂詞隱含的所有條件
                                      * (除非是目標rel,請參閱check_index_predicates()中的註釋),
                                      * parent relation's baserestrictinfo
                                      * list, less any conditions implied by
                                      * the index's predicate (unless it's a
                                      * target rel, see comments in
                                      * check_index_predicates()) */
 
     bool        predOK;         /* True,如索引謂詞滿足查詢要求,true if index predicate matches query */
     bool        unique;         /* 是否唯一索引,true if a unique index */
     bool        immediate;      /* 唯一性校驗是否立即生效,is uniqueness enforced immediately? */
     bool        hypothetical;   /* 是否虛擬索引,true if index doesn't really exist */
 
     /* Remaining fields are copied from the index AM's API struct: */
     //從Index Relation複製過來的AM(訪問方法)API資訊
     bool        amcanorderbyop; /* does AM support order by operator result? */
     bool        amoptionalkey;  /* can query omit key for the first column? */
     bool        amsearcharray;  /* can AM handle ScalarArrayOpExpr quals? */
     bool        amsearchnulls;  /* can AM search for NULL/NOT NULL entries? */
     bool        amhasgettuple;  /* does AM have amgettuple interface? */
     bool        amhasgetbitmap; /* does AM have amgetbitmap interface? */
     bool        amcanparallel;  /* does AM support parallel scan? */
     /* Rather than include amapi.h here, we declare amcostestimate like this */
     void        (*amcostestimate) ();   /* 訪問方法的估算函式,AM's cost estimator */
 } IndexOptInfo;
 

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數

二、原始碼解讀

create_index_path
該函式建立索引掃描路徑節點,其中呼叫函式cost_index計算索引掃描成本.


//----------------------------------------------- create_index_path

 /*
  * create_index_path
  *    Creates a path node for an index scan.
  *    建立索引掃描路徑節點
  *
  * 'index' is a usable index.
  * 'indexclauses' is a list of RestrictInfo nodes representing clauses
  *          to be used as index qual conditions in the scan.
  * 'indexclausecols' is an integer list of index column numbers (zero based)
  *          the indexclauses can be used with.
  * 'indexorderbys' is a list of bare expressions (no RestrictInfos)
  *          to be used as index ordering operators in the scan.
  * 'indexorderbycols' is an integer list of index column numbers (zero based)
  *          the ordering operators can be used with.
  * 'pathkeys' describes the ordering of the path.
  * 'indexscandir' is ForwardScanDirection or BackwardScanDirection
  *          for an ordered index, or NoMovementScanDirection for
  *          an unordered index.
  * 'indexonly' is true if an index-only scan is wanted.
  * 'required_outer' is the set of outer relids for a parameterized path.
  * 'loop_count' is the number of repetitions of the indexscan to factor into
  *      estimates of caching behavior.
  * 'partial_path' is true if constructing a parallel index scan path.
  *
  * Returns the new path node.
  */
 IndexPath *
 create_index_path(PlannerInfo *root,//最佳化器資訊
                   IndexOptInfo *index,//索引資訊
                   List *indexclauses,//索引約束條件連結串列
                   List *indexclausecols,//索引約束條件列編號連結串列,與indexclauses一一對應
                   List *indexorderbys,//ORDER BY原始表示式連結串列
                   List *indexorderbycols,//ORDER BY列編號連結串列
                   List *pathkeys,//排序路徑鍵
                   ScanDirection indexscandir,//掃描方向
                   bool indexonly,//純索引掃描?
                   Relids required_outer,//需依賴的外部Relids
                   double loop_count,//用於估計快取的重複次數
                   bool partial_path)//是否並行索引掃描
 {
     IndexPath  *pathnode = makeNode(IndexPath);//構建節點
     RelOptInfo *rel = index->rel;//索引對應的Rel
     List       *indexquals,
                *indexqualcols;
 
     pathnode->path.pathtype = indexonly ? T_IndexOnlyScan : T_IndexScan;//路徑型別
     pathnode->path.parent = rel;//Relation
     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;//worker數目
     pathnode->path.pathkeys = pathkeys;//排序路徑鍵
 
     /* Convert clauses to  the executor can handle */
     //轉換條件子句(clauses)為執行器可處理的索引表示式(indexquals)
     expand_indexqual_conditions(index, indexclauses, indexclausecols,
                                 &indexquals, &indexqualcols);
 
     /* 填充路徑節點資訊,Fill in the pathnode */
     pathnode->indexinfo = index;
     pathnode->indexclauses = indexclauses;
     pathnode->indexquals = indexquals;
     pathnode->indexqualcols = indexqualcols;
     pathnode->indexorderbys = indexorderbys;
     pathnode->indexorderbycols = indexorderbycols;
     pathnode->indexscandir = indexscandir;
 
     cost_index(pathnode, root, loop_count, partial_path);//估算成本
 
     return pathnode;
 }


//------------------------------------ expand_indexqual_conditions

 /*
  * expand_indexqual_conditions
  *    Given a list of RestrictInfo nodes, produce a list of directly usable
  *    index qual clauses.
  *    給定RestrictInfo節點(約束條件),產生直接可用的索引表示式子句
  *
  * Standard qual clauses (those in the index's opfamily) are passed through
  * unchanged.  Boolean clauses and "special" index operators are expanded
  * into clauses that the indexscan machinery will know what to do with.
  * RowCompare clauses are simplified if necessary to create a clause that is
  * fully checkable by the index.
  * 標準的條件子句(位於索引opfamily中)可不作修改直接使用.
  * 布林子句和"special"索引運算子擴充套件為索引掃描執行器可以處理的子句.
  * 如需要,RowCompare子句將簡化為可由索引完全檢查的子句。
  * 
  * In addition to the expressions themselves, there are auxiliary lists
  * of the index column numbers that the clauses are meant to be used with;
  * we generate an updated column number list for the result.  (This is not
  * the identical list because one input clause sometimes produces more than
  * one output clause.)
  * 除了表示式本身之外,還有索引列號的輔助連結串列,這些子句將使用這些列號.這些列號
  * 將被更新用於結果返回(不是相同的連結串列,因為一個輸入子句有時會產生多個輸出子句。).
  * 
  * The input clauses are sorted by column number, and so the output is too.
  * (This is depended on in various places in both planner and executor.)
  * 輸入子句透過列號排序,輸出子句也是如此.
  */
 void
 expand_indexqual_conditions(IndexOptInfo *index,
                             List *indexclauses, List *indexclausecols,
                             List **indexquals_p, List **indexqualcols_p)
 {
     List       *indexquals = NIL;
     List       *indexqualcols = NIL;
     ListCell   *lcc,
                *lci;
 
     forboth(lcc, indexclauses, lci, indexclausecols)//掃描索引子句連結串列和匹配的列號
     {
         RestrictInfo *rinfo = (RestrictInfo *) lfirst(lcc);
         int         indexcol = lfirst_int(lci);
         Expr       *clause = rinfo->clause;//條件子句
         Oid         curFamily;
         Oid         curCollation;
 
         Assert(indexcol < index->nkeycolumns);
 
         curFamily = index->opfamily[indexcol];//索引列的opfamily
         curCollation = index->indexcollations[indexcol];//排序規則
 
         /* First check for boolean cases */
         if (IsBooleanOpfamily(curFamily))//布林
         {
             Expr       *boolqual;
 
             boolqual = expand_boolean_index_clause((Node *) clause,
                                                    indexcol,
                                                    index);//布林表示式
             if (boolqual)
             {
                 indexquals = lappend(indexquals,
                                      make_simple_restrictinfo(boolqual));//新增到結果中
                 indexqualcols = lappend_int(indexqualcols, indexcol);//列號
                 continue;
             }
         }
 
         /*
          * Else it must be an opclause (usual case), ScalarArrayOp,
          * RowCompare, or NullTest
          */
         if (is_opclause(clause))//普通的運算子子句
         {
             indexquals = list_concat(indexquals,
                                      expand_indexqual_opclause(rinfo,
                                                                curFamily,
                                                                curCollation));//合併到結果連結串列中
             /* expand_indexqual_opclause can produce multiple clauses */
             while (list_length(indexqualcols) < list_length(indexquals))
                 indexqualcols = lappend_int(indexqualcols, indexcol);
         }
         else if (IsA(clause, ScalarArrayOpExpr))//ScalarArrayOpExpr
         {
             /* no extra work at this time */
             indexquals = lappend(indexquals, rinfo);
             indexqualcols = lappend_int(indexqualcols, indexcol);
         }
         else if (IsA(clause, RowCompareExpr))//RowCompareExpr
         {
             indexquals = lappend(indexquals,
                                  expand_indexqual_rowcompare(rinfo,
                                                              index,
                                                              indexcol));
             indexqualcols = lappend_int(indexqualcols, indexcol);
         }
         else if (IsA(clause, NullTest))//NullTest
         {
             Assert(index->amsearchnulls);
             indexquals = lappend(indexquals, rinfo);
             indexqualcols = lappend_int(indexqualcols, indexcol);
         }
         else
             elog(ERROR, "unsupported indexqual type: %d",
                  (int) nodeTag(clause));
     }
 
     *indexquals_p = indexquals;//結果賦值
     *indexqualcols_p = indexqualcols;
 }
 
//------------------------------------ cost_index
 /*
  * cost_index
  *    Determines and returns the cost of scanning a relation using an index.
  *    確定和返回索引掃描的成本 
  *
  * 'path' describes the indexscan under consideration, and is complete
  *      except for the fields to be set by this routine
  * path-位於考慮之列的索引掃描路徑,除了本例程要設定的欄位外其他資訊已完整.
  *
  * 'loop_count' is the number of repetitions of the indexscan to factor into
  *      estimates of caching behavior
  * loop_count-用於估計快取的重複次數
  *
  * In addition to rows, startup_cost and total_cost, cost_index() sets the
  * path's indextotalcost and indexselectivity fields.  These values will be
  * needed if the IndexPath is used in a BitmapIndexScan.
  * 除了行、startup_cost和total_cost之外,函式cost_index
  * 還設定了訪問路徑的indextotalcost和indexselectivity欄位。
  * 如果在點陣圖索引掃描中使用IndexPath,則需要這些值。
  *
  * NOTE: path->indexquals must contain only clauses usable as index
  * restrictions.  Any additional quals evaluated as qpquals may reduce the
  * number of returned tuples, but they won't reduce the number of tuples
  * we have to fetch from the table, so they don't reduce the scan cost.
  * 注意:path->indexquals必須僅包含用作索引約束條件的子句。任何作為qpquals評估的
  * 額外條件可能會減少返回元組的數量,但它們不會減少必須從表中獲取的元組
  * 數量,因此它們不會降低掃描成本。
  */
 void
 cost_index(IndexPath *path, PlannerInfo *root, double loop_count,
            bool partial_path)
 {
     IndexOptInfo *index = path->indexinfo;//索引資訊
     RelOptInfo *baserel = index->rel;//RelOptInfo資訊
     bool        indexonly = (path->path.pathtype == T_IndexOnlyScan);//是否純索引掃描
     amcostestimate_function amcostestimate;//索引訪問方法成本估算函式
     List       *qpquals;//qpquals連結串列
     Cost        startup_cost = 0;//啟動成本
     Cost        run_cost = 0;//執行成本
     Cost        cpu_run_cost = 0;//cpu執行成本
     Cost        indexStartupCost;//索引啟動成本
     Cost        indexTotalCost;//索引總成本
     Selectivity indexSelectivity;//選擇率
     double      indexCorrelation,//
                 csquared;//
     double      spc_seq_page_cost,
                 spc_random_page_cost;
     Cost        min_IO_cost,//最小IO成本
                 max_IO_cost;//最大IO成本
     QualCost    qpqual_cost;//表示式成本
     Cost        cpu_per_tuple;//每個tuple處理成本
     double      tuples_fetched;//取得的元組數量
     double      pages_fetched;//取得的page數量
     double      rand_heap_pages;//隨機訪問的堆page數量
     double      index_pages;//索引page數量
 
     /* Should only be applied to base relations */
     Assert(IsA(baserel, RelOptInfo) &&
            IsA(index, IndexOptInfo));
     Assert(baserel->relid > 0);
     Assert(baserel->rtekind == RTE_RELATION);
 
     /*
      * Mark the path with the correct row estimate, and identify which quals
      * will need to be enforced as qpquals.  We need not check any quals that
      * are implied by the index's predicate, so we can use indrestrictinfo not
      * baserestrictinfo as the list of relevant restriction clauses for the
      * rel.
      */
     if (path->path.param_info)//存在引數化資訊
     {
         path->path.rows = path->path.param_info->ppi_rows;
         /* qpquals come from the rel's restriction clauses and ppi_clauses */
         qpquals = list_concat(
                               extract_nonindex_conditions(path->indexinfo->indrestrictinfo,
                                                           path->indexquals),
                               extract_nonindex_conditions(path->path.param_info->ppi_clauses,
                                                           path->indexquals));
     }
     else
     {
         path->path.rows = baserel->rows;//基表的估算行數
         /* qpquals come from just the rel's restriction clauses */跑
         qpquals = extract_nonindex_conditions(path->indexinfo->indrestrictinfo,
                                               path->indexquals);//從rel的約束條件子句中獲取qpquals
     }
 
     if (!enable_indexscan)
         startup_cost += disable_cost;//禁用索引掃描
     /* we don't need to check enable_indexonlyscan; indxpath.c does that */
 
     /*
      * Call index-access-method-specific code to estimate the processing cost
      * for scanning the index, as well as the selectivity of the index (ie,
      * the fraction of main-table tuples we will have to retrieve) and its
      * correlation to the main-table tuple order.  We need a cast here because
      * relation.h uses a weak function type to avoid including amapi.h.
      */
     amcostestimate = (amcostestimate_function) index->amcostestimate;//索引訪問路徑成本估算函式
     amcostestimate(root, path, loop_count,
                    &indexStartupCost, &indexTotalCost,
                    &indexSelectivity, &indexCorrelation,
                    &index_pages);//呼叫函式btcostestimate
 
     /*
      * Save amcostestimate's results for possible use in bitmap scan planning.
      * We don't bother to save indexStartupCost or indexCorrelation, because a
      * bitmap scan doesn't care about either.
      */
     path->indextotalcost = indexTotalCost;//賦值
     path->indexselectivity = indexSelectivity;
 
     /* all costs for touching index itself included here */
     startup_cost += indexStartupCost;
     run_cost += indexTotalCost - indexStartupCost;
 
     /* estimate number of main-table tuples fetched */
     tuples_fetched = clamp_row_est(indexSelectivity * baserel->tuples);//取得的元組數量
 
     /* fetch estimated page costs for tablespace containing table */
     get_tablespace_page_costs(baserel->reltablespace,
                               &spc_random_page_cost,
                               &spc_seq_page_cost);//表空間訪問page成本
 
     /*----------
      * Estimate number of main-table pages fetched, and compute I/O cost.
      *
      * When the index ordering is uncorrelated with the table ordering,
      * we use an approximation proposed by Mackert and Lohman (see
      * index_pages_fetched() for details) to compute the number of pages
      * fetched, and then charge spc_random_page_cost per page fetched.
      *
      * When the index ordering is exactly correlated with the table ordering
      * (just after a CLUSTER, for example), the number of pages fetched should
      * be exactly selectivity * table_size.  What's more, all but the first
      * will be sequential fetches, not the random fetches that occur in the
      * uncorrelated case.  So if the number of pages is more than 1, we
      * ought to charge
      *      spc_random_page_cost + (pages_fetched - 1) * spc_seq_page_cost
      * For partially-correlated indexes, we ought to charge somewhere between
      * these two estimates.  We currently interpolate linearly between the
      * estimates based on the correlation squared (XXX is that appropriate?).
      *
      * If it's an index-only scan, then we will not need to fetch any heap
      * pages for which the visibility map shows all tuples are visible.
      * Hence, reduce the estimated number of heap fetches accordingly.
      * We use the measured fraction of the entire heap that is all-visible,
      * which might not be particularly relevant to the subset of the heap
      * that this query will fetch; but it's not clear how to do better.
      *----------
      */
     if (loop_count > 1)//次數 > 1
     {
         /*
          * For repeated indexscans, the appropriate estimate for the
          * uncorrelated case is to scale up the number of tuples fetched in
          * the Mackert and Lohman formula by the number of scans, so that we
          * estimate the number of pages fetched by all the scans; then
          * pro-rate the costs for one scan.  In this case we assume all the
          * fetches are random accesses.
          */
         pages_fetched = index_pages_fetched(tuples_fetched * loop_count,
                                             baserel->pages,
                                             (double) index->pages,
                                             root);
 
         if (indexonly)
             pages_fetched = ceil(pages_fetched * (1.0 - baserel->allvisfrac));
 
         rand_heap_pages = pages_fetched;
 
         max_IO_cost = (pages_fetched * spc_random_page_cost) / loop_count;
 
         /*
          * In the perfectly correlated case, the number of pages touched by
          * each scan is selectivity * table_size, and we can use the Mackert
          * and Lohman formula at the page level to estimate how much work is
          * saved by caching across scans.  We still assume all the fetches are
          * random, though, which is an overestimate that's hard to correct for
          * without double-counting the cache effects.  (But in most cases
          * where such a plan is actually interesting, only one page would get
          * fetched per scan anyway, so it shouldn't matter much.)
          */
         pages_fetched = ceil(indexSelectivity * (double) baserel->pages);
 
         pages_fetched = index_pages_fetched(pages_fetched * loop_count,
                                             baserel->pages,
                                             (double) index->pages,
                                             root);
 
         if (indexonly)
             pages_fetched = ceil(pages_fetched * (1.0 - baserel->allvisfrac));
 
         min_IO_cost = (pages_fetched * spc_random_page_cost) / loop_count;
     }
     else //次數 <= 1
     {
         /*
          * Normal case: apply the Mackert and Lohman formula, and then
          * interpolate between that and the correlation-derived result.
          */
         pages_fetched = index_pages_fetched(tuples_fetched,
                                             baserel->pages,
                                             (double) index->pages,
                                             root);//取得的page數量
 
         if (indexonly)
             pages_fetched = ceil(pages_fetched * (1.0 - baserel->allvisfrac));//純索引掃描
 
         rand_heap_pages = pages_fetched;//隨機訪問的堆page數量
 
         /* max_IO_cost is for the perfectly uncorrelated case (csquared=0) */
         //最大IO成本,假定所有的page都是隨機訪問獲得(csquared=0)
         max_IO_cost = pages_fetched * spc_random_page_cost;
 
         /* min_IO_cost is for the perfectly correlated case (csquared=1) */
         //最小IO成本,假定索引和堆資料都是順序儲存(csquared=1)
         pages_fetched = ceil(indexSelectivity * (double) baserel->pages);
 
         if (indexonly)
             pages_fetched = ceil(pages_fetched * (1.0 - baserel->allvisfrac));
 
         if (pages_fetched > 0)
         {
             min_IO_cost = spc_random_page_cost;
             if (pages_fetched > 1)
                 min_IO_cost += (pages_fetched - 1) * spc_seq_page_cost;
         }
         else
             min_IO_cost = 0;
     }
 
     if (partial_path)//並行
     {
         /*
          * For index only scans compute workers based on number of index pages
          * fetched; the number of heap pages we fetch might be so small as to
          * effectively rule out parallelism, which we don't want to do.
          */
         if (indexonly)
             rand_heap_pages = -1;
 
         /*
          * Estimate the number of parallel workers required to scan index. Use
          * the number of heap pages computed considering heap fetches won't be
          * sequential as for parallel scans the pages are accessed in random
          * order.
          */
         path->path.parallel_workers = compute_parallel_worker(baserel,
                                                               rand_heap_pages,
                                                               index_pages,
                                                               max_parallel_workers_per_gather);
 
         /*
          * Fall out if workers can't be assigned for parallel scan, because in
          * such a case this path will be rejected.  So there is no benefit in
          * doing extra computation.
          */
         if (path->path.parallel_workers <= 0)
             return;
 
         path->path.parallel_aware = true;
     }
 
     /*
      * Now interpolate based on estimated index order correlation to get total
      * disk I/O cost for main table accesses.
      * 根據估算的索引順序關聯來插值,以獲得主表訪問的總I/O成本
      */
     csquared = indexCorrelation * indexCorrelation;
 
     run_cost += max_IO_cost + csquared * (min_IO_cost - max_IO_cost);
 
     /*
      * Estimate CPU costs per tuple.
      * 估算處理每個元組的CPU成本
      *
      * What we want here is cpu_tuple_cost plus the evaluation costs of any
      * qual clauses that we have to evaluate as qpquals.
      */
     cost_qual_eval(&qpqual_cost, qpquals, root);
 
     startup_cost += qpqual_cost.startup;
     cpu_per_tuple = cpu_tuple_cost + qpqual_cost.per_tuple;
 
     cpu_run_cost += cpu_per_tuple * tuples_fetched;
 
     /* tlist eval costs are paid per output row, not per tuple scanned */
     startup_cost += path->path.pathtarget->cost.startup;
     cpu_run_cost += path->path.pathtarget->cost.per_tuple * path->path.rows;
 
     /* Adjust costing for parallelism, if used. */
     if (path->path.parallel_workers > 0)
     {
         double      parallel_divisor = get_parallel_divisor(&path->path);
 
         path->path.rows = clamp_row_est(path->path.rows / parallel_divisor);
 
         /* The CPU cost is divided among all the workers. */
         cpu_run_cost /= parallel_divisor;
     }
 
     run_cost += cpu_run_cost;
 
     path->path.startup_cost = startup_cost;
     path->path.total_cost = startup_cost + run_cost;
 }


//------------------------- btcostestimate
 void
 btcostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
                Cost *indexStartupCost, Cost *indexTotalCost,
                Selectivity *indexSelectivity, double *indexCorrelation,
                double *indexPages)
 {
     IndexOptInfo *index = path->indexinfo;
     List       *qinfos;
     GenericCosts costs;
     Oid         relid;
     AttrNumber  colnum;
     VariableStatData vardata;
     double      numIndexTuples;
     Cost        descentCost;
     List       *indexBoundQuals;
     int         indexcol;
     bool        eqQualHere;
     bool        found_saop;
     bool        found_is_null_op;
     double      num_sa_scans;
     ListCell   *lc;
 
     /* Do preliminary analysis of indexquals */
     qinfos = deconstruct_indexquals(path);//拆解路徑,生成條件連結串列
 
     /*
      * For a btree scan, only leading '=' quals plus inequality quals for the
      * immediately next attribute contribute to index selectivity (these are
      * the "boundary quals" that determine the starting and stopping points of
      * the index scan).  Additional quals can suppress visits to the heap, so
      * it's OK to count them in indexSelectivity, but they should not count
      * for estimating numIndexTuples.  So we must examine the given indexquals
      * to find out which ones count as boundary quals.  We rely on the
      * knowledge that they are given in index column order.
      * 對於btree掃描,只有下一個屬性的前導'='條件加上不等號條件
      * 有助於索引選擇性(這些是確定索引掃描起始和停止的“邊界條件”)。
      * 額外的條件可以抑制對堆資料的訪問,所以在indexSelectivity中
      * 統計它們是可以的,但是它們不應該在估算索引元組數目(索引也是元組的一種)的時
      * 候統計。因此,必須檢查給定的索引條件,以找出哪些被
      * 算作邊界條件。需依賴索引資訊給出的索引列順序進行判斷.
      *
      * For a RowCompareExpr, we consider only the first column, just as
      * rowcomparesel() does.
      *
      * If there's a ScalarArrayOpExpr in the quals, we'll actually perform N
      * index scans not one, but the ScalarArrayOpExpr's operator can be
      * considered to act the same as it normally does.
      */
     indexBoundQuals = NIL;//索引邊界條件
     indexcol = 0;//索引列編號
     eqQualHere = false;//
     found_saop = false;
     found_is_null_op = false;
     num_sa_scans = 1;
     foreach(lc, qinfos)//遍歷條件連結串列
     {
         IndexQualInfo *qinfo = (IndexQualInfo *) lfirst(lc);
         RestrictInfo *rinfo = qinfo->rinfo;
         Expr       *clause = rinfo->clause;
         Oid         clause_op;
         int         op_strategy;
 
         if (indexcol != qinfo->indexcol)//indexcol匹配才進行後續處理
         {
             /* Beginning of a new column's quals */
             if (!eqQualHere)
                 break;          /* done if no '=' qual for indexcol */
             eqQualHere = false;
             indexcol++;
             if (indexcol != qinfo->indexcol)
                 break;          /* no quals at all for indexcol */
         }
 
         if (IsA(clause, ScalarArrayOpExpr))//ScalarArrayOpExpr
         {
             int         alength = estimate_array_length(qinfo->other_operand);
 
             found_saop = true;
             /* count up number of SA scans induced by indexBoundQuals only */
             if (alength > 1)
                 num_sa_scans *= alength;
         }
         else if (IsA(clause, NullTest))
         {
             NullTest   *nt = (NullTest *) clause;
 
             if (nt->nulltesttype == IS_NULL)
             {
                 found_is_null_op = true;
                 /* IS NULL is like = for selectivity determination purposes */
                 eqQualHere = true;
             }
         }
 
         /*
          * We would need to commute the clause_op if not varonleft, except
          * that we only care if it's equality or not, so that refinement is
          * unnecessary.
          */
         clause_op = qinfo->clause_op;
 
         /* check for equality operator */
         if (OidIsValid(clause_op))//普通的運算子
         {
             op_strategy = get_op_opfamily_strategy(clause_op,
                                                    index->opfamily[indexcol]);
             Assert(op_strategy != 0);   /* not a member of opfamily?? */
             if (op_strategy == BTEqualStrategyNumber)
                 eqQualHere = true;
         }
 
         indexBoundQuals = lappend(indexBoundQuals, rinfo);
     }
 
     /*
      * If index is unique and we found an '=' clause for each column, we can
      * just assume numIndexTuples = 1 and skip the expensive
      * clauselist_selectivity calculations.  However, a ScalarArrayOp or
      * NullTest invalidates that theory, even though it sets eqQualHere.
      * 如果index是唯一的,並且我們為每個列找到了一個'='子句,那麼可以
      * 假設numIndexTuples = 1,並跳過昂貴的clauselist_selectivity計算結果。
      * 這種判斷不適用於ScalarArrayOp或NullTest。
      */
     if (index->unique &&
         indexcol == index->nkeycolumns - 1 &&
         eqQualHere &&
         !found_saop &&
         !found_is_null_op)
         numIndexTuples = 1.0;//唯一索引
     else//非唯一索引
     {
         List       *selectivityQuals;
         Selectivity btreeSelectivity;//選擇率
 
         /*
          * If the index is partial, AND the index predicate with the
          * index-bound quals to produce a more accurate idea of the number of
          * rows covered by the bound conditions.
          */
         selectivityQuals = add_predicate_to_quals(index, indexBoundQuals);//新增謂詞
 
         btreeSelectivity = clauselist_selectivity(root, selectivityQuals,
                                                   index->rel->relid,
                                                   JOIN_INNER,
                                                   NULL);//獲取選擇率
         numIndexTuples = btreeSelectivity * index->rel->tuples;//索引元組數目
 
         /*
          * As in genericcostestimate(), we have to adjust for any
          * ScalarArrayOpExpr quals included in indexBoundQuals, and then round
          * to integer.
          */
         numIndexTuples = rint(numIndexTuples / num_sa_scans);
     }
 
     /*
      * Now do generic index cost estimation.
      * 執行常規的索引成本估算
      */
     MemSet(&costs, 0, sizeof(costs));
     costs.numIndexTuples = numIndexTuples;
 
     genericcostestimate(root, path, loop_count, qinfos, &costs);
 
     /*
      * Add a CPU-cost component to represent the costs of initial btree
      * descent.  We don't charge any I/O cost for touching upper btree levels,
      * since they tend to stay in cache, but we still have to do about log2(N)
      * comparisons to descend a btree of N leaf tuples.  We charge one
      * cpu_operator_cost per comparison.
      * 新增一個cpu成本元件來表示初始化BTree樹層次下降的成本。
      * BTree上層節點可以認為已存在於快取中,因此不耗成本,但沿著樹往下沉時,需要
      * 執行log2(N)次比較(N個葉子元組的BTree)。每次比較,成本為cpu_operator_cost
      * 
      * If there are ScalarArrayOpExprs, charge this once per SA scan.  The
      * ones after the first one are not startup cost so far as the overall
      * plan is concerned, so add them only to "total" cost.
      * 如存在ScalarArrayOpExprs,則每次SA掃描成本增加cpu_operator_cost
      */
     if (index->tuples > 1)      /* avoid computing log(0) */
     {
         descentCost = ceil(log(index->tuples) / log(2.0)) * cpu_operator_cost;
         costs.indexStartupCost += descentCost;
         costs.indexTotalCost += costs.num_sa_scans * descentCost;
     }
 
     /*
      * Even though we're not charging I/O cost for touching upper btree pages,
      * it's still reasonable to charge some CPU cost per page descended
      * through.  Moreover, if we had no such charge at all, bloated indexes
      * would appear to have the same search cost as unbloated ones, at least
      * in cases where only a single leaf page is expected to be visited.  This
      * cost is somewhat arbitrarily set at 50x cpu_operator_cost per page
      * touched.  The number of such pages is btree tree height plus one (ie,
      * we charge for the leaf page too).  As above, charge once per SA scan.
      * BTree樹往下遍歷時的成本descentCost=(樹高+1)*50*cpu_operator_cost
      */
     descentCost = (index->tree_height + 1) * 50.0 * cpu_operator_cost;
     costs.indexStartupCost += descentCost;
     costs.indexTotalCost += costs.num_sa_scans * descentCost;
 
     /*
      * If we can get an estimate of the first column's ordering correlation C
      * from pg_statistic, estimate the index correlation as C for a
      * single-column index, or C * 0.75 for multiple columns. (The idea here
      * is that multiple columns dilute the importance of the first column's
      * ordering, but don't negate it entirely.  Before 8.0 we divided the
      * correlation by the number of columns, but that seems too strong.)
      * 如果我們可以從pg_statistical中得到第一列排序相關C的估計,那麼對於單列索引,
      * 可以將索引相關性估計為C,對於多列,可以將其估計為C * 0.75。
      * (這裡的想法是,多列淡化了第一列排序的重要性,但不要完全否定它。
      * 在8.0之前,我們將相關性除以列數,這種做法似乎太過了)。
      */
     MemSet(&vardata, 0, sizeof(vardata));
 
     if (index->indexkeys[0] != 0)
     {
         /* Simple variable --- look to stats for the underlying table */
         RangeTblEntry *rte = planner_rt_fetch(index->rel->relid, root);
 
         Assert(rte->rtekind == RTE_RELATION);
         relid = rte->relid;
         Assert(relid != InvalidOid);
         colnum = index->indexkeys[0];
 
         if (get_relation_stats_hook &&
             (*get_relation_stats_hook) (root, rte, colnum, &vardata))
         {
             /*
              * The hook took control of acquiring a stats tuple.  If it did
              * supply a tuple, it'd better have supplied a freefunc.
              */
             if (HeapTupleIsValid(vardata.statsTuple) &&
                 !vardata.freefunc)
                 elog(ERROR, "no function provided to release variable stats with");
         }
         else
         {
             vardata.statsTuple = SearchSysCache3(STATRELATTINH,
                                                  ObjectIdGetDatum(relid),
                                                  Int16GetDatum(colnum),
                                                  BoolGetDatum(rte->inh));
             vardata.freefunc = ReleaseSysCache;
         }
     }
     else
     {
         /* Expression --- maybe there are stats for the index itself */
         relid = index->indexoid;
         colnum = 1;
 
         if (get_index_stats_hook &&
             (*get_index_stats_hook) (root, relid, colnum, &vardata))
         {
             /*
              * The hook took control of acquiring a stats tuple.  If it did
              * supply a tuple, it'd better have supplied a freefunc.
              */
             if (HeapTupleIsValid(vardata.statsTuple) &&
                 !vardata.freefunc)
                 elog(ERROR, "no function provided to release variable stats with");
         }
         else
         {
             vardata.statsTuple = SearchSysCache3(STATRELATTINH,
                                                  ObjectIdGetDatum(relid),
                                                  Int16GetDatum(colnum),
                                                  BoolGetDatum(false));
             vardata.freefunc = ReleaseSysCache;
         }
     }
 
     if (HeapTupleIsValid(vardata.statsTuple))
     {
         Oid         sortop;
         AttStatsSlot sslot;
 
         sortop = get_opfamily_member(index->opfamily[0],
                                      index->opcintype[0],
                                      index->opcintype[0],
                                      BTLessStrategyNumber);
         if (OidIsValid(sortop) &&
             get_attstatsslot(&sslot, vardata.statsTuple,
                              STATISTIC_KIND_CORRELATION, sortop,
                              ATTSTATSSLOT_NUMBERS))
         {
             double      varCorrelation;
 
             Assert(sslot.nnumbers == 1);
             varCorrelation = sslot.numbers[0];
 
             if (index->reverse_sort[0])
                 varCorrelation = -varCorrelation;
 
             if (index->ncolumns > 1)
                 costs.indexCorrelation = varCorrelation * 0.75;
             else
                 costs.indexCorrelation = varCorrelation;
 
             free_attstatsslot(&sslot);
         }
     }
 
     ReleaseVariableStats(vardata);
 
     *indexStartupCost = costs.indexStartupCost;
     *indexTotalCost = costs.indexTotalCost;
     *indexSelectivity = costs.indexSelectivity;
     *indexCorrelation = costs.indexCorrelation;
     *indexPages = costs.numIndexPages;
 }

//------------------------- index_pages_fetched

 /*
  * index_pages_fetched
  *    Estimate the number of pages actually fetched after accounting for
  *    cache effects.
  *    估算在考慮快取影響後實際獲取的頁面數量。
  * 
  * 估算方法是Mackert和Lohman提出的方法:
  *     "Index Scans Using a Finite LRU Buffer: A Validated I/O Model"
  * We use an approximation proposed by Mackert and Lohman, "Index Scans
  * Using a Finite LRU Buffer: A Validated I/O Model", ACM Transactions
  * on Database Systems, Vol. 14, No. 3, September 1989, Pages 401-424.
  * The Mackert and Lohman approximation is that the number of pages
  * fetched is
  *  PF =
  *      min(2TNs/(2T+Ns), T)            when T <= b
  *      2TNs/(2T+Ns)                    when T > b and Ns <= 2Tb/(2T-b)
  *      b + (Ns - 2Tb/(2T-b))*(T-b)/T   when T > b and Ns > 2Tb/(2T-b)
  * where
  *      T = # pages in table
  *      N = # tuples in table
  *      s = selectivity = fraction of table to be scanned
  *      b = # buffer pages available (we include kernel space here)
  *
  * We assume that effective_cache_size is the total number of buffer pages
  * available for the whole query, and pro-rate that space across all the
  * tables in the query and the index currently under consideration.  (This
  * ignores space needed for other indexes used by the query, but since we
  * don't know which indexes will get used, we can't estimate that very well;
  * and in any case counting all the tables may well be an overestimate, since
  * depending on the join plan not all the tables may be scanned concurrently.)
  *
  * The product Ns is the number of tuples fetched; we pass in that
  * product rather than calculating it here.  "pages" is the number of pages
  * in the object under consideration (either an index or a table).
  * "index_pages" is the amount to add to the total table space, which was
  * computed for us by query_planner.
  *
  * Caller is expected to have ensured that tuples_fetched is greater than zero
  * and rounded to integer (see clamp_row_est).  The result will likewise be
  * greater than zero and integral.
  */
 double
 index_pages_fetched(double tuples_fetched, BlockNumber pages,
                     double index_pages, PlannerInfo *root)
 {
     double      pages_fetched;
     double      total_pages;
     double      T,
                 b;
 
     /* T is # pages in table, but don't allow it to be zero */
     T = (pages > 1) ? (double) pages : 1.0;
 
     /* Compute number of pages assumed to be competing for cache space */
     total_pages = root->total_table_pages + index_pages;
     total_pages = Max(total_pages, 1.0);
     Assert(T <= total_pages);
 
     /* b is pro-rated share of effective_cache_size */
     b = (double) effective_cache_size * T / total_pages;
 
     /* force it positive and integral */
     if (b <= 1.0)
         b = 1.0;
     else
         b = ceil(b);
 
     /* This part is the Mackert and Lohman formula */
     if (T <= b)
     {
         pages_fetched =
             (2.0 * T * tuples_fetched) / (2.0 * T + tuples_fetched);
         if (pages_fetched >= T)
             pages_fetched = T;
         else
             pages_fetched = ceil(pages_fetched);
     }
     else
     {
         double      lim;
 
         lim = (2.0 * T * b) / (2.0 * T - b);
         if (tuples_fetched <= lim)
         {
             pages_fetched =
                 (2.0 * T * tuples_fetched) / (2.0 * T + tuples_fetched);
         }
         else
         {
             pages_fetched =
                 b + (tuples_fetched - lim) * (T - b) / T;
         }
         pages_fetched = ceil(pages_fetched);
     }
     return pages_fetched;
 }

三、跟蹤分析

測試指令碼如下

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;

啟動gdb

(gdb) b create_index_path
Breakpoint 1 at 0x78f050: file pathnode.c, line 1037.
(gdb) c
Continuing.

主要考察t_grxx上的索引訪問路徑,即t_grxx.dwbh = '1001'(透過等價類產生並下推的限制條件)

(gdb) c
Continuing.

Breakpoint 1, create_index_path (root=0x2737d70, index=0x274be80, indexclauses=0x274f1f8, indexclausecols=0x274f248, 
    indexorderbys=0x0, indexorderbycols=0x0, pathkeys=0x0, indexscandir=ForwardScanDirection, indexonly=false, 
    required_outer=0x0, loop_count=1, partial_path=false) at pathnode.c:1037
1037        IndexPath  *pathnode = makeNode(IndexPath);

索引資訊:樹高度為1/索引列1個/indexlist連結串列,元素為TargetEntry,相關資訊為varno = 3, varattno = 1,索引訪問方法成本估算使用的函式為btcostestimate

(gdb) p *index
$3 = {type = T_IndexOptInfo, indexoid = 16752, reltablespace = 0, rel = 0x274b870, pages = 276, tuples = 100000, 
  tree_height = 1, ncolumns = 1, nkeycolumns = 1, indexkeys = 0x274bf90, indexcollations = 0x274bfa8, opfamily = 0x274bfc0, 
  opcintype = 0x274bfd8, sortopfamily = 0x274bfc0, reverse_sort = 0x274c008, nulls_first = 0x274c020, 
  canreturn = 0x274bff0, relam = 403, indexprs = 0x0, indpred = 0x0, indextlist = 0x274c0f8, indrestrictinfo = 0x274dc58, 
  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>}

執行各項賦值操作

(gdb) n
1038        RelOptInfo *rel = index->rel;
(gdb) 
1042        pathnode->path.pathtype = indexonly ? T_IndexOnlyScan : T_IndexScan;
(gdb) 
1043        pathnode->path.parent = rel;
(gdb) 
1044        pathnode->path.pathtarget = rel->reltarget;
(gdb) 
1045        pathnode->path.param_info = get_baserel_parampathinfo(root, rel,
(gdb) 
1047        pathnode->path.parallel_aware = false;
(gdb) 
1048        pathnode->path.parallel_safe = rel->consider_parallel;
(gdb) 
1049        pathnode->path.parallel_workers = 0;
(gdb) 
1050        pathnode->path.pathkeys = pathkeys;
(gdb) 
1053        expand_indexqual_conditions(index, indexclauses, indexclausecols,
(gdb) 
1057        pathnode->indexinfo = index;

執行expand_indexqual_conditions,給定RestrictInfo節點(約束條件),產生直接可用的索引表示式子句

(gdb) p *indexclauses
$4 = {type = T_List, length = 1, head = 0x274f1d8, tail = 0x274f1d8} -->t_grxx.dwbh = '1001'
(gdb) p *indexclausecols
$9 = {type = T_IntList, length = 1, head = 0x274f228, tail = 0x274f228}
(gdb) p indexclausecols->head->data.int_value
$10 = 0

進入cost_index函式

(gdb) 
1065        cost_index(pathnode, root, loop_count, partial_path);
(gdb) step
cost_index (path=0x274ecb8, root=0x2737d70, loop_count=1, partial_path=false) at costsize.c:480
480     IndexOptInfo *index = path->indexinfo;

呼叫訪問方法成本估算函式

...
(gdb) 
547     amcostestimate(root, path, loop_count,
(gdb) 
557     path->indextotalcost = indexTotalCost;

相關返回值

(gdb) p indexStartupCost
$22 = 0.29249999999999998
(gdb) p indexTotalCost
$23 = 4.3675000000000006
(gdb) p indexSelectivity
$24 = 0.00010012021638664612
(gdb) p indexCorrelation
$25 = 0.82452213764190674
(gdb) p index_pages
$26 = 1

loop_count=1

599     if (loop_count > 1)
(gdb) 
651                                             (double) index->pages,
(gdb) p loop_count
$27 = 1

取得的page數量,計算IO大小等

(gdb) n
649         pages_fetched = index_pages_fetched(tuples_fetched,
(gdb) 
654         if (indexonly)
(gdb) p pages_fetched
$28 = 10
...
(gdb) p max_IO_cost
$30 = 40
(gdb) p min_IO_cost
$31 = 4

呼叫完成,檢視最終結果

749     path->path.total_cost = startup_cost + run_cost;
(gdb) 
750 }
(gdb) p *path
$37 = {path = {type = T_IndexPath, pathtype = T_IndexScan, parent = 0x274b870, pathtarget = 0x274ba98, param_info = 0x0, 
    parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 10, startup_cost = 0.29249999999999998, 
    total_cost = 19.993376803383146, pathkeys = 0x0}, indexinfo = 0x274be80, indexclauses = 0x274f1f8, 
  indexquals = 0x274f3a0, indexqualcols = 0x274f3f0, indexorderbys = 0x0, indexorderbycols = 0x0, 
  indexscandir = ForwardScanDirection, indextotalcost = 4.3675000000000006, indexselectivity = 0.00010012021638664612}
(gdb) n
create_index_path (root=0x2737d70, index=0x274be80, indexclauses=0x274f1f8, indexclausecols=0x274f248, indexorderbys=0x0, 
    indexorderbycols=0x0, pathkeys=0x0, indexscandir=ForwardScanDirection, indexonly=false, required_outer=0x0, 
    loop_count=1, partial_path=false) at pathnode.c:1067
1067        return pathnode;  

該SQL語句的執行計劃,其中Index Scan using idx_t_grxx_dwbh on public.t_grxx t1 (cost=0.29..19.99...的成本0.29/19.99,與訪問路徑中的startup_cost/total_cost相對應.

testdb=# explain verbose 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;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.87..111.60 rows=10 width=37)
   Output: a.dwmc, a.dwbh, a.dwdz, t1.grbh, t2.je, t1.dwbh
   ->  Nested Loop  (cost=0.58..28.40 rows=10 width=29)
         Output: a.dwmc, a.dwbh, a.dwdz, t1.grbh, t1.dwbh
         ->  Index Scan using t_dwxx_pkey on public.t_dwxx a  (cost=0.29..8.30 rows=1 width=20)
               Output: a.dwmc, a.dwbh, a.dwdz
               Index Cond: ((a.dwbh)::text = '1001'::text)
         ->  Index Scan using idx_t_grxx_dwbh on public.t_grxx t1  (cost=0.29..19.99 rows=10 width=9)
               Output: t1.dwbh, t1.grbh, t1.xm, t1.xb, t1.nl
               Index Cond: ((t1.dwbh)::text = '1001'::text)
   ->  Index Scan using idx_t_jfxx_grbh on public.t_jfxx t2  (cost=0.29..8.31 rows=1 width=13)
         Output: t2.grbh, t2.ny, t2.je
         Index Cond: ((t2.grbh)::text = (t1.grbh)::text)
(13 rows)

四、參考資料

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

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

相關文章