Thread: Optimization on UPDATEs and FOREIGN KEYs...

Does the optimizer optimize away the foreign key checks on a primary
key if its value doesn't change, even though it's had a value assigned
to it?  Here's the example:

CREATE TABLE t1 (
   i INT PRIMARY,
   j TEXT
);

CREATE TABLE t2 (
   i INT,
   k INT8,
   FOREIGN KEY(i) REFERENCES t1(i)
);

INSERT INTO t1 (i,j) VALUES (1,'foo');
UPDATE t1 SET i = 1 WHERE i = 1;

Does the optimizer optimize away the foreign key checks since t1.i's
value hasn't changed (OLD.i = NEW.i)?  I couldn't find anything that
suggested that this statement became a no-op internally.  In the
EXPLAIN output, it's clear that the backend is searching through t1.i's
index, but I don't know if the UPDATE is scanning through t2 looking
for key violations.  Since foreign key constraints don't appear in the
output of EXPLAIN VERBOSE (or maybe it does and it's already doing this
optimization and is trimming it before the VERBOSE output is produced)
and grep(1) wasn't of much use, I figured I'd ask.  TIA.  -sc

--
Sean Chittenden

Sean Chittenden <sean@chittenden.org> writes:
> Does the optimizer optimize away the foreign key checks on a primary
> key if its value doesn't change, even though it's had a value assigned
> to it?

The optimizer has nothing to do with this, but in most cases the foreign
key triggers themselves will short-circuit when the key value is found
to be the same as before.

            regards, tom lane