From: | Zdenek Kotala <Zdenek(dot)Kotala(at)Sun(dot)COM> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | using hash index when BETWEEN is specified |
Date: | 2008-09-10 10:39:42 |
Message-ID: | 48C7A3EE.4020009@sun.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Asko Oja | 2008-09-10 10:52:19 | Re: using hash index when BETWEEN is specified |
Previous Message | Martijn van Oosterhout | 2008-09-10 10:23:34 | Re: WIP patch: Collation support |