Re: Over-indexing m2m table for FKs?

Actually, if those are your only queries (or maybe even just main/user
facing queries (we usually don't worry about queries used in admin tools
unless they are outrageously bad)), I don't think that you need either of
the extra individual indexes.

Query 1 would use the unique together index (searching for a specific a_id
and then using all of the b_ids)

Query 2 wouldn't need to use any index (though it could use the
unique(a_id, b_id) index) given that you aren't filtering or ordering by
either a_id or b_id.

If you needed to reverse the 1st query, it would probably make sense to
have the index on b_id. (reversed means: select sidea.* from a_to_b, sidea
where a_to_b.a_id = sidea.id and a_to_b.b_id = X). A side note: if you do
need to query both ways, you would possibly want to reverse the unique
index if you will be filtering by b_id needing a_id more than you will be
filter by a_id needing b_id.

On Sat, May 18, 2013 at 1:39 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

>
> > 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'd add a 2nd index: create index on a_to_b(b_id).
>
> This should be sufficient for most uses. Certainly an additional index
> on only (a_id) would be redundant.
>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>
>