Tom Lane wrote:
>
> Ed Loehr <ELOEHR@austin.rr.com> writes:
> > create index mytable_dog_idx on mytable(dog_id);
> > create index mytable_cat_idx on mytable(cat_id);
> > create index mytable_dogcat_idx on mytable(dog_id,cat_id);
>
> > ...thinking these indices would allow the fastest lookups from 3 different
> > angles (at the cost of slower inserts, of course). Not sure my intuition
> > here corresponds directly with the technical reality...
>
> I doubt the 2-column index earns its keep given that you have another
> index on the front column. A multicolumn index is a pretty specialized
> beast, so I don't recommend creating one unless you have a very specific
> heavily-used query in mind. (Of course, if you're making a multicol
> UNIQUE index to enforce uniqueness of a multicol primary key, that's
> a different matter entirely. But if you're just fishing for performance
> improvements, you're probably fishing in the wrong place.)
Actually I think that the first (dog_id) is worthless in this situation as
(dog_id,cat_id) can be used instead of it.
I vaguely remember that Hiroshi posted a patch some time ago that fixed
the plan to use more then only the first column of multi-column index
if possible.
The first column of a multi-column index has always been used afaik.
------------------------
Hannu