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
Something is really wrong with UPDATE in PostgreSQL I think.
Scott Marlowe wrote:
> On Tue, Sep 30, 2008 at 1:00 PM, Bill Thoen <firstname.lastname@example.org> 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.
>> Or am I just expecting too much?
> The problem is that on older versions of pgsql, the db had to update
> each index for each row updated as well as the rows. The latest
> version, with a low enough fill factor, can update non-indedexed
> fields by using the free space in each page and not have to hit the
> indexes. But on 8.1 you don't get that optimization.