Re: PostgreSQL performance issues

On 8/29/06, Willo van der Merwe <willo(at)studentvillage(dot)co(dot)za> wrote:

> and it has 743321 rows and a explain analyze select count(*) from
> property_values;
>

you have a number of options:
1. keep a sequence on the property values and query it. if you want
exact count you must do some clever locking however. this can be made
to be exact and very fast.
2. analyze the table periodically and query pg_class (inexact)
3. keep a control record and update it in a transaction. this has
concurrency issues vs. #1 but is a bit easier to control
4. normalize

other databases for example mysql optimize the special case select
count(*). because of mvcc, postgresql cannot do this easily. you
will find that applying any where condition to the count will slow
those servers down substantially becuase the special case optimization
does not apply.

I am curious why you need to query the count of records in the log
table to six digits of precision.

merlin