Re: indexing primary and foreign keys w/lookup table

On 1/25/07, Neal Clark <nclark@securescience.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