When in clause becomes large enough (>20-30 cases), It is much better to use "join" way of processing..
I mean, "SELECT * FROM table WHERE field IN (1,2...30)" will be slower than "SELECT * FROM table JOIN (SRF returning 1...30) USING(field)"
I'm not quite sure, where the difference starts, but sometimes I need to make selects with 30 or more items by primary key and I get significant speed up by this transform:
CREATE OR REPLACE FUNCTION array2table(arr int[]) RETURNS SETOF int
select * from persons join (select array2table as id from array2table(array[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,2 3,24,25,26,27,28,29,30])) a using(id);
I'm sure that backend could do that in a much faster and elegant fasion. Bitmap-or is nice, but for many IN arguments it is still much slower than join.
---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ?