I have an update statement that affects every row in a given table.
For that table it changes the value in a single column, which itself
has a foreign key constraint. The table has an additional 9 foreign
keys, some of which reference large tables.
My expectation would be that only the changed column would be checked
against the foreign key of interest, instead I find that all the
foreign keys are checked when this statement is executed.
I decided to create a simple test case to demonstrate this behaviour,
but what I found was strange. The first time I created the test cases
the behaviour matches my experience but the second time I created it
the behaviour was then as I would have expected. This is the result I
am experiencing with the unnecessary foreign key verification:
testdb=# explain analyze update t1 set B = 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..25.10 rows=1510 width=22) (actual
time=0.026..0.029 rows=1 loops=1)
Trigger for constraint fk1: time=0.111 calls=1
Trigger for constraint fk2: time=0.014 calls=1
Total runtime: 0.259 ms
(4 rows)
Only fk1's column is being updated, not fk2's.
Below is both sessions. Any feedback on this and how to avoid it is
appreciated as well as whether the developers would consider this a
bug, I am inclined to believe so as it hurts performance.
Thanks,
-J
*************************************
testdb=# create table t1 (A BIGINT, B BIGINT);
CREATE TABLE
testdb=# create table t2 (B BIGINT PRIMARY KEY);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"t2_pkey" for table "t2"
CREATE TABLE
testdb=# alter table t1 add constraint fk1 foreign key (B) references t2 (B);
ALTER TABLE
testdb=# explain analyze insert into t2 values (1);
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.008..0.010
rows=1 loops=1)
Total runtime: 45.508 ms
(2 rows)
testdb=# explain analyze insert into t1 values (1, 1);
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.008..0.010
rows=1 loops=1)
Trigger for constraint fk1: time=0.256 calls=1
Total runtime: 0.345 ms
(3 rows)
testdb=# explain analyze update t1 set A = 2;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..27.70 rows=1770 width=14) (actual
time=0.019..0.022 rows=1 loops=1)
Total runtime: 0.125 ms
(2 rows)
testdb=# explain analyze update t1 set B = 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..27.70 rows=1770 width=14) (actual
time=0.021..0.024 rows=1 loops=1)
Total runtime: 0.115 ms
(2 rows)
testdb=# explain analyze update t1 set B = 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..27.70 rows=1770 width=14) (actual
time=0.021..0.024 rows=1 loops=1)
Total runtime: 0.113 ms
(2 rows)
testdb=# explain analyze insert into t2 values (2);
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.008..0.010
rows=1 loops=1)
Total runtime: 0.120 ms
(2 rows)
testdb=# explain analyze update t1 set B = 2;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..27.70 rows=1770 width=14) (actual
time=0.020..0.024 rows=1 loops=1)
Trigger for constraint fk1: time=0.112 calls=1
Total runtime: 0.233 ms
(3 rows)
testdb=# explain analyze update t1 set A = 99;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..27.70 rows=1770 width=14) (actual
time=0.021..0.025 rows=1 loops=1)
Total runtime: 0.117 ms
(2 rows)
testdb=# alter table t1 add b2 bigint;
ALTER TABLE
testdb=# alter table t1 add constraint fk2 foreign key (B2) references t2 (B);
ALTER TABLE
testdb=# explain analyze update t1 set B = 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..25.10 rows=1510 width=22) (actual
time=0.026..0.029 rows=1 loops=1)
Trigger for constraint fk1: time=0.111 calls=1
Trigger for constraint fk2: time=0.014 calls=1
Total runtime: 0.259 ms
(4 rows)
testdb=#
testdb=#
testdb=#
testdb=#
testdb=#
testdb=# drop table t1;
DROP TABLE
testdb=# drop table t2;
DROP TABLE
testdb=# create table t1 (A BIGINT, B BIGINT, B2 BIGINT);
CREATE TABLE
testdb=# create table t2 (B BIGINT PRIMARY KEY);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"t2_pkey" for table "t2"
CREATE TABLE
testdb=# alter table t1 add constraint fk1 foreign key (B) references t2 (B);
ALTER TABLE
testdb=# alter table t1 add constraint fk2 foreign key (B2) references t2 (B);
ALTER TABLE
testdb=# insert into t2 values (1), (2);
INSERT 0 2
testdb=# insert into t1 values (1, 1, 1);
INSERT 0 1
testdb=# explain analyze update t1 set A = 2;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..25.10 rows=1510 width=22) (actual
time=0.017..0.020 rows=1 loops=1)
Total runtime: 0.118 ms
(2 rows)
testdb=# explain analyze update t1 set B = 2;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..25.10 rows=1510 width=22) (actual
time=0.021..0.024 rows=1 loops=1)
Trigger for constraint fk1: time=0.111 calls=1
Total runtime: 0.237 ms
(3 rows)
testdb=# explain analyze update t1 set B2 = 2;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..25.10 rows=1510 width=22) (actual
time=0.023..0.026 rows=1 loops=1)
Trigger for constraint fk2: time=0.117 calls=1
Total runtime: 0.252 ms
(3 rows)
testdb=# explain analyze update t1 set B2 = 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..25.10 rows=1510 width=22) (actual
time=0.021..0.024 rows=1 loops=1)
Trigger for constraint fk2: time=0.112 calls=1
Total runtime: 0.244 ms
(3 rows)
testdb=# explain analyze update t1 set B = 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..25.10 rows=1510 width=22) (actual
time=0.021..0.024 rows=1 loops=1)
Trigger for constraint fk1: time=0.111 calls=1
Total runtime: 0.237 ms
(3 rows)
testdb=# explain analyze update t1 set B = 1, b2 = 2;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..25.10 rows=1510 width=14) (actual
time=0.021..0.024 rows=1 loops=1)
Trigger for constraint fk2: time=0.111 calls=1
Total runtime: 0.236 ms
(3 rows)
testdb=# explain analyze update t1 set B = 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..25.10 rows=1510 width=22) (actual
time=0.022..0.025 rows=1 loops=1)
Trigger for constraint fk1: time=0.112 calls=1
Total runtime: 0.237 ms
(3 rows)
