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