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
EDIT:
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;
OUTPUT:
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
EDIT 2:
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