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 519EFD9776; Mon, 26 Sep 2005 14:52:50 -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 54414-07; Mon, 26 Sep 2005 17:52:44 +0000 (GMT) Received: from mail.clickdiario.com (mail.clickdiario.com [70.85.167.114]) by svr1.postgresql.org (Postfix) with ESMTP id DAE67D9604; Mon, 26 Sep 2005 14:52:44 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by mail.clickdiario.com (Postfix) with ESMTP id A8CED10154; Mon, 26 Sep 2005 12:56:17 -0500 (CDT) Received: from mail.clickdiario.com ([127.0.0.1]) by localhost (mail.clickdiario.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 00327-04; Mon, 26 Sep 2005 12:56:17 -0500 (CDT) Received: from cristian1 (unknown [216.230.158.50]) by mail.clickdiario.com (Postfix) with ESMTP id BA5C21015A; Mon, 26 Sep 2005 12:56:15 -0500 (CDT) From: "Cristian Prieto" To: "'Sean Davis'" , , Subject: Re: Index use in BETWEEN statement... Date: Mon, 26 Sep 2005 11:49:59 -0600 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 In-Reply-To: Thread-Index: AcXCuMx2dqpjkzX3R1aD0UXOnoXs9wACX5SA Message-Id: <20050926175615.BA5C21015A@mail.clickdiario.com> X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at example.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.015 required=5 tests=[AWL=0.015] X-Spam-Level: X-Archive-Number: 200509/1029 X-Sequence-Number: 84044 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..78033.96 rows=230141 width=8) (actual time=13015.538..13508.708 rows=1 loops=1) Filter: (('216.230.158.50'::inet >= start_block) AND ('216.230.158.50'::inet <= end_block)) Total runtime: 13508.905 ms (3 rows) mydb=# alter table geoip_block add constraint pkey_geoip_block primary key (start_block, end_block); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "pkey_geoip_block" for table "geoip_block" ALTER TABLE mydb=# vacuum analyze geoip_block; 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..101121.01 rows=308324 width=8) (actual time=12128.190..12631.550 rows=1 loops=1) Filter: (('216.230.158.50'::inet >= start_block) AND ('216.230.158.50'::inet <= end_block)) Total runtime: 12631.679 ms (3 rows) mydb=# As you see it still using a sequential scan in the table and ignores the index, any other suggestion? -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sean Davis Sent: Lunes, 26 de Septiembre de 2005 10:24 a.m. To: Cristian Prieto; pgsql-general@postgresql.org Subject: Re: [GENERAL] Index use in BETWEEN statement... 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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq