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 A694753F9C; Wed, 27 Apr 2005 10:44:09 -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 59155-01; Wed, 27 Apr 2005 13:43:59 +0000 (GMT) Received: from trolak.mydnsbox2.com (ns1.mydnsbox2.com [207.44.142.118]) by svr1.postgresql.org (Postfix) with ESMTP id 025AE53F7A; Wed, 27 Apr 2005 10:43:58 -0300 (ADT) Received: from [192.168.1.103] (cpe-024-211-165-134.nc.res.rr.com [24.211.165.134]) (authenticated (0 bits)) by trolak.mydnsbox2.com (8.11.6/8.11.6) with ESMTP id j3RCt7u26816; Wed, 27 Apr 2005 07:55:07 -0500 Message-ID: <426F9703.4010108@dunslane.net> Date: Wed, 27 Apr 2005 09:43:31 -0400 From: Andrew Dunstan User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.7) Gecko/20050417 Fedora/1.7.7-1.3.1 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Mischa Sandberg Cc: josh@agliodbs.com, pgsql-perform , pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Bad n_distinct estimation; hacks suggested? References: <200504191209.05181.josh@agliodbs.com> <19276.1114442580@sss.pgh.pa.us> <1114454941.21529.245.camel@localhost.localdomain> <200504251213.18565.josh@agliodbs.com> <426D565E.8040400@dunslane.net> <1114580284.426f253cc0087@webmail.telus.net> In-Reply-To: <1114580284.426f253cc0087@webmail.telus.net> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.023 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/764 X-Sequence-Number: 12161 Mischa Sandberg wrote: > >Perhaps I can save you some time (yes, I have a degree in Math). If I >understand correctly, you're trying extrapolate from the correlation >between a tiny sample and a larger sample. Introducing the tiny sample >into any decision can only produce a less accurate result than just >taking the larger sample on its own; GIGO. Whether they are consistent >with one another has no relationship to whether the larger sample >correlates with the whole population. You can think of the tiny sample >like "anecdotal" evidence for wonderdrugs. > > > Ok, good point. I'm with Tom though in being very wary of solutions that require even one-off whole table scans. Maybe we need an additional per-table statistics setting which could specify the sample size, either as an absolute number or as a percentage of the table. It certainly seems that where D/N ~ 0.3, the estimates on very large tables at least are way way out. Or maybe we need to support more than one estimation method. Or both ;-) cheers andrew