From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> |
Cc: | "McKinzie, Alan (Alan)" <alanmck(at)avaya(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Are there known performance issues with defining all Foreign Keys as deferrable initially immediate |
Date: | 2012-09-16 15:37:15 |
Message-ID: | 11998.1347809835@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Craig Ringer <ringerc(at)ringerc(dot)id(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2012-09-16 21:39:34 | Re: [PERFORM] Planner selects different execution plans depending on limit |
Previous Message | Craig Ringer | 2012-09-16 14:12:13 | Re: Are there known performance issues with defining all Foreign Keys as deferrable initially immediate |