> 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.
>
> 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.
I now understand how this feature could be used in both cases where we
match key1 and key3, and when we use OR on second key.
I see what you are saying, that the values are already in the index, so
you could scan the index looking for the third key or the OR clause, and
not have to fetch the heap to look it up.
I will add this to the TODO list under performance. It is sort of
exotic, but why not add it:
* use index to restrict rows returned by multi-key index for
non-consecutive keys or OR clauses
--
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)
