pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAre there known performance issues with defining all Foreign Keys as deferrable initially immediate
6+ messages / 3 participants
[nested] [flat]
* Are there known performance issues with defining all Foreign Keys as deferrable initially immediate
@ 2012-09-14 15:56 McKinzie, Alan (Alan) <alanmck@avaya.com>
0 siblings, 1 reply; 6+ messages in thread
From: McKinzie, Alan (Alan) @ 2012-09-14 15:56 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Are there known performance issues with defining all Foreign Keys as deferrable initially immediate
@ 2012-09-16 13:45 Craig Ringer <ringerc@ringerc.id.au>
parent: McKinzie, Alan (Alan) <alanmck@avaya.com>
0 siblings, 1 reply; 6+ messages in thread
From: Craig Ringer @ 2012-09-16 13:45 UTC (permalink / raw)
To: McKinzie, Alan (Alan) <alanmck@avaya.com>; +Cc: pgsql-performance
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-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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Are there known performance issues with defining all Foreign Keys as deferrable initially immediate
@ 2012-09-16 14:12 Craig Ringer <ringerc@ringerc.id.au>
parent: Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 1 reply; 6+ messages in thread
From: Craig Ringer @ 2012-09-16 14:12 UTC (permalink / raw)
To: McKinzie, Alan (Alan) <alanmck@avaya.com>; +Cc: pgsql-performance
On 09/16/2012 09:45 PM, Craig Ringer wrote:
> This seems under-documented and I haven't found much good info on it,
> so the best thing to do is test it.
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.
--
Craig Ringer
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Are there known performance issues with defining all Foreign Keys as deferrable initially immediate
@ 2012-09-16 15:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2012-09-16 15:37 UTC (permalink / raw)
To: Craig Ringer <ringerc@ringerc.id.au>; +Cc: McKinzie, Alan (Alan) <alanmck@avaya.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Are there known performance issues with defining all Foreign Keys as deferrable initially immediate
@ 2012-09-17 02:59 Craig Ringer <ringerc@ringerc.id.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Craig Ringer @ 2012-09-17 02:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: McKinzie, Alan (Alan) <alanmck@avaya.com>; pgsql-performance
On 09/16/2012 11:37 PM, Tom Lane wrote:
> 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.
Ah, thanks. I missed that detail.
--
Craig Ringer
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Are there known performance issues with defining all Foreign Keys as deferrable initially immediate
@ 2012-09-17 13:38 McKinzie, Alan (Alan) <alanmck@avaya.com>
parent: Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 0 replies; 6+ messages in thread
From: McKinzie, Alan (Alan) @ 2012-09-17 13:38 UTC (permalink / raw)
To: Craig Ringer <ringerc@ringerc.id.au>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Thanks for the information guys. And Yes, I am only updating the Foreign Key definitions to be deferrable. I am not modifying the Unique/Primary Key definitions.
Thanks again,
Alan
-----Original Message-----
From: Craig Ringer [mailto:ringerc@ringerc.id.au]
Sent: Sunday, September 16, 2012 9:59 PM
To: Tom Lane
Cc: McKinzie, Alan (Alan); pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Are there known performance issues with defining all Foreign Keys as deferrable initially immediate
On 09/16/2012 11:37 PM, Tom Lane wrote:
> 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.
Ah, thanks. I missed that detail.
--
Craig Ringer
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-09-17 13:38 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-09-14 15:56 Are there known performance issues with defining all Foreign Keys as deferrable initially immediate McKinzie, Alan (Alan) <alanmck@avaya.com>
2012-09-16 13:45 ` Craig Ringer <ringerc@ringerc.id.au>
2012-09-16 14:12 ` Craig Ringer <ringerc@ringerc.id.au>
2012-09-16 15:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-09-17 02:59 ` Craig Ringer <ringerc@ringerc.id.au>
2012-09-17 13:38 ` McKinzie, Alan (Alan) <alanmck@avaya.com>
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