Re: Index use in BETWEEN statement...

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