Re: indexing primary and foreign keys w/lookup table

> CREATE TABLE stuff_by_account (
> account_id BIGINT REFERENCES accounts(id),
> stuff_id BIGINT REFERENCES stuff(id)
> );
> CREATE INDEX stuff_by_account_account_id ON stuff_by_account(account_id);
> CREATE INDEX stuff_by_account_stuff_id ON stuff_by_account(stuff_id);
>
> 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?

You should have indexes on the fields used in joins.

So if you join stuff_by_account to accounts using account_id, make sure
there is an index on both sides of that (primary key already has one but
the lookup table needs one too).

Foreign keys need them because when a row gets added/removed, the index
is used to quickly make sure the data is in the external table.

--
Postgresql & php tutorials
http://www.designmagick.com/