Optimization on UPDATEs and FOREIGN KEYs...

Поиск
Список
Период
Сортировка
От Sean Chittenden
Тема Optimization on UPDATEs and FOREIGN KEYs...
Дата
Msg-id 32BA01DB-8490-11D8-B7C6-000A95C705DC@chittenden.org
обсуждение исходный текст
Ответы Re: Optimization on UPDATEs and FOREIGN KEYs...  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: "Durai "
Дата:
Сообщение: PostgreSQL 7.4.2 warnings in HPUX IPF: LOG: could not resolve "localhost": host nor service provided
Следующее
От: Adam Witney
Дата:
Сообщение: Re: row-level security model