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

Search Discussions

  • Michael Fuhr at Oct 29, 2005 at 2:24 pm

    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
  • Bruno Wolff III at Oct 29, 2005 at 2:47 pm

    On Sat, Oct 29, 2005 at 08:24:32 -0600, Michael Fuhr 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.
  • Michael Fuhr at Oct 29, 2005 at 4:05 pm

    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

    --
    Michael Fuhr
  • Bruce Momjian at Oct 29, 2005 at 4:19 pm

    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
  • Tom Lane at Oct 29, 2005 at 6:02 pm

    Michael Fuhr 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
  • Tom Lane at Oct 29, 2005 at 6:35 pm

    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
  • Bruce Momjian at Dec 7, 2005 at 4:41 am
    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
  • Bruce Momjian at Jun 14, 2006 at 10:13 pm
    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. +
  • Bruce Momjian at Aug 22, 2006 at 3:55 am

    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. +
  • Bruce Momjian at Sep 26, 2007 at 8:22 am
    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. +
  • Bruce Momjian at Mar 7, 2008 at 7:21 pm
    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. +
  • Bruno Wolff III at Oct 29, 2005 at 2:46 pm

    On Sat, Oct 29, 2005 at 13:10:31 +0200, Martin Lesser 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.
  • Bruce Momjian at Oct 29, 2005 at 4:05 pm

    Bruno Wolff III wrote:
    On Sat, Oct 29, 2005 at 13:10:31 +0200,
    Martin Lesser 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
  • Thomas F. O'Connell at Oct 30, 2005 at 4:32 am

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

    On Sat, Oct 29, 2005 at 13:10:31 +0200,
    Martin Lesser 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)
  • Bruce Momjian at Oct 30, 2005 at 2:10 pm

    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
  • Martin Lesser at Oct 30, 2005 at 8:16 pm

    Bruce Momjian 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).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedOct 29, '05 at 11:10a
activeMar 7, '08 at 7:21p
posts17
users7
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase