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> (raw)
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
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: beth@vizxlabs.com
Subject: Re: make a unique index for foreign keys?
In-Reply-To: <004f01c211a2$8832f410$0200000a@bethvizx>
* 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