Thread: indexing primary and foreign keys w/lookup table

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi All.

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

CREATE TABLE accounts (
    id        BIGSERIAL PRIMARY KEY,
    name        TEXT,
    email        TEXT,
);
CREATE INDEX accounts_id ON accounts(id);

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?

- -Neal
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (Darwin)

iD8DBQFFuC6POUuHw4wCzDMRArt1AJoC9QUwmTxgcUKw+Agp+zYIDq/G/QCgolHT
oDFkLBCLjZBST7ypzbOOfew=
=CCSs
-----END PGP SIGNATURE-----

> 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.