From: Dawid Kuroczko <qnex42@gmail.com>
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 17:30:35 +0200
Message-ID: <758d5e7f05070808305c049aae@mail.gmail.com> (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 7/8/05, Steve Wampler <swampler@noao.edu> wrote:
> > None of those transactions have COMMITted, so there are some 78 tuples
> > "in limbo" spread across 16 transactions.
> >
> > If there were some "single secret place" with a count, how would you
> > suggest it address those 78 tuples and 16 transactions that aren't yet
> > (and maybe never will be) part of the count?
>
> 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
> table while counting (I assume) so the current implementation is really
> just an approximate count in the above scenario anyway. Or even when
> not, since the true 'count' is likely to have changed by the time the
> 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.
Use
EXPLAIN SELECT * FROM yourcountedtable;
Planner seems to track estimated statistics on-the-fly. :)
You can even wrap EXPLAIN SELECT in a pgsql function if you
need it.
Regards,
Dawid
PS: And be aware that these are 'statistics'. And the statement that there
are lies, big lies and statistics is sometimes true even for PostgreSQL. ;-)
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: qnex42@gmail.com, swampler@noao.edu, cbbrowne@acm.org
Subject: Re: Make COUNT(*) Faster?
In-Reply-To: <758d5e7f05070808305c049aae@mail.gmail.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