I have a large postgresql 7.3 database, a number of the tables have over 1
million records, the largest having over 8 million.
When I run the following query:
SELECT attrib1 FROM table1 WHERE attrib2 IN ( '1', '3', '5', '7' );
It takes several minutes to complete.
Looking more closely, internally this is converted to:
... WHERE attrib2 = '1' OR attrib2 = '3' OR attrib2= '5'...
If I instead run the query:
SELECT attrib1 FROM table1 WHERE attrib2 = '1'
UNION
SELECT attrib1 FROM table1 WHERE attrib2 = '3'
UNION
SELECT attrib1 FROM table1 WHERE attrib2 = '5'
UNION
SELECT attrib1 FROM table1 WHERE attrib2 = '7';
Then it return in seconds.
Why does using IN (or OR) give such a performance hit?
Is there any way I can resolve this, it seem odd to be forced to UNION
everything, and may not always be practicle to do so. It certianly makes the
query text much longer!
thanks!
M.
_________________________________________________________________
Surf together with new Shared Browsing
http://join.msn.com/?page=features/browse&pgmarket=en-gb&XAPID=74&DI=1059
