Home • Explore • Blog • CI
☰



postgresql
24th chunk of `doc/src/sgml/indexam.sgml`
5a22a43b8ab04bda4c605daff3d01efe41379a26c486f99e0000000100000834
 multiple of <varname>cpu_operator_cost</varname> should
   be charged for any comparison operators invoked during index processing
   (especially evaluation of the indexquals themselves).
  </para>

  <para>
   The access costs should include all disk and CPU costs associated with
   scanning the index itself, but <emphasis>not</emphasis> the costs of retrieving or
   processing the parent-table rows that are identified by the index.
  </para>

  <para>
   The <quote>start-up cost</quote> is the part of the total scan cost that
   must be expended before we can begin to fetch the first row.  For most
   indexes this can be taken as zero, but an index type with a high start-up
   cost might want to set it nonzero.
  </para>

  <para>
   The <parameter>indexSelectivity</parameter> should be set to the estimated fraction of the parent
   table rows that will be retrieved during the index scan.  In the case
   of a lossy query, this will typically be higher than the fraction of
   rows that actually pass the given qual conditions.
  </para>

  <para>
   The <parameter>indexCorrelation</parameter> should be set to the correlation (ranging between
   -1.0 and 1.0) between the index order and the table order.  This is used
   to adjust the estimate for the cost of fetching rows from the parent
   table.
  </para>

  <para>
   The <parameter>indexPages</parameter> should be set to the number of leaf pages.
   This is used to estimate the number of workers for parallel index scan.
  </para>

  <para>
   When <parameter>loop_count</parameter> is greater than one, the returned numbers
   should be averages expected for any one scan of the index.
  </para>

  <procedure>
   <title>Cost Estimation</title>
   <para>
    A typical cost estimator will proceed as follows:
   </para>

   <step>
    <para>
     Estimate and return the fraction of parent-table rows that will be visited
     based on the given qual conditions.  In the absence of any index-type-specific
     knowledge, use the standard optimizer function <function>clauselist_selectivity()</function>:

<programlisting>
Title: Index Access Cost, Start-Up Cost, Selectivity, Correlation, and Loop Count in Cost Estimation
Summary
This section elaborates on calculating index access costs, clarifying the inclusion of disk and CPU costs associated with scanning the index itself. It defines start-up cost as the cost incurred before fetching the first row and explains how to set the indexSelectivity, accounting for lossy queries. It also describes how to set the indexCorrelation based on the order correlation between the index and the table and how to set the indexPages used for estimating the number of workers for parallel index scan. The section also advises averaging returned numbers for each scan when loop_count is greater than one before beginning to walk through a typical cost estimation process.