Re: IN () vs. BETWEEN differences

On Jul 21, 2011, at 1:36 PM, Brian Ghidinelli wrote:

>
> I think the answer is no, but this would settle a debate. Assuming the IN() example contains the complete set of values between 10 and 40, is there any practical or other differences between:
>
> WHERE integer_field IN (10, 11, 12, 13, 21, 22, 23, 30, 31, 32)
>
> and
>
> WHERE integer_field BETWEEN 10 AND 40
>
> Thanks!

Yes:

postgres=# explain analyze select i from x where i in (10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on x (cost=128.04..226.13 rows=31 width=4) (actual time=0.094..0.096 rows=31 loops=1)
Recheck Cond: (i = ANY ('{10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40}'::integer[]))
-> Bitmap Index Scan on x_pkey (cost=0.00..128.03 rows=31 width=0) (actual time=0.086..0.086 rows=31 loops=1)
Index Cond: (i = ANY ('{10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40}'::integer[]))
Total runtime: 0.133 ms
(5 rows)

postgres=# explain analyze select i from x where i between 10 and 40;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Index Scan using x_pkey on x (cost=0.00..8.86 rows=30 width=4) (actual time=0.014..0.024 rows=31 loops=1)
Index Cond: ((i >= 10) AND (i <= 40))
Total runtime: 0.050 ms
(3 rows)

--
-- Christophe Pettus
xof(at)thebuild(dot)com