"Rosser Schwarz" <rschwarz@totalcardinc.com> writes:
>> You can create a new table using 'create table as' to produce your
>> target results. This is real fast ...
>> I often see 2 orders of magnitude improvement doing this, and no
>> need to vacuum.
> Indeed:
> "Query returned successfully with no result in 582761 ms."
> Though I must say, ten minutes is nominally more than two orders of
> mangitude performance improvement, versus several days.
Hm. There is no way that inserting a row is two orders of magnitude
faster than updating a row --- they both require storing a new row and
making whatever index entries are needed. The only additional cost of
the update is finding the old row (not a very big deal AFAICS in the
examples you gave) and marking it deleted (definitely cheap). So
there's something awfully fishy going on here.
I'm inclined to suspect an issue with foreign-key checking. You didn't
give us any details about foreign key relationships your "cust" table is
involved in --- could we see those? And the schemas of the other tables
involved?
Also, exactly which PG version is this?
regards, tom lane