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 C4B07D1E128 for ; Tue, 16 Mar 2004 07:31:07 +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 42912-10 for ; Tue, 16 Mar 2004 03:31:07 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id E8EB5D1D2DC for ; Tue, 16 Mar 2004 03:31:05 -0400 (AST) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id D036617C129; Tue, 16 Mar 2004 02:31:06 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1B392Y-0001vJ-00; Tue, 16 Mar 2004 02:31:06 -0500 To: "Rosser Schwarz" Cc: "'Postgresql Performance'" Subject: Re: atrocious update performance References: <001501c40ad2$b3f7ade0$2500fa0a@CardServices.TCI.com> In-Reply-To: <001501c40ad2$b3f7ade0$2500fa0a@CardServices.TCI.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 16 Mar 2004 02:31:06 -0500 Message-ID: <871xnt1c5h.fsf@stark.xeocode.com> Lines: 22 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/217 X-Sequence-Number: 6073 "Rosser Schwarz" 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