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 F1C27DACB6 for ; Sat, 29 Oct 2005 13:19:28 -0300 (ADT) 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 95767-01 for ; Sat, 29 Oct 2005 16:19:27 +0000 (GMT) Received: from candle.pha.pa.us (candle.pha.pa.us [64.139.89.126]) by svr1.postgresql.org (Postfix) with ESMTP id 5326DDAC97 for ; Sat, 29 Oct 2005 13:19:27 -0300 (ADT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id j9TGJE009833; Sat, 29 Oct 2005 12:19:14 -0400 (EDT) From: Bruce Momjian Message-Id: <200510291619.j9TGJE009833@candle.pha.pa.us> Subject: Re: Effects of cascading references in foreign keys In-Reply-To: <20051029160527.GA80135@winnie.fuhr.org> To: Michael Fuhr Date: Sat, 29 Oct 2005 12:19:14 -0400 (EDT) Cc: Bruno Wolff III , Martin Lesser , pgsql-performance@postgresql.org X-Mailer: ELM [version 2.4ME+ PL121 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.014 required=5 tests=[AWL=0.014] X-Spam-Score: 0.014 X-Spam-Level: X-Archive-Number: 200510/456 X-Sequence-Number: 15224 Michael Fuhr wrote: > On Sat, Oct 29, 2005 at 09:49:47AM -0500, Bruno Wolff III wrote: > > On Sat, Oct 29, 2005 at 08:24:32 -0600, Michael Fuhr wrote: > > > My tests suggest that a lookup on the referring key is done only > > > if the referenced key is changed. Here's an example from 8.1beta4; > > > I used this version because EXPLAIN ANALYZE shows triggers and the > > > time spent in them, but I see similar performance characteristics > > > in earlier versions. I've intentionally not put an index on the > > > referring column to make lookups on it slow. > > > > It looks like this feature was added last May, so I think it only applies > > to 8.1. > > Earlier versions appear to have at least some kind of optimization. > Here's a test in 7.3.11 using the same tables I used in 8.1beta4, > although on a slower box. > > test=> UPDATE foo SET x = 1 WHERE id = 100000; > UPDATE 1 > Time: 32.18 ms > > test=> UPDATE foo SET x = 1, id = 200000 WHERE id = 100000; > UPDATE 1 > Time: 4144.95 ms > > test=> DROP TABLE bar; > DROP TABLE > Time: 240.87 ms > > test=> UPDATE foo SET x = 1, id = 100000 WHERE id = 200000; > UPDATE 1 > Time: 63.52 ms Yes, I think in 8.0.X those triggers were queued on firing did nothing while in 8.1 the triggers are not even fired. The 8.1 commit to ri_triggers.c has: revision 1.79 date: 2005/05/30 07:20:58; author: neilc; state: Exp; lines: +131 -65 When enqueueing after-row triggers for updates of a table with a foreign key, compare the new and old row versions. If the foreign key column has not changed, we needn't enqueue the trigger, since the update cannot violate the foreign key. This optimization was previously applied in the RI trigger function, but it is more efficient to avoid firing the trigger altogether. Per recent discussion on pgsql-hackers. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073