pingcap / pingcap/tidb

store/copr, executor: tidb_store_batch_size has no effect for tables with non-integer clustered primary key

Open
#67,150 0 comments 0 reactions 0 assignees View on GitHub
contribution report/customer sig/execution sig/planner
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement

### Summary

`tidb_store_batch_size` silently has no effect for tables whose primary key is a non-integer clustered index (e.g. `VARCHAR`, composite PK). The batching optimization only activates for single-integer PK tables.

### Minimal reproduce step

```sql
CREATE TABLE t1 (
id VARCHAR(64) PRIMARY KEY,
user_id BIGINT NOT NULL
-- other columns omitted; actual row size is one of the factors affecting region splits
); -- billions of rows

CREATE TABLE t2 (
ref_id VARCHAR(64) PRIMARY KEY
-- other columns omitted; actual row size is one of the factors affecting region splits
); -- about half of t1

SELECT t1.id
FROM t1
LEFT JOIN t2 ON t1.id = t2.ref_id
WHERE t1.user_id = ?
AND t2.ref_id IS NULL;
```

When `t1.user_id = ?` matches a large number of rows, TiDB executes this with an `IndexHashJoin` where the probe side fetches rows from `t2` using matched `t1.id` values as lookup keys. With `tidb_store_batch_size > 0`, one would expect the probe-side cop tasks to be merged into batched requests. However, they are not — each lookup key is dispatched as a separate cop task.

The following is an excerpt from an actual `EXPLAIN ANALYZE` output with 41 outer rows from `t1`:

```sql
+---------------------------+----------+---------+-----------+-----------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+---------------------------+----------+---------+-----------+-----------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+----------+------+
| IndexHashJoin_42 | 5.00 | 41 | root | | time:13.4ms, loops:1, inner:{total:10.2ms, concurrency:5, task:1, construct:84.2µs, fetch:10.1ms, build:24µs, join:15.7µs} | left outer join, inner:TableReader_37, outer key:t1.id, inner key:t2.ref_id, equal cond:eq(t1.id, t2.ref_id) | 58.9 KB | N/A |
| ├─IndexReader_45(Build) | 5.00 | 41 | root | | time:3ms, loops:3, cop_task: {num: 1, max: 2.87ms, proc_keys: 41, tot_proc: 345.5µs, tot_wait: 548.6µs, copr_cache: disabled, build_task_duration: 15µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:2.85ms}} | index:IndexRangeScan_44 | 2.74 KB | N/A |
| │ └─IndexRangeScan_44 | 5.00 | 41 | cop[tikv] | table:t1, index:idx_user_id | tikv_task:{time:1ms, loops:2} | range:[? ?], keep order:true, desc | N/A | N/A |
| └─TableReader_37(Probe) | 5.00 | 0 | root | | time:9.75ms, loops:1, cop_task: {num: 40, max: 4.4ms, min: 1.94ms, avg: 2.89ms, p95: 3.93ms, tot_proc: 5.73ms, tot_wait: 18.6ms, copr_cache: disabled, build_task_duration: 97.5µs, max_distsql_concurrency: 15}, rpc_info:{Cop:{num_rpc:40, total_time:115.1ms}} | data:TableRangeScan_36 | N/A | N/A |
| └─TableRangeScan_36 | 5.00 | 0 | cop[tikv] | table:t2 | tikv_task:{proc max:1ms, min:0s, avg: 75µs, p80:0s, p95:1ms, iters:40, tasks:40} | range: decided by [eq(t2.ref_id, t1.id)], keep order:false | N/A | N/A |
+---------------------------+----------+---------+-----------+-----------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+----------+------+
```

Pay attention to the probe-side executor `TableReader_37`:

```
└─TableReader_37(Probe) cop_task:{num:40, tot_proc:5.73ms, tot_wait:18.6ms} rpc_info:{Cop:{num_rpc:40, total_time:115.1ms}}
```

41 outer rows produced 40 separate cop tasks. Actual TiKV processing time was only `5.73ms`, but total RPC time reached `115.1ms` — roughly 20x overhead from request dispatch. Setting `tidb_store_batch_size > 0` does not reduce this number.

### Expected behavior

`tidb_store_batch_size` should reduce the number of coprocessor RPCs for index join table-side lookups regardless of primary key type, as it does today for single-integer PK tables.

### Current limitation

Store batch coprocessor (`tidb_store_batch_size`) is currently only effective for tables with a single-integer primary key. Tables with a non-integer clustered primary key (e.g. `VARCHAR`, composite PK) are not supported.

### Related issues

- #41582 — scan details of store-batched copr are not correctly handled

### TiDB version

```
Release Version: v8.5.3
```

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.