cockroachdb / cockroachdb/cockroach
explain: incorrect "inaccurate row count" warning for scans which stop early
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
In `EXPLAIN ANALYZE` output we print an "inaccurate row count" warning in `scan` nodes when the actual row count is very different from the expected row count. However, this warning can be incorrect when the actual row count is low due to the scan stopping early, rather than the table statistics being stale.
Here's an example using TPC-C. First load the dataset:
```
cockroach demo tpcc
```
Then `EXPLAIN ANALYZE` the query from `check33211`:
```sql
EXPLAIN ANALYZE
SELECT count(*) FROM
(SELECT order_count, new_order_count FROM
(SELECT o_w_id, o_d_id, count(*) order_count FROM "order" GROUP BY o_w_id, o_d_id),
(SELECT no_w_id, no_d_id, count(*) new_order_count FROM new_order GROUP BY no_w_id, no_d_id),
(SELECT c_w_id, c_d_id FROM customer GROUP BY c_w_id, c_d_id)
WHERE (o_w_id, o_d_id) = (no_w_id, no_d_id)
AND (no_w_id, no_d_id) = (c_w_id, c_d_id))
WHERE order_count - new_order_count != 2100;
```
The scan of `customer` incorrectly shows this warning even though the stats are fresh. This is because a merge join above the scan caused the scan to stop early after reading only 1024 rows:
```
└── • merge join
│ nodes: n1
│ actual row count: 0
│ estimated max memory allocated: 330 KiB
│ estimated max sql temp disk usage: 0 B
│ sql cpu time: 11µs
│ estimated row count: 3
│ equality: (no_w_id, no_d_id) = (c_w_id, c_d_id)
│ left cols are key
│ right cols are key
│
...
│
└── • distinct
│ nodes: n1
│ actual row count: 1
│ sql cpu time: 6µs
│ estimated row count: 10
│ distinct on: c_d_id, c_w_id
│ order key: c_d_id, c_w_id
│
└── • scan
nodes: n1
actual row count: 1,024
KV time: 8ms
KV contention time: 0µs
KV rows read: 1,024
KV bytes read: 64 KiB
KV gRPC calls: 1
estimated max memory allocated: 1.8 MiB
sql cpu time: 202µs
estimated row count: 30,000 (100% of the table; stats collected 20 seconds ago)
table: customer@customer_idx ---------------------- WARNING: the row count estimate is inaccurate, consider running 'ANALYZE customer'
spans: FULL SCAN
WARNING: the row count estimate on table "customer" is inaccurate, consider running 'ANALYZE customer'
```
Jira issue: CRDB-27484
Contributor guide
Assessment
This issue has not been assessed yet.