Re: Index use in BETWEEN statement...

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: Cristian Prieto <cristian(at)clickdiario(dot)com>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: Index use in BETWEEN statement...
Date: 2005-09-26 16:24:00
Message-ID: BF5D98E0.F228%sdavis2@mail.nih.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance

On 9/26/05 11:26 AM, "Cristian Prieto" <cristian(at)clickdiario(dot)com> wrote:

>
> 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?

Did you vacuum analyze the table after creating the index?

Sean

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Raj Gupta 2005-09-26 16:43:28 Error migrating from 7.4.3 to 8.0.3
Previous Message Sean Davis 2005-09-26 15:45:52 Re: Data Entry Tool for PostgreSQL

Browse pgsql-performance by date

  From Date Subject
Next Message Gurpreet Aulakh 2005-09-26 17:10:56 Re: Query slower on 8.0.3 (Windows) vs 7.3 (cygwin)
Previous Message Cristian Prieto 2005-09-26 15:26:43 Index use in BETWEEN statement...