Re: Effects of cascading references in foreign keys

> 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