Обсуждение: AW: [HACKERS] OR clause status report

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

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

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

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

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