Re: Effects of cascading references in foreign keys

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(at)fuhr(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073