From: "Cristian Prieto" <cristian@clickdiario.com>
To: <pgsql-general@postgresql.org>
Subject: Index use in BETWEEN statement...
Date: Mon, 26 Sep 2005 09:26:43 -0600
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
In-Reply-To: <20050923220857.3A0841001E@mail.clickdiario.com>
Thread-Index: AcXAipbGc98Fe8XwQlKGyZnlcfBNQQCJAatw
Message-Id: <20050926153244.C336210149@mail.clickdiario.com>


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?

Thanks a lot!