From: Gabriele Bartolini <angusgb@tin.it>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Normal case or bad query plan?
Date: Tue, 12 Oct 2004 07:26:12 +0200
Message-ID: <6.1.2.0.2.20041012072112.01ed8810@box.tin.it> (raw)
In-Reply-To: <23515.1097530437@sss.pgh.pa.us>
References: <6.1.2.0.2.20041011224414.01fadec0@box.tin.it>
<23515.1097530437@sss.pgh.pa.us>
Hi Tom,
thanks for your interest.
At 23.33 11/10/2004, Tom Lane wrote:
>Gabriele Bartolini <angusgb@tin.it> writes:
> > QUERY PLAN
> >
> ---------------------------------------------------------------------------------------------------------------------
> > Seq Scan on ip2location (cost=0.00..30490.65 rows=124781 width=8)
> > (actual time=5338.120..40237.283 rows=1 loops=1)
> > Filter: ((1040878301::bigint >= ip_address_from) AND
> > (1040878301::bigint <= ip_address_to))
> > Total runtime: 40237.424 ms
>
> > Is this a normal case or should I worry? What am I missing?
>
>The striking thing about that is the huge difference between estimated
>rowcount (124781) and actual (1). The planner would certainly have
>picked an indexscan if it thought the query would select only one row.
>
>I suspect that you haven't ANALYZEd this table in a long time, if ever.
>You really need reasonably up-to-date ANALYZE stats if you want the
>planner to do an adequate job of planning range queries.
That's the thing ... I had just peformed a VACUUM ANALYSE :-(
> It may well be that you need to increase the analyze statistics target
> for this table,
>also --- in BIGINT terms the distribution is probably pretty irregular,
>which will mean you need finer-grain statistics to get good estimates.
You mean ... SET STATISTICS for the two columns, don't you?
>(BTW, have you looked at the inet datatype to see if that would fit your
>needs?)
Yes, I know. In other cases I use it. But this is a type of data coming
from an external source (www.ip2location.com) and I can't change it.
Thank you so much. I will try to play with the grain of the statistics,
otherwise - if worse comes to worst - I will simply disable the seq scan
after connecting.
-Gabriele
--
Gabriele Bartolini: Web Programmer, ht://Dig & IWA/HWG Member, ht://Check
maintainer
Current Location: Prato, Toscana, Italia
angusgb@tin.it | http://www.prato.linux.it/~gbartolini | ICQ#129221447
> "Leave every hope, ye who enter!", Dante Alighieri, Divine Comedy, The
Inferno
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.773 / Virus Database: 520 - Release Date: 05/10/2004
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: angusgb@tin.it, tgl@sss.pgh.pa.us
Subject: Re: Normal case or bad query plan?
In-Reply-To: <6.1.2.0.2.20041012072112.01ed8810@box.tin.it>
* 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