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 ABD95D1B8A4 for ; Sat, 30 Aug 2003 05:05:51 +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 95889-07 for ; Sat, 30 Aug 2003 02:05:21 -0300 (ADT) Received: from mail.speakeasy.net (mail13.speakeasy.net [216.254.0.213]) by svr1.postgresql.org (Postfix) with ESMTP id 2EF4DD1B550 for ; Sat, 30 Aug 2003 02:05:19 -0300 (ADT) Received: (qmail 22523 invoked from network); 30 Aug 2003 05:05:18 -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 05:05:18 -0000 Message-ID: <3F50308E.6070100@speakeasy.org> Date: Fri, 29 Aug 2003 22:05:18 -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/521 X-Sequence-Number: 3337 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. Can't do that comparison. Remember, with seqscan it fails. (Oh, and that nested loops solution I thought was fast actually took 31 minutes versus 29 for index scan in 7.4b2.) I ran another query across the same data: select price_date, count(*) from day_ends group by price_date; It used a table scan and hashed aggregates, and it ran in 5.5 minutes. Considering that, pgsql should be able to do the query that I had been running in a little more time than that. So... From what I've learned, we want to convince the optimizer to use a table scan; that's a good thing. I want it to use hashed aggregates, but I can't convince it to (unless maybe I removed all of the statistics.) To use group aggregates, it first sorts the results of the table scan (all 17 million rows!) There ought to be some way to tell pgsql not to do sorts above a certain size. In this case, if I set enable_sort=false, it goes back to the index scan. If I then set enable_indexscan=false, it goes back to sorting.