From: Martin Lesser <ml-pgsql@bettercom.de>
To: pgsql-performance@postgresql.org
Subject: Effects of cascading references in foreign keys
Date: Sat, 29 Oct 2005 13:10:31 +0200
Message-ID: <87acgszihk.fsf@nb-aspire.bettercom.de> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ml-pgsql@bettercom.de
Subject: Re: Effects of cascading references in foreign keys
In-Reply-To: <87acgszihk.fsf@nb-aspire.bettercom.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox