pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mary Edie Meredith <maryedie@osdl.org>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Cc: osdldbt-general <osdldbt-general@lists.sourceforge.net>
Subject: Re: [osdldbt-general] Re: [GENERAL] how to get accurate
Date: 10 Sep 2003 17:17:15 -0700
Message-ID: <1063239434.14271.8130.camel@ibm-e.pdx.osdl.net> (raw)
In-Reply-To: <200309101944.h8AJiod26547@candle.pha.pa.us>
References: <200309101944.h8AJiod26547@candle.pha.pa.us>

We tried 1000 as the default and found that the plans were good plans
and were consistent, but the pg_statistics was not exactly the same.

We took Tom's' advice and tried SET SEED=0 (actually select setseed (0)
).

We did runs last night on our project machine which produced consistent
pg_statistics data and (of course) the same plans.

We will next try runs where we vary the default buckets.  Other than 10
and 1000, what numbers would you like us to try besides.  Previously the
number 100 was mentioned.  Are there others?

On Wed, 2003-09-10 at 12:44, Bruce Momjian wrote:
> 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?
-- 
Mary Edie Meredith <maryedie@osdl.org>
Open Source Development Lab




view thread (2+ messages)  latest in thread

Message-ID: <1063239434.14271.8130.camel@ibm-e.pdx.osdl.net>
Permalink:  ../1063239434.14271.8130.camel@ibm-e.pdx.osdl.net/
Also on:    postgresql.org/message-id/1063239434.14271.8130.camel@ibm-e.pdx.osdl.net

 · 

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: maryedie@osdl.org, pgman@candle.pha.pa.us, tgl@sss.pgh.pa.us, osdldbt-general@lists.sourceforge.net
  Subject: Re: [osdldbt-general] Re: [GENERAL] how to get accurate
  In-Reply-To: <1063239434.14271.8130.camel@ibm-e.pdx.osdl.net>
  References: <200309101944.h8AJiod26547@candle.pha.pa.us> <1063239434.14271.8130.camel@ibm-e.pdx.osdl.net>

* 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