From: Andres Freund <andres@anarazel.de>
To: pgsql-performance@postgresql.org
Subject: Re: Deferred constraint and delete performance
Date: Wed, 10 Feb 2010 18:36:23 +0100
User-Agent: KMail/1.13.0 (Linux/2.6.33-rc5-andres-00243-g6ea299e; KDE/4.3.98;
	x86_64; ; )
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
 Franck Routier <franck.routier@axege.com>
References: <1265799331.28404.14.camel@franck-laptop>
	<16009.1265813800@sss.pgh.pa.us>
In-Reply-To: <16009.1265813800@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <201002101836.25964.andres@anarazel.de>

On Wednesday 10 February 2010 15:56:40 Tom Lane wrote:
> Franck Routier <franck.routier@axege.com> writes:
> > I am wondering if deferring foreign key constraints (instead of
> > disableing them) would increase performance, compared to non deferred
> > constraints
> 
> No, it wouldn't make any noticeable difference AFAICS.  It would
> postpone the work from end-of-statement to end-of-transaction,
> but not make the work happen any more (or less) efficiently.
It could make a difference if the transaction is rather long and updates the 
same row repeatedly because of better cache usage. But I admit thats a bit of 
a constructed scenario (where one likely would get into trigger-queue size 
problems as well)

Andres