From: Craig Ringer <craig@postnewspapers.com.au>
To: Andrew Hall <andrewah@hotmail.com>
Cc: PostgreSQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: Foreign key - Indexing & Deadlocking.
Date: Wed, 30 Dec 2009 09:18:42 +0800
Message-ID: <4B3AAA72.5030302@postnewspapers.com.au> (raw)
In-Reply-To: <COL122-W493DEB26AA345A7DCE0058CD7A0@phx.gbl>
References: <COL122-W493DEB26AA345A7DCE0058CD7A0@phx.gbl>
On 30/12/2009 6:59 AM, Andrew Hall wrote:
> Hi,
>
> I'm porting some Oracle software to PostgreSQL, & have a question about
> best practices with foreign keys.
>
> In the Oracle environment, you're basically obliged to index all foreign
> keys (more specifically foreign key columns should appear on the leading
> edge of an index) due to the way that it manages locks.
>
> Failure to index foreign keys in this way in Oracle often causes
> deadlock situations.
>
> Although I could no find a mention of anything similar in the postgreSQL
> documentation, I just wanted to check whether or not the same was true
> of PostgreSQL to be on the safe side.
It is not the case. It's quite common in PostgreSQL to leave foreign
keys without indexes. Doing so does mean that referential integrity
checks being made on DELETE from the tablew with the referenced primary
key will require a sequential scan of the referencing table(s) to check
integrity, though.
PostgreSQL isn't smart enough (yet) to group up such checks into a
single pass down the target table. So if you delete 100 rows from a
table in a query, and that table is referenced by another table via a
foreign key without an index, the referencing table will be sequentially
scanned 100 times.
Needless to say, you want to add indexes to your foreign keys if you
expect to delete from the parent, or (for some reason) update the
primary key value for rows in the parent table.
--
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-sql@postgresql.org
Cc: craig@postnewspapers.com.au, andrewah@hotmail.com
Subject: Re: Foreign key - Indexing & Deadlocking.
In-Reply-To: <4B3AAA72.5030302@postnewspapers.com.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