DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=oTd66lxaMVbU5QHOKuDx1kWha8ATWWzPFnx61tq9N2M2L45Os9Lpb5jcNHb1+b6554sijw2VeiQM5C1Rlcs2XaQuV9V/4XMHuHd8HosDJCKbblWyllus825B3Ecipu7iFZ0CdSxbsnaUL5f7Y0IgTi6zMsWyHs7+AvFz16s4F00=
Message-ID: <758d5e7f05070808305c049aae@mail.gmail.com>
Date: Fri, 8 Jul 2005 17:30:35 +0200
From: Dawid Kuroczko <qnex42@gmail.com>
Reply-To: Dawid Kuroczko <qnex42@gmail.com>
To: Steve Wampler <swampler@noao.edu>
Subject: Re: Make COUNT(*) Faster?
Cc: Chris Browne <cbbrowne@acm.org>, pgsql-sql@postgresql.org
In-Reply-To: <42CE89CA.2070607@noao.edu>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
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?
>=20
> 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.)
>=20
> 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. ;-=
)