Re: AW: [HACKERS] OR clause status report

From: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
To: vadim(at)krs(dot)ru (Vadim Mikheev)
Cc: andreas(dot)zeugswetter(at)telecom(dot)at, hackers(at)postgreSQL(dot)org
Subject: Re: AW: [HACKERS] OR clause status report
Date: 1998-08-01 05:14:52
Message-ID: 199808010514.BAA12152@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

> 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(at)candle(dot)pha(dot)pa(dot)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)

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter T Mount 1998-08-01 08:18:42 Re: [HACKERS] User authentication bug?
Previous Message Vince Vielhaber 1998-08-01 01:21:44 Re: [HACKERS] OR clause status report - working