On Fri, May 22, 2009 at 10:38 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
> Greg Smith <gsmith@gregsmith.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