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

Re: Very specific server situation

From: "Mauro N(dot) Infantino" <mauroi(at)digbang(dot)com>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Very specific server situation
Date: 2007-04-28 20:43:01
Message-ID: 000b01c789d5$d0827b40$ec01010a@intranet.db (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

Thank you very much for your suggestions.

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us] 
> Have you checked to make sure the query plans are reasonable? 

I've attached the main query and its explain plan. I can't find a way to
improve it.

Does it make any difference if it's executed from a stored procedure? Is
there any difference between the SP's language (PL/pgSQL, PL/php, etc. It
needs to make some other tiny things besides the query)?

> You might want to try contrib/pgstattuple

Thanks. I'll give it a try and report the results here.

> absolutely critical to boost checkpoint_segments far enough 

How do I know how ofen checkpoints are done?
I've modified the parameters:

checkpoint_segments = 36 # it was 12 before
checkpoint_timeout = 1000
checkpoint_warning = 300 # so, I'll get a warning if it's too frequent.
commit_delay = 5000
commit_siblings = 2

> adjusting bgwriter and/or vacuum cost delay parameters

I've used a moderate cost delay configuration to see how it responds
(vacuum_cost_delay = 100 & vacuum_cost_limit = 200).
Do you have any advice on how to configure the bgwriter? I have no clue
about it and couldn't find anything clear.

Also, I know an upgrade to 8.2 is always a good thing, but is there any
change that could help this specific situation?

Again, thank you very much for your answers (and, of course, everything you
do in pgsql).


Attachment: plan.txt
Description: text/plain (2.3 KB)

In response to

pgsql-performance by date

Next:From: henk de witDate: 2007-04-28 22:51:39
Subject: Re: Redundant sub query triggers slow nested loop left join
Previous:From: Carlos MorenoDate: 2007-04-28 20:34:49
Subject: Re: Feature Request --- was: PostgreSQL Performance Tuning

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