Doc updates for index cost estimator change - Mailing list pgsql-hackers

I have written the attached bit of doco about the new index cost
estimator procedure definition, but I am not sure where to put it.
There isn't (AFAICT) any existing documentation about how to make
a new kind of index, which would be the proper place for it.
May I impose on you to find/make a place for this and mark it up
properly?

Also, doc/src/graphics/catalogs.ag needs to be updated, but I have
no idea how.  (The amopselect and amopnpages fields of pg_amop
are gone; pg_am has a new field amcostestimate.)
        regards, tom lane


Index cost estimation functions
-------------------------------

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 'amcostestimate' field of the access method's pg_am entry.
(Note: prior to Postgres 7.0, a different scheme was used for registering
index-specific cost estimation functions.)

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 tuples 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.

Each amcostestimate function must have the signature

void
amcostestimate (Query *root,               RelOptInfo *rel,               IndexOptInfo *index,               List
*indexQuals,              Cost *indexAccessCost,               Selectivity *indexSelectivity);
 

The first four parameters are inputs:
root        The query being processedrel        The relation the index is onindex        The index itselfindexQuals
Listof index qual clauses (implicitly ANDed);        NIL list indicates no qualifiers are available
 

The last two parameters are pass-by-reference outputs:
*indexAccessCost    Set to cost of index processing*indexSelectivity    Set to index selectivity

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.

The indexAccessCost should be computed in the units used by
src/backend/optimizer/path/costsize.c: a disk block fetch has cost 1.0,
and the cost of processing one index tuple should usually be taken as
cpu_index_page_weight (which is a user-adjustable optimizer parameter).
The access cost should include all disk and CPU costs associated with
scanning the index itself, but NOT the cost of retrieving or processing
the main-table tuples that are identified by the index.

The indexSelectivity should be set to the estimated fraction of the main
table tuples that will be retrieved during the index scan.  In the case
of a lossy index, this will typically be higher than the fraction of
tuples that actually pass the given qual conditions.

A typical cost estimator will proceed as follows:

1. Estimate and return the fraction of main-table tuples that will be visited
based on the given qual conditions.  In the absence of any index-type-specific
knowledge, use the standard optimizer function clauselist_selec():
   *indexSelectivity = clauselist_selec(root, indexQuals);

2. Estimate the number of index tuples that will be visited during the
scan.  For many index types this is the same as indexSelectivity times
the number of tuples in the index, but it might be more.  (Note that the
index's size in pages and tuples is available from the IndexOptInfo struct.)

3. 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.

4. Compute the index access cost as
   *indexAccessCost = numIndexPages + cpu_index_page_weight * numIndexTuples;

Examples of cost estimator functions can be found in
src/backend/utils/adt/selfuncs.c.

By convention, the pg_proc entry for an amcostestimate function should show

prorettype = 0
pronargs = 6
proargtypes = 0 0 0 0 0 0

We use zero ("opaque") for all the arguments since none of them have types
that are known in pg_type.