Hi Tom,
thanks for your interest.
At 23.33 11/10/2004, Tom Lane wrote:
Gabriele Bartolini <[EMAIL PROTECTED]> 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
[EMAIL PROTECTED] | 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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
