Updates, deletes and inserts are very slow. What can I do make them bearable?

From: Tim Uckun <timuckun(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Updates, deletes and inserts are very slow. What can I do make them bearable?
Date: 2010-10-21 02:24:00
Message-ID: AANLkTi=UxAPpo49Laq-wxG_DBLtfgp3E_rn9tFywnMU=@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a very simple update query.

update cu
set screenshot_file_name = tu.screenshot_file_name,
screenshot_content_type = tu.screenshot_content_type,
screenshot_file_size = tu.screenshot_file_size,
screenshot_status = tu.screenshot_status

from cu
inner join tu on tu.cu_id = cu.id

The join has five to six thousand rows in it.

The only indexed field in the update is screenshot_status which is an integer.

I changed the checkpoint settings to

checkpoint_segments = 256
checkpoint_completion_target = 0.9

but it still does not help.

I am having similar problems with deletes and inserts. Trying to
delete even a few thousand records takes forever. The selects seem to
be just fine.

I am running this on my laptop with no other processes hitting the
database. It's a i5 with lots of RAM and quad core and a IDE drive.

Where is the FAST button for postgres updates? What parameter do I
have to set in order to update 6000 records in under an hour?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2010-10-21 02:28:34 Re: Composite Index question
Previous Message DM 2010-10-21 01:47:30 Question on Explain : Index Scan