Skip site navigation (1) Skip section navigation (2)

Re: Why Does UPDATE Take So Long?

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "Bill Thoen" <bthoen(at)gisnet(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Why Does UPDATE Take So Long?
Date: 2008-09-30 21:57:34
Message-ID: dcc563d10809301457j13ca6469y7df1cb3752e688fb@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Martijn van OosterhoutDate: 2008-09-30 22:02:32
Subject: Re: Free Cache Memory (Linux) and Postgresql
Previous:From: Bill ThoenDate: 2008-09-30 21:53:09
Subject: Re: Why Does UPDATE Take So Long?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group