X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0E7CDDB40F for ; Sun, 30 Oct 2005 16:16:29 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 56125-06 for ; Sun, 30 Oct 2005 20:16:23 +0000 (GMT) Received: from h1.bettercom.de (h1.bettercom.de [213.239.194.41]) by svr1.postgresql.org (Postfix) with ESMTP id BD2CEDB3BF for ; Sun, 30 Oct 2005 16:16:23 -0400 (AST) Received: (qmail 22605 invoked by uid 208); 30 Oct 2005 20:16:20 -0000 Received: from 80.171.82.228 by h1 (envelope-from , uid 0) with qmail-scanner-1.23 (clamdscan: 0.87. spamassassin: 3.04. Clear:RC:1(80.171.82.228):. Processed in 0.15354 secs); 30 Oct 2005 20:16:20 -0000 Received: from unknown (HELO nb-aspire.bettercom.de) ([80.171.82.228]) (envelope-sender ) by h1.bettercom.de (qmail-ldap-1.03) with SMTP for ; 30 Oct 2005 20:16:20 -0000 Received: (nullmailer pid 4657 invoked by uid 1000); Sun, 30 Oct 2005 20:16:20 -0000 Subject: Re: Effects of cascading references in foreign keys X-PGP-Key: http://bettercom.de/misc/3A547DE6.asc X-Face: "d[&>8')a)wbF:+L#^<_cohnX6#m5RCCeKF/6_gD(iQ9bX?xe2~Aq*!')D(1ks`?YhomOYbL3R:{4e4a]qft_]<.q/Lf4hIr,`G+LX33&TYp}XGfe~^5m$28R"$C(EwnB\n8t In-Reply-To: <200510301410.j9UEAnj02881@candle.pha.pa.us> (Bruce Momjian's message of "Sun, 30 Oct 2005 09:10:49 -0500 (EST)") References: <200510301410.j9UEAnj02881@candle.pha.pa.us> To: pgsql-performance@postgresql.org From: Martin Lesser Date: Sun, 30 Oct 2005 21:16:20 +0100 Message-ID: <87fyqiu5ez.fsf@nb-aspire.bettercom.de> User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4 (Jumbo Shrimp, linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200510/466 X-Sequence-Number: 15234 Bruce Momjian writes: > Thomas F. O'Connell wrote: >> It seems like this warrants an item somewhere in the release notes, >> and I'm not currently seeing it (or a related item) anywhere. Perhaps >> E.1.3.1 (Performance Improvements)? For some of the more extreme >> UPDATE scenarios I've seen, this could be a big win. > Hard to say, perhaps: > > Prevent referential integrity triggers from firing if referenced > columns are not changed by an UPDATE > > Previously, triggers would fire but do nothing. And this "firing" has negative effects for the performance at least in versions before 8.1 (we use 8.0.3 in our production). One really dirty hack that comes in mind is to put an additional pk_table (with only one field, the pk from the master) between the "master"-table and the ~30 detail-tables so each update in the "master" would in most cases only trigger a lookup in one table. Only if a pk was really changed the CASCADEd trigger would force a triggered UPDATE in the detail-tables. After denormalization of two of the largest detail-tables into one table the performance improvement was about 10% due to the fact that up to 1 mio. of rows (of about 30 mio) in the "master"-table are updated daily and triggered a lookup in 190 mio. rows (before denormalization) resp. 115 rows (after denormalization).