Thread: OR clause issues
As promised, I am posting an analysis of the current OR clause issues.
They are causing people problems, and this is something that I want to
address for 6.4.
We have two problems. First, indexes are not used with OR's. This is a
serious problem, with no good workaround. I have looked at the code,
and there are two places that need changes. First, there is much code
in the optimizer to handle OR's, but it was turned off because it did
not work. There is also no support in the executor to handle multiple
OR values when using indexes. I have fixed the optimizer so it can now
identify OR clauses and handle them properly:
test=> explain select * from test where x=3 or x=4;
NOTICE: equal: don't know whether nodes of type 200 are equal
NOTICE: QUERY PLAN:
Index Scan using i_test on test (cost=4.10 size=1 width=4)
As you can see, I am getting a NOTICE I have to check into. Also, the
executor is only returning the FIRST of the OR conditions, because I
have not yet added code to nodeIndexscan.c to handle multiple values.
This code is not installed in the main source tree. I will complete my
cleanups and tests, and install it. I may need help with
nodeIndexscan.c. My idea is to hook up multiple ScanKeys, and to move
on to the next one when the first finishes. Perhaps someone (Vadim?)
could help as I am a little lost in how to do that. Pointers to similar
code would help.
Second issue is the palloc failure on complex OR conditions caused by
cnf-ifying the qualification (cnfify()). I believe there may be a way
to restrict cnfify'ing the entire qualification. Perhaps we can prevent
full cnf'ification when multiple OR's are supplied, and each is a
constant. I will have to check into this, but if others have ideas, I
would like to hear them.
--
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)
Bruce Momjian wrote:
>
> We have two problems. First, indexes are not used with OR's. This is a
> serious problem, with no good workaround. I have looked at the code,
> and there are two places that need changes. First, there is much code
> in the optimizer to handle OR's, but it was turned off because it did
> not work. There is also no support in the executor to handle multiple
^^^^^^^^^^^^^^^^^^^^^^^^^^
> OR values when using indexes. I have fixed the optimizer so it can now
> identify OR clauses and handle them properly:
>
> test=> explain select * from test where x=3 or x=4;
> NOTICE: equal: don't know whether nodes of type 200 are equal
> NOTICE: QUERY PLAN:
>
> Index Scan using i_test on test (cost=4.10 size=1 width=4)
>
> As you can see, I am getting a NOTICE I have to check into. Also, the
> executor is only returning the FIRST of the OR conditions, because I
> have not yet added code to nodeIndexscan.c to handle multiple values.
>
> This code is not installed in the main source tree. I will complete my
> cleanups and tests, and install it. I may need help with
> nodeIndexscan.c. My idea is to hook up multiple ScanKeys, and to move
> on to the next one when the first finishes. Perhaps someone (Vadim?)
> could help as I am a little lost in how to do that. Pointers to similar
> code would help.
execnodes.h:
/* ----------------
* IndexScanState information
*
* IndexPtr current index in use
* NumIndices number of indices in this scan
* ScanKeys Skey structures to scan index rels
* NumScanKeys array of no of keys in each Skey struct
- some support is already in Executor!
Functions in nodeIndexscan.c also handle this.
Currently, IndexPtr is ALWAYS ZERO - so you have to add code to
switch to the next index after NULL is returned by index_getnext()
(in IndexNext()).
Note that different indices (of the same table) may be used
in single scan (x = 3 or y = 1)!
The most complex stuff to be implemented for something
like (x = 3 or y = 1) is to check that for tuples, fetched
by second index sub-scan, x IS NOT EQUAL 3!
Maybe IndexScan->indxqual can help you...
Vadim