Re: Why Does UPDATE Take So Long?

On Tuesday 30 September 2008, Bill Thoen <bthoen@gisnet.com> 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.

update creates new rows for all affected rows. If the table is indexed, it
creates new index rows for all affected rows in every index. Slow updates
is a common PostgreSQL complaint.

--
Alan