Re: Revisiting default_statistics_target

-----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.

Er...so why should we change our defaults to support data warehousing
users? Certainly the rest of the postgresql.conf settings don't attempt
to do that.

> The bump from 10 to 100 was supported by microbenchmarks that suggested it
> would be tolerable.

No, the 10 to 100 was supported by years of people working in the field who
routinely did that adjustment (and >100) and saw great gains. Also, as the one
who originally started the push to 100, my original goal was to get it over the
"magic 99" bump, at which the planner started acting very differently. This
caused a huge performance regression in one of the Postgres releases (don't
remember which one exactly), which severely impacted one of our large clients.

> 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. :)

> 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.

> 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.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation
PGP Key: 0x14964AC8 200905221239
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkoW1iEACgkQvJuQZxSWSsh1gACgqHBcwEd0zLsfbZJvCnXywlGp
jZ8AoNn79heFG+iLE2uh6eZ0lxRmwuHR
=/A/F
-----END PGP SIGNATURE-----