From: | Vadim Mikheev <vadim(at)krs(dot)ru> |
---|---|
To: | Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us> |
Cc: | hackers(at)postgreSQL(dot)org |
Subject: | Re: [HACKERS] OR with multi-key indexes |
Date: | 1998-08-01 16:08:30 |
Message-ID: | 35C33D7E.EA37C587@krs.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
Vadim
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 1998-08-01 17:13:56 | Re: [HACKERS] OR clause - check code |
Previous Message | Bruce Momjian | 1998-08-01 15:29:40 | Re: [HACKERS] OR with multi-key indexes |