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
