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

On Fri, Oct 21, 2011 at 10:57 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>> 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?)

Well, because memory access takes time, and accessing more memory
takes more time. In the testing that I've done recently, performance
on in-memory workloads seems to be extremely sensitive to memory
speed, so you'd think that cutting down on memory access would be a
win.

> 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.

Huh. Not sure whether that's significant or not.

> 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.

That's an interesting point. I sort of assumed that would only help
for not-all-in-memory, but maybe not. The trouble is that I think
there are some problematic concurrency issues there.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company