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 3453632A207 for ; Tue, 12 Oct 2004 13:21:13 +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 88801-06 for ; Tue, 12 Oct 2004 12:21:01 +0000 (GMT) Received: from hotmail.com (bay18-dav8.bay18.hotmail.com [65.54.187.188]) by svr1.postgresql.org (Postfix) with ESMTP id 91A2F32A1EB for ; Tue, 12 Oct 2004 13:21:00 +0100 (BST) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Tue, 12 Oct 2004 05:21:00 -0700 Received: from 67.81.98.198 by BAY18-DAV8.phx.gbl with DAV; Tue, 12 Oct 2004 12:20:46 +0000 X-Originating-IP: [67.81.98.198] X-Originating-Email: [awerman2@hotmail.com] X-Sender: awerman2@hotmail.com From: "Aaron Werman" To: "Kris Jurka" , "Gabriele Bartolini" Cc: References: <6.1.2.0.2.20041011224414.01fadec0@box.tin.it> Subject: Re: Normal case or bad query plan? Date: Tue, 12 Oct 2004 08:20:52 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Message-ID: X-OriginalArrivalTime: 12 Oct 2004 12:21:00.0672 (UTC) FILETIME=[EF4CE800:01C4B055] 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/160 X-Sequence-Number: 8636 Makes sense. See DB2 8.2 info on their new implementation of cross column statistics. If this is common and you're willing to change code, you can fake that by adding a operation index on some hash function of both columns, and search for both columns and the hash. ----- Original Message ----- From: "Kris Jurka" To: "Gabriele Bartolini" Cc: Sent: Monday, October 11, 2004 5:17 PM Subject: Re: [PERFORM] Normal case or bad query plan? > > > On Mon, 11 Oct 2004, Gabriele Bartolini wrote: > > > -------------------------------------------------------------------------- ------------------------------------------- > > 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 > > > > I believe the problem is that pg's lack of cross-column statistics is > producing the poor number of rows estimate. The number of rows mataching > just the first 1040878301::bigint >= ip_address_from condition is 122774 > which is roughtly 10% of the table. I imagine the query planner > believes that the other condition alone will match the other 90% of the > table. The problem is that it doesn't know that these two ranges' > intersection is actually tiny. The planner assumes a complete or nearly > complete overlap so it thinks it will need to fetch 10% of the rows from > both the index and the heap and chooses a seqscan. > > Kris Jurka > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >