Re: Why Does UPDATE Take So Long?

On Tue, Sep 30, 2008 at 2:51 PM, Bill Thoen <bthoen(at)gisnet(dot)com> wrote:
> Doesn't look like that's the problem. I moved my table over to another Linux
> box running PG 8.3 and update performance was pretty bad there as well. In
> the time that PG 8.3 was struggling with update there I created a copy of my
> table on my PG 8.1 machine and inserted all columns with one containing the
> altered values I wanted and that took less than two minutes. Meanwhile, a
> half-hour later, my PG 8.3 machine was still thrashing away trying to update
> that one column that's not even part of any index..
>
> Something is really wrong with UPDATE in PostgreSQL I think.

You'll remember I mentioned a low fill factor. With a 100% fillfactor
you'll get no advantage from 8.3

The default tuning in postgresql allows it to run reasonably well on
things like laptops and desktops. It's impossible to deliver it ready
for a 32 CPU 200 drive megaserver with the same configuration file
you'd use for a laptop.

Do a quick google search on postgresql performance tuning and you'll
turn up quite a few sites and wikis on it. The 5 minute version:

set shared_buffers = 1/4 memory.
set work_mem to something like 8 megs.
Turn on the autovacuum daemon