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)