cockroachdb / cockroachdb/cockroach
sql: truncated, sampled values should not be included in "num_eq" in histograms
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
Consider the example:
```sql
CREATE TABLE t (
k INT PRIMARY KEY,
s STRING,
INDEX (s)
);
INSERT INTO t VALUES (1, repeat('abcdefghi', 6000));
ANALYZE t;
SHOW STATISTICS USING JSON FOR TABLE t;
-- [
-- ...
-- {
-- "columns": [
-- "s"
-- ],
-- "distinct_count": 1,
-- "histo_buckets": [
-- {
-- "distinct_range": 0,
-- "num_eq": 1,
-- "num_range": 0,
-- "upper_bound": "abcdefghiabcdefghi... (abbreviated to 400 bytes)"
-- }
-- ],
-- "histo_col_type": "STRING",
-- "null_count": 0,
-- "row_count": 1
-- },
-- ...
-- ]
```
Notice that the histogram shows that a single row is equal to a truncated value of the `s` row. This inaccuracy could lead to suboptimal plans, especially in cases where the 400-byte prefix of indexed columns are heavy hitters.
Ideally, sampled values that are truncated would contribute to the `num_range` field of a bucket with the closest `upper_bound` that is greater than the truncated value. This would yield more accurate histograms and better query plans.
Jira issue: CRDB-46616
Contributor guide
Assessment
This issue has not been assessed yet.