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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
