Re: Faster count(*)?

Owen Jacobson wrote:
> Salve.
>
> I understand from various web searches and so on that PostgreSQL's MVCC
> mechanism makes it very hard to use indices or table metadata to optimise
> count(*). Is there a better way to guess the "approximate size" of a table?

Plenty of good answers on how to estimate table-size, but it sounds like
you just want to run your maintenance function "every so often".

1. Create a sequence "my_table_tracker_seq"
2. On insert, call nextval(my_table_tracker_seq)
3. If value modulo 1000 = 0, run the maintenance routine

That's about as fast as you can get, and might meet your needs. Of
course you'll need to be more complex if you insert multiple rows at a time.

If you do a lot of deletion on the table and want to take that into
account, have a second sequence you increment on deletion and subtract
the one from the other.

Not always accurate enough, but it is quick.
--
Richard Huxton
Archonet Ltd