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/

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

On Wed, Jan 24, 2007 at 20:14:07 -0800,
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.

Not exactly. Primary keys already result in an index being created to enforce
uniqueness, so the manually created indexes are redundant.

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);

For this last case, you most likely want to declare either account_id, stuff_id
or stuff_id, account_id as a primary key. You may want to create an index
just on the second column of the primary key, depending on your usage pattern.
You almost certainly wouldn't want to create an index on the first column
of the primary key.