cockroachdb / cockroachdb/cockroach
kvstreamer: use avg_size column stats to calculate initial avg response size estimate
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
Consider the following setup:
```sql
CREATE TABLE small (k INT PRIMARY KEY);
INSERT INTO small SELECT generate_series(1, 10);
ANALYZE small;
CREATE TABLE large (k1 INT, k2 INT, blob STRING, PRIMARY KEY (k1, k2), INDEX(blob));
INSERT INTO large SELECT i, 1, repeat('a', 10000) FROM generate_series(1, 10) AS g(i);
ANALYZE large;
ALTER TABLE large SPLIT AT SELECT generate_series(1, 10);
```
and the following query:
```sql
SELECT * FROM small INNER LOOKUP JOIN large ON k = k1;
```
If we examine the behavior of the streamer for the lookup join, we see this (from the trace in the stmt bundle):
```
enqueuedRequests=10 enqueuedSingleRangeRequests=10 batchRequestsIssued=19 resumeBatchRequests=9 resumeSingleRangeRequests=9 numSpilledResults=0 emptyBatchResponses=9 droppedBatchResponses=0 avgResponseSize=15 KiB
```
The streamer needs to fetch 10 rows in parallel, but since each row is 10KB in size, only "head-of-the-line" `BatchRequest` succeeds from the initial set of requests, all other 9 `BatchRequest`s come back empty. This is because we currently use the hard-coded 1KiB estimate for the avg response size.
Instead, we should consult `avg_size` statistic to figure out better initial estimate. In this case, we do have stats on `blob` column, so we could have avoided those empty `BatchResponse`s.
Jira issue: CRDB-28090
Contributor guide
Assessment
This issue has not been assessed yet.