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

Re: Why Does UPDATE Take So Long?

From: Jeff Davis <pgsql(at)j-davis(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 19:36:13
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
On Tue, 2008-09-30 at 13:00 -0600, Bill Thoen wrote:
> Working with PG 8.1 I'm trying to update a char(4) column, and it's 
> taking a very long time; 15 minutes so far and no end in sight. From the 
> explain, it doesn't seem like it should take that long, and this column 
> is not indexed. Sure, there's 2.7 million records but it only takes a 
> few minutes to scan the whole file. Is there some special overhead I 
> should be aware of with an UPDATE? I VACUUMed and ANALYZEd first, too.

In PostgreSQL, think of an UPDATE as an INSERT+DELETE. It's got to write
the new versions of the tuples, and it has to keep the old versions
until there are no more transactions that might reference those old
versions. Imagine if you canceled the query halfway through, for
example. Also, it has to create new index entries for the same reason,
which is expensive.

There are some optimizations in 8.3 for when the same tuple gets updated
many times, but that won't help you in this case. 

	Jeff Davis

In response to

pgsql-general by date

Next:From: Felix HomannDate: 2008-09-30 19:38:00
Subject: Re: Alias name from subquery
Previous:From: Andreas KretschmerDate: 2008-09-30 19:20:52
Subject: Re: Why Does UPDATE Take So Long?

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