Re: using hash index when BETWEEN is specified

On Wed, Sep 10, 2008 at 1:39 PM, Zdenek Kotala <Zdenek(dot)Kotala(at)sun(dot)com>wrote:

> I has played with new hash index implementation and I tried following
> command:
>
> postgres=# select * from test where id between 1 and 5;
> Time: 9651,033 ms
> postgres=# explain select * from test where id between 1 and 5;
> QUERY PLAN
> ---------------------------------------------------------
> Seq Scan on test (cost=0.00..141681.00 rows=1 width=4)
> Filter: ((id >= 1) AND (id <= 5))
> (2 rows)
>
>
> Hash index is created on id column. However when I use
>
> postgres=# explain select * from test where id in (1,2,3,4,5);
> QUERY PLAN
> -------------------------------------------------------------------------
> Bitmap Heap Scan on test (cost=22.24..332.53 rows=83 width=4)
> Recheck Cond: (id = ANY ('{1,2,3,4,5}'::integer[]))
> -> Bitmap Index Scan on test_idx (cost=0.00..22.22 rows=83 width=0)
> Index Cond: (id = ANY ('{1,2,3,4,5}'::integer[]))
> (4 rows)
>
> Time: 1,352 ms
>
> I'm not planner guru but it seems to me that BETWEEN clause could be
> rewritten as a IN clause for integer data types and small interval.
>

Where should the line be drawn.
Define small :)

>
>
> Zdenek
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>