Re: [HACKERS] OR with multi-key indexes

> Bruce Momjian wrote:
> >
> > > > > II. Extend multi-key indexing: (y = 1 or y = 2) could be
> > > > > qualified by index access methods itself because of Y is
> > > > > one of index keys. Only first key would be used for finding
> > > > > index tuples but additional qualification could decrease
> > > > > number of heap_fetch calls and this would be nice!
> > > >
> > > > > This feature would be also usefull for:
> > > > >
> > > > > create index on table (a,b,c);
> > > > > select * from table where a = 1 and c = 2;
> > > > >                                     ^^^^^
> > > > >     additional qualification would be performed on index level
> > > > >
> > > > > Personally, I would like to see II implemented first because
> > > > > of it works for both query examples.
> > > >
> > > > Doesn't the existing code already use both keys in the above query.
> > > > What is gained by moving this to the index access methods?
> > >
> > > I hadn't time to implement this year ago...
> > >
> > > Let's say we have 1000 tuples with a = 1 and only 10 with
> > > a = 1 and c = 2 - currently, all 1000 index tuples will be returned
> > > to Executor and all corresponding 1000 heap tuples will be fetched...
> > > Having this feature, only 10 index tuples would be returned
> > > and heap_fetch would be called 10 times only.
> >
> > OK, stupid question, but why do we have multi-key indexes then?  Just to
> > allow UNIQUE index failure?
>
> In the example above only 1st and _3rd_ index keys are used
> in WHERE and so only 1st key will be used by index.
> For cases like WHERE a = 1 and b = 3 index will use
> 1st and 2nd keys.
> For cases like WHERE a = 1 and b = 3 and c = 2 index will use
> all three keys.
>
> Extending II means not using 3rd key to _find_ index tuples
> but using 3rd key to reduce # of index tuples returned
> to executor. Btree will read all tuples with a = 3 but
> will not return index tuple with a = 3 and c = 0 (ie).

Oh, stupid me.  I see now.  Hardly seems worth adding the extra code to
reduce a match.  I agree, let the executor handle it.

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)