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=83width=4) Recheck Cond: (id = ANY ('{1,2,3,4,5}'::integer[])) -> Bitmap Index Scan on test_idx
(cost=0.00..22.22rows=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
