Re: Better default_statistics_target

On Wed, 2007-11-14 at 00:00 -0500, Tom Lane wrote:
> Greg Sabino Mullane <greg(at)turnstep(dot)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.

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.

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.

Two thoughts:

- why did we pick 100 for the LIKE operator?

- should we document the better selectivity for LIKE operators at 100?

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com