cockroachdb / cockroachdb/cockroach
sql/stats: some forecasting cluster settings do not immediately take effect
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
The following cluster settings do not immediately take effect, and instead only take effect when new statistics are collected for a table.
- `sql.stats.forecasts.max_decrease`
- `sql.stats.forecasts.min_goodness_of_fit`
- `sql.stats.forecasts.min_observations`
- `sql.stats.forecasts.independent_histograms.enabled`
Confusingly, `SHOW STATISTICS WITH FORECAST` seems to show them taking effect immediately, but they do not actually take effect until new statistics are collected, as can be seen with `EXPLAIN`.
Here's a demonstration using `sql.stats.forecasts.min_observations`:
```sql
CREATE TABLE a (a INT PRIMARY KEY) WITH (sql_stats_automatic_collection_enabled = false);
INSERT INTO a VALUES (1);
CREATE STATISTICS __auto__ FROM a;
-- neither SHOW nor EXPLAIN indicate that we're using a forecast
SHOW STATISTICS FOR TABLE a WITH FORECAST;
EXPLAIN SELECT * FROM a;
-- set the cluster setting
SET CLUSTER SETTING sql.stats.forecasts.min_observations = 1;
-- SHOW seems to show the cluster setting taking effect
SHOW STATISTICS FOR TABLE a WITH FORECAST;
-- but in EXPLAIN we do not see "using stats forecast"
EXPLAIN SELECT * FROM a;
-- it's not until *another* statistics collection that the cluster setting takes effect
CREATE STATISTICS __auto__ FROM a;
-- now both SHOW and EXPLAIN indicate that we're using a forecast
SHOW STATISTICS FOR TABLE a WITH FORECAST;
EXPLAIN SELECT * FROM a;
```
These settings were added in https://github.com/cockroachdb/cockroach/pull/122779, which failed to add any kind of invalidation of the stats cache when they are changed.
This is true as of v23.1.20 and v24.1.0-beta.3 (these settings have not yet been released in v23.2).
Jira issue: CRDB-38572
Contributor guide
Research direction
First reproduce the behavior with the SQL sequence in the issue, comparing SHOW STATISTICS WITH FORECAST and EXPLAIN before and after changing each setting. Then inspect the statistics-cache handling added around PR 122779. Done means each listed setting is reflected by EXPLAIN immediately after it changes, without requiring another statistics collection.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100