From: | "Cristian Prieto" <cristian(at)clickdiario(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Index use in BETWEEN statement... |
Date: | 2005-09-26 15:26:43 |
Message-ID: | 20050926153244.C336210149@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!
From | Date | Subject | |
---|---|---|---|
Next Message | ruben | 2005-09-26 15:27:22 | could not send data to server: Operation not supported |
Previous Message | Douglas McNaught | 2005-09-26 15:20:29 | Re: Function keys cause psql to segfault |
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Davis | 2005-09-26 16:24:00 | Re: Index use in BETWEEN statement... |
Previous Message | Ron Peacetree | 2005-09-26 11:01:59 | Re: Advice on RAID card |