From: Rod Taylor <pg@rbt.ca>
To: Steve Wampler <swampler@noao.edu>
Cc: pgsql-sql@postgresql.org, Bruno Wolff III <bruno@wolff.to>
In-Reply-To: <42CE96AF.3020903@noao.edu>
References: <9EF2931D-1097-470D-8ECB-59D00166C96F@apple.com>
	<60vf3mklue.fsf@dba2.int.libertyrms.com> <42CE89CA.2070607@noao.edu>
	<20050708143508.GD22725@wolff.to>  <42CE96AF.3020903@noao.edu>
Date: Fri, 08 Jul 2005 11:22:30 -0400
Message-Id: <1120836150.24708.251.camel@home>
Mime-Version: 1.0
Subject: Re: Make COUNT(*) Faster?
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

> So, leave COUNT(*) alone.  But it would be very handy to have a
> way to get an approximate table size that is more accurate than is
> provided by a pg_class.reltuples that is only updated on vacuums.

Create 2 sequences, one for counting tuple additions and one for
counting tuple deletions.

When you INSERT a tuple, bump the "added" sequence (select nextval());

When you DELETE a tuple, bump the "deleted" sequence (select nextval());

To retrieve an approximate count, take the current value of both
sequences (select directly -- don't use currval) and subtract the
"deletes" from the "adds".


This is a very fast tracking mechanism with the catch that it does not
handle rollbacks -- but you only wanted approximate. Put all of the
logic inside a pair of triggers and a function within the DB.


--