Re: Revisiting default_statistics_target

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

--
Jignesh Shah http://blogs.sun.com/jkshah
The New Sun Microsystems,Inc http://sun.com/postgresql