| PostgreSQL 9.1.18 Documentation | ||||
|---|---|---|---|---|
| Prev | Up | Chapter 52. Index Access Method Interface Definition | Next | |
The amcostestimate function is given information describing
a possible index scan, including lists of WHERE and ORDER BY 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 parent-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.
Each amcostestimate function must have the signature:
void
amcostestimate (PlannerInfo *root,
IndexOptInfo *index,
List *indexQuals,
List *indexOrderBys,
RelOptInfo *outer_rel,
Cost *indexStartupCost,
Cost *indexTotalCost,
Selectivity *indexSelectivity,
double *indexCorrelation);The first five parameters are inputs:
The planner's information about the query being processed.
The index being considered.
List of index qual clauses (implicitly ANDed); a NIL list indicates no qualifiers are available. Note that the list contains expression trees with RestrictInfo nodes at the top, not ScanKeys.
List of indexable ORDER BY operators, or NIL if none. Note that the list contains expression trees, not ScanKeys.
If the index is being considered for use in a join inner indexscan, the planner's information about the outer side of the join. Otherwise NULL. When non-NULL, some of the qual clauses will be join clauses with this rel rather than being simple restriction clauses. Also, the cost estimator should expect that the index scan will be repeated for each row of the outer rel.
The last four parameters are pass-by-reference outputs:
Set to cost of index start-up processing
Set to total cost of index processing
Set to index selectivity
Set to correlation coefficient between index scan order and underlying table's order
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 index access costs should be computed using the parameters used by src/backend/optimizer/path/costsize.c: a sequential disk block fetch has cost seq_page_cost, 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. 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).
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 parent-table rows that are identified by the index.
The "start-up cost" 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.
The indexSelectivity 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.
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 parent table.
In the join case, the returned numbers should be averages expected for any one scan of the index.
Cost Estimation
A typical cost estimator will proceed as follows:
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 clauselist_selectivity():
*indexSelectivity = clauselist_selectivity(root, indexQuals,
index->rel->relid,
JOIN_INNER, NULL);
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.)
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.
Compute the index access cost. A generic estimator might do this:
/*
* Our generic assumption is that the index pages will be read
* sequentially, so they cost seq_page_cost 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, root);
*indexStartupCost = index_qual_cost.startup;
*indexTotalCost = seq_page_cost * numIndexPages +
(cpu_index_tuple_cost + index_qual_cost.per_tuple) * numIndexTuples;However, the above does not account for amortization of index reads across repeated index scans in the join case.
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).
Examples of cost estimator functions can be found in src/backend/utils/adt/selfuncs.c.