Date: Sat, 29 Oct 2005 08:24:32 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Martin Lesser <ml-pgsql@bettercom.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Effects of cascading references in foreign keys
Message-ID: <20051029142432.GA79557@winnie.fuhr.org>
References: <87acgszihk.fsf@nb-aspire.bettercom.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <87acgszihk.fsf@nb-aspire.bettercom.de>

> 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