Re: the big picture for index-only scans

2011/5/10 Kevin Grittner <Kevin.Grittner@wicourts.gov>:
> Simon Riggs <simon@2ndQuadrant.com> wrote:
>> The typical speed up for non-covered indexes will come when we
>> access a very large table (not in cache) via an index scan that is
>> smaller than a bitmapindex scan. Will we be able to gauge
>> selectivities sufficiently accurately to be able to pinpoint that
>> during optimization? How will we know that the table is not in
>> cache? Or is this an optimisation in the executor for a bitmapheap
>> scan?
>
> I would continue to object to using current cache contents for plan
> choice because of plan instability and the fact that an odd initial
> cache load could skew plans in a bad direction indefinitely.  I do
> agree (and have already posted) that I think the hardest part of
> this might be developing a good cost model.  I doubt that's an
> insoluble problem, especially since it is something we can refine
> over time as we gain experience with the edge cases.

you will have the same possible instability in planning with the
index(-only?) scan because we may need to access heap anyway and this
needs is based on estimation, or I miss something ? I understood the
idea was just to bypass the heap access *if* we can for *this*
heap-page.

In reality, I am not really scared by plan instability because of a
possible PG/OS cache estimation. The percentages remain stable in my
observations ... I don't know yet how it will go for vis map.

And, we already have plan instability currently, which is *good* : at
some point a seq scan is better than an bitmap heap scan. Because the
relation size change and because ANALYZE re-estimate the distribution
of the data. I will be very happy to issue ANALYZE CACHE as I have to
ANALYZE temp table for large query if it allows the planner to provide
me the best plan in some scenario...but this is another topic, sorry
for the digression..

--
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support