Re: atrocious update performance

"Rosser Schwarz" <rschwarz(at)totalcardinc(dot)com> writes:

> Actually, there are no foreign keys to those columns. Once they're
> populated, I'll apply a foreign key constraint and they'll refer to the
> appropriate row in the prod and subprod tables, but nothing will
> reference account.cust.[sub]prodid. There are, of course, several foreign
> keys referencing account.cust.custid.

Just to be clear, the foreign key constraints they're worrying about are not
constraints on the table you're updating. They're constraints on other tables
referring to the table you're updating.

Since you're updating the column here postgres has to be sure nothing is
referring to the old value you're obliterating, and to do that it has to
select for possible records in the referencing tables referring to the value.
If there are any references in other tables referring to this column then you
need an index on the column in the referencing table to be able to update the
column in referenced table efficiently.

--
greg