Skip site navigation (1) Skip section navigation (2)

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

From: "McKinzie, Alan (Alan)" <alanmck(at)avaya(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Are there known performance issues with defining all Foreign Keys as deferrable initially immediate
Date: 2012-09-14 15:56:04
Message-ID: E151F470685C15418AEA609E825A171022EC8EA823@DC-US1MBEX4.global.avaya.com (view raw or flat)
Thread:
Lists: pgsql-performance
I am looking at changing all of the foreign key definitions to be deferrable (initially immediate).  Then during a few scenarios performed by the application, set all foreign key constraints to be deferred (initially deferred) for that given transaction.

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.

I have read that there can be a difference in behavior/performance when a Primary Key/Unique Key is changed to deferred, due to assumptions the optimizer can or cannot make regarding whether the associated index is unique.  But I have not found any negatives in regard to changing foreign key definitions to be deferrable.

Thanks,
Alan


Responses

pgsql-performance by date

Next:From: S├ębastien LorionDate: 2012-09-14 20:11:05
Subject: Setting autovacuum_vacuum_scale_factor to 0 a good idea ?
Previous:From: Nikolay UlyanitskyDate: 2012-09-14 15:35:20
Subject: Re: 20% performance drop on PostgreSQL 9.2 from kernel 3.5.3 to 3.6-rc5 on AMD chipsets

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group