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 6A343D97B3;
	Mon, 26 Sep 2005 16:48:12 -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 81240-04; Mon, 26 Sep 2005 19:48:09 +0000 (GMT)
Received: from mx.cbeyond.com (mx.cbeyond.net [66.180.96.58])
	by svr1.postgresql.org (Postfix) with ESMTP id 0B5C1D976D;
	Mon, 26 Sep 2005 16:48:08 -0300 (ADT)
Received: from [66.180.98.234] (port=37771 helo=[10.0.1.11])
	by mx.cbeyond.com with esmtp (Exim 4.34)
	id 1EJyxN-00033I-VI; Mon, 26 Sep 2005 15:48:10 -0400
Message-ID: <43384FAA.9030404@soundenergy.com>
Date: Mon, 26 Sep 2005 14:44:42 -0500
From: Don Isgitt <djisgitt@soundenergy.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.5) Gecko/20041217
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Cristian Prieto <cristian@clickdiario.com>
Cc: 'Sean Davis' <sdavis2@mail.nih.gov>,
	pgsql-general@postgresql.org, pgsql-performance@postgresql.org
Subject: Re: Index use in BETWEEN statement...
References: <20050926175615.BA5C21015A@mail.clickdiario.com>
In-Reply-To: <20050926175615.BA5C21015A@mail.clickdiario.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.05 required=5 tests=[AWL=-0.000,
	FORGED_RCVD_HELO=0.05]
X-Spam-Level: 
X-Archive-Number: 200509/1041
X-Sequence-Number: 84056



Cristian Prieto wrote:

>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?
>
>Cristian,
>  
>
Please note that the planner thinks 308324 rows are being returned, 
while there is actually only 1 (one!). You might try altering statistics 
for the relevant column(s), analyzing the table, and then try again. If 
that doesn't give you a more accurate row estimate, though, it won't help.

Don