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

Re: Why Does UPDATE Take So Long?

From: Bill Moran <wmoran(at)collaborativefusion(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 20:53:05
Message-ID: 20080930165305.fec04844.wmoran@collaborativefusion.com (view raw or flat)
Thread:
Lists: pgsql-general
In response to Bill Thoen <bthoen(at)gisnet(dot)com>:

> 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.

That's an interesting theory, although it's completely wrong and founded
in ridiculosity.  If something were "really wrong with UPDATE" in every
version of PostgreSQL, you'd be reading about it on the mailing lists,
and you won't.

What I suspect is that the typical tuning advice applies here.  I don't
see any information about your configuration or your hardware setup.
* What are shared_buffers set at?
* What do the checkpoint configs look like?
* In general, what does your postgresql.conf look like, how much tuning
  have you done?
* What is your hardware setup?  You're not running RAID 5 are you?

-- 
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran(at)collaborativefusion(dot)com
Phone: 412-422-3463x4023

In response to

Responses

pgsql-general by date

Next:From: Alvaro HerreraDate: 2008-09-30 21:05:31
Subject: Re: Why Does UPDATE Take So Long?
Previous:From: Bill ThoenDate: 2008-09-30 20:51:42
Subject: Re: Why Does UPDATE Take So Long?

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