indexing primary and foreign keys w/lookup table

Поиск
Список
Период
Сортировка
От Neal Clark
Тема indexing primary and foreign keys w/lookup table
Дата
Msg-id 7C6BDCA8-0943-4221-AB3D-6794C30A3F77@securescience.net
обсуждение исходный текст
Ответы Re: indexing primary and foreign keys w/lookup table  (Chris <dmagick@gmail.com>)
Re: indexing primary and foreign keys w/lookup table  ("Merlin Moncure" <mmoncure@gmail.com>)
Re: indexing primary and foreign keys w/lookup table  (Bruno Wolff III <bruno@wolff.to>)
Список pgsql-general
-----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-----

В списке pgsql-general по дате отправления:

Предыдущее
От: Alvaro Herrera
Дата:
Сообщение: Re: Example of RETURNING clause to get auto-generated keys
Следующее
От: Chris
Дата:
Сообщение: Re: indexing primary and foreign keys w/lookup table