Re: Revisiting default_statistics_target

On Fri, May 22, 2009 at 10:38 AM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

I did a couple runs to see the effects on our Portland perf lab DL380 G5 system:

The scripts in the kit capture EXPLAIN (no ANALYZE) before and after
the test and running a diff doesn't show any changes. Someone will
have to run the queries by hand to get the EXPLAIN ANALYZE data:

New 8.4 Defaults (constraint_exclusion=partition,
default_statistics_target=100):

report:

http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.j.2/report/

Plans:
http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.j.2/db/plan0.out
http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.j.2/db/plan1.out

With constraint_exclusion=off, default_statistics_target=10:

http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.j.1/report/

Plans:
http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.j.1/db/plan0.out
http://207.173.203.223/~markwkm/community6/dbt2/m1500-8.4beta2/m1500.8.4beta2.j.1/db/plan1.out

On the DL380 GB system, where I'm using a lot more drives the Jignesh,
I see a performance change of under 5%. 15651.14 notpm vs 16333.32
notpm. And this is after a bit of tuning, not sure how much the out
of the box experience changes on this system.

Now if only I couldn't figure out why oprofile doesn't like this system...

Regards.
Mark Wong