Thread: Effects of cascading references in foreign keys
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
> 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
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.
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.
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
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
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
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
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
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)
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
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).
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
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. +
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. +
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. +
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. +