Skip site navigation (1) Skip section navigation (2)

Re: PostgreSQL performance issues

From: "Merlin Moncure" <mmoncure(at)gmail(dot)com>
To: "Willo van der Merwe" <willo(at)studentvillage(dot)co(dot)za>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: PostgreSQL performance issues
Date: 2006-08-29 23:39:57
Message-ID: b42b73150608291639o226b645cq41175b416590f029@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: CodelogicDate: 2006-08-30 00:16:36
Subject: Re: PostgreSQL performance issues
Previous:From: Rusty ConoverDate: 2006-08-29 21:47:17
Subject: Re: PostgreSQL performance issues

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group