Re: So, is COUNT(*) fast now?

On Fri, Oct 21, 2011 at 11:14 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Oct 21, 2011 at 2:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> On Fri, Oct 21, 2011 at 1:18 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>> I don't know why you'd imagine that touching an index is free, or even
>>>> cheap, CPU-wise.  The whole point of the index-only optimization is to
>>>> avoid I/O.  When you try it on a case where there's no I/O to be saved,
>>>> *and* no shared-buffers contention to be avoided, there's no way it's
>>>> going to be a win.
>>
>>> Well, call me naive, but I would have thought touching six times less
>>> data would make the operation run faster, not slower.
>>
>> It's not "touching six times less data".  It's touching the exact same
>> number of tuples either way, just index tuples in one case and heap
>> tuples in the other.
>
> Yeah, but it works out to fewer pages.

But since those pages are already in RAM, why would it matter all that
much? (Other than in the case of highly concurrent access, which you
don't seem to be testing?)

One of Tom's commits that made it not lock the same index page over
and over again (once for each tuple on it) made me think it should be
much faster than the seq scan, but a bit of random flailing about
convinced me that any saving from this were compensated for by the
high over head of FunctionCall2Coll and all of the hokey-pokey that
that call entails, which a seqscan can skip entirely.

If count(*) could cause the index-only scan to happen in physical
order of the index, rather than logical order, that might be a big
win. Both for all in memory and for not-all-in-memory.

Cheers,

Jeff