PostgreSQL – UPDATE Column Without FK Triggers Constraint Check

performancepostgresqlpostgresql-performance

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

Best Answer

Tom Lane answered on the mailing list

This happens because an UDPATE invalidates the foreign key triggers for a previous INSERT in the same transaction, so postgres fires them when there is an old tuple version in the same transaction.
It doesn't have the data to know that the previous change on that row was an update without fk changes.