X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 372C4D1B981 for ; Sat, 30 Aug 2003 13:45:50 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 34785-01 for ; Sat, 30 Aug 2003 10:45:40 -0300 (ADT) Received: from www.postgresql.com (unknown [64.117.225.209]) by svr1.postgresql.org (Postfix) with ESMTP id 3B355D1B8BA for ; Sat, 30 Aug 2003 10:45:39 -0300 (ADT) Received: from mail.speakeasy.net (mail13.speakeasy.net [216.254.0.213]) by www.postgresql.com (Postfix) with ESMTP id 03A24CF7A60 for ; Sat, 30 Aug 2003 04:39:14 -0300 (ADT) Received: (qmail 18070 invoked from network); 30 Aug 2003 07:38:14 -0000 Received: from unknown (HELO speakeasy.org) (kgeis@[66.93.130.165]) (envelope-sender ) by mail13.speakeasy.net (qmail-ldap-1.03) with AES256-SHA encrypted SMTP for ; 30 Aug 2003 07:38:14 -0000 Message-ID: <3F505465.6010504@speakeasy.org> Date: Sat, 30 Aug 2003 00:38:13 -0700 From: Ken Geis User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4) Gecko/20030612 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Bruno Wolff III Cc: pgsql-performance@postgresql.org Subject: Re: bad estimates References: <20030829025109.GD4373@wolff.to> <3F4EBF7E.8020602@speakeasy.org> <20030829030156.GF4373@wolff.to> <3F4EC1D0.5010503@speakeasy.org> <20030829033818.GA5577@wolff.to> <3F4ECC78.7090502@speakeasy.org> <20030829040519.GA5746@wolff.to> <3F4ED1DC.50808@speakeasy.org> <20030829042453.GA5931@wolff.to> <3F4F0777.8090503@speakeasy.org> <20030830025503.GA30691@wolff.to> In-Reply-To: <20030830025503.GA30691@wolff.to> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/527 X-Sequence-Number: 3343 Bruno Wolff III wrote: > I haven't come up with any great ideas for this one. It might be interesting > to compare the explain analyze output from the distinct on query with > and without seqscans enabled. After digging through planner code, I found that bumping up the sort_mem will make the planner prefer a full table scan and hashed aggregation. The sort memory is where the hash table is stored. In the end, the query runs in 4.5 minutes, which is reasonable. I had planned to try Manfred's index correlation patch to see if it would give better estimates for an index scan. The index scan method took maybe 6.5x as long, but the estimate was that it would take 1400x as long. I think instead of trying out his patch I might actually work on my application! Ken