sql/stats: change order of operations in histogram.adjustCounts

[michae2]

Follow up from #79872. This issue tracks this TODO in histogram.adjustCounts:

cockroach/pkg/sql/stats/histogram.go

Lines 314 to 316 in 6a51183

	// TODO(michae2): Consider moving this section above the sections adjusting
	// NumEq and NumRange for distinct counts. This would help the adjustments be
	// less surprising in some cases.

We use histogram.adjustCounts to adjust an existing histogram to match a given row count and distinct count. It is used both by statistics collection and statistics forecasting. While working on statistics forecasting, I noticed that sometimes adjustCounts produces unexpected adjustments, and I believe this is because it accounts for the new distinct count before accounting for the new row count. If those were reversed, the adjustments would be better (at least in simple cases).

For example:

CREATE TABLE j (t TIME PRIMARY KEY) WITH (sql_stats_automatic_collection_enabled = false);
ALTER TABLE j INJECT STATISTICS '[
      {
          "avg_size": 1,
          "columns": [
              "t"
          ],
          "created_at": "2022-08-13 00:00:00.000000",
          "distinct_count": 6,
          "histo_buckets": [
              {
                  "distinct_range": 0,
                  "num_eq": 1,
                  "num_range": 0,
                  "upper_bound": "00:01:00"
              },
              {
                  "distinct_range": 2,
                  "num_eq": 1,
                  "num_range": 2,
                  "upper_bound": "00:02:00"
              },
              {
                  "distinct_range": 2,
                  "num_eq": 0,
                  "num_range": 2,
                  "upper_bound": "00:03:00"
              }
          ],
          "histo_col_type": "TIME",
          "histo_version": 2,
          "name": "__auto__",
          "null_count": 0,
          "row_count": 6
      },
      {
          "avg_size": 1,
          "columns": [
              "t"
          ],
          "created_at": "2022-08-14 00:00:00.000000",
          "distinct_count": 10,
          "histo_buckets": [
              {
                  "distinct_range": 0,
                  "num_eq": 2,
                  "num_range": 0,
                  "upper_bound": "00:01:00"
              },
              {
                  "distinct_range": 4,
                  "num_eq": 2,
                  "num_range": 4,
                  "upper_bound": "00:02:00"
              },
              {
                  "distinct_range": 4,
                  "num_eq": 0,
                  "num_range": 4,
                  "upper_bound": "00:03:00"
              }
          ],
          "histo_col_type": "TIME",
          "histo_version": 2,
          "name": "__auto__",
          "null_count": 0,
          "row_count": 12
      },
      {
          "avg_size": 1,
          "columns": [
              "t"
          ],
          "created_at": "2022-08-15 00:00:00.000000",
          "distinct_count": 14,
          "histo_buckets": [
              {
                  "distinct_range": 0,
                  "num_eq": 3,
                  "num_range": 0,
                  "upper_bound": "00:01:00"
              },
              {
                  "distinct_range": 6,
                  "num_eq": 3,
                  "num_range": 6,
                  "upper_bound": "00:02:00"
              },
              {
                  "distinct_range": 6,
                  "num_eq": 0,
                  "num_range": 6,
                  "upper_bound": "00:03:00"
              }
          ],
          "histo_col_type": "TIME",
          "histo_version": 2,
          "name": "__auto__",
          "null_count": 0,
          "row_count": 18
      }
]';
SELECT jsonb_pretty(stat->'histo_buckets')
FROM (
  SELECT jsonb_array_elements(statistics) AS stat
  FROM [SHOW STATISTICS USING JSON FOR TABLE j WITH FORECAST]
)
WHERE stat->>'name' = '__forecast__';

Because this is a TIME column we cannot currently forecast the histogram, so instead we call adjustCounts on the latest histogram with row count = 24 and distinct count = 18. I think the adjusted histogram should look like:

  [
      {
          "distinct_range": 0,
          "num_eq": 4,
          "num_range": 0,
          "upper_bound": "00:01:00"
      },
      {
          "distinct_range": 8,
          "num_eq": 4,
          "num_range": 8,
          "upper_bound": "00:02:00"
      },
      {
          "distinct_range": 8,
          "num_eq": 0,
          "num_range": 8,
          "upper_bound": "00:03:00"
      }
  ]

But instead, it currently is:

  [
      {
          "distinct_range": 0,
          "num_eq": 3,
          "num_range": 0,
          "upper_bound": "00:01:00"
      },
      {
          "distinct_range": 8,
          "num_eq": 3,
          "num_range": 9,
          "upper_bound": "00:02:00"
      },
      {
          "distinct_range": 8,
          "num_eq": 0,
          "num_range": 9,
          "upper_bound": "00:03:00"
      }
  ]

Jira issue: CRDB-18781