Re: Strange count(*) implementation?

hi,

On Tue, 2004-10-26 at 10:16, Henk Ernst Blok wrote:
> Hi Posgres users/developers,
>
> Can anyone explain why PosgreSQL (version 7.4.5 on Linux) does a full
> table scan to compute a count(*) on a base table after a vacuum analyze
> has been done with no following updates that might have outdated any
> statistics. Strangly the explain command does give the correct number of
> tuples instantaniously from the catalog, as one would expect. Still the
> optimizer thinks it needs a full table scan to do count.
>
...
> The consequence of this seemingly odd count implementation is a very
> very slow count.

How should the query planner know the vacuum was recent enough and there
were no modifications to the table since?

If you are interested in rough numbers you could read the system tables
for the last vacuum statistics. If you need fast count and can spend
some cycles on inserts, just make a buffer table with count results
after insert.

Unqualified count e.g. without a WHERE clause should not need to
be used a lot.

Regards
Tino