Обсуждение: [BUGS] BUG #14855: index-only scans not used in simple cases

The following bug has been logged on the website:

Bug reference:      14855
Logged by:          Andrew Gierth
Email address:      andrew@tao11.riddles.org.uk
PostgreSQL version: 9.5.4
Operating system:   any
Description:

This came up on IRC:

Given a unique index on (a) and a (slightly higher cost) index on (a,b), the
query

select a,b from sometable where a=123;

will not do an index-only scan unless the allvisfrac is *exactly* 1.0, and
in the more normal case where only almost all of the pages are all-visible,
it will generate the plain index scan on a instead, with the extra heap
fetch.

This is obviously because cost_index is using ceil(pages_fetched * (1.0 -
baserel->allvisfrac)), and since this is a 1-row fetch then pages_fetched is
still 1 after the adjustment for any value of allvisfrac less than exactly
1.0.

(If the index part of the cost is the same between the two indexes, then
what happens will depend on the order of index creation.)

This is ... quite limiting, since this query seems to be one of the
paradigmatic cases of wanting to use an index-only scan in the first place.
We ought to be preferring the index-only scan even when the index is
slightly larger, since we're more likely to get a cache hit on nonleaf index
pages than on the heap.




--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

andrew@tao11.riddles.org.uk writes:
> Given a unique index on (a) and a (slightly higher cost) index on (a,b), the
> query
> select a,b from sometable where a=123;
> will not do an index-only scan unless the allvisfrac is *exactly* 1.0, and
> in the more normal case where only almost all of the pages are all-visible,
> it will generate the plain index scan on a instead, with the extra heap
> fetch.

> This is obviously because cost_index is using ceil(pages_fetched * (1.0 -
> baserel->allvisfrac)), and since this is a 1-row fetch then pages_fetched is
> still 1 after the adjustment for any value of allvisfrac less than exactly
> 1.0.

One idea is to remove the allvisfrac correction from the pages_fetched
calculation altogether, and instead apply it to the I/O cost numbers
at the end, ie
max_IO_cost *= (1.0 - baserel->allvisfrac);min_IO_cost *= (1.0 - baserel->allvisfrac);

just before the partial_path stanza.  While that would improve your
particular complaint I'm not sure if it's a good idea in general.
        regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs