Re: [HACKERS] OR with multi-key indexes

> Bruce Momjian wrote:
> >
> > > > create index i_test on test(x,y);
> > > > insert into test values(1,2);
> > > > select * from test where x=3 and (y=1 or y=2);
> >
> > > 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?

--
Bruce Momjian | 830 Blythe Avenue
maillist(at)candle(dot)pha(dot)pa(dot)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)