Re: atrocious update performance

"Rosser Schwarz" <rschwarz@totalcardinc.com> writes:
> As for foreign keys, three tables refer to account.cust; all of them
> refer to account.cust.custid, the pk.  One of those tables has several
> hundred thousand rows, many more to come; the others are empty.  Unless
> I've woefully misunderstood, the presence or absence of a foreign key
> referring to one column should be moot for updates writing another
> column, shouldn't it?

Well, that is the crux of the issue, and also why I was asking about
versions.  It's only been since 7.3.4 or so that we skip checking FKs on
update.

Looking at the code, though, the update check is only skipped if the
previous version of the row predates the current transaction.
(Otherwise we can't be sure that the value was ever checked.)  This
means that slow FK checks could be your problem if the application is
set up to issue multiple UPDATEs affecting the same row(s) during a
single transaction.  I'm not clear on whether that applies to you or not.

And anyway the bottom line is: have you got indexes on the columns
*referencing* account.cust.custid?  If not you probably ought to add
them, since without them there will definitely be some slow cases.

            regards, tom lane