Re: REVIEW: Optimize referential integrity checks (todo item) - Mailing list pgsql-hackers

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)