Re: Make COUNT(*) Faster?

On 7/8/05, Steve Wampler <swampler(at)noao(dot)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. ;-)