From: Josh Berkus <josh@agliodbs.com>
To: Beth Gatewood <beth@vizxlabs.com>
To: pgsql-sql@postgresql.org
Subject: Re: make a unique index for foreign keys?
Date: Tue, 11 Jun 2002 17:24:34 -0700
Message-ID: <200206111724.34953.josh@agliodbs.com> (raw)
In-Reply-To: <004f01c211a2$8832f410$0200000a@bethvizx>
References: <004f01c211a2$8832f410$0200000a@bethvizx>
Beth,
> 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).
Thank you! Can you send that article back to me, privately? I'll turn it
into an Adventures in PostgreSQL column.
> 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?
Actually, I can only think of one case where you would *not* need and index on
a foriegn key: Where the list of referenced values is very small (like 2-5
choices) and the referenceing data table is very large (thousands to
millions). In this case (and only in this case) Seq Scans are the fastest
way to find referenced values and as such an index is superfluous.
For example, say you had a table containing every ice cream retailer in the
US:
retailers (
id int primary key
name varchar
address text
retail_type int references retail_types(type_id)
);
... and this table had 280,000 records, but you have only 3 retail types:
1 Ice Cream Parlor
2 Supermarket
3 Corner Market
In this case, the index on retail_type would almost never be used because any
selection of retail type would involve 1/3 of the retailers table and thus a
Seq Scan is faster.
However, in every other case, you very definitely want every Foriegn Key
column indexed. In fact, in the above case, you should probably index it
anyway because you might change the data population someday and the overhead
of a single INT index is quite small.
--
-Josh Berkus
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: josh@agliodbs.com, beth@vizxlabs.com
Subject: Re: make a unique index for foreign keys?
In-Reply-To: <200206111724.34953.josh@agliodbs.com>
* 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