Re: default statistics target testing (was: Simple postgresql.conf wizard)

On Fri, Dec 5, 2008 at 2:16 PM, Guillaume Smet <guillaume(dot)smet(at)gmail(dot)com> wrote:
> On Fri, Dec 5, 2008 at 8:11 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> Correct. As compared with 8.2.9, ANALYZE was substantially faster,
>> but query planning was significantly slower.
>>
>> Thanks also to Greg for reposting my emails. I didn't even realize I
>> hadn't sent them to the list.
>
> Any chance you could do the same test with a 8.3? It could be
> interesting to see if it's a HEAD thing or if the slow down was
> introduced in 8.3.

OK, here are the results from 8.2.9, 8.3.5, and CVS HEAD. A couple of
these numbers are slightly different than the ones I posted before due
to, uh, me having typo'd my awk script last time. However, the basic
picture remains unchanged. 8.3.5 is very similar to CVS HEAD; 8.2.9
plans faster but analyzes more slowly.

Q1

CVS HEAD 12/5/2008 default_statistics_target=10 367 ms
CVS HEAD 12/5/2008 default_statistics_target=1000 380 ms
8.3.5 default_statistics_target=10 367 ms
8.3.5 default_statistics_target=1000 379 ms
8.2.9 default_statistics_target=10 279 ms
8.2.9 default_statistics_target=1000 285 ms

Average of Q2-Q7

CVS HEAD 12/5/2008 default_statistics_target=10 18.14 ms
CVS HEAD 12/5/2008 default_statistics_target=1000 21.75 ms
8.3.5 default_statistics_target=10 18.26 ms
8.3.5 default_statistics_target=1000 21.35 ms
8.2.9 default_statistics_target=10 15.47 ms
8.2.9 default_statistics_target=1000 17.42 ms

ANALYZE

CVS HEAD 12/5/2008 default_statistics_target=10 4.283007
CVS HEAD 12/5/2008 default_statistics_target=1000 69.861321
8.3.5 default_statistics_target=10 4.052358
8.3.5 default_statistics_target=1000 65.12919
8.2.9 default_statistics_target=10 13.030161
8.2.9 default_statistics_target=1000 297.099143

....Robert