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

On Sat, Jun 16, 2012 at 9:50 AM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>wrote:

Then in HEAD:
> EXPLAIN ANALYSE UPDATE fk_table SET b=b+1, c=c+1, d=d+1;
>
> QUERY PLAN
>
> -----------------------------------------------------------------------------------------------------------------------
> Update on fk_table (cost=0.00..2300.00 rows=100000 width=26) (actual
> time=1390.037..1390.037 rows=0 loops=1)
> -> Seq Scan on fk_table (cost=0.00..2300.00 rows=100000 width=26)
> (actual time=0.010..60.841 rows=100000 loops=1)
> Trigger for constraint fk_table_e_fkey: time=210.184 calls=90000
> Total runtime: 1607.626 ms
> (4 rows)
>
> So the RI trigger is fired 90000 times, for the unchanged NULL FK rows.
>
> With this patch, the RI trigger is not fired at all:
> EXPLAIN ANALYSE UPDATE fk_table SET b=b+1, c=c+1, d=d+1;
>
> QUERY PLAN
>
> -----------------------------------------------------------------------------------------------------------------------
> Update on fk_table (cost=0.00..2300.00 rows=100000 width=26) (actual
> time=1489.640..1489.640 rows=0 loops=1)
> -> Seq Scan on fk_table (cost=0.00..2300.00 rows=100000 width=26)
> (actual time=0.010..66.328 rows=100000 loops=1)
> Total runtime: 1489.679 ms
> (3 rows)
>
>
> Similarly, if I update the FK column in HEAD the RI trigger is fired
> for every row:
> EXPLAIN ANALYSE UPDATE fk_table SET e=e-1;
>
> QUERY PLAN
>
> -----------------------------------------------------------------------------------------------------------------------
> Update on fk_table (cost=0.00..1800.00 rows=100000 width=26) (actual
> time=1565.148..1565.148 rows=0 loops=1)
> -> Seq Scan on fk_table (cost=0.00..1800.00 rows=100000 width=26)
> (actual time=0.010..42.725 rows=100000 loops=1)
> Trigger for constraint fk_table_e_fkey: time=705.962 calls=100000
> Total runtime: 2279.408 ms
> (4 rows)
>
> whereas with this patch it is only fired for the non-NULL FK rows that
> are changing:
> EXPLAIN ANALYSE UPDATE fk_table SET e=e-1;
>
> QUERY PLAN
>
> -----------------------------------------------------------------------------------------------------------------------
> Update on fk_table (cost=0.00..5393.45 rows=299636 width=26) (actual
> time=1962.755..1962.755 rows=0 loops=1)
> -> Seq Scan on fk_table (cost=0.00..5393.45 rows=299636 width=26)
> (actual time=0.023..52.850 rows=100000 loops=1)
> Trigger for constraint fk_table_e_fkey: time=257.845 calls=10000
> Total runtime: 2221.912 ms
> (4 rows)
>

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'.

Even the time consumed by Seq scans is higher in patched version, so I
think the patch's affect on performance needs to be evaluated.

Best regards,
--
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company