Re: Do foreign key triggers get ran even if the key's value doesn't change?

Joe Van Dyk <joe(at)tanga(dot)com> writes:
> On Thu, May 22, 2014 at 10:52 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> FWIW, I profiled this example (after cranking it up to 500 target tables
>> just because). AFAICT the primary component of the runtime increase is
>> query startup overhead associated with the increased number of target
>> tables.

> I must be missing something, there's only one table being updated?

Sorry, I wrote that a bit too hastily. The startup cost is actually
mainly driven by the number of triggers attached to the UPDATE's target
table. Specifically, what I'm seeing in oprofile is

samples % symbol name
32694 26.0730 MemoryContextStrdup
22608 18.0296 hash_search_with_hash_value
9979 7.9581 AllocSetAlloc
6919 5.5178 ri_KeysEqual
3910 3.1182 AllocSetCheck
3641 2.9036 AfterTriggerSaveEvent
2852 2.2744 MemoryContextAlloc
2778 2.2154 hash_any
2410 1.9219 ri_FetchConstraintInfo
2236 1.7832 hash_search
1906 1.5200 CopyTriggerDesc
1833 1.4618 hash_uint32
1817 1.4490 ri_NullCheck
1663 1.3262 RI_FKey_pk_upd_check_required
1338 1.0670 sentinel_ok
(nothing else above 1%)

The MemoryContextStrdup calls are all coming from CopyTriggerDesc, which
has to copy 1000 triggers' names in my test case; those calls presumably
also account for a good chunk of the AllocSetAlloc cycles. (It's possible
we could get rid of the TriggerDesc copy step in InitResultRelInfo, but it
seems rather nervous-making to do so, and it wouldn't really be worth
doing for examples with more realistic numbers of triggers.)

The hash_search calls are coming from a bunch of places, mostly relation
lookup and buffer cache lookup. There are some coming from ri_KeysEqual's
lookup of which comparison function to use to perform the IS NOT DISTINCT
FROM tests, but that doesn't seem to be a large component of that profile
item.

Anyway, it's hard to see how to improve this much, short of a major
redesign to avoid treating independent foreign key constraints as
independent triggers.

regards, tom lane