opt: add cluster setting to enable histograms

[jseldess]

PR: cockroachdb/cockroach#38812

From release notes:

Added cluster setting sql.stats.histogram_collection.enabled to enable collection of histograms during statistics collection. [#38812][#38812] {% comment %}doc{% endcomment %}

[jseldess]

@rmloveland, this setting is already in the docs here: https://www.cockroachlabs.com/docs/v19.2/cluster-settings.html. But I'm not sure if you'll want to mention it here as well: https://www.cockroachlabs.com/docs/v19.2/cost-based-optimizer.html#table-statistics

[rmloveland]

@rytaft I have some questions

1. As a user, when would I ever toggle this setting from the default? In 19.2-rc1 looks like it's on by default. Why would I turn it off?
2. If I do turn it off, approximately what effects will it have on my queries / the cluster?

[rytaft]

Hi @rmloveland,

You might turn it off for two reasons:

1. Histograms add a small amount of memory and CPU overhead to statistics collection and query planning. If your workload is extremely performance-sensitive and can't tolerate this overhead, you might want to disable histogram collection.
2. In some cases use of histograms can change the query plan selected by the optimizer, since it changes the optimizer's estimate of how selective a predicate is. In most cases the plan will be better (or unchanged), but there is a chance it might be worse.

If you turn it off, the automatic statistics collection will not collect histograms, and as a result, the optimizer will not use histograms for statistics estimation.

Does this help?

[rmloveland]

Yes, thanks! Do you think it's worth updating #5795 with this info? Or is it perhaps too much detail since it might let someone shoot themselves in the foot? (Since presumably almost all users should leave them enabled almost all of the time?)

[rytaft]

I think what you've included in #5795 is good. I personally wouldn't add much more detail, but I defer to whatever you guys think.