Index use in BETWEEN statement...
От | Cristian Prieto |
---|---|
Тема | Index use in BETWEEN statement... |
Дата | |
Msg-id | 20050926153244.C336210149@mail.clickdiario.com обсуждение исходный текст |
Ответы |
Re: Index use in BETWEEN statement...
|
Список | pgsql-general |
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!
В списке pgsql-general по дате отправления: