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?