Re: Simple postgresql.conf wizard

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:

> Your factual comments are accurate, but for Josh's stated target of Data
> Warehousing, a stats target of 400 is not unreasonable in some cases.
> What you forget to mention is that sample size is also determined by
> stats target and for large databases this can be a more important
> consideration than the points you mention.

Even for data warehousing I would not recommend setting it as a *default*
statistics target, at least not without verifying that it doesn't cause any
problems.

I would certainly consider 400 reasonable for specific columns. But the
default statistics target controls how large a histogram to store for *every*
column. Even columns never used by any clauses or used by clauses which do not
have any indexes on them.

Actually a plausible argument could be made that for data warehousing
databases in particular large values of default_statistics_target are
especially damaging. Queries on these databases are likely to have a large
number of clauses which are not indexed and a large number of joins with
complex join clauses.

Not every data warehouse query runs for hours, what I'm afraid of is
potentially the first time someone pops up complaining how Postgres sucks
because it randomly takes minutes to plan their queries. Only to find it's
retrieving kilobytes of data from toasted statistics arrays and performing n^2
comparisons of that data.

> In any case, saying that somebody is certifiably insane in a public
> forum is at best questionable. I would like to see the comment
> withdrawn.

I'm not too nervous that Josh might have actually thought I thought he was
really insane. (Or for that matter that anyone else reading it might have
thought so.)

On the other hand what does occur to me in retrospect is that I regret that I
didn't think about how I was disparaging the importance of mental illness and
hope nobody took offense for that reason.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!