The following bug has been logged online:
Bug reference: 4974
Logged by: Ian Turner
Email address: ian.turner@deshaw.com
PostgreSQL version: 8.3
Operating system: Ubuntu 8.10
Description: Equivalent of "= ANY" and "BETWEEN" not observed by
planner.
Details:
Consider the following table with a few thousand rows:
CREATE TABLE example (pk INTEGER PRIMARY KEY);
The following queries are equivalent, because there are no integers between
5 and 6 and because the BETWEEN operator contemplates a closed range.
SELECT * FROM example WHERE pk IN (5,6);
SELECT * FROM example WHERE pk BETWEEN 5 AND 6;
Yet the two queries generate very different plans:
sysdb=# explain select * from example where pk between 5 and 6;
QUERY PLAN
----------------------------------------------------------------------------
-----
Index Scan using example_pkey on example (cost=0.00..8.27 rows=1 width=71)
Index Cond: ((uid >= 5) AND (uid <= 6))
(2 rows)
ysdb=# explain select * from example where pk IN (5, 6);
QUERY PLAN
----------------------------------------------------------------------------
-
Bitmap Heap Scan on example (cost=8.52..14.88 rows=2 width=71)
Recheck Cond: (pk = ANY ('{5,6}'::integer[]))
-> Bitmap Index Scan on example_pkey (cost=0.00..8.52 rows=2 width=0)
Index Cond: (pk = ANY ('{5,6}'::integer[]))
(4 rows)
The bug is that the planner should be able to consider the use of a vanilla
index scan for = ANY operators when the values are consecutive for the value
type in question. Probably the easiest way is to detect this case and
rewrite it as using <= / >= operators.
More generally, it might be desirable to use the index scan even when values
are not consecutive (but are very close). This last idea is a lot more
complex, however, as it depends on the distribution of values in the table.