BUG #4974: Equivalent of "= ANY" and "BETWEEN" not observed by planner. - Mailing list pgsql-bugs

From Ian Turner
Subject BUG #4974: Equivalent of "= ANY" and "BETWEEN" not observed by planner.
Date
Msg-id 200908120152.n7C1qVo1018894@wwwmaster.postgresql.org
Whole thread Raw
Responses Re: BUG #4974: Equivalent of "= ANY" and "BETWEEN" not observed by planner.
List pgsql-bugs
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.

pgsql-bugs by date:

Previous
From: Dean Rasheed
Date:
Subject: Re: misleading error message in 8.5, and bad (?) way deferred uniqueness works
Next
From: Tom Lane
Date:
Subject: Re: BUG #4974: Equivalent of "= ANY" and "BETWEEN" not observed by planner.