SEO

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

> 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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/cvsw...kend/commands/
for trigger.c.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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/cvsw...kend/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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

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

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/cvsw...kend/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)

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly