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> (raw)
In-Reply-To: <200206121634.30493.josh@agliodbs.com>
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
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: beth@vizxlabs.com, josh@agliodbs.com
Subject: Re: make a unique index for foreign keys?
In-Reply-To: <003301c2126b$135651e0$6501a8c0@bethvizx>
* 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