Re: Revisiting default_statistics_target - Mailing list pgsql-hackers
From | Jignesh K. Shah |
---|---|
Subject | Re: Revisiting default_statistics_target |
Date | |
Msg-id | 4A16E50C.4030701@sun.com Whole thread Raw |
In response to | Re: Revisiting default_statistics_target ("Greg Sabino Mullane" <greg@turnstep.com>) |
List | pgsql-hackers |
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 greg@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
pgsql-hackers by date: