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>
Subject: Re: make a unique index for foreign keys?
Date: Wed, 12 Jun 2002 16:34:30 -0700
References: <005201c211b7$f4f72480$0200000a@bethvizx>
In-Reply-To: <005201c211b7$f4f72480$0200000a@bethvizx>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200206121634.30493.josh@agliodbs.com>


Beth,

> So one quick question while I have you ;-) So those indexes would be
> non-unique indexes....I thought that I read somewhere that one shouldn't
> bother with those types of indexes (was that a total mis-statement...it
> kinda seems like it would be)

That's incorrect.  It's only in the extreme case of non-uniqueness -- for=
=20
example, 3 possible values in a table of 10,000 records -- that indexes are=
=20
useless.  In all other cases, indexes are a Good Thing.

Questions for "Shall I create an index?"
1. Will the column be part of JOIN statementents in common queries?
2. Will the column often be used in a WHERE clause?  If so, how?
3. Will the column be used in an ORDER BY clause?

If the answer to any of the above is yes, you want an index.  What *kind* o=
f=20
index is a more complex topic.   But as a reference, about 50% of the colum=
ns=20
in most of my databases are indexed.

--=20
-Josh Berkus