Index use in BETWEEN statement...

From: "Cristian Prieto" <cristian(at)clickdiario(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Index use in BETWEEN statement...
Date: 2005-09-23 22:03:11
Message-ID: 20050923220857.3A0841001E@mail.clickdiario.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance

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!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message hildebardo 2005-09-24 00:55:32 question about to return two diferent tables from a function
Previous Message Tony Wasson 2005-09-23 21:46:52 Re: Getting the amount of overlap when using OVERLAPS

Browse pgsql-performance by date

  From Date Subject
Next Message Mark Kirkwood 2005-09-24 01:40:25 Re: SELECT LIMIT 1 VIEW Performance Issue
Previous Message Tom Lane 2005-09-23 21:12:49 Re: Query slower on 8.0.3 (Windows) vs 7.3 (cygwin)