Re: **SPAM** Faster count(*)?

dracula007@atlas.cz writes:
> I believe running count(*) means fulltable scan, and there's no way
> to do it without it. But what about some "intermediate" table, with
> the necessary counts?

There's a fairly complete discussion in the PG list archives of a
reasonably-efficient scheme for maintaining such counts via triggers.
It wasn't efficient enough that we were willing to impose the overhead
on every application ... but if you really NEED a fast count(*) you
could implement it.  I'd like to see someone actually do it and put
up working code on pgfoundry; AFAIK it's only a paper design so far.

If you only want a very-approximate count, the best bet is to rely on
the planner's estimates, eg

regression=# explain select * from tenk1;                        QUERY PLAN                          
-------------------------------------------------------------Seq Scan on tenk1  (cost=0.00..458.00 rows=10000
width=244)                                          ^^^^^
 

Current best practice is to run the explain and parse out the "rows"
figure using a perl (or axe-of-choice) regexp, though we could be
persuaded to supply a simpler API if there's enough demand for it.
        regards, tom lane