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.
Scott Marlowe wrote:
On Tue, Sep 30, 2008 at 1:00 PM, Bill Thoen <[EMAIL PROTECTED]> 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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
