Re: using hash index when BETWEEN is specified

On Wed, Sep 10, 2008 at 1:39 PM, Zdenek Kotala <Zdenek.Kotala@sun.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.

               Zdenek


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