Over-indexing m2m table for FKs?

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

Responses

Browse sfpug by date

  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