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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: So, is COUNT(*) fast now?
Date: 2011-10-21 18:08:24
Message-ID: 29252.1319220504@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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. You've arranged the test case so that all these
tuples are in shared buffers already, so there's no data movement to be
avoided. What this test case proves is that btree's overhead per index
tuple touched is significantly more than the cost of the fastest path
through HeapTupleSatisfiesMVCC, which I don't find surprising
considering how much sweat has been expended on that code path over the
years.

(It may well be that it's not even btree at fault but the per-tuple
visits to the visibility map ... did you do any oprofiling yet?)

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2011-10-21 18:10:10 Re: Synchronized snapshots versus multiple databases
Previous Message Florian Pflug 2011-10-21 18:06:13 Re: Synchronized snapshots versus multiple databases