Re: Simple postgresql.conf wizard

On Mon, Dec 1, 2008 at 9:32 PM, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:
> On Mon, 1 Dec 2008, Mark Wong wrote:
>
>> So then I attempted to see if there might have been difference between the
>> executing time of each individual query with the above parameters. The
>> queries that don't seem to be effected are Q1, Q4, Q12, Q13, and Q15. Q17
>> suggests that anything higher than default_statistics_target=10 is an
>> improvement. The rest of the queries appears not to follow any particular
>> trend with respect to default_statistics_target.
>
> The interesting ones are Q2, Q9, Q17, Q18, and Q20, and that data is much
> more useful than the summary. As you mention, Q17 improves significantly
> with a higher target. All of the rest are dramatically slower in one or
> both tests going from default_statistics_target=10 to 100. Those look like
> the most useful data points on the X axis--the increases from 100 up to 1000
> aren't particularly interesting in most of these, except in Q20 where the
> "Power Test" seems to oscillate between degrees of good and bad behavior
> seemingly at random.
>
> My picks for the most useful graphs from the long list Mark sent:
>
> http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q2.png
> http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q9.png
> http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q17.png
> http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q18.png
> http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q20.png
>
> I think the tests you could consider next is to graph the target going from
> 10 to 100 in steps of 10 just for those 5 queries. If it gradually
> degrades, that's interesting but hard to nail down. But if there's a sharp
> transition, getting an explain plan for the two sides of that should provide
> some insight. I'm really more interested in the ones that slowed down than
> the one that improved, understanding that might finally provide some
> evidence against increasing it by default.

I've updated the charts to include results from setting
default_statistics_target from 20-90. The links to the charts are the
same. The links to the raw data are in
http://207.173.203.223/~markwkm/community6/dbt3/ in directories 45-52.

Regards,
Mark