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).
