Re: Faster 'select count(*) from table' ?

On Tue, 25 Feb 2003, Matt Mello wrote:

> Does anyone know if there is a fast way to find out how many records are
> in a table?
>
> "Select count(*) from table" is very slow.
>
> I would think that PG would keep up with the number of undeleted rows on
> a realtime basis. Is that the case? If so, how would I query it?

Sorry, it doesn't, and it's one of the areas that having an MVCC style
database costs you. Also, if postgresql kept up with this automatically,
it would have an overhead for each table, but how often do you use it on
ALL your tables? Most the time, folks use count(*) on a few tables only,
and it would be a waste to have a seperate counting mechanism for all
tables when you'd only need it for a few.

The general mailing list has several postings in the last 12 months about
how to setup a trigger to a single row table that keeps the current
count(*) of the master table.

If you need a rough count, you can get one from the statistics gathered by
analyze in the pg_* tables.