Skip site navigation (1) Skip section navigation (2)

Re: IN () vs. BETWEEN differences

From: Christophe Pettus <xof(at)thebuild(dot)com>
To: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: IN () vs. BETWEEN differences
Date: 2011-07-21 20:50:33
Message-ID: 3958B912-355B-4D90-A981-73D8D062C6B7@thebuild.com (view raw or flat)
Thread:
Lists: 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


In response to

Responses

sfpug by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group