Re: Long count(*) time

--- David Monarchi <david(dot)e(dot)monarchi(at)gmail(dot)com> wrote:

> 70 seconds seems to be a long time for this kind of query. Is this normal?

Do to the nature of PostgreSQL's MVCC system, all Count(*) operations with no where clauses will
trigger a full table scan. You could possible shave off a some time if you perform a VACUUM FULL
on this table. Vacuum full will recover all the space from dead tuples. The end result is that
fewer pages left will require less time to scan.

However, if you have a good auto-vacuum policy implemented you can get a very close estimate of
the number of records in you table in much less time. This link has a good example of how this is
done:
http://www.commandprompt.com/blogs/joshua_drake/2007/08/how_many_rows_do_i_have_anyway/

Regards,
Richard Broersma Jr.