Le lundi 21 mai 2012 15:35:55, Luca Ferrari a écrit :
> Hi all,
> I don't fully understand how is the cost of a bitmap heap scan
> computed. For instance when the explain output node is similar to the
> following:
>
> Bitmap Heap Scan on test (cost=17376.49..48595.93 rows=566707 width=6)
> Recheck Cond: ((text1 = 'A'::text) OR (text1 = 'C'::text))
> Filter: (num1 > 1)
>
> how is the cost of the node (48595.93 - 17376.49) computed? I think it
> should be something like:
> (reltuples * ( index_filtering_factor_A + index_filtering_factor_B) )
> * (cpu_tuple_cost + cpu_operator_cost)
> + (reltuples * ( index_filtering_factor_A + index_filtering_factor_B)
> ) / tuples_per_pages
>
> but this does not equal the optimizer cost, so I guess I'm doing
> something wrong. Suggestions?
the random_page_cost is evaluated with random_page_cost and seq_page_cost, it
depends of the number of rows fetch and the number of relpages.
Read ./src/backend/optimizer/path/costsize.c
/*
* For small numbers of pages we should charge spc_random_page_cost
* apiece, while if nearly all the table's pages are being read, it's more
* appropriate to charge spc_seq_page_cost apiece. The effect is
* nonlinear, too. For lack of a better idea, interpolate like this to
* determine the cost per page.
*/
if (pages_fetched >= 2.0)
cost_per_page = spc_random_page_cost -
(spc_random_page_cost - spc_seq_page_cost)
* sqrt(pages_fetched / T);
else
cost_per_page = spc_random_page_cost;
--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation