Search Postgresql Archives

Re: Index use in BETWEEN statement...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 9/27/05 7:45 AM, "Yonatan Ben-Nes" <da@xxxxxxxxxxxx> wrote:

> Tom Lane wrote:
>> "Cristian Prieto" <cristian@xxxxxxxxxxxxxxx> 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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux