> Vadim Mikheev wrote:
> >
> > Andreas Zeugswetter wrote:
> > >
> > > Vadim wrote:
> > > >Bruce Momjian wrote:
> > > >>
> > > >> I have succeeded in making OR clauses use indexes. I have not dealt
> > > >> with some of the more complex issues like x=3 or y=5 as Vadim mentioned,
> > > >> but at least it works.
> > > >>
> > > >> test=> select * from test where x=102532 or x=102533;
> > > >
> > > >But did you care about the case when two indices (on test(x) and
> > > >on test(y)) exist ?
> > >
> > > Do you mean using two indices in one access plan,
> > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> > This.
>
> Actually, I meant NOT USING indices if both exist, because of
> currently there is no check that tuples returned by second index
> were not returned by first.
>
> > > or the decision which index to use ?
>
> Either both indices should be used or no one...
>
> Also, Bruce, did you test the case (x = 5 or x > 4) ?
> What about (x = 5 or x = 5)? - I'm not sure does cnfify()
> get rid of duplicates or not...
It does get rid of duplicates, and only uses indexes if ALL clauses have
an availble index, but as you noted, x=5 or x > 4 must be handled. It
works now:
test=> select * from test where x >= 102665 or x= 102665;
x
------
102665
(1 row)
test=> explain select * from test where x >= 102665 or x= 102665;
NOTICE: QUERY PLAN:
Index Scan using i_test on test (cost=1503.32 size=1 width=4)
I do it with this code:
ExecStoreTuple(tuple, /* tuple to store */
slot, /* slot to store in */
buffer, /* buffer associated with tuple */
false); /* don't pfree */
for (prev_index = 0; prev_index < indexstate->iss_IndexPtr;
prev_index++)
{
if (ExecQual(nth(prev_index, node->indxqual),
scanstate->cstate.cs_ExprContext))
{
prev_matches = true;
break;
}
}
if (!prev_matches)
return slot;
On an index scan, I compare the qualifications of previous OR index
scans, and return the row only if the current row does not match one of
the previous qualifications. Sounds like a winner. I have not yet
committed this code to the CVS tree.
--
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)
