>
> 1) Increase checkpoint_segments (>64, increases beyond that can be helpful
> but they eventually level out)
Changed it back to 64 from 256
> 2) Increase shared_buffers (~25% of RAM is normal)
Changed it to one gig (25% of my RAM) obviously this involved changing
the shmmax and shmall settings in the kernel.
> 3) Confirm there are no constraints or foreign keys happening at each update
There are none.
> 4) Make sure your indexes aren't filled with junk and that VACUUM is running
> effectively. REINDEX or CLUSTER tables that haven't been well maintained in
> the past.
Autovacuum is on. I presume it's doing it's job. Didn't re-index or
recluster because it's a dev database and the data rarely changes.
> 5) Upgrade to better hardware that has a battery-backed write cache
Not for my laptop.
> Disable synchronous_commit and cheat on individual commits, at the expense
> of potential lost transactions after a crash.
I will leave this as a last resort.
> Updating rows in PostgreSQL is one of the most intensive things you do to
> your disks, and it's hard to get a laptop drive to do a very good job at
> that.
>
After making the above changes I re-ran the query. It's been running
for five minutes and it's still running.
This is a database with nothing else hitting it.
So obviously something else is out of kilter.
I'll ask the same question I asked Gary.
Say I just apt-get install postgres and do nothing else. One table has
about 500K records. The other has about 5K records. The joins are on
indexed integer fields (one is the primary key). How long should it
take to update five to six thousand records in your experience? Out
of the box with no tuning. How long should this take on an almost new
laptop, four gigs of RAM, i5 quad core processor?