<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Index Cost Estimation Functions</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.0.11 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Internals"
HREF="internals.html"><LINK
REL="PREVIOUS"
TITLE="Further Reading"
HREF="geqo-biblio.html"><LINK
REL="NEXT"
TITLE="GiST Indexes"
HREF="gist.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2007-02-02T03:57:22"></HEAD
><BODY
CLASS="CHAPTER"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.0.11 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="geqo-biblio.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="geqo.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="gist.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="gist.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="CHAPTER"
><H1
><A
NAME="INDEXCOST"
></A
>Chapter 47. Index Cost Estimation Functions</H1
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Author: </B
> Written by Tom Lane (<CODE
CLASS="EMAIL"
><<A
HREF="mailto:tgl@sss.pgh.pa.us"
>tgl@sss.pgh.pa.us</A
>></CODE
>) on 2000-01-24
</P
></BLOCKQUOTE
></DIV
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
> This must eventually become part of a much larger chapter about
writing new index access methods.
</P
></BLOCKQUOTE
></DIV
><P
> Every index access method must provide a cost estimation function for
use by the planner/optimizer. The procedure OID of this function is
given in the <TT
CLASS="LITERAL"
>amcostestimate</TT
> field of the access
method's <TT
CLASS="LITERAL"
>pg_am</TT
> entry.
</P><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
> Prior to <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> 7.0, a different
scheme was used for registering
index-specific cost estimation functions.
</P
></BLOCKQUOTE
></DIV
><P>
</P
><P
> The amcostestimate function is given a list of WHERE clauses that have
been determined to be usable with the index. It must return estimates
of the cost of accessing the index and the selectivity of the WHERE
clauses (that is, the fraction of main-table rows that will be
retrieved during the index scan). For simple cases, nearly all the
work of the cost estimator can be done by calling standard routines
in the optimizer; the point of having an amcostestimate function is
to allow index access methods to provide index-type-specific knowledge,
in case it is possible to improve on the standard estimates.
</P
><P
> Each amcostestimate function must have the signature:
</P><PRE
CLASS="PROGRAMLISTING"
>void
amcostestimate (Query *root,
RelOptInfo *rel,
IndexOptInfo *index,
List *indexQuals,
Cost *indexStartupCost,
Cost *indexTotalCost,
Selectivity *indexSelectivity,
double *indexCorrelation);
</PRE
><P>
The first four parameters are inputs:
<P
></P
></P><DIV
CLASS="VARIABLELIST"
><DL
><DT
>root</DT
><DD
><P
> The query being processed.
</P
></DD
><DT
>rel</DT
><DD
><P
> The relation the index is on.
</P
></DD
><DT
>index</DT
><DD
><P
> The index itself.
</P
></DD
><DT
>indexQuals</DT
><DD
><P
> List of index qual clauses (implicitly ANDed);
a NIL list indicates no qualifiers are available.
</P
></DD
></DL
></DIV
><P>
</P
><P
> The last four parameters are pass-by-reference outputs:
<P
></P
></P><DIV
CLASS="VARIABLELIST"
><DL
><DT
>*indexStartupCost</DT
><DD
><P
> Set to cost of index start-up processing
</P
></DD
><DT
>*indexTotalCost</DT
><DD
><P
> Set to total cost of index processing
</P
></DD
><DT
>*indexSelectivity</DT
><DD
><P
> Set to index selectivity
</P
></DD
><DT
>*indexCorrelation</DT
><DD
><P
> Set to correlation coefficient between index scan order and
underlying table's order
</P
></DD
></DL
></DIV
><P>
</P
><P
> Note that cost estimate functions must be written in C, not in SQL or
any available procedural language, because they must access internal
data structures of the planner/optimizer.
</P
><P
> The index access costs should be computed in the units used by
<TT
CLASS="FILENAME"
>src/backend/optimizer/path/costsize.c</TT
>: a sequential disk block fetch
has cost 1.0, a nonsequential fetch has cost random_page_cost, and
the cost of processing one index row should usually be taken as
cpu_index_tuple_cost (which is a user-adjustable optimizer parameter).
In addition, an appropriate multiple of cpu_operator_cost should be charged
for any comparison operators invoked during index processing (especially
evaluation of the indexQuals themselves).
</P
><P
> The access costs should include all disk and CPU costs associated with
scanning the index itself, but NOT the costs of retrieving or processing
the main-table rows that are identified by the index.
</P
><P
> The <SPAN
CLASS="QUOTE"
>"start-up cost"</SPAN
> 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.
</P
><P
> The indexSelectivity should be set to the estimated fraction of the main
table rows that will be retrieved during the index scan. In the case
of a lossy index, this will typically be higher than the fraction of
rows that actually pass the given qual conditions.
</P
><P
> The indexCorrelation 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 main
table.
</P
><DIV
CLASS="PROCEDURE"
><P
><B
>Cost Estimation</B
></P
><P
> A typical cost estimator will proceed as follows:
</P
><OL
TYPE="1"
><LI
CLASS="STEP"
><P
> Estimate and return the fraction of main-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 <CODE
CLASS="FUNCTION"
>clauselist_selectivity()</CODE
>:
</P><PRE
CLASS="PROGRAMLISTING"
>*indexSelectivity = clauselist_selectivity(root, indexQuals,
rel->relid, JOIN_INNER);
</PRE
><P>
</P
></LI
><LI
CLASS="STEP"
><P
> Estimate the number of index rows that will be visited during the
scan. For many index types this is the same as indexSelectivity times
the number of rows in the index, but it might be more. (Note that the
index's size in pages and rows is available from the IndexOptInfo struct.)
</P
></LI
><LI
CLASS="STEP"
><P
> Estimate the number of index pages that will be retrieved during the scan.
This might be just indexSelectivity times the index's size in pages.
</P
></LI
><LI
CLASS="STEP"
><P
> Compute the index access cost. A generic estimator might do this:
</P><PRE
CLASS="PROGRAMLISTING"
> /*
* Our generic assumption is that the index pages will be read
* sequentially, so they have cost 1.0 each, not random_page_cost.
* Also, we charge for evaluation of the indexquals at each index row.
* All the costs are assumed to be paid incrementally during the scan.
*/
cost_qual_eval(&index_qual_cost, indexQuals);
*indexStartupCost = index_qual_cost.startup;
*indexTotalCost = numIndexPages +
(cpu_index_tuple_cost + index_qual_cost.per_tuple) * numIndexTuples;
</PRE
><P>
</P
></LI
><LI
CLASS="STEP"
><P
> Estimate the index correlation. For a simple ordered index on a single
field, this can be retrieved from pg_statistic. If the correlation
is not known, the conservative estimate is zero (no correlation).
</P
></LI
></OL
></DIV
><P
> Examples of cost estimator functions can be found in
<TT
CLASS="FILENAME"
>src/backend/utils/adt/selfuncs.c</TT
>.
</P
><P
> By convention, the <TT
CLASS="LITERAL"
>pg_proc</TT
> entry for an
<TT
CLASS="LITERAL"
>amcostestimate</TT
> function should show
eight arguments all declared as <TT
CLASS="TYPE"
>internal</TT
> (since none of them have
types that are known to SQL), and the return type is <TT
CLASS="TYPE"
>void</TT
>.
</P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="geqo-biblio.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="gist.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Further Reading</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="internals.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>GiST Indexes</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
