Greg Sabino Mullane <greg@turnstep.com> writes: > Per a recent bug in which the planner can behave very differently at < > 100, and accounting for the fact that analyze is still plenty fast on > today's systems even at a tenfold increase, attached is a patch to > change default_statistics_target from 10 to 100. This is not happening without a whole lot more evidence (as in, more than zero) to back up the choice of value. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend

-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 Simon spoke: > The choice of 100 is because of the way the LIKE estimator is > configured. Greg is not suggesting he measured it and found 100 to be > best, he is saying that the LIKE operator is hard-coded at 100 and so > the stats_target should reflect that. Exactly. > Setting it to 100 for all columns because of LIKE doesn't make much > sense. I think we should set stats target differently depending upon the > data type, but thats probably an 8.4 thing. Long text fields that might > use LIKE should be set to 100. CHAR(1) and general fields should be set > to 10. Agreed, this would be a nice 8.4 thing. But what about 8.3 and 8.2? Is there a reason not to make this change? I know I've been lazy and not run any absolute figures, but rough tests show that raising it (from 10 to 100) results in a very minor increase in analyze time, even for large databases. I think the burden of a slightly slower analyze time, which can be easily adjusted, both in postgresql.conf and right before running an analyze, is very small compared to the pain of some queries - which worked before - suddenly running much, much slower for no apparent reason at all. Sure, 100 may have been chosen somewhat arbitrarily for the LIKE thing, but this is a current real-world performance regression (aka a bug, according to a nearby thread). Almost everyone agrees that 10 is too low, so why not make it 100, throw a big warning in the release notes, and then start some serious re-evaluation for 8.4? - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200712050920 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFHVrSivJuQZxSWSsgRAyDNAKCInH9SJRO8ly1L1MomJUPlBslBlgCeLQ1v +w4ZumRcB5U5L3SGT0rk4AE= =I8Ur -----END PGP SIGNATURE----- ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate