From: Martin Lesser <ml-pgsql@bettercom.de>
To: pgsql-performance@postgresql.org
Subject: Re: Effects of cascading references in foreign keys
Date: Sun, 30 Oct 2005 21:16:20 +0100
Message-ID: <87fyqiu5ez.fsf@nb-aspire.bettercom.de> (raw)
In-Reply-To: <200510301410.j9UEAnj02881@candle.pha.pa.us>
References: <200510301410.j9UEAnj02881@candle.pha.pa.us>
Bruce Momjian <pgman@candle.pha.pa.us> 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).
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: <87fyqiu5ez.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