Date: Fri, 8 Jul 2005 09:35:08 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Steve Wampler <swampler@noao.edu>
Cc: Chris Browne <cbbrowne@acm.org>, pgsql-sql@postgresql.org
Subject: Re: Make COUNT(*) Faster?
Message-ID: <20050708143508.GD22725@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>,
	Steve Wampler <swampler@noao.edu>, Chris Browne <cbbrowne@acm.org>,
	pgsql-sql@postgresql.org
References: <9EF2931D-1097-470D-8ECB-59D00166C96F@apple.com>
	<60vf3mklue.fsf@dba2.int.libertyrms.com>
	<42CE89CA.2070607@noao.edu>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <42CE89CA.2070607@noao.edu>
User-Agent: Mutt/1.5.6i

On Fri, Jul 08, 2005 at 07:12:26 -0700,
  Steve Wampler <swampler@noao.edu> wrote:
> 
> 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

It only counts tuples visible to the current transaction.

> table while counting (I assume) so the current implementation is really
> just an approximate count in the above scenario anyway.  Or even when

No, it is an exact count.

> not, since the true 'count' is likely to have changed by the time the

There is no single true count. There is a separate true count for each
transaction.

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

Because it costs resources to keep track of that and people don't usually need
exact tuple counts for whole tables. Those that do and are willing to pay the
price can use triggers to maintain a count in a separate table.