Re: Are there known performance issues with defining all Foreign Keys as deferrable initially immediate

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