> When looking at these numbers one might think the threshold of pain
> is about 50, rather than 100 which is where I'd put it for the text
> example. However, this is probably an extreme worst case.
>
> On the whole I think we have some evidence here to say that upping the
> default value of default_stats_target to 100 wouldn't be out of line,
> but 1000 definitely is. Comments?
Do you think there's any value in making it scale based on the size of
the table? How hard would it be? If you made it MIN(10 + 0.001 *
estimated_rows, 100), you would probably get most of the benefit while
avoiding unnecessary overhead for small tables.
Otherwise, I am a bit concerned that 10 -> 100 may be too big a jump
for one release, especially since it may cause the statistics to get
toasted in some cases, which comes with a significant performance hit.I would raise it to 30 or 50 and plan to consider
raisingit further
down the road. (I realize I just made about a million enemies with
that suggestion.)
> BTW, does anyone have an opinion about changing the upper limit for
> default_stats_target to, say, 10000? These tests suggest that you
> wouldn't want such a value for a column used as a join key, but
> I can see a possible argument for high values in text search and
> similar applications.
I think that's a good idea. Given that most people probably don't
both fiddling with this parameter at all, it doesn't strike me as much
of a foot-gun. I think you'd need a heck of a big table to justify a
value in that range, but some people may have them.
...Robert