Re: simple update queries take a long time - postgres 8.3.1

On 04/04/2008 05:43 PM, mark wrote:

> stats with new settings are below..

These stats look good for me.

> but even with this sometimes update queries take more than coupla
> seconds sometimes...

> checkpoint_completion_target = 0.8

It looks like that this is set too high for your workload. If you have
spikes of activity then it would be possible that checkpoint will not
end it's work before the next one should be started - I think it would
then halt everything until finished. Try to set it back to 0.5 and observe.

Did you turn on log_checkpoints as recommended? Could you quote your log
lines about checkpoints? Do they correlate with long running queries?

> sometimes INSERT statements are also slow. is there any settings
> can tweak to make INSERT statements fast?

For postgresql update in table data is the same as delete+insert, so the
same optimizations apply.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh