Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "Beth Gatewood" <beth@vizxlabs.com>, <pgsql-sql@postgresql.org>
Subject: Re: make a unique index for foreign keys?
Date: Tue, 11 Jun 2002 17:24:34 -0700
References: <004f01c211a2$8832f410$0200000a@bethvizx>
In-Reply-To: <004f01c211a2$8832f410$0200000a@bethvizx>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200206111724.34953.josh@agliodbs.com>

Beth,

> So searching the archives I found a really nice description from Josh Ber=
kus
> about rules for creating indices
> (http://groups.google.com/groups?hl=3Den&lr=3D&selm=3Dweb-115943%40davinc=
i.ethosme
> dia.com&rnum=3D2).

Thank you!  Can you send that article back to me, privately?   I'll turn it=
=20
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 prima=
ry
> 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 t=
he
> 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=20
a foriegn key:  Where the list of referenced values is very small (like 2-5=
=20
choices) and the referenceing data table is very large (thousands to=20
millions).  In this case (and only in this case) Seq Scans are the fastest=
=20
way to find referenced values and as such an index is superfluous.=20

For example, say you had a table containing every ice cream retailer in the=
=20
US:
retailers (
	id int primary key
	name varchar=20
	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 a=
ny=20
selection of retail type would involve 1/3 of the retailers table and thus =
a=20
Seq Scan is faster.=20=20

However, in every other case, you very definitely want every Foriegn Key=20
column indexed.   In fact, in the above case, you should probably index it=
=20
anyway because you might change the data population someday and the overhea=
d=20
of a single INT index is quite small.

--=20
-Josh Berkus