Re: Index use in BETWEEN statement...

On 9/27/05 7:45 AM, "Yonatan Ben-Nes" <da(at)canaan(dot)co(dot)il> wrote:

> Tom Lane wrote:
>> "Cristian Prieto" <cristian(at)clickdiario(dot)com> writes:
>>
>>> mydb=# explain analyze select locid from geoip_block where
>>> '216.230.158.50'::inet between start_block and end_block;
>>
>>
>>> As you see it still using a sequential scan in the table and ignores the
>>> index, any other suggestion?
>>
>>
>> That two-column index is entirely useless for this query; in fact btree
>> indexes of any sort are pretty useless. You really need some sort of
>> multidimensional index type like rtree or gist. There was discussion
>> just a week or three ago of how to optimize searches for intervals
>> overlapping a specified point, which is identical to your problem.
>> Can't remember if the question was about timestamp intervals or plain
>> intervals, but try checking the list archives.
>>
>> regards, tom lane
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>
> I think that Tom is talking about a discussion which I started entitled
> "Planner create a slow plan without an available index" search for it
> maybe it will help you.
> At the end I created an RTREE index and it did solved my problem though
> my data was 2 INT fields and not INET fields as yours so im not sure how
> can you work with that... To solve my problem I created boxes from the 2
> numbers and with them I did overlapping.

There is some code in this thread that shows the box approach explicitly:

http://archives.postgresql.org/pgsql-sql/2005-09/msg00189.php

Sean