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 BCB1D54665; Mon, 25 Apr 2005 15:53:11 -0300 (ADT) 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 73345-07; Mon, 25 Apr 2005 18:52:18 +0000 (GMT) Received: from cmailg1.svr.pol.co.uk (cmailg1.svr.pol.co.uk [195.92.195.171]) by svr1.postgresql.org (Postfix) with ESMTP id 873B053AA4; Mon, 25 Apr 2005 15:52:17 -0300 (ADT) Received: from modem-1679.tiger.dialup.pol.co.uk ([62.136.214.143] helo=192.168.0.102) by cmailg1.svr.pol.co.uk with esmtp (Exim 4.41) id 1DQ8gm-0001Ai-00; Mon, 25 Apr 2005 19:52:12 +0100 Subject: Re: [HACKERS] Bad n_distinct estimation; hacks suggested? From: Simon Riggs To: Tom Lane Cc: josh@agliodbs.com, Greg Stark , Marko Ristola , pgsql-perform , pgsql-hackers@postgresql.org In-Reply-To: <19276.1114442580@sss.pgh.pa.us> References: <200504191209.05181.josh@agliodbs.com> <200504221336.08325.josh@agliodbs.com> <8764ydapky.fsf@stark.xeocode.com> <200504231639.11897.josh@agliodbs.com> <1114419467.21529.210.camel@localhost.localdomain> <19276.1114442580@sss.pgh.pa.us> Content-Type: text/plain Organization: 2nd Quadrant Date: Mon, 25 Apr 2005 19:49:01 +0100 Message-Id: <1114454941.21529.245.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 (2.0.2-3) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200504/679 X-Sequence-Number: 12076 On Mon, 2005-04-25 at 11:23 -0400, Tom Lane wrote: > Simon Riggs writes: > > My suggested hack for PostgreSQL is to have an option to *not* sample, > > just to scan the whole table and find n_distinct accurately. > > ... > > What price a single scan of a table, however large, when incorrect > > statistics could force scans and sorts to occur when they aren't > > actually needed ? > > It's not just the scan --- you also have to sort, or something like > that, if you want to count distinct values. I doubt anyone is really > going to consider this a feasible answer for large tables. Assuming you don't use the HashAgg plan, which seems very appropriate for the task? (...but I understand the plan otherwise). If that was the issue, then why not keep scanning until you've used up maintenance_work_mem with hash buckets, then stop and report the result. The problem is if you don't do the sort once for statistics collection you might accidentally choose plans that force sorts on that table. I'd rather do it once... The other alternative is to allow an ALTER TABLE command to set statistics manually, but I think I can guess what you'll say to that! Best Regards, Simon Riggs