X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 526A8D1B909 for ; Tue, 16 Mar 2004 18:04:50 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 71998-05 for ; Tue, 16 Mar 2004 14:04:48 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id B0ED5D1B55C for ; Tue, 16 Mar 2004 14:04:46 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i2GI4kJv026149; Tue, 16 Mar 2004 13:04:46 -0500 (EST) To: "Rosser Schwarz" Cc: pgsql-performance@postgresql.org Subject: Re: atrocious update performance In-reply-to: <001d01c40b7c$b80c98a0$2500fa0a@CardServices.TCI.com> References: <001d01c40b7c$b80c98a0$2500fa0a@CardServices.TCI.com> Comments: In-reply-to "Rosser Schwarz" message dated "Tue, 16 Mar 2004 11:32:56 -0600" Date: Tue, 16 Mar 2004 13:04:46 -0500 Message-ID: <26148.1079460286@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/221 X-Sequence-Number: 6077 "Rosser Schwarz" 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