X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 59A95D9896; Tue, 27 Sep 2005 07:55:24 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 01495-06; Tue, 27 Sep 2005 10:55:20 +0000 (GMT) Received: from wimpy.net.nih.gov (wimpy.net.nih.gov [128.231.88.106]) by svr1.postgresql.org (Postfix) with ESMTP id AFF99D98C1; Tue, 27 Sep 2005 07:55:20 -0300 (ADT) Received: from wimpy.net.nih.gov (localhost [127.0.0.1]) by wimpy.net.nih.gov (8.12.11/8.11.7) with ESMTP id j8RAtJvx013475; Tue, 27 Sep 2005 06:55:23 -0400 (EDT) Received: from [128.231.145.14] (holmes.nhgri.nih.gov [128.231.145.14]) by wimpy.net.nih.gov (8.12.11/8.11.7) with ESMTP id j8RAtJ7e013472; Tue, 27 Sep 2005 06:55:19 -0400 (EDT) User-Agent: Microsoft-Entourage/10.1.4.030702.0 Date: Tue, 27 Sep 2005 06:54:51 -0400 Subject: Re: Index use in BETWEEN statement... From: Sean Davis To: Yonatan Ben-Nes , Tom Lane Cc: Cristian Prieto , , Message-ID: In-Reply-To: <433930E5.7090800@canaan.co.il> Mime-version: 1.0 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.014 required=5 tests=[AWL=0.014] X-Spam-Level: X-Archive-Number: 200509/1069 X-Sequence-Number: 84084 On 9/27/05 7:45 AM, "Yonatan Ben-Nes" wrote: > Tom Lane wrote: >> "Cristian Prieto" 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