cockroachdb / cockroachdb/cockroach
sql/opt: stats underestimate after enum value dropped
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
When an enum value that is a histogram bucket boundary is dropped, `DecodeBuckets` skips the bucket and carries forward its range counts. If the dropped value was the first bucket, a new first bucket is prepended at the minimum remaining enum value (to maintain the NumRange=0 invariant on the first bucket). However, this new bucket is created with NumEq=0 because the decode logic has no way to know how many of the next bucket's range rows belong to the new boundary value specifically.
In the example below, the original histogram stores `range=45` on the `critical` bucket, representing 30 `medium` + 15 `high` rows between `low` and `critical`. After dropping `low`:
1. The `low` bucket is skipped (its value no longer exists in the enum type)
2. The `critical` bucket survives with its original `range=45`
3. `medium` is prepended as the new min-value bucket with NumEq=0
4. `critical`'s `range=45` now claims to represent rows between `medium` and `critical`, but only `high` (15 rows) is actually in that range — the other 30 rows are *at* `medium`
The result is a 30x misattribution: `medium` shows NumEq=0 (should be 30) and `critical` shows NumRange=45 (should be 15). The total row count is preserved, but the distribution is wrong enough that the optimizer estimates 1 row for `priority = 'medium'` when the actual is 30.
A possible improvement would be to redistribute range counts when prepending a new min-value bucket: if the histogram knows the total number of remaining enum values in the range, it could estimate `NumEq ≈ NumRange / num_values_in_range` for the new boundary value and subtract that from the next bucket's range count. This would be approximate but much closer than NumEq=0.
Reproduction:
```sql
CREATE TYPE priority AS ENUM ('low', 'medium', 'high', 'critical');
CREATE TABLE t (priority priority, data INT, INDEX (priority))
WITH (sql_stats_histogram_buckets_count = 2);
INSERT INTO t SELECT 'low', generate_series(1, 50);
INSERT INTO t SELECT 'medium', generate_series(1, 30);
INSERT INTO t SELECT 'high', generate_series(1, 15);
INSERT INTO t SELECT 'critical', generate_series(1, 5);
CREATE STATISTICS s FROM t;
-- Histogram before drop:
-- [low: eq=50, range=0] [critical: eq=5, range=45]
-- The range=45 accounts for medium (30) + high (15).
DELETE FROM t WHERE priority = 'low';
ALTER TYPE priority DROP VALUE 'low';
-- After dropping 'low', the decoded stale histogram becomes:
-- [medium: eq=0, range=0] [critical: eq=5, range=45]
--
-- 'medium' was prepended as the new min-value bucket, but with NumEq=0.
-- The 30 actual 'medium' rows are stuck in critical's range=45.
-- Optimizer estimates 1 row instead of 30:
EXPLAIN (OPT, VERBOSE) SELECT * FROM t WHERE priority = 'medium';
```
Jira issue: CRDB-62556
Contributor guide
Assessment
This issue has not been assessed yet.