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

On Thu, Nov 27, 2008 at 6:46 PM, Gregory Stark <stark@enterprisedb.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