From: Josh Berkus <josh@agliodbs.com>
To: Beth Gatewood <beth@vizxlabs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: make a unique index for foreign keys?
Date: Wed, 12 Jun 2002 16:34:30 -0700
Message-ID: <200206121634.30493.josh@agliodbs.com> (raw)
In-Reply-To: <005201c211b7$f4f72480$0200000a@bethvizx>
References: <005201c211b7$f4f72480$0200000a@bethvizx>
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
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: <200206121634.30493.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