Message-ID: <42CE89CA.2070607@noao.edu>
Date: Fri, 08 Jul 2005 07:12:26 -0700
From: Steve Wampler <swampler@noao.edu>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
MIME-Version: 1.0
To: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Make COUNT(*) Faster?
References: <9EF2931D-1097-470D-8ECB-59D00166C96F@apple.com>
	<60vf3mklue.fsf@dba2.int.libertyrms.com>
In-Reply-To: <60vf3mklue.fsf@dba2.int.libertyrms.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Chris Browne wrote:

> None of those transactions have COMMITted, so there are some 78 tuples
> "in limbo" spread across 16 transactions.
> 
> 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?

Hmmm, I understand this and don't doubt it, but out of curiousity, how
does the current SELECT COUNT(*) handle this?  It doesn't lock the entire
table while counting (I assume) so the current implementation is really
just an approximate count in the above scenario anyway.  Or even when
not, since the true 'count' is likely to have changed by the time the
user does anything with the result of SELECT COUNT(*) on any active table
(and on an inactive table, pg_class.reltuples is nearly as good as
SELECT COUNT(*) and far faster to get to.)

I assume this has been beaten well past death, but I don't see why it
wouldn't be possible to keep pg_class.reltuples a bit more up-to-date
instead of updating it only on vacuums.

-- 
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.