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.)