Re: Revisiting default_statistics_target

On Fri, 2009-05-22 at 16:43 +0000, Greg Sabino Mullane wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>
> > While most cases were dead even or a modest improvement, his dbt-2 results
> > suggest a 15-20% regression in 8.4. Changing the default_statistics_taget
> > to 100 was responsible for about 80% of that regression.
> ...
> > The situation where the stats target being so low hurts things the most
> > are the data warehouse use cases.

Nor is it our primary user base. If we want to do this we need to have
more than one conf as a tmpl.

> > That doesn't seem to be reality here though, and it's questionable whether
> > this change really helps the people who need to fool with the value the most.
>
> The goal of defaults is not to help people who fool with the value - it's to
> get a good default out of the box for people who *don't* fool with all the
> values. :)

Right. If someone is really doing a DW they are already spending time
with the postgresql.conf.

> > But unless someone has some compelling evidence to the contrary, it looks like
> > the stats target needs to go back to a lower value.
>
> Please don't. This is a very good change, and I don't see why changing it back
> because it might hurt people doing DW is a good thing, when most of users are
> not doing DW.

I haven't seen any evidence to suggest that Jignesh's findings provide
anything but a single data point in a vast metric of our smallest user
base. Reverting a value based on that seems like a mistake.

>
> > As for the change to constraint_exclusion, the regression impact there is
> > much less severe and the downside of getting it wrong is pretty bad.
>
> Similarly, the people who are affected by something like presumably are not
> running a default postgresql.conf anyway, so they can toggle it back to squeeze
> a little more performance out of their system.
>

Right.

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997