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 95118329F15 for ; Mon, 11 Oct 2004 22:34:07 +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 78526-01 for ; Mon, 11 Oct 2004 21:33:56 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 7CE31329F2B for ; Mon, 11 Oct 2004 22:33:57 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i9BLXv1q023516; Mon, 11 Oct 2004 17:33:58 -0400 (EDT) To: Gabriele Bartolini Cc: pgsql-performance@postgresql.org Subject: Re: Normal case or bad query plan? In-reply-to: <6.1.2.0.2.20041011224414.01fadec0@box.tin.it> References: <6.1.2.0.2.20041011224414.01fadec0@box.tin.it> Comments: In-reply-to Gabriele Bartolini message dated "Mon, 11 Oct 2004 23:05:59 +0200" Date: Mon, 11 Oct 2004 17:33:57 -0400 Message-ID: <23515.1097530437@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/144 X-Sequence-Number: 8620 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. 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