Re: Simple postgresql.conf wizard

On Thu, 13 Nov 2008, Josh Berkus wrote:

>> Even though we all agree default_statistics_target = 10 is too low,
>> proposing a 40X increase in the default value requires more evidence
>> than this.  In particular, the prospect of a 1600-fold increase in
>> the typical cost of eqjoinsel() is a mite scary.
>
> It's a *completely* acceptable tradeoff for a *data warehousing* database, 
> where queries take multiple seconds to execute even under the best plans ... 
> and minutes or hours for the worst.  And that's what I'm proposing a value of 
> 400 for

The idea that planning time is trivial compared with query runtime in a 
data warehouse application is certainly true.  I remain a bit concerned 
about making the target so large for everyone just because they picked 
that option though.  I'd hate to see somebody who doesn't quite understand 
what that term implies get their plan times exploding.

Since Josh's latest parameter model takes a database size as an input, 
perhaps a reasonable way to proceed here is to put the DW model into size 
tiers.  Something like this:

DW default_statistics_target:

db size        setting
<1GB        10
1GB-10GB    50
10GB-100GB    100
100GB-1TB    200
>1TB        400

Going along with my idea that this tool should produce a reasonable result 
with minimal data, I was thinking of making the database size default to 
10GB if there isn't any input given there.  That would give someone who 
specified DW but nothing else a result of 100, which seems a less 
controversial setting.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD