Horton

Thread

  1. Effects of cascading references in foreign keys

    Martin Lesser <ml-pgsql@bettercom.de> — 2005-10-29T11:10:31Z

    Which effects have UPDATEs on REFERENCEd TABLEs when only columns in the
referenced table are updated which are not part of the FOREIGN KEY
constraint?

I have one "master"-table like

 create table t_master (
   m_id   serial primary key,
   m_fld1 ...,
   m_fld2 ...,
   ...
 )

The table above is referenced from several (~30) other tables, i.e. like

 create table t_detail (
   d_ebid int REFERENCES t_master (m_id) ON UPDATE CASCADE ON DELETE CASCADE,
   d_fld1 ...,
   d_fld2 ...,
   ...
 )

All tables which reference t_master have appropriate indexes on the
referencing columns, vacuum/analyze is done regularly (daily).

Does an UPDATE of e.g. m_fld1 in t_master cause a 'lookup' in all tables
which have a cascading update-rule or is this 'lookup' only triggered if
the referenced column in t_master is explicitly updated? After removing
some detail tables which are not longer needed we see an improvemed
performance so at the moment it _looks_ like each update in t_master
triggers a 'lookup' in each referencing table also if the referenced
column (m_id) is not changed.

I've read "If the row is updated, but the referenced column is not
actually changed, no action is done." in the docs but it is not clear
for me whether this "no action" really means "null action" and so the
improved performance has other reasons.

TIA, Martin

  2. Re: Effects of cascading references in foreign keys

    Michael Fuhr <mike@fuhr.org> — 2005-10-29T14:24:32Z

    > Does an UPDATE of e.g. m_fld1 in t_master cause a 'lookup' in all tables
> which have a cascading update-rule or is this 'lookup' only triggered if
> the referenced column in t_master is explicitly updated?

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.

CREATE TABLE foo (id serial PRIMARY KEY, x integer NOT NULL);
CREATE TABLE bar (fooid integer NOT NULL REFERENCES foo ON UPDATE CASCADE);

INSERT INTO foo (x) SELECT * FROM generate_series(1, 100000);
INSERT INTO bar (fooid) SELECT * FROM generate_series(1, 100000);

ANALYZE foo;
ANALYZE bar;

EXPLAIN ANALYZE UPDATE foo SET x = 1 WHERE id = 100000;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Index Scan using foo_pkey on foo  (cost=0.00..3.01 rows=1 width=10) (actual time=0.059..0.070 rows=1 loops=1)
   Index Cond: (id = 100000)
 Total runtime: 0.633 ms
(3 rows)

EXPLAIN ANALYZE UPDATE foo SET x = 1, id = 200000 WHERE id = 100000;
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Index Scan using foo_pkey on foo  (cost=0.00..3.01 rows=1 width=6) (actual time=0.082..0.092 rows=1 loops=1)
   Index Cond: (id = 100000)
 Trigger for constraint bar_fooid_fkey: time=232.612 calls=1
 Total runtime: 233.073 ms
(4 rows)

I'm not sure if this is the right place to look, but I see several
places in src/backend/utils/adt/ri_triggers.c with code that looks
like this:

    /*
     * No need to do anything if old and new keys are equal
     */
    if (ri_KeysEqual(pk_rel, old_row, new_row, &qkey,
                     RI_KEYPAIR_PK_IDX))
    {
        heap_close(fk_rel, RowExclusiveLock);
        return PointerGetDatum(NULL);
    }

> After removing some detail tables which are not longer needed we
> see an improvemed performance so at the moment it _looks_ like each
> update in t_master triggers a 'lookup' in each referencing table
> also if the referenced column (m_id) is not changed.

Do you have statistics enabled?  You might be able to infer what
happens by looking at pg_stat_user_tables or pg_statio_user_tables
before and after an update, assuming that no concurrent activity
is also affecting the statistics.

I suppose there's overhead just from having a foreign key constraint,
and possibly additional overhead for each constraint.  If so then
that might explain at least some of the performance improvement.
Maybe one of the developers will comment.

-- 
Michael Fuhr

  3. Re: Effects of cascading references in foreign keys

    Bruno Wolff III <bruno@wolff.to> — 2005-10-29T14:48:35Z

    On Sat, Oct 29, 2005 at 13:10:31 +0200,
  Martin Lesser <ml-pgsql@bettercom.de> wrote:
> Which effects have UPDATEs on REFERENCEd TABLEs when only columns in the
> referenced table are updated which are not part of the FOREIGN KEY
> constraint?

In 8.1 there is a check to see if the foreign key value has changed and if
not a trigger isn't queued. In the currently released versions any update
will fire triggers.
The check in comment for trigger.c didn't say if this optimization applied
to both referencing and referenced keys or just one of those.
If you need to know more you can look at the code at:
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/
for trigger.c.

  4. Re: Effects of cascading references in foreign keys

    Bruno Wolff III <bruno@wolff.to> — 2005-10-29T14:49:47Z

    On Sat, Oct 29, 2005 at 08:24:32 -0600,
  Michael Fuhr <mike@fuhr.org> wrote:
> > Does an UPDATE of e.g. m_fld1 in t_master cause a 'lookup' in all tables
> > which have a cascading update-rule or is this 'lookup' only triggered if
> > the referenced column in t_master is explicitly updated?
> 
> 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.

  5. Re: Effects of cascading references in foreign keys

    Michael Fuhr <mike@fuhr.org> — 2005-10-29T16:05:27Z

    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

-- 
Michael Fuhr

  6. Re: Effects of cascading references in foreign keys

    Bruce Momjian <pgman@candle.pha.pa.us> — 2005-10-29T16:05:33Z

    Bruno Wolff III wrote:
> On Sat, Oct 29, 2005 at 13:10:31 +0200,
>   Martin Lesser <ml-pgsql@bettercom.de> wrote:
> > Which effects have UPDATEs on REFERENCEd TABLEs when only columns in the
> > referenced table are updated which are not part of the FOREIGN KEY
> > constraint?
> 
> In 8.1 there is a check to see if the foreign key value has changed and if
> not a trigger isn't queued. In the currently released versions any update
> will fire triggers.
> The check in comment for trigger.c didn't say if this optimization applied
> to both referencing and referenced keys or just one of those.
> If you need to know more you can look at the code at:
> http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/
> for trigger.c.

It applies to both.  See
src/backend/utils/adt/ri_triggers.c::RI_FKey_keyequal_upd_pk() and
RI_FKey_keyequal_upd_fk().  The first is for primary keys (pk), the
second for foreign keys (fk). These are called by
src/backend/command/triggers.c::AfterTriggerSaveEvent().  The checks
prevent the trigger from being registered at all if there is no change
in the primary/foreign key relationship.

-- 
  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

  7. Re: Effects of cascading references in foreign keys

    Bruce Momjian <pgman@candle.pha.pa.us> — 2005-10-29T16:19:14Z

    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

  8. Re: Effects of cascading references in foreign keys

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-10-29T18:01:58Z

    Michael Fuhr <mike@fuhr.org> writes:
> On Sat, Oct 29, 2005 at 09:49:47AM -0500, Bruno Wolff III wrote:
>> 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.

Yeah.  IIRC, for quite some time we've had tests inside the FK update
triggers to not bother to search the other table if the key value hasn't
changed.  What we did in 8.1 was to push that test further upstream, so
that the trigger event isn't even queued if the key value hasn't
changed.  (This is why you don't see the trigger shown as being called
even once.)

Looking at this, I wonder if there isn't a bug or at least an
inefficiency in 8.1.  The KeysEqual short circuit tests are still there
in ri_triggers.c; aren't they now redundant with the test in triggers.c?
And don't they need to account for the special case mentioned in the
comment in triggers.c, that the RI check must still be done if we are
looking at a row updated by the same transaction that created it?

			regards, tom lane

  9. Re: Effects of cascading references in foreign keys

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-10-29T18:35:25Z

    I wrote:
> Looking at this, I wonder if there isn't a bug or at least an
> inefficiency in 8.1.  The KeysEqual short circuit tests are still there
> in ri_triggers.c; aren't they now redundant with the test in triggers.c?
> And don't they need to account for the special case mentioned in the
> comment in triggers.c, that the RI check must still be done if we are
> looking at a row updated by the same transaction that created it?

OK, I take back the possible-bug comment: the special case only applies
to the FK-side triggers, which is to say RI_FKey_check, and that routine
doesn't attempt to skip the check on equal old/new keys.  I'm still
wondering though if the KeysEqual tests in the other RI triggers aren't
now a waste of cycles.

			regards, tom lane

  10. Re: Effects of cascading references in foreign keys

    Thomas F. O'Connell <tfo@sitening.com> — 2005-10-30T04:32:21Z

    On Oct 29, 2005, at 9:48 AM, Bruno Wolff III wrote:

> On Sat, Oct 29, 2005 at 13:10:31 +0200,
>   Martin Lesser <ml-pgsql@bettercom.de> wrote:
>
>> Which effects have UPDATEs on REFERENCEd TABLEs when only columns  
>> in the
>> referenced table are updated which are not part of the FOREIGN KEY
>> constraint?
>
> In 8.1 there is a check to see if the foreign key value has changed  
> and if
> not a trigger isn't queued. In the currently released versions any  
> update
> will fire triggers.
> The check in comment for trigger.c didn't say if this optimization  
> applied
> to both referencing and referenced keys or just one of those.
> If you need to know more you can look at the code at:
> http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/
> for trigger.c.

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.

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Open Source Solutions. Optimized Web Development.

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)

  11. Re: Effects of cascading references in foreign keys

    Bruce Momjian <pgman@candle.pha.pa.us> — 2005-10-30T14:10:49Z

    Thomas F. O'Connell wrote:
> > In 8.1 there is a check to see if the foreign key value has changed  
> > and if
> > not a trigger isn't queued. In the currently released versions any  
> > update
> > will fire triggers.
> > The check in comment for trigger.c didn't say if this optimization  
> > applied
> > to both referencing and referenced keys or just one of those.
> > If you need to know more you can look at the code at:
> > http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/
> > for trigger.c.
> 
> 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.

However, the description seems more complex than it is worth.

-- 
  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

  12. Re: Effects of cascading references in foreign keys

    Martin Lesser <ml-pgsql@bettercom.de> — 2005-10-30T20:16:20Z

    Bruce Momjian <pgman@candle.pha.pa.us> 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).

  13. Re: Effects of cascading references in foreign keys

    Bruce Momjian <pgman@candle.pha.pa.us> — 2005-12-07T04:41:16Z

    Would someone add a comment in the code about this, or research it?

---------------------------------------------------------------------------

Tom Lane wrote:
> I wrote:
> > Looking at this, I wonder if there isn't a bug or at least an
> > inefficiency in 8.1.  The KeysEqual short circuit tests are still there
> > in ri_triggers.c; aren't they now redundant with the test in triggers.c?
> > And don't they need to account for the special case mentioned in the
> > comment in triggers.c, that the RI check must still be done if we are
> > looking at a row updated by the same transaction that created it?
> 
> OK, I take back the possible-bug comment: the special case only applies
> to the FK-side triggers, which is to say RI_FKey_check, and that routine
> doesn't attempt to skip the check on equal old/new keys.  I'm still
> wondering though if the KeysEqual tests in the other RI triggers aren't
> now a waste of cycles.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq
> 

-- 
  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

  14. Re: Effects of cascading references in foreign keys

    Bruce Momjian <pgman@candle.pha.pa.us> — 2006-06-14T22:12:48Z

    Would someone please find the answer to Tom's last question?

---------------------------------------------------------------------------

Tom Lane wrote:
> I wrote:
> > Looking at this, I wonder if there isn't a bug or at least an
> > inefficiency in 8.1.  The KeysEqual short circuit tests are still there
> > in ri_triggers.c; aren't they now redundant with the test in triggers.c?
> > And don't they need to account for the special case mentioned in the
> > comment in triggers.c, that the RI check must still be done if we are
> > looking at a row updated by the same transaction that created it?
> 
> OK, I take back the possible-bug comment: the special case only applies
> to the FK-side triggers, which is to say RI_FKey_check, and that routine
> doesn't attempt to skip the check on equal old/new keys.  I'm still
> wondering though if the KeysEqual tests in the other RI triggers aren't
> now a waste of cycles.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq
> 

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

  15. Re: Effects of cascading references in foreign keys

    Bruce Momjian <bruce@momjian.us> — 2006-08-22T03:50:02Z

    Tom Lane wrote:
> I wrote:
> > Looking at this, I wonder if there isn't a bug or at least an
> > inefficiency in 8.1.  The KeysEqual short circuit tests are still there
> > in ri_triggers.c; aren't they now redundant with the test in triggers.c?
> > And don't they need to account for the special case mentioned in the
> > comment in triggers.c, that the RI check must still be done if we are
> > looking at a row updated by the same transaction that created it?
> 
> OK, I take back the possible-bug comment: the special case only applies
> to the FK-side triggers, which is to say RI_FKey_check, and that routine
> doesn't attempt to skip the check on equal old/new keys.  I'm still
> wondering though if the KeysEqual tests in the other RI triggers aren't
> now a waste of cycles.

Would someone please research this?  Thanks.

-- 
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

  16. Re: Effects of cascading references in foreign keys

    Bruce Momjian <bruce@momjian.us> — 2007-09-26T08:21:12Z

    This has been saved for the 8.4 release:

	http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > On Sat, Oct 29, 2005 at 09:49:47AM -0500, Bruno Wolff III wrote:
> >> 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.
> 
> Yeah.  IIRC, for quite some time we've had tests inside the FK update
> triggers to not bother to search the other table if the key value hasn't
> changed.  What we did in 8.1 was to push that test further upstream, so
> that the trigger event isn't even queued if the key value hasn't
> changed.  (This is why you don't see the trigger shown as being called
> even once.)
> 
> Looking at this, I wonder if there isn't a bug or at least an
> inefficiency in 8.1.  The KeysEqual short circuit tests are still there
> in ri_triggers.c; aren't they now redundant with the test in triggers.c?
> And don't they need to account for the special case mentioned in the
> comment in triggers.c, that the RI check must still be done if we are
> looking at a row updated by the same transaction that created it?
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

-- 
  Bruce Momjian  <bruce@momjian.us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

  17. Re: Effects of cascading references in foreign keys

    Bruce Momjian <bruce@momjian.us> — 2008-03-07T19:19:03Z

    Added to TODO:

* Improve referential integrity checks

  http://archives.postgresql.org/pgsql-performance/2005-10/msg00458.php


---------------------------------------------------------------------------

Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > On Sat, Oct 29, 2005 at 09:49:47AM -0500, Bruno Wolff III wrote:
> >> 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.
> 
> Yeah.  IIRC, for quite some time we've had tests inside the FK update
> triggers to not bother to search the other table if the key value hasn't
> changed.  What we did in 8.1 was to push that test further upstream, so
> that the trigger event isn't even queued if the key value hasn't
> changed.  (This is why you don't see the trigger shown as being called
> even once.)
> 
> Looking at this, I wonder if there isn't a bug or at least an
> inefficiency in 8.1.  The KeysEqual short circuit tests are still there
> in ri_triggers.c; aren't they now redundant with the test in triggers.c?
> And don't they need to account for the special case mentioned in the
> comment in triggers.c, that the RI check must still be done if we are
> looking at a row updated by the same transaction that created it?
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +