From: Christopher Browne <cbbrowne@libertyrms.info>
To: pgsql-performance@postgresql.org
Subject: Re: [GENERAL] how to get accurate values in pg_statistic
Date: Thu, 11 Sep 2003 12:32:01 -0400
Message-ID: <60he3jwake.fsf@dev6.int.libertyrms.info> (raw)
References: <21962.1063254659@sss.pgh.pa.us>
<Pine.LNX.4.33.0309110953090.18742-100000@css120.ihs.com>
scott.marlowe@ihs.com ("scott.marlowe") writes:
> On Thu, 11 Sep 2003, Tom Lane wrote:
>
>> Christopher Browne <cbbrowne@libertyrms.info> 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")
<http://dev6.int.libertyrms.com/;
Christopher Browne
(416) 646 3304 x124 (land)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: cbbrowne@libertyrms.info
Subject: Re: [GENERAL] how to get accurate values in pg_statistic
In-Reply-To: <60he3jwake.fsf@dev6.int.libertyrms.info>
References: <21962.1063254659@sss.pgh.pa.us> <Pine.LNX.4.33.0309110953090.18742-100000@css120.ihs.com> <60he3jwake.fsf@dev6.int.libertyrms.info>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox