Re: IN () vs. BETWEEN differences

From: Christophe Pettus SF Postgres Re: IN () vs. BETWEEN differences 2011-07-21 20:50:33 3958B912-355B-4D90-A981-73D8D062C6B7@thebuild.com (view raw or whole thread) 2011-07-21 20:36:34 from Brian Ghidinelli  2011-07-21 20:50:33 from Christophe Pettus   2011-07-21 22:31:54 from Brian Ghidinelli sfpug
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

sfpug by date

 Next: From: Brian Ghidinelli Date: 2011-07-21 22:31:54 Subject: Re: IN () vs. BETWEEN differences Previous: From: Brian Ghidinelli Date: 2011-07-21 20:36:34 Subject: IN () vs. BETWEEN differences