X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BE35F329F0D for ; Tue, 12 Oct 2004 06:26:29 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 92567-06 for ; Tue, 12 Oct 2004 05:26:19 +0000 (GMT) Received: from vsmtp4.tin.it (unknown [212.216.176.150]) by svr1.postgresql.org (Postfix) with ESMTP id 7CF2D329F33 for ; Tue, 12 Oct 2004 06:26:17 +0100 (BST) Received: from angus.tin.it (82.53.64.64) by vsmtp4.tin.it (7.0.027) id 41568C9D006D2FD9; Tue, 12 Oct 2004 07:26:09 +0200 Message-Id: <6.1.2.0.2.20041012072112.01ed8810@box.tin.it> X-Sender: angusgb@box.tin.it (Unverified) X-Mailer: QUALCOMM Windows Eudora Version 6.1.2.0 Date: Tue, 12 Oct 2004 07:26:12 +0200 To: Tom Lane From: Gabriele Bartolini Subject: Re: Normal case or bad query plan? Cc: pgsql-performance@postgresql.org 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> Mime-Version: 1.0 Content-Type: multipart/mixed; x-avg-checked=avg-ok-2C527FEE; boundary="=======636B5308=======" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_RFCI X-Spam-Level: X-Archive-Number: 200410/155 X-Sequence-Number: 8631 --=======636B5308======= Content-Type: text/plain; x-avg-checked=avg-ok-2C527FEE; charset=us-ascii; format=flowed Content-Transfer-Encoding: 8bit Hi Tom, thanks for your interest. At 23.33 11/10/2004, Tom Lane wrote: >Gabriele Bartolini 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 --=======636B5308======= Content-Type: text/plain; charset=us-ascii; x-avg=cert; x-avg-checked=avg-ok-2C527FEE Content-Disposition: inline --- 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 --=======636B5308=======--