Re: BUG #4974: Equivalent of "= ANY" and "BETWEEN" not observed by planner.

On Wed, Aug 12, 2009 at 3:15 AM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Ian Turner" <ian(dot)turner(at)deshaw(dot)com> writes:
>> 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;
>
> The planner intentionally does not do very many inferences that are as
> datatype-dependent as this one would be.  It doesn't fit into the system
> design.  For the most part the possible gain is not large anyway.

Hm, we could do it in a data-type independent way which would work
even for non-integral values by imposing a recheck condition. That
would work for any data type with a btree ordering.

I think you're right that the potential gain isn't very big. A series
of equality values versus a range scan which uses the same index pages
is going to be about the same i/o. The only saving is the repeated
descent of the tree to find the leaf pages. Which should hopefully all
be in cache anyways.

I suppose it might help if you were then doing a merge join or order
by on the same key since the range scan will be in order but the
bitmap index scan would have to be sorted. But if there were enough
tuples for the sort to actually matter surely we would want the bitmap
index scan anyways.

--
greg
http://mit.edu/~gsstark/resume.pdf