Re: REVIEW: Optimize referential integrity checks (todo item)

On 17 June 2012 18:30, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Gurjeet Singh  wrote:
>> Dean Rasheed wrote:
>
>> in HEAD:
>>> ... (actual time=1390.037..1390.037 rows=0 loops=1)
>>> Trigger for constraint fk_table_e_fkey: time=210.184 calls=90000
>>> Total runtime: 1607.626 ms
>
>>> With this patch:
>>> ... (actual time=1489.640..1489.640 rows=0 loops=1)
>>> [no triggers fired]
>>> Total runtime: 1489.679 ms
>
>>> for every row:
>>> ... (actual time=1565.148..1565.148 rows=0 loops=1)
>>> Trigger for constraint fk_table_e_fkey: time=705.962 calls=100000
>>> Total runtime: 2279.408 ms
>
>>> with this patch
>>> ... (actual time=1962.755..1962.755 rows=0 loops=1)
>>> Trigger for constraint fk_table_e_fkey: time=257.845 calls=10000
>>> Total runtime: 2221.912 ms
>
>> I find it interesting that 'actual time' for top level 'Update on
>> fk_table' is always higher in patched versions, and yet the 'Total
>> runtime' is lower for the patched versions. I would've expected
>> 'Total runtime' to be proportional to the increase in top-level
>> row-source's 'actual time'.
>
> I figured that the trigger time was counted separately.  It seems to
> add up pretty well that way.  I guess the question is whether there
> is a case where the increase in seqscan time is *not* compensated by
> less time in the triggers.
>
> -Kevin

I wouldn't read too much into the individual timings I posted above,
since I get massive variations between runs. If I repeat it enough
times, I can convince myself that the update times excluding trigger
execution are unchanged on average, but the trigger execution times
(which are indeed counted separately) are a real savings.

Regards,
Dean