Re: Effects of cascading references in foreign keys

Поиск
Список
Период
Сортировка
От Martin Lesser
Тема Re: Effects of cascading references in foreign keys
Дата
Msg-id 87fyqiu5ez.fsf@nb-aspire.bettercom.de
обсуждение исходный текст
Ответ на Re: Effects of cascading references in foreign keys  (Bruce Momjian <pgman@candle.pha.pa.us>)
Список pgsql-performance
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).

В списке pgsql-performance по дате отправления:

Предыдущее
От: Svenne Krap
Дата:
Сообщение: Re: multi-layered view join performance oddities
Следующее
От: "Qingqing Zhou"
Дата:
Сообщение: Re: performance of implicit join vs. explicit conditions on inet queries?