From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Michael Fuhr <mike@fuhr.org>
Cc: Bruno Wolff III <bruno@wolff.to>
Cc: Martin Lesser <ml-pgsql@bettercom.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Effects of cascading references in foreign keys
Date: Sat, 29 Oct 2005 12:19:14 -0400 (EDT)
Message-ID: <200510291619.j9TGJE009833@candle.pha.pa.us> (raw)
In-Reply-To: <20051029160527.GA80135@winnie.fuhr.org>
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 <mike@fuhr.org> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pgman@candle.pha.pa.us, mike@fuhr.org, bruno@wolff.to, ml-pgsql@bettercom.de
Subject: Re: Effects of cascading references in foreign keys
In-Reply-To: <200510291619.j9TGJE009833@candle.pha.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox