To: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Make COUNT(*) Faster? 
In-reply-to: <60vf3mklue.fsf@dba2.int.libertyrms.com> 
References: <9EF2931D-1097-470D-8ECB-59D00166C96F@apple.com>
	<60vf3mklue.fsf@dba2.int.libertyrms.com>
Comments: In-reply-to Chris Browne <cbbrowne@acm.org>
	message dated "Thu, 07 Jul 2005 23:39:21 -0400"
Date: Fri, 08 Jul 2005 00:27:23 -0400
Message-ID: <8508.1120796843@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Chris Browne <cbbrowne@acm.org> writes:
> vmehta@apple.com (Varun Mehta) writes:
>> If I run an EXPLAIN on this query I can see that it is doing a
>> sequential scan, which seems quite needless, as surely this
>> information is cached in some secret location.

> [ example scenario snipped ]
> If there were some "single secret place" with a count, how would you
> suggest it address those 78 tuples and 16 transactions that aren't yet
> (and maybe never will be) part of the count?

It's worse than that: once some of those transactions have committed,
the right answer is observer-dependent, since some onlooker transactions
may see those guys as committed while others think they are not yet
committed.  So there could certainly not be just one secret place...

There are solutions suggested in the archives, but they all amount
to making COUNT(*)-with-no-WHERE-or-GROUP-BY-clause fast at the price
of nontrivial distributed overhead for all updates --- overhead that
would be paid whether or not the application ever did such a COUNT.
That's not a tradeoff we've wanted to make in general.  You can
implement it yourself via triggers for specific tables that you think
it's worth doing for.

Also, if an approximate answer is good enough, there are a whole other
set of possible solutions.

			regards, tom lane