From: Craig Ringer <ringerc@ringerc.id.au>
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
Date: Sun, 16 Sep 2012 21:45:35 +0800
Message-ID: <5055D7FF.2070908@ringerc.id.au> (raw)
In-Reply-To: <E151F470685C15418AEA609E825A171022EC8EA823@DC-US1MBEX4.global.avaya.com>
References: <E151F470685C15418AEA609E825A171022EC8EA823@DC-US1MBEX4.global.avaya.com>
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.htmlhttp://stackoverflow.com/questions/10032272/constraint-defined-deferrable-initially-immediate-is-sti...
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ringerc@ringerc.id.au, alanmck@avaya.com
Subject: Re: Are there known performance issues with defining all Foreign Keys as deferrable initially immediate
In-Reply-To: <5055D7FF.2070908@ringerc.id.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox