cockroachdb / cockroachdb/cockroach

sql/stats: some forecasting cluster settings do not immediately take effect

Open
#123,852 3 comments 0 reactions 0 assignees View on GitHub
A-sql-table-stats C-bug docs-known-limitation T-sql-queries
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.