pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Browne <cbbrowne@libertyrms.info>
To: pgsql-performance@postgresql.org
Subject: Re: [GENERAL] how to get accurate values in pg_statistic (continued)
Date: Wed, 10 Sep 2003 18:22:04 -0400
Message-ID: <60znhcxp0z.fsf@dev6.int.libertyrms.info> (raw)
References: <11537.1062717375@sss.pgh.pa.us>
	<200309101944.h8AJiod26547@candle.pha.pa.us>

pgman@candle.pha.pa.us (Bruce Momjian) writes:
> Tom Lane wrote:
>> Mary Edie Meredith <maryedie@osdl.org> writes:
>> > Stephan Szabo kindly responded to our earlier queries suggesting
>> > we look at default_statistics_target and ALTER TABLE ALTER COLUMN
>> > SET STATISTICS.
>> 
>> > These determine the number of bins in the histogram for a given
>> > column.  But for a large number of rows (for example 6 million)
>> > the maximum value (1000) does not guarantee that ANALYZE will do
>> > a full scan of the table.  We do not see a way to guarantee the
>> > same statistics run to run without forcing ANALYZE to examine
>> > every row of every table.
>> 
>> Do you actually still have a problem with the plans changing when
>> the stats target is above 100 or so?  I think the notion of "force
>> ANALYZE to do a full scan" is inherently wrongheaded ... it
>> certainly would not produce numbers that have anything to do with
>> ordinary practice.
>> 
>> If you have data statistics that are so bizarre that the planner
>> still gets things wrong with a target of 1000, then I'd like to
>> know more about why.
>
> Has there been any progress in determining if the number of default
> buckets (10) is the best value?

I would think this is much more the key to the issue for their
benchmark than issues of correctly replicating the random number
generator.

I'm not clear on how data is collected into the histogram bins;
obviously it's not selecting all 6 million rows, but how many rows is
it?

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
 b) Attaching an appropriate sample size to those bins.

What is apparently going wrong with the benchmark (and this can
doubtless arise in "real life," too) is that the random selection is
pulling too few records with the result that some of the bins are
being filled in a "skewed" manner that causes the optimizer to draw
the wrong conclusions.  (I may merely be restating the obvious here,
but if I say it a little differently than it has been said before,
someone may notice the vital "wrong assumption.")

If the samples are crummy, then perhaps:
 - There need to be more bins
 - There need to be more samples

Does the sample size change if you increase the number of bins?  If
not, then having more, smaller bins will lead to them getting
increasingly skewed if there is any accidental skew in the selection.

Do we also need a parameter to control sample size?
-- 
output = reverse("ofni.smrytrebil" "@" "enworbbc")
<http://dev6.int.libertyrms.com/;
Christopher Browne
(416) 646 3304 x124 (land)



view thread (22+ messages)  latest in thread

Message-ID: <60znhcxp0z.fsf@dev6.int.libertyrms.info>
Permalink:  ../60znhcxp0z.fsf@dev6.int.libertyrms.info/
Also on:    postgresql.org/message-id/60znhcxp0z.fsf@dev6.int.libertyrms.info

 · 

reply

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 (continued)
  In-Reply-To: <60znhcxp0z.fsf@dev6.int.libertyrms.info>
  References: <11537.1062717375@sss.pgh.pa.us> <200309101944.h8AJiod26547@candle.pha.pa.us> <60znhcxp0z.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