Re: COUNT(*) and index-only scans

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Oct 12, 2011 at 9:52 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> What bothers me considerably more is the issue about how specific
>> queries might see an all-visible fraction that's very substantially
>> different from the table's overall ratio,

> - Suppose VACUUM processes the table and makes it all-visible. Then,
> somebody comes along and updates one tuple on every page, making them
> all not-all-visible, but not trigger VACUUM because we're nowhere
> close the 20% threshold. Now COUNT(*) will think it should use an
> index-scan, but really... not so much. In fact, even if it's only
> that a tuple has been updated on 25% of the pages, we're probably in
> trouble.

Yeah, but that would be a pretty unlucky pattern, and in any case the
fix for it is going to be to make autovacuum more aggressive.

> - Suppose the table has a million rows and we're going to read 100 of
> them, or 0.01%. Now it might appear that a covering index has a
> negligible advantage over a non-covering index, but in fact I think we
> still want to err on the side of trying to use the covering index.

Given that fact pattern we still will, I think. We'll still prefer an
indexscan over a seqscan, for sure. In any case, if you believe the
assumption that those 100 rows are more likely to be recently-dirtied
than the average row, I'm not sure why you think we should be trying to
force an assumption that index-only will succeed here.

regards, tom lane