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 8038953554; Thu, 28 Apr 2005 12:21:47 -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 58578-06; Thu, 28 Apr 2005 15:21:44 +0000 (GMT) Received: from priv-edtnes51.telusplanet.net (outbound04.telus.net [199.185.220.223]) by svr1.postgresql.org (Postfix) with ESMTP id B539C529DF; Thu, 28 Apr 2005 12:21:40 -0300 (ADT) Received: from localhost ([199.185.220.240]) by priv-edtnes51.telusplanet.net (InterMail vM.6.01.04.04 201-2131-118-104-20050224) with ESMTP id <20050428152137.IFCN14099.priv-edtnes51.telusplanet.net@localhost>; Thu, 28 Apr 2005 09:21:37 -0600 Received: from 209.17.183.249 ( [209.17.183.249]) as user a3a18850@192.168.200.1 by webmail.telus.net with HTTP; Thu, 28 Apr 2005 08:21:36 -0700 Message-ID: <1114701696.4270ff80d577c@webmail.telus.net> Date: Thu, 28 Apr 2005 08:21:36 -0700 From: Mischa Sandberg To: Josh Berkus Cc: Andrew Dunstan , pgsql-perform , pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Bad n_distinct estimation; hacks suggested? References: <200504191209.05181.josh@agliodbs.com> <1114580284.426f253cc0087@webmail.telus.net> <426F9703.4010108@dunslane.net> <200504270825.17550.josh@agliodbs.com> In-Reply-To: <200504270825.17550.josh@agliodbs.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit User-Agent: Internet Messaging Program (IMP) 3.1-cvs X-Originating-IP: 209.17.183.249 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.082 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_POST, FORGED_RCVD_HELO X-Spam-Level: * X-Archive-Number: 200504/761 X-Sequence-Number: 12158 Quoting Josh Berkus : > > >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. > > Actually, it's more to characterize how large of a sample we need. For > example, if we sample 0.005 of disk pages, and get an estimate, and then > sample another 0.005 of disk pages and get an estimate which is not even > close to the first estimate, then we have an idea that this is a table which > defies analysis based on small samples. Wheras if the two estimates are < > 1.0 stdev apart, we can have good confidence that the table is easily > estimated. Note that this doesn't require progressively larger samples; any > two samples would work. We're sort of wandering away from the area where words are a good way to describe the problem. Lacking a common scratchpad to work with, could I suggest you talk to someone you consider has a background in stats, and have them draw for you why this doesn't work? About all you can get out of it is, if the two samples are disjunct by a stddev, yes, you've demonstrated that the union of the two populations has a larger stddev than either of them; but your two stddevs are less info than the stddev of the whole. Breaking your sample into two (or three, or four, ...) arbitrary pieces and looking at their stddevs just doesn't tell you any more than what you start with. -- "Dreams come true, not free." -- S.Sondheim, ITW