From: Tom Lane <tgl@sss.pgh.pa.us>
To: Craig Ringer <ringerc@ringerc.id.au>
cc: "McKinzie, Alan (Alan)" <alanmck@avaya.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Are there known performance issues with defining all Foreign Keys
	as deferrable initially immediate
In-reply-to: <5055DE3D.4040603@ringerc.id.au>
References: 
 <E151F470685C15418AEA609E825A171022EC8EA823@DC-US1MBEX4.global.avaya.com>
	<5055D7FF.2070908@ringerc.id.au> <5055DE3D.4040603@ringerc.id.au>
Comments: In-reply-to Craig Ringer <ringerc@ringerc.id.au>
	message dated "Sun, 16 Sep 2012 22:12:13 +0800"
Date: Sun, 16 Sep 2012 11:37:15 -0400
Message-ID: <11998.1347809835@sss.pgh.pa.us>

Craig Ringer <ringerc@ringerc.id.au> writes:
> Found it, it's in the NOTES for CREATE TABLE.
> http://www.postgresql.org/docs/current/static/sql-createtable.html:

> When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL 
> checks for uniqueness immediately whenever a row is inserted or 
> modified. The SQL standard says that uniqueness should be enforced only 
> at the end of the statement; this makes a difference when, for example, 
> a single command updates multiple key values. To obtain 
> standard-compliant behavior, declare the constraint as DEFERRABLE but 
> not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be 
> significantly slower than immediate uniqueness checking.

Note that that is addressing uniqueness constraints, and *only*
uniqueness constraints.  Foreign key constraints are implemented
differently.  There is no equivalent to an immediate check of a foreign
key constraint --- it's checked either at end of statement or end of
transaction, depending on the DEFERRED property.  So there's really no
performance difference for FKs, unless you let a large number of pending
checks accumulate over multiple commands within a transaction.

			regards, tom lane