cockroachdb / cockroachdb/cockroach
sql: do not recommend a stats collection if there was one recently
- 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
Assessment
This issue has not been assessed yet.