pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Cristian Prieto <cristian@clickdiario.com>
To: 'Sean Davis' <sdavis2@mail.nih.gov>
To: pgsql-general@postgresql.org
To: pgsql-performance@postgresql.org
Subject: Re: Index use in BETWEEN statement...
Date: Mon, 26 Sep 2005 11:49:59 -0600
Message-ID: <20050926175615.BA5C21015A@mail.clickdiario.com> (raw)
In-Reply-To: <BF5D98E0.F228%sdavis2@mail.nih.gov>


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?

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sean Davis
Sent: Lunes, 26 de Septiembre de 2005 10:24 a.m.
To: Cristian Prieto; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Index use in BETWEEN statement...

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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq




view thread (8+ messages)  latest in thread

Message-ID: <20050926175615.BA5C21015A@mail.clickdiario.com>
Permalink:  ../20050926175615.BA5C21015A@mail.clickdiario.com/
Also on:    postgresql.org/message-id/20050926175615.BA5C21015A@mail.clickdiario.com

 · 

reply

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: cristian@clickdiario.com, sdavis2@mail.nih.gov, pgsql-general@postgresql.org
  Subject: Re: Index use in BETWEEN statement...
  In-Reply-To: <20050926175615.BA5C21015A@mail.clickdiario.com>

* 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