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

On Thu, May 22, 2014 at 10:52 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
>> On Wed, May 21, 2014 at 7:48 PM, Joe Van Dyk <joe(at)tanga(dot)com> wrote:
>>> I was expecting that the RI update triggers would have a "when (new.key is
>>> distinct from old.key)" condition on them, which would mean that the number
>>> of referencing tables wouldn't matter.
>
>> But that condition is checked for each constraint individually, not for all
>> constraints simultaneously. A table can be referenced on multiple
>> combinations of columns, so just one check may not suffice. I guess the
>> triggers could be organized into groups of identical firing criteria and
>> then checked only once per group, but that seems like a pretty obscure
>> optimization to make. I don't know how you would reorganize such groupings
>> in a concurrency safe way when constraints were added or removed.
>
> 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?

start_time = clock_timestamp();

FOR i IN 1..100000 LOOP
UPDATE test_fk SET junk = ' '
WHERE id = i;
END LOOP;

end_time = clock_timestamp();

Joe

> If the UPDATE were touching more than one tuple then it might
> get to the point where per-tuple costs dominate, but it's not there in
> this example. If we tried to do something like what Jeff suggests to
> improve the per-tuple costs, it could actually make this example slower
> by adding more startup overhead.
>
> regards, tom lane