Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

Next:From: Tom LaneDate: 2011-10-21 18:10:10
Subject: Re: Synchronized snapshots versus multiple databases
Previous:From: Florian PflugDate: 2011-10-21 18:06:13
Subject: Re: Synchronized snapshots versus multiple databases

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group