Re: [HACKERS] "ExecInitIndexScan: both left and right..." meaning?

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