By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,767 Members | 1,118 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,767 IT Pros & Developers. It's quick & easy.

Optimization on UPDATEs and FOREIGN KEYs...

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
1 Reply

P: n/a
Sean Chittenden <se**@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.