| From: | Neal Clark <nclark(at)securescience(dot)net> | 
|---|---|
| To: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> | 
| Subject: | indexing primary and foreign keys w/lookup table | 
| Date: | 2007-01-25 04:14:07 | 
| Message-ID: | 7C6BDCA8-0943-4221-AB3D-6794C30A3F77@securescience.net | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | 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-----
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Chris | 2007-01-25 04:40:57 | Re: indexing primary and foreign keys w/lookup table | 
| Previous Message | Alvaro Herrera | 2007-01-25 03:49:46 | Re: Example of RETURNING clause to get auto-generated keys |