Re: [PERFORM] Slow query: bitmap scan troubles

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On 5 January 2013 22:18, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> No. The argument is that if we don't have some such correction, the
>> planner is liable to believe that different-sized indexes have *exactly
>> the same cost*, if a given query would fetch the same number of index
>> entries.

> The only difference between a large and a small index is the initial
> fetch, since the depth of the index may vary. After that the size of
> the index is irrelevant to the cost of the scan, since we're just
> scanning across the leaf blocks. (Other differences may exist but not
> related to size).

Right: except for the "fudge factor" under discussion, all the indexscan
costs that we model come from accessing index leaf pages and leaf
tuples. So to the extent that the fudge factor has any principled basis
at all, it's an estimate of index descent costs. And in that role I
believe that total index size needs to be taken into account.

> Perhaps the cost of the initial fetch is what you mean by a
> "correction"? In that case, why not use the index depth directly from
> the metapage, rather than play with size?

IIRC, one of my very first attempts to deal with this was to charge
random_page_cost per level of index descended. This was such a horrid
overestimate that it never went anywhere. I think that reflects that in
practical applications, the upper levels of the index tend to stay in
cache. We could ignore I/O on that assumption and still try to model
CPU costs of the descent, which is basically what Jeff is proposing.
My objection to his formula is mainly that it ignores physical index
size, which I think is important to include somehow for the reasons
I explained in my other message.

regards, tom lane