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 C0E735394B; Tue, 26 Apr 2005 18:06:18 -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 18991-01; Tue, 26 Apr 2005 21:06:15 +0000 (GMT) Received: from cmailm4.svr.pol.co.uk (cmailm4.svr.pol.co.uk [195.92.193.211]) by svr1.postgresql.org (Postfix) with ESMTP id 549A1534C4; Tue, 26 Apr 2005 18:06:14 -0300 (ADT) Received: from modem-581.leopard.dialup.pol.co.uk ([217.135.146.69] helo=192.168.0.102) by cmailm4.svr.pol.co.uk with esmtp (Exim 4.41) id 1DQXFg-0002ht-Oq; Tue, 26 Apr 2005 22:05:53 +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: <24355.1114463455@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> <1114454941.21529.245.camel@localhost.localdomain> <24355.1114463455@sss.pgh.pa.us> Content-Type: text/plain Organization: 2nd Quadrant Date: Tue, 26 Apr 2005 22:02:31 +0100 Message-Id: <1114549351.21529.363.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-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200504/708 X-Sequence-Number: 12105 On Mon, 2005-04-25 at 17:10 -0400, Tom Lane wrote: > Simon Riggs writes: > > On Mon, 2005-04-25 at 11:23 -0400, Tom Lane wrote: > >> 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). > > The context here is a case with a very large number of distinct > values... Yes, but is there another way of doing this other than sampling a larger proportion of the table? I don't like that answer either, for the reasons you give. The manual doesn't actually say this, but you can already alter the sample size by setting one of the statistics targets higher, but all of those samples are fixed sample sizes, not a proportion of the table itself. It seems reasonable to allow an option to scan a higher proportion of the table. (It would be even better if you could say "keep going until you run out of memory, then stop", to avoid needing to have an external sort mode added to ANALYZE). Oracle and DB2 allow a proportion of the table to be specified as a sample size during statistics collection. IBM seem to be ignoring their own research note on estimating ndistinct... > keep in mind also that we have to do this for *all* the > columns of the table. You can collect stats for individual columns. You need only use an option to increase sample size when required. Also, if you have a large table and the performance of ANALYZE worries you, set some fields to 0. Perhaps that should be the default setting for very long text columns, since analyzing those doesn't help much (usually) and takes ages. (I'm aware we already don't analyze var length column values > 1024 bytes). > A full-table scan for each column seems > right out to me. Some systems analyze multiple columns simultaneously. Best Regards, Simon Riggs