Optimization on UPDATEs and FOREIGN KEYs...

[Sean Chittenden <sean@chittenden.org>]

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


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

              http://archives.postgresql.org