Re: Why Does UPDATE Take So Long?

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.

Regards,
Jeff Davis