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, or the decision which index to use ?

I think the case Bruce has fixed was the most important for those "alien MS Access users" among us.
Thanks Bruce !

Andreas


Fri, 31 Jul 1998 10:51:43 +0200
Andreas Zeugswetter <andreas.zeugswetter@telecom.at>
AW: [HACKERS] OR clause status report
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, or the decision which index to use ?
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
This.

Vadim

Fri, 31 Jul 1998 17:06:30 +0800
Vadim Mikheev <vadim@krs.ru>
Re: AW: [HACKERS] OR clause status report
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...

Vadim

Fri, 31 Jul 1998 22:02:59 +0800
Vadim Mikheev <vadim@krs.ru>
Re: AW: [HACKERS] OR clause status report
> 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, or the decision which index to use ?
>               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> This.

Yes.  I still need to run some tests.  Just wanted people to know I had
gotten the trivial case working so far.

-- 
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)

Fri, 31 Jul 1998 10:32:58 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: AW: [HACKERS] OR clause status report
> 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)

Sat, 1 Aug 1998 01:14:52 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: AW: [HACKERS] OR clause status report