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

On Thu, Nov 27, 2008 at 6:46 PM, Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
>> ANALYZE with default_statistics_target set to 10 takes 13 s. With
>> 100, 92 s. With 1000, 289 s.
>
> That is interesting. It would also be interesting to total up the time it
> takes to run EXPLAIN (without ANALYZE) for a large number of queries.

OK, I did this. I actually tried 10 .. 100 in increments of 10 and
then 100 ... 1000 in increments of 50, for 7 different queries of
varying complexity (but all generally similar, including all of them
having LIMIT 100 as is typical for this database). I planned each
query 100 times with each default_statistics_target. The results were
somewhat underwhelming.

The query which was by far the most complex, slowest, and most
expensive to plan took 2% longer to plan with
default_statistics_target = 1000 vs. default_statistics_target = 10
(284 ms vs. 279 ms). The average of the remaining 6 queries was a 12%
increase in planning time (17.42 ms vs. 15.47 ms).

The ANALYZE times as a function of default_statistics_target were:

10 13.030161
20 22.523386
30 32.38686
40 42.200557
50 51.280172
60 60.510998
70 69.319333
80 77.942732
90 85.96144
100 93.235432
150 120.251883
200 131.297581
250 142.410084
300 152.763004
350 164.222845
400 175.989113
450 186.762032
500 199.075595
550 210.241334
600 224.366766
650 233.036997
700 240.685552
750 249.516471
800 259.522957
850 268.19841
900 277.227745
950 290.639858
1000 297.099143

I'm attaching the test harness in case anyone wants to try this out
with their own queries (disclaimer: this is pretty quick and dirty -
it expects the input to be in files called q1.txt through q7.txt and
you have to edit the code to change that, or, uh, anything else).
Obviously these queries aren't very interesting in terms of d_s_t;
maybe someone has some where it makes more of a difference.

...Robert