Date: Mon, 09 Jun 2008 10:45:00 -0700
From: Josh Berkus <josh@agliodbs.com>
Subject: Re: Overhauling GUCS
In-reply-to: <87hcc3le52.fsf@oxford.xeocode.com>
To: Gregory Stark <stark@enterprisedb.com>
Cc: "Joshua D. Drake" <jd@commandprompt.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	Robert Treat <xzilla@users.sourceforge.net>,
	pgsql-hackers@postgresql.org, Peter Eisentraut <peter_e@gmx.net>,
	Greg Smith <gsmith@gregsmith.com>,
	Andreas Pflug <pgadmin@pse-consulting.de>, Decibel! <decibel@decibel.org>
Message-id: <484D6C1C.5070004@agliodbs.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=UTF-8
Content-transfer-encoding: 7BIT
References: <48409D1E.3070208@agliodbs.com> <29525.1212613052@sss.pgh.pa.us>
	<200806061435.01196.peter_e@gmx.net>
	<200806061624.29105.xzilla@users.sourceforge.net>
	<8358.1212797962@sss.pgh.pa.us> <1212800891.14299.35.camel@jd-laptop>
	<87hcc3le52.fsf@oxford.xeocode.com>
User-Agent: Thunderbird 2.0.0.12 (Macintosh/20080213)

Tom,

>>> Actually, the reason it's still 10 is that the effort expended to get it
>>> changed has been *ZERO*.  I keep asking for someone to make some
>>> measurements, do some benchmarking, anything to make a plausible case
>>> for a specific higher value as being a reasonable place to set it.
>>> The silence has been deafening.
>> Not surprising really. It is a simple adjustment to make and it also is
>> easy to spot when its a problem. However it is not trivial to test for
>> (in terms of time and effort). I know 10 is wrong and so do you. If you
>> don't I am curious why I see so many posts from you saying, "Your
>> estimates are off, what is your default_statistics_target?" with yet
>> even more responses saying, "Uhh 10." 

I tried (back in 7.4) to do some systematic testing of this.  The 
problem is that the cases were higher d_s_t are required are 
specifically ones with complex, unbalanced data distributions and/or 
very large databases.  This makes test cases extremely difficult and 
time-consuming to generate; further, I found that the test cases I had 
from my clients' databases were not portable (in addition to being 
confidential).

Also, I'd actually assert that "10" seems to be perfectly adequate for 
the majority of users.  That is, the number of users where I've 
recommended increasing d_s_t for the whole database is smaller than the 
number where I don't, and of course we never hear from most users at 
all.  So I'm pretty happy recommending "Leave the default.  If you 
encounter problem queries, increase it to 100, and analyse the database. 
  If you're running a data warehouse, increase it to 1000."

Where analyze does systematically fall down is with databases over 500GB 
in size, but that's not a function of d_s_t but rather of our tiny 
sample size.

--Josh