On Sat, Jun 16, 2012 at 9:50 AM, Dean Rasheed <dean.a.rash...@gmail.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
