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.
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 test that was greatly impacted is DBT-2 (OLTP Benchmark) with 8.4
defaults but seems to work fine/better when reverted to use 8.3
defaults. The 8.4 defaults seemed to improve DBT-3 (Data Warehousing)
though I haven't retested them with 8.3 defaults in 8.4. Of course I
am not a big fan of DBT-2 myself and I am just providing datapoints of
what I observed during my testing with various workloads. I certainly
don't claim to understand what is happening (yet).
-Jignesh
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 g...@turnstep.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-----
--
Jignesh Shah http://blogs.sun.com/jkshah
The New Sun Microsystems,Inc http://sun.com/postgresql
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
