Re: Simple postgresql.conf wizard

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.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD