Re: Performance difference between ANY and IN, also array

Bart Grantham wrote:
> Hello, all. I work for a Manhattan ISP and have developed an internal
> systems management/housekeeping app on php/postgres 7.4. I am trying to
> speed up some bits with stored procedures and have had great success,
> except I've now run into a bit of trouble. It comes down to this:
>
> # SELECT * FROM connections WHERE connectee_node_id IN ( 28543,28542 );
> -snip-
> Time: 1.410 ms

If you can divide your data set into ranges of adjacent values, it is
even faster to use series of BETWEEN clauses. Kind of depends on the
amount of processing you need to do to get those ranges. The values
being ordered beforehand helps a great deal.

For example, you could do WHERE value IN (1,2,3,4,6,7,8,9,10) or you
could use WHERE value BETWEEN 1 AND 4 OR value BETWEEN 6 AND 10.

You'll also want to prevent having queries like WHERE value BETWEEN 2
AND 3. There IN (2,3) is probably the better alternative.

--
Alban Hertroys
MAG Productions

T: +31(0)53 4346874
F: +31(0)53 4346876
E: alban(at)magproductions(dot)nl
W: http://www.magproductions.nl