From: Josh Berkus <josh@agliodbs.com>
To: Gregory Stark <stark@enterprisedb.com>
Cc: Joshua D. Drake <jd@commandprompt.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Treat <xzilla@users.sourceforge.net>
Cc: pgsql-hackers@postgresql.org, Peter Eisentraut <peter_e@gmx.net>
Cc: Greg Smith <gsmith@gregsmith.com>
Cc: Andreas Pflug <pgadmin@pse-consulting.de>
Cc: Decibel! <decibel@decibel.org>
Subject: Re: Overhauling GUCS
Date: Mon, 09 Jun 2008 10:45:00 -0700
Message-ID: <484D6C1C.5070004@agliodbs.com> (raw)
In-Reply-To: <87hcc3le52.fsf@oxford.xeocode.com>
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>
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
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 using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: josh@agliodbs.com, stark@enterprisedb.com, jd@commandprompt.com, tgl@sss.pgh.pa.us, xzilla@users.sourceforge.net, peter_e@gmx.net, gsmith@gregsmith.com, pgadmin@pse-consulting.de, decibel@decibel.org
Subject: Re: Overhauling GUCS
In-Reply-To: <484D6C1C.5070004@agliodbs.com>
* 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