Skip site navigation (1) Skip section navigation (2)

Re: Normal case or bad query plan?

From: Gabriele Bartolini <angusgb(at)tin(dot)it>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Normal case or bad query plan?
Date: 2004-10-12 05:26:12
Message-ID: 6.1.2.0.2.20041012072112.01ed8810@box.tin.it (view raw or flat)
Thread:
Lists: pgsql-performance
Hi Tom,

    thanks for your interest.

At 23.33 11/10/2004, Tom Lane wrote:

>Gabriele Bartolini <angusgb(at)tin(dot)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(at)tin(dot)it | http://www.prato.linux.it/~gbartolini | ICQ#129221447
 > "Leave every hope, ye who enter!", Dante Alighieri, Divine Comedy, The 
Inferno

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2004-10-12 05:26:53
Subject: Re: why my query is not using index??
Previous:From: Francisco ReyesDate: 2004-10-12 04:59:37
Subject: Re: Understanding explains

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group