From: "Beth Gatewood" <beth@vizxlabs.com>
To: <pgsql-sql@postgresql.org>
Subject: make a unique index for foreign keys?
Date: Tue, 11 Jun 2002 16:48:47 -0700
Message-ID: <004f01c211a2$8832f410$0200000a@bethvizx>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Importance: Normal

All-

I am inheriting a MySQL database and they have created indices for all
columns that should be foreign keys (since the version of MySQL that is used
doesn't support FKs).  Yes-I know-that is why I am trying to migrate to
pgsql.

This has made me start wondering about the feasibility of always creating
indices on foreign keys.  I was taught it wasn't necessary in Oracle (I
don't know why).

I have started wondering if this is a good idea....in most cases they would
have to be non-unique indices...but would this help to speed up queries with
a join?

So searching the archives I found a really nice description from Josh Berkus
about rules for creating indices
(http://groups.google.com/groups?hl=en&lr=&selm=web-115943%40davinci.ethosme
dia.com&rnum=2).  One of his rules was to set unique indices on all unique
columns.  In my case most of the time FK would not be unique....but there
are a few cases where they are.  From the CREATE TABLE idocs for 7.2 it
suggests that an index on a foreign key will help for updates (" If primary
key column is updated frequently, it may be wise to add an index to the
REFERENCES column so that NO ACTION and CASCADE actions associated with the
REFERENCES column can be more efficiently performed").
Can anyone give me an explanation of why or why not I would want an index on
the foreign key?
TIA-
Beth