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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD