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
