Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 8086262DBD7
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 29 Dec 2009 21:19:03 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 34841-09 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 30 Dec 2009 01:18:52 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.postnewspapers.com.au
	(202-89-185-120.static.dsl.amnet.net.au [202.89.185.120])
	by mail.postgresql.org (Postfix) with ESMTP id 7742D62DBD1
	for <pgsql-sql@postgresql.org>; Tue, 29 Dec 2009 21:18:52 -0400 (AST)
Received: from localhost (access [127.0.0.1])
	by mail.postnewspapers.com.au (Postfix) with ESMTP id 81AB71E043A;
	Wed, 30 Dec 2009 09:18:47 +0800 (WST)
X-Virus-Scanned: Debian amavisd-new at postnewspapers.com.au
Received: from mail.postnewspapers.com.au ([127.0.0.1])
	by localhost (access.postnewspapers.com.au [127.0.0.1]) (amavisd-new,
	port 10024)
	with LMTP id s3NLszyQcX5Z; Wed, 30 Dec 2009 09:18:45 +0800 (WST)
Received: from [10.1.1.6] (124-169-133-89.dyn.iinet.net.au [124.169.133.89])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(Client CN "Craig Ringer",
	Issuer "POST Certificate Authority" (verified OK))
	by mail.postnewspapers.com.au (Postfix) with ESMTPSA id 4FFA81E0439;
	Wed, 30 Dec 2009 09:18:45 +0800 (WST)
Message-ID: <4B3AAA72.5030302@postnewspapers.com.au>
Date: Wed, 30 Dec 2009 09:18:42 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB;
	rv:1.9.1.5) Gecko/20091204 Thunderbird/3.0
MIME-Version: 1.0
To: Andrew Hall <andrewah@hotmail.com>
CC: PostgreSQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: Foreign key - Indexing & Deadlocking.
References: <COL122-W493DEB26AA345A7DCE0058CD7A0@phx.gbl>
In-Reply-To: <COL122-W493DEB26AA345A7DCE0058CD7A0@phx.gbl>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200912/87
X-Sequence-Number: 33525

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