From: Richard Huxton <dev@archonet.com>
To: Owen Jacobson <ojacobson@osl.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Faster count(*)?
Date: Wed, 10 Aug 2005 15:04:48 +0100
Message-ID: <42FA0980.1060104@archonet.com> (raw)
In-Reply-To: <000101c59d33$36a4a560$9b00015a@osl.com>
References: <000101c59d33$36a4a560$9b00015a@osl.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, ojacobson@osl.com
Subject: Re: Faster count(*)?
In-Reply-To: <42FA0980.1060104@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox