Re: indexing primary and foreign keys w/lookup table

On 1/25/07, Neal Clark <nclark(at)securescience(dot)net> wrote:
> I was wondering...I currently have indexes on the primary key id and
> foreign key id's for tables that resemble the following. Is this a
> good idea/when would it benefit me? I don't want waste a lot of
> unnecessary space on indexes.

> CREATE TABLE stuff (
> id BIGSERIAL PRIMARY KEY,
> stuff TEXT
> );
> CREATE INDEX stuff_id ON stuff(id);

postgresql will create an index for you if you have a primary key on
the table...so you don't have to create one yourself.

> CREATE TABLE stuff_by_account (
> account_id BIGINT REFERENCES accounts(id),
> stuff_id BIGINT REFERENCES stuff(id)
> );

I this is wrong. as you have laid it out, the create way to create
this table would be

CREATE TABLE stuff_by_account
(
account_id BIGINT REFERENCES accounts(id),
stuff_id BIGINT REFERENCES stuff(id),
primary key(account_id, stuff_id)
);

this will create a key (and thus an index), on account_id, stuff_id.
This will speed up lookups to account and greatly speed lookups to
account and stuff at the same time. However, you may want to create
in index on stuff alone.

> do I need any/all of these indexes for my lookup table to work well?
> I am thinking I can get rid of stuff_id and accounts_id. Thoughts?

Try giving natural keys a whirl. This means not automatically making
a primary serial key for every table and trying to make primary keys
from the non autogenerated keys in the table.

merlin