From: Sean Davis <sdavis2@mail.nih.gov>
To: Cristian Prieto <cristian@clickdiario.com>
To: pgsql-general@postgresql.org
Subject: Re: Index use in BETWEEN statement...
Date: Mon, 26 Sep 2005 12:24:00 -0400
Message-ID: <BF5D98E0.F228%sdavis2@mail.nih.gov> (raw)
In-Reply-To: <20050926153244.C336210149@mail.clickdiario.com>
On 9/26/05 11:26 AM, "Cristian Prieto" <cristian@clickdiario.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sdavis2@mail.nih.gov, cristian@clickdiario.com, pgsql-general@postgresql.org
Subject: Re: Index use in BETWEEN statement...
In-Reply-To: <BF5D98E0.F228%sdavis2@mail.nih.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox