Quick select, slow update - help with performance problems

From: Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
To: pgsql-sql(at)postgresql(dot)org
Subject: Quick select, slow update - help with performance problems
Date: 2008-07-01 10:20:41
Message-ID: 200807011120.41664.gary.stainburn@ringways.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi folks.

My system is slowing down, more notably over the last few weeks.
The main reason is that it's on an old slow machine, and I'm in the process of
sorting this.

However, I think that there are some issues within my database which I need to
investigate.

There seems to be some performance issues with updating the database. I have a
select, an insert and an update based on the same table and condition.

The select:

select count(ud_id)
from used_diary
where ud_valet_completed is null and
ud_valet_required < CURRENT_DATE-'7 days'::interval;

completed in about a second. The insert

insert into used_diary_log (ul_u_id, ul_ud_id, ul_changes)
select 25, ud_id, 'Valet automatically cleared down'
from used_diary
where ud_valet_completed is null and
ud_valet_required < CURRENT_DATE-'7 days'::interval;

also completed in about a second. However the update

update used_diary set
ud_valet_completed=now(), ud_valet_completed_by=25
where ud_valet_completed is null and
ud_valet_required < CURRENT_DATE-'7 days'::interval

is still running after approx 1 1/2 minutes. I've noticed that other updates
also seem to take a long time.

Could I have any suggestions on how I could start looking into why this is.
Could it be the config of postgresql, issues with my schema, or something
else?

Gary

--
Gary Stainburn

This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Huxton 2008-07-01 11:17:16 Re: Quick select, slow update - help with performance problems
Previous Message Gavin 'Beau' Baumanis 2008-06-28 17:12:05 Re: distinct / group by assistance.