Re: Better default_statistics_target

-----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(at)turnstep(dot)com
End Point Corporation
PGP Key: 0x14964AC8 200712050920
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFHVrSivJuQZxSWSsgRAyDNAKCInH9SJRO8ly1L1MomJUPlBslBlgCeLQ1v
+w4ZumRcB5U5L3SGT0rk4AE=
=I8Ur
-----END PGP SIGNATURE-----