From: Bruno Wolff III <bruno@wolff.to>
To: Steve Wampler <swampler@noao.edu>
Cc: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Make COUNT(*) Faster?
Date: Fri, 8 Jul 2005 09:35:08 -0500
Message-ID: <20050708143508.GD22725@wolff.to> (raw)
In-Reply-To: <42CE89CA.2070607@noao.edu>
References: <9EF2931D-1097-470D-8ECB-59D00166C96F@apple.com>
<60vf3mklue.fsf@dba2.int.libertyrms.com>
<42CE89CA.2070607@noao.edu>
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.
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: bruno@wolff.to, swampler@noao.edu, cbbrowne@acm.org
Subject: Re: Make COUNT(*) Faster?
In-Reply-To: <20050708143508.GD22725@wolff.to>
* 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