Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Joe Van Dyk <joe@xxxxxxxxx> writes:
> On Thu, May 22, 2014 at 10:52 AM, Tom Lane <tgl@xxxxxxxxxxxxx> 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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux