> Bruce Momjian wrote:
> >
> > create table test (x int4, y int4);
> > 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);
> >
> > This is going to use the i_test index, but only with key x=3, and do a
> > scan of the index looking for y=1 or y=2, and will not use the second
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> Server will fetch heap tuple for each tuple with x = 3
> returned by index access methods and call ExecQual...
>
> > key of the index.
>
> There are two ways.
>
> I. Rewrite this into
>
> (x = 3 and y = 1) or (x = 3 and y = 2)
>
> and scan index twice using both keys for finding index tuples.
Yes. One way to do this may be to have OR processed AFTER AND in the
optimizer, and use the existing index matches to look for matches
further down the key. I am not sure where the actual multiple index
plans are created, so I am not sure how to implement this.
> 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!
I see what you are saying. In the first case, you look for key1,key2a,
then for key1,key2b, etc. In the second case, you look for key1, then
sitting on key1, you can get key2a, key2b, etc, without having to find
key1 for every OR.
> 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?
--
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)
