Questions about TiDB LIMIT Execution Plan

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: tidb limit执行计划疑惑

| username: TiDBer_KkruFifg

[TiDB Usage Environment] Production Environment / Test / Poc
Poc

[TiDB Version]
5.1.4

[Encountered Problem]
Why is the actRows in the execution plan at the TiKV layer 7 for the following SQL with limit 1 (Limit_28(Build))? Shouldn’t it be 1? Please help take a look, thank you.

desc analyze SELECT status, expiry_time FROM modell_var_tab force index (idx_tag_entity) WHERE tag_id = 8884146564707019816 AND (entity_id >= 470781680 AND entity_type >= ‘user’) ORDER BY entity_id, entity_type LIMIT 1;
±-------------------------------------±--------±--------±----------±---------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
±-------------------------------------±--------±--------±----------±---------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
| Projection_8 | 0.84 | 1 | root | | time:3.27ms, loops:2, Concurrency:OFF | userdb_tes_mode_aa_db.modell_var_tab.status, userdb_tes_mode_aa_db.modell_var_tab.expiry_time | 2.07 KB | N/A |
| └─Limit_14 | 0.84 | 1 | root | | time:3.26ms, loops:2 | offset:0, count:1 | N/A | N/A |
| └─Projection_30 | 0.84 | 1 | root | | time:3.26ms, loops:1, Concurrency:OFF | userdb_tes_mode_aa_db.modell_var_tab.entity_id, userdb_tes_mode_aa_db.modell_var_tab.entity_type, userdb_tes_mode_aa_db.modell_var_tab.tag_id, userdb_tes_mode_aa_db.modell_var_tab.status, userdb_tes_mode_aa_db.modell_var_tab.expiry_time | 2.43 KB | N/A |
| └─IndexLookUp_29 | 0.84 | 1 | root | | time:3.26ms, loops:1, index_task: {total_time: 1.11ms, fetch_handle: 1.1ms, build: 3.82µs, wait: 3.14µs}, table_task: {total_time: 15.4ms, num: 3, concurrency: 5} | | 7.48 KB | N/A |
| ├─Limit_28(Build) | 0.84 | 7 | cop[tikv] | | time:1.1ms, loops:4, cop_task: {num: 7, max: 1.07ms, min: 854µs, avg: 953.3µs, p95: 1.07ms, max_proc_keys: 32, p95_proc_keys: 32, tot_proc: 2ms, tot_wait: 1ms, rpc_num: 7, rpc_time: 6.6ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:7, tasks:7}, scan_detail: {total_process_keys: 224, total_keys: 231, rocksdb: {delete_skipped_count: 0, key_skipped_count: 224, block: {cache_hit_count: 91, read_count: 1, read_byte: 31.2 KB}}} | offset:0, count:1 | N/A | N/A |
| │ └─Selection_27 | 0.84 | 224 | cop[tikv] | | tikv_task:{proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:7, tasks:7} | ge(userdb_tes_mode_aa_db.modell_var_tab.entity_type, “user”) | N/A | N/A |
| │ └─IndexRangeScan_25 | 0.84 | 224 | cop[tikv] | table:modell_var_tab, index:idx_tag_entity(tag_id, entity_id, entity_type) | tikv_task:{proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:7, tasks:7} | range:[8884146564707019816 470781680,8884146564707019816 +inf], keep order:true | N/A | N/A |
| └─TableRowIDScan_26(Probe) | 0.84 | 7 | cop[tikv] | table:modell_var_tab | time:1.82ms, loops:6, cop_task: {num: 7, max: 601.6µs, min: 518.9µs, avg: 564.6µs, p95: 601.6µs, max_proc_keys: 1, p95_proc_keys: 1, rpc_num: 7, rpc_time: 3.89ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:0s, min:0s, p80:0s, p95:0s, iters:7, tasks:7}, scan_detail: {total_process_keys: 7, total_keys: 7, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 84, read_count: 0, read_byte: 0 Bytes}}} | keep order:false, stats:pseudo | N/A | N/A |
±-------------------------------------±--------±--------±----------±---------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
[Reproduction Path] What operations were performed to encounter the problem
[Problem Phenomenon and Impact]

[Attachments]

Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.

2
| username: TiDBer_KkruFifg | Original post link 
desc analyze SELECT `status`, `expiry_time` FROM modell_var_tab force index (idx_tag_entity) WHERE tag_id = 8884146564707019816 AND (entity_id >= 470781680 AND entity_type >= 'user') ORDER BY entity_id, entity_type LIMIT 1;
+--------------------------------------+---------+---------+-----------+----------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id                                   | estRows | actRows | task      | access object                                                              | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | operator info                                                                                                                                                                                                                               | memory  | disk |
+--------------------------------------+---------+---------+-----------+----------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| Projection_8                         | 0.84    | 1       | root      |                                                                            | time:3.27ms, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                 | userdb_tes_mode_aa_db.modell_var_tab.status, userdb_tes_mode_aa_db.modell_var_tab.expiry_time                                                                                                                                               | 2.07 KB | N/A  |
| └─Limit_14                           | 0.84    | 1       | root      |                                                                            | time:3.26ms, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | offset:0, count:1                                                                                                                                                                                                                           | N/A     | N/A  |
|   └─Projection_30                    | 0.84    | 1       | root      |                                                                            | time:3.26ms, loops:1, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                 | userdb_tes_mode_aa_db.modell_var_tab.entity_id, userdb_tes_mode_aa_db.modell_var_tab.entity_type, userdb_tes_mode_aa_db.modell_var_tab.tag_id, userdb_tes_mode_aa_db.modell_var_tab.status, userdb_tes_mode_aa_db.modell_var_tab.expiry_time | 2.43 KB | N/A  |
|     └─IndexLookUp_29                 | 0.84    | 1       | root      |                                                                            | time:3.26ms, loops:1, index_task: {total_time: 1.11ms, fetch_handle: 1.1ms, build: 3.82µs, wait: 3.14µs}, table_task: {total_time: 15.4ms, num: 3, concurrency: 5}                                                                                                                                                                                                                                                                                                                    |                                                                                                                                                                                                                                              | 7.48 KB | N/A  |
|       ├─Limit_28(Build)              | 0.84    | 7       | cop[tikv] |                                                                            | time:1.1ms, loops:4, cop_task: {num: 7, max: 1.07ms, min: 854µs, avg: 953.3µs, p95: 1.07ms, max_proc_keys: 32, p95_proc_keys: 32, tot_proc: 2ms, tot_wait: 1ms, rpc_num: 7, rpc_time: 6.6ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:7, tasks:7}, scan_detail: {total_process_keys: 224, total_keys: 231, rocksdb: {delete_skipped_count: 0, key_skipped_count: 224, block: {cache_hit_count: 91, read_count: 1, read_byte: 31.2 KB}}}   | offset:0, count:1                                                                                                                                                                                                                            | N/A     | N/A  |
|       │ └─Selection_27               | 0.84    | 224     | cop[tikv] |                                                                            | tikv_task:{proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:7, tasks:7}                                                                                                                                                                                                                                                                                                                                                                                                                   | ge(userdb_tes_mode_aa_db.modell_var_tab.entity_type, "user")                                                                                                                                                                                 | N/A     | N/A  |
|       │   └─IndexRangeScan_25        | 0.84    | 224     | cop[tikv] | table:modell_var_tab, index:idx_tag_entity(tag_id, entity_id, entity_type) | tikv_task:{proc max:1ms, min:0s, p80:1ms, p95:1ms, iters:7, tasks:7}                                                                                                                                                                                                                                                                                                                                                                                                                  | range:[8884146564707019816 470781680,8884146564707019816 +inf], keep order:true                                                                                                                                                              | N/A     | N/A  |
|       └─TableRowIDScan_26(Probe)     | 0.84    | 7       | cop[tikv] | table:modell_var_tab                                                       | time:1.82ms, loops:6, cop_task: {num: 7, max: 601.6µs, min: 518.9µs, avg: 564.6µs, p95: 601.6µs, max_proc_keys: 1, p95_proc_keys: 1, rpc_num: 7, rpc_time: 3.89ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:0s, min:0s, p80:0s, p95:0s, iters:7, tasks:7}, scan_detail: {total_process_keys: 7, total_keys: 7, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 84, read_count: 0, read_byte: 0 Bytes}}}                                      | keep order:false, stats:pseudo                                                                                                                                                                                                               | N/A     | N/A  |
+--------------------------------------+---------+---------+-----------+----------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
3
| username: Meditator | Original post link

It’s best to share the table structure.

4
| username: TiDBer_KkruFifg | Original post link

CREATE TABLE tb_model (
entity_id bigint(20) unsigned NOT NULL,
entity_type char(8) NOT NULL,
tag_id bigint(20) unsigned NOT NULL,
region char(4) NOT NULL,
status tinyint(3) unsigned NOT NULL DEFAULT ‘1’,
expiry_time bigint(20) unsigned NOT NULL DEFAULT ‘0’,
create_time bigint(20) unsigned NOT NULL,
update_time bigint(20) unsigned NOT NULL,
id bigint(20) NOT NULL,
PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,
KEY idx_tag_entity (tag_id,entity_id,entity_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

5
| username: tidb狂热爱好者 | Original post link

First, take 7, then limit it to 1. The data is taken as 7.

6
| username: h5n1 | Original post link

cop_task: {num: 7 7 cop tasks, each returning one row

7
| username: TiDBer_KkruFifg | Original post link

May I ask, with only limit 1, why does it fetch 7 rows of data?

actRows indicates the actual number of rows output by the operator.

8
| username: Meditator | Original post link

Since the query in the TiKV layer is parallel, with 7 concurrent tasks, and because it is a limit 1 query, each task returns one piece of data that meets the where and order conditions. Then, the TiDB server layer continues to aggregate and sort the results. As for why there are 7 concurrent tasks, it is due to: 1) parameter constraints, and 2) the number of regions in the queried table, which might be 7.

9
| username: TiDBer_KkruFifg | Original post link

Just to confirm, is the parameter tidb_executor_concurrency?

10
| username: Meditator | Original post link

It is not tidb_executor_concurrency. The detailed explanation of tidb_executor_concurrency is as follows:
image
It is tidb_distsql_scan_concurrency. The detailed explanation is as follows:

image
image726×265 12.3 KB

11
| username: TiDBer_KkruFifg | Original post link

Thanks, it shouldn’t be this parameter, it should include something like an index.

12
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.