Re: help understanding the bitmap heap scan costs - Mailing list pgsql-general

From Cédric Villemain
Subject Re: help understanding the bitmap heap scan costs
Date
Msg-id 201205211710.01292.cedric@2ndquadrant.com
Whole thread Raw
In response to help understanding the bitmap heap scan costs  (Luca Ferrari <fluca1978@infinito.it>)
List pgsql-general
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

Attachment

pgsql-general by date:

Previous
From: John Townsend
Date:
Subject: Re: Libpq question
Next
From: Cédric Villemain
Date:
Subject: Re: Global Named Prepared Statements