cockroachdb / cockroachdb/cockroach

sql: do not recommend a stats collection if there was one recently

Open
#127,858 1 comment 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

The output of `EXPLAIN ANALYZE` sometimes includes a warning suggesting the user to collect new table statistics. The decision to display this warning is currenlty based on only the estimated row count and the actual row count—if they are significantly different, the warning is printed.

However, this warning is confusing when statistics were recently collected. It will instruct the use to collect statistics, when they might have just done so.

Here's an example:

```sql
CREATE TABLE t (
k INT PRIMARY KEY,
a INT,
b INT,
INDEX (a, b)
);

PREPARE ins AS
INSERT INTO t
SELECT i, i%10, i%100
FROM generate_series($1, $2) AS g(i);

EXECUTE ins(1, 10000);
EXECUTE ins(10001, 20000);
EXECUTE ins(20001, 30000);
EXECUTE ins(30001, 40000);
EXECUTE ins(40001, 50000);
EXECUTE ins(50001, 60000);
EXECUTE ins(60001, 70000);

ANALYZE t;

EXPLAIN ANALYZE
SELECT * FROM t
WHERE a = 9 AND b = 0;
-- info
-- -----------------------------------------------------------------------------------------------------------------------------
-- planning time: 149µs
-- execution time: 270µs
-- distribution: local
-- vectorized: true
-- cumulative time spent in KV: 208µs
-- maximum memory usage: 30 KiB
-- network usage: 0 B (0 messages)
-- regions: us-east1
-- sql cpu time: 7µs
-- isolation level: serializable
-- priority: normal
-- quality of service: regular
--
-- • scan
-- nodes: n1
-- regions: us-east1
-- actual row count: 0
-- KV time: 208µs
-- KV contention time: 0µs
-- KV rows decoded: 0
-- KV bytes read: 0 B
-- KV gRPC calls: 1
-- estimated max memory allocated: 30 KiB
-- sql cpu time: 7µs
-- estimated row count: 586 (0.84% of the table; stats collected 0 seconds ago)
-- table: t@t_a_b_idx ---------------------- WARNING: the row count estimate is inaccurate, consider running 'ANALYZE t'
-- spans: [/9/0 - /9/0]
--
-- WARNING: the row count estimate on table "t" is inaccurate, consider running 'ANALYZE t'
-- (29 rows)
```

We should omit this message if stats were collected recently. Part of this task will be defining a resonable definition for "recently".

Jira issue: CRDB-40643

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.