From: "Beth Gatewood" <beth@vizxlabs.com>
To: <josh@agliodbs.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: make a unique index for foreign keys?
Date: Wed, 12 Jun 2002 16:44:20 -0700
Message-ID: <003301c2126b$135651e0$6501a8c0@bethvizx>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
In-Reply-To: <200206121634.30493.josh@agliodbs.com>
Importance: Normal

Hey Josh-

Thanks for the response and guidelines.  I think that I figured out
something concerning indices and foreign keys...It seems like a non-unique
index is automatically getting created for my foreign keys.  Does this make
sense?

I got this hunch from this function I found while peeking through the
archives today...
pg_get_indexdef(oid);  // oid should be the oid of the index you are
investigating

Using this I discovered some indices on foreign keys that I didn't
explicitly create.

Is this true?

Thanks-Beth


-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Wednesday, June 12, 2002 4:35 PM
To: Beth Gatewood
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] make a unique index for foreign keys?


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
example, 3 possible values in a table of 10,000 records -- that indexes are
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* of
index is a more complex topic.   But as a reference, about 50% of the
columns
in most of my databases are indexed.

--
-Josh Berkus