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 ACC49D9564 for ; Mon, 26 Sep 2005 13:24:04 -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 27739-04 for ; Mon, 26 Sep 2005 16:23:56 +0000 (GMT) Received: from wimpy.net.nih.gov (wimpy.net.nih.gov [128.231.88.106]) by svr1.postgresql.org (Postfix) with ESMTP id A7E74D971F for ; Mon, 26 Sep 2005 13:23:55 -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 j8QGNrOj007248 for ; Mon, 26 Sep 2005 12:23:54 -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 j8QGNrkn007243; Mon, 26 Sep 2005 12:23:53 -0400 (EDT) User-Agent: Microsoft-Entourage/10.1.4.030702.0 Date: Mon, 26 Sep 2005 12:24:00 -0400 Subject: Re: Index use in BETWEEN statement... From: Sean Davis To: Cristian Prieto , Message-ID: In-Reply-To: <20050926153244.C336210149@mail.clickdiario.com> 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/1019 X-Sequence-Number: 84034 On 9/26/05 11:26 AM, "Cristian Prieto" wrote: > > Hello pals, I have the following table in Postgresql 8.0.1 > > Mydb# \d geoip_block > Table "public.geoip_block" > Column | Type | Modifiers > -------------+--------+----------- > locid | bigint | > start_block | inet | > end_block | inet | > > mydb# explain analyze select locid from geoip_block where > '216.230.158.50'::inet between start_block and end_block; > QUERY PLAN > ---------------------------------------------------------------------------- > ------------------------------------------- > Seq Scan on geoip_block (cost=0.00..142772.86 rows=709688 width=8) (actual > time=14045.384..14706.927 rows=1 loops=1) > Filter: (('216.230.158.50'::inet >= start_block) AND > ('216.230.158.50'::inet <= end_block)) > Total runtime: 14707.038 ms > > Ok, now I decided to create a index to "speed" a little the query > > Mydb# create index idx_ipblocks on geoip_block(start_block, end_block); > CREATE INDEX > > clickad=# explain analyze select locid from geoip_block where > '216.230.158.50'::inet between start_block and end_block; > QUERY PLAN > ---------------------------------------------------------------------------- > ------------------------------------------ > Seq Scan on geoip_block (cost=0.00..78033.96 rows=230141 width=8) (actual > time=12107.919..12610.199 rows=1 loops=1) > Filter: (('216.230.158.50'::inet >= start_block) AND > ('216.230.158.50'::inet <= end_block)) > Total runtime: 12610.329 ms > (3 rows) > > I guess the planner is doing a sequential scan in the table, why not use the > compound index? Do you have any idea in how to speed up this query? Did you vacuum analyze the table after creating the index? Sean