Re: using hash index when BETWEEN is specified

Hannu Krosing napsal(a):
> On Wed, 2008-09-10 at 07:13 -0400, Robert Haas wrote:
>>>> 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.
>>> Where should the line be drawn.
>>> Define small :)
>> When the estimated cost is lower?
>
> You still need to draw a line for when to even try estimating the cost .
>
> Will this be interval of 10 ? or 100 ? or 10000 ?

I think it depends of ration of unique integer number in a table and
numbers of requested interval, number distribution and total number of rows.

For example if you have 10 distinct number and each has 100 occurrence
then full scan is better (for between 1 and 5). But if each number
occurs 100000x. Then using hash index should be effective.

Zdenek