Re: PostgreSQL performance issues

On Wed, 30 Aug 2006, Willo van der Merwe wrote:

> Merlin Moncure wrote:
> > 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:
> All good ideas and I'll be sure to implement them later.
>
> > I am curious why you need to query the count of records in the log
> > table to six digits of precision.
> I'm not with you you here.
> I'm drawing statistic for the my users on a per user basis in real-time,
> so there are a couple of where clauses attached.

Most of the advice so far has been aimed at improving the performance of
the query you gave. If this query isn't representative of your load then
you'll get better advice if you post the queries you are actually making
along with EXPLAIN ANALYZE output.

> Hi Merlin,
>
> This was just an example. All queries have slowed down. Could it be that
> I've reached some cut-off and now my disk is thrashing?
>
> Currently the load looks like this:
> Cpu0 : 96.8% us, 1.9% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 1.0% si
> Cpu1 : 97.8% us, 1.6% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si
> Cpu2 : 96.8% us, 2.6% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si
> Cpu3 : 96.2% us, 3.2% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si

It seems to be a sort of standing assumption on this list that databases
are much larger than memory and that database servers are almost always IO
bound. This isn't always true, but as we don't know the size of your
database or working set we can't tell. You'd have to look at your OS's IO
statistics to be sure, but it doesn't look to me to be likely that you're
IO bound.

If there are significant writes going on then it may also be interesting
to know your context switch rate and whether dropping your foreign key
constraint makes any difference. IIRC your foreign key constraint will
result in the row in log_sites being locked FOR UPDATE and cause updates
and inserts into your log table for a particular site to be serialized (I
may be out of date on this, it's a while since I heavily used foreign
keys).