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