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)
