Message-ID: <42FA0980.1060104@archonet.com>
Date: Wed, 10 Aug 2005 15:04:48 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
MIME-Version: 1.0
To: Owen Jacobson <ojacobson@osl.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Faster count(*)?
References: <000101c59d33$36a4a560$9b00015a@osl.com>
In-Reply-To: <000101c59d33$36a4a560$9b00015a@osl.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Owen Jacobson wrote:
> Salve.
> 
> I understand from various web searches and so on that PostgreSQL's MVCC
> mechanism makes it very hard to use indices or table metadata to optimise
> count(*).  Is there a better way to guess the "approximate size" of a table?

Plenty of good answers on how to estimate table-size, but it sounds like 
you just want to run your maintenance function "every so often".

1. Create a sequence "my_table_tracker_seq"
2. On insert, call nextval(my_table_tracker_seq)
3. If value modulo 1000 = 0, run the maintenance routine

That's about as fast as you can get, and might meet your needs. Of 
course you'll need to be more complex if you insert multiple rows at a time.

If you do a lot of deletion on the table and want to take that into 
account, have a second sequence you increment on deletion and subtract 
the one from the other.

Not always accurate enough, but it is quick.
--
   Richard Huxton
   Archonet Ltd