From: Don Isgitt <djisgitt@soundenergy.com>
To: Cristian Prieto <cristian@clickdiario.com>
Cc: Sean Davis <sdavis2@mail.nih.gov>
Cc: pgsql-general@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: Index use in BETWEEN statement...
Date: Mon, 26 Sep 2005 14:44:42 -0500
Message-ID: <43384FAA.9030404@soundenergy.com> (raw)
In-Reply-To: <20050926175615.BA5C21015A@mail.clickdiario.com>
References: <20050926175615.BA5C21015A@mail.clickdiario.com>
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
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: djisgitt@soundenergy.com, cristian@clickdiario.com, sdavis2@mail.nih.gov, pgsql-general@postgresql.org
Subject: Re: Index use in BETWEEN statement...
In-Reply-To: <43384FAA.9030404@soundenergy.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