Re: Normal case or bad query plan? - Mailing list pgsql-performance

From Tom Lane
Subject Re: Normal case or bad query plan?
Date
Msg-id 23515.1097530437@sss.pgh.pa.us
Whole thread Raw
In response to Normal case or bad query plan?  (Gabriele Bartolini <angusgb@tin.it>)
Responses Re: Normal case or bad query plan?  (Gabriele Bartolini <angusgb@tin.it>)
List pgsql-performance
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.  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.

(BTW, have you looked at the inet datatype to see if that would fit your
needs?)

            regards, tom lane

pgsql-performance by date:

Previous
From: Janning Vygen
Date:
Subject: Re: why my query is not using index??
Next
From: Rosser Schwarz
Date:
Subject: Re: Understanding explains