cockroachdb / cockroachdb/cockroach

sql: truncated, sampled values should not be included in "num_eq" in histograms

Open
#139,380 0 comments 0 reactions 0 assignees View on GitHub
A-sql-table-stats C-enhancement T-sql-queries
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.