Message-ID: <5055D7FF.2070908@ringerc.id.au>
Date: Sun, 16 Sep 2012 21:45:35 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120717 Thunderbird/14.0
MIME-Version: 1.0
To: "McKinzie, Alan (Alan)" <alanmck@avaya.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Are there known performance issues with defining all
	Foreign Keys as deferrable initially immediate
References: 
 <E151F470685C15418AEA609E825A171022EC8EA823@DC-US1MBEX4.global.avaya.com>
In-Reply-To: 
 <E151F470685C15418AEA609E825A171022EC8EA823@DC-US1MBEX4.global.avaya.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 09/14/2012 11:56 PM, McKinzie, Alan (Alan) wrote:

> My underlying question/concern is "will this change have any adverse
> affects (on performance) during normal operations when the foreign keys
> are set to deferrable initially immediate" .vs. the foreign keys being
> defined as NOT DEFERRABLE.

AFAIK in PostgreSQL DEFERRABLE INITIALLY IMMEDIATE is different to NOT 
DEFERRABLE.

DEFERRABLE INITIALLY IMMEDIATE is executed at the end of the statement, 
while NOT DEFERRABLE is executed as soon as it arises.

http://www.postgresql.org/docs/current/static/sql-set-constraints.html

http://stackoverflow.com/questions/10032272/constraint-defined-deferrable-initially-immediate-is-still-deferred

Again from memory there's a performance cost to deferring constraint 
checks to the end of the statement rather than doing them as soon as 
they arise, so NOT DEFERRED can potentially perform better or at least 
not hit limits that DEFERRABLE INITIALLY DEFERRED might hit in Pg.

This seems under-documented and I haven't found much good info on it, so 
the best thing to do is test it.

--
Craig Ringer