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 EB30FD1C9ED for ; Tue, 6 Apr 2004 00:19:39 +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 57188-01 for ; Mon, 5 Apr 2004 21:20:11 -0300 (ADT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id C8E7DD1C995 for ; Mon, 5 Apr 2004 21:19:37 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 13F8A35517; Mon, 5 Apr 2004 17:20:20 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 127A4350A3; Mon, 5 Apr 2004 17:20:20 -0700 (PDT) Date: Mon, 5 Apr 2004 17:20:20 -0700 (PDT) From: Stephan Szabo To: Rosser Schwarz Cc: pgsql-performance@postgresql.org Subject: Re: atrocious update performance In-Reply-To: <009901c41b48$8357c860$2500fa0a@CardServices.TCI.com> Message-ID: <20040405170631.M19491@megazone.bigpanda.com> References: <009901c41b48$8357c860$2500fa0a@CardServices.TCI.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/54 X-Sequence-Number: 6354 On Mon, 5 Apr 2004, Rosser Schwarz wrote: > while you weren't looking, Kevin Barnard wrote: > > > Have you added indexes for the custid column for tables > > account.acct accunt.orgacct and note? > > They were indexed in the original case, yes. There was no > need to index them in today's test case, as that was done > purely in attempt to rule in or out foreign key validation > as the cause of the performance hit. No foreign keys that > might be validated, no need to index the foreign key columns. > > > I haven't followed the entire thread but it you have > > cascading FK on those tables without an index on the > > column that could cause your delay. > > The issue is that the foreign keys are being validated at > all, when the column being referenced by those foreign keys > (account.cust.custid) is never touched. > > Regardless of whether or not the referencing columns are > indexed, validating them at all--in this specific case--is > broken. The column they refer to is never touched; they > should remain utterly ignorant of whatever happens to other > columns in the same row. It shouldn't be checking the other table if the values of the key column hadn't changed. The ri_KeysEqual check should be causing it to return just before actually doing the check on the other table (it still does a few things before then but nothing that should be particularly expensive). In some simple tests on my 7.4.2 machine, this appears to work for me on pk cascade updates. It would be interesting to know if it's actually doing any checks for you, you might be able to poke around the triggers (backend/utils/adt/ri_triggers.c).