I'm using PostgreSQL 9.6 and i have noticed that when i execute an UPDATE on a single column without foreign key it will fire all triggers for other columns that have a value and a foreign key.
I see this thanks to EXPLAIN ANALYZE that gives me "Trigger for constraint" for each one of those other columns.
With massive updates those triggers takes 50% of the execution time
CREATE TABLE parent ( id INTEGER, PRIMARY KEY(id) ); CREATE TABLE child ( id INTEGER, is_enabled BOOLEAN DEFAULT FALSE, parent_id INTEGER references parent(id), PRIMARY KEY(id) ); INSERT INTO parent (id) VALUES (1); INSERT INTO child (id, is_enabled, parent_id) VALUES (1, FALSE, 1); UPDATE child SET is_enabled = FALSE WHERE id = 1; EXPLAIN ANALYZE UPDATE child SET is_enabled = TRUE WHERE id = 1;
Update on child (cost=0.16..8.17 rows=1 width=15) (actual time=0.017..0.017 rows=0 loops=1) -> Index Scan using child_pkey on child (cost=0.16..8.17 rows=1 width=15) (actual time=0.004..0.005 rows=1 loops=1) Index Cond: (id = 1) Planning time: 0.033 ms Trigger for constraint child_parent_id_fkey: time=0.168 calls=1 Execution time: 0.199 ms
Notice Trigger for constraint child_parent_id_fkey even if i haven't changed parent_id
It happens when issuing 2 update statements, same transaction, that covers the same row even if nothing is changed and no FK column is used.
Here is a dbfiddle by joanolo
Is this an expected behavior? Should i fill a bug report?
Now i'm trying to avoid to touch same rows twice in the same transaction