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 CAD8AD1B528 for ; Thu, 11 Sep 2003 17:38:23 +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 78902-06 for ; Thu, 11 Sep 2003 14:38:14 -0300 (ADT) Received: from news.hub.org (unknown [64.117.224.194]) by svr1.postgresql.org (Postfix) with ESMTP id 09363D1B537 for ; Thu, 11 Sep 2003 14:38:13 -0300 (ADT) Received: from news.hub.org (host-64-117-224-194.altec1.com [64.117.224.194] (may be forged)) by news.hub.org (8.12.9/8.12.9) with ESMTP id h8BHcCsl098380 for ; Thu, 11 Sep 2003 17:38:12 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id h8BHA2Pf090761 for pgsql-performance@postgresql.org; Thu, 11 Sep 2003 17:10:02 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: [GENERAL] how to get accurate values in pg_statistic Date: Thu, 11 Sep 2003 12:32:01 -0400 Organization: Hub.Org Networking Services Lines: 49 Message-ID: <60he3jwake.fsf@dev6.int.libertyrms.info> References: <21962.1063254659@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1003 (Gnus v5.10.3) XEmacs/21.4 (Rational FORTRAN, linux) Cancel-Lock: sha1:wAtzRsG9N4xJfnmez4OypNB5S7M= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/192 X-Sequence-Number: 3558 scott.marlowe@ihs.com ("scott.marlowe") writes: > On Thu, 11 Sep 2003, Tom Lane wrote: > >> Christopher Browne writes: >> > The "right answer" for most use seems likely to involve: >> > a) Getting an appropriate number of bins (I suspect 10 is a bit >> > small, but I can't justify that mathematically), and >> >> I suspect that also, but I don't have real evidence for it either. >> We've heard complaints from a number of people for whom it was indeed >> too small ... but that doesn't prove it's not appropriate in the >> majority of cases ... >> >> > Does the sample size change if you increase the number of bins? >> >> Yes, read the comments in backend/commands/analyze.c. >> >> > Do we also need a parameter to control sample size? >> >> Not if the paper I read before writing that code is correct. > > I was just talking to a friend of mine who does statistical analysis, and > he suggested a different way of looking at this. I know little of the > analyze.c, but I'll be reading it some today. > > His theory was that we can figure out the number of target bins by > basically running analyze twice with two different random seeds, and > initially setting the bins to 10. > > The, compare the variance of the two runs. If the variance is great, > increase the target by X, and run two again. repeat, wash, rinse, until > the variance drops below some threshold. > > I like the idea, I'm not at all sure if it's practical for Postgresql to > implement it. It may suffice to do some analytic runs on some "reasonable datasets" in order to come up with a better default than 10. If you run this process a few times on some different databases and find that the variance keeps dropping pretty quickly, then that would be good material for arguing that 10 should change to 17 or 23 or 31 or some such value. (The only interesting pttern in that is that those are all primes :-).) -- output = ("cbbrowne" "@" "libertyrms.info") Christopher Browne (416) 646 3304 x124 (land)