[HACKERS] raising the default default_statistics_target

[Neil Conway]

From time to time, people on IRC ask for help with performance 
problems, and the cause of the difficulty is ultimately traced to a 
poor query plan that is chosen because default_statistics_target is 
too low. While there will always need to be *some* tuning of the 
statistics target by advanced users, I wanted to see what the 
performance penalty would be to increase the default stats target out 
of the box -- that way, more users will get good query plans without 
needing to manually tweak the configuration.

In the simple test I performed, raising the default_statistics_target 
from 10 to 25 resulted in a 40% increase in the time to ANALYZE a 
large table. (I picked 25 more or less at random -- would 15 or 20 be 
better?)

That's a larger hit than I was hoping to see; however, it can be 
argued that ANALYZE isn't really performance-critical anyway (since it 
doesn't hold the same kind of locks that VACUUM and especially VACUUM 
FULL hold). Also, I only have anecdotal evidence that this is actually 
a problem. It may also be the case that for those people for whom 10 
is an insufficient stats target, 25 is also insufficient.

Any comments on whether increasing the default stats target is a good 
idea for 7.5? (Details on the test I performed are included below)

-Neil

I created a 2.1 GB table with 3 columns (int, varchar, and float):

nconway=# select relpages from pg_class where relname = 'abc';
 relpages
----------
   279621
(1 row)
nconway=# select reltuples from pg_class where relname = 'abc';
  reltuples
-------------
 3.35545e+07
(1 row)
I tested two default_statistcs_target settings: 10 (the current 
default), and 25. The test machine is a P4 1.8 Ghz with 768 MB of RAM 
and a pretty mediocre 7200 RPM IDE disk running Linux 2.6.3. I 
rebooted the machine before and between tests.

ANALYZE w/ stats target = 10: 51.643 seconds
ANALYZE w/ stats target = 25: 71.969 seconds
(Additional tests performed w/o rebooting seem to be consistent with 
these numbers.)

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend