From: | Brian Ghidinelli <brian(at)pukkasoft(dot)com> |
---|---|
To: | SF Postgres <sfpug(at)postgresql(dot)org> |
Subject: | Over-indexing m2m table for FKs? |
Date: | 2013-05-18 20:29:34 |
Message-ID: | 5197E4AE.6090704@pukkasoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
Hi all - I would appreciate help on what indices are needed exactly for
a many to many table. This is on 9.0 today, will be 9.2 soon. Here's a
contrived example:
table sidea (a_id)
table sideb (b_id)
table a_to_b (a_id references sidea on delete cascade, b_id references
sideb on delete cascade)
a_to_b is unique, so: create unique index idx ON a_to_b (a_id, b_id)
I have two primary use cases:
1. select sideb.* from a_to_b, sideb where a_to_b.b_id = sideb.b_id and
a_to_b.a_id = X
2. select sidea.*, sideb.* from sidea, a_to_b, sideb where sidea.a_id =
a_to_b.a_id and a_to_b.b_id = sideb.b_id
I currently have separate indices on a_to_b for a_id and b_id and I'm
not sure they're required. Can I delete them without impacting FK
lookups, cacades and joins?
Thanks,
Brian
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2013-05-18 20:39:07 | Re: Over-indexing m2m table for FKs? |
Previous Message | Sam Talebbeik | 2013-04-12 23:35:42 | Re: Embedding postgresql in an application |