Re: Revisiting default_statistics_target

Greg Smith <gsmith(at)gregsmith(dot)com> writes:
> Yesterday Jignesh Shah presented his extensive benchmark results comparing
> 8.4-beta1 with 8.3.7 at PGCon:
> http://blogs.sun.com/jkshah/entry/pgcon_2009_performance_comparison_of

> 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 remainder
> was from the constraint_exclusion change. That 80/20 proportion was
> mentioned in the talk but not in the slides. Putting both those back to
> the 8.3 defaults swapped things where 8.4b1 was ahead by 5% instead.

Yeah, I saw that talk and I'm concerned too, but I think it's premature
to conclude that the problem is precisely that stats_target is now too
high. I'd like to see Jignesh check through the individual queries in
the test and make sure that none of them had plans that changed for the
worse. The stats change might have just coincidentally tickled some
other planning issue.

Assuming that we do confirm that the hit comes from extra stats
processing time during planning, I'd still not want to go all the way
back to 10 as default. Perhaps we'd end up compromising at something
like 50.

regards, tom lane