tune tidb_enable_paging or tidb_min_paging_size to make TTL scan more efficient #58342
Description
Enhancement
With paging:
> explain analyze select id from t_pk_bigint_t_timestamp_150d_200M where t < '2024-01-16' order by id asc limit 500;
+----------------------------+---------+---------+-----------+-----------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------+---------+---------+-----------+-----------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+---------+------+
| Projection_7 | 500.00 | 500 | root | | time:20.9ms, open:659.5µs, close:74.2µs, loops:2, RU:116.080384, Concurrency:OFF | ttl_test.t_pk_bigint_t_timestamp_150d_200m.id | 10.9 KB | N/A |
| └─Limit_12 | 500.00 | 500 | root | | time:20.9ms, open:655.4µs, close:72.9µs, loops:2 | offset:0, count:500 | N/A | N/A |
| └─TableReader_25 | 500.00 | 501 | root | | time:20.9ms, open:649.5µs, close:72.5µs, loops:1, cop_task: {num: 3, max: 9.7ms, min: 3.53ms, avg: 6.67ms, p95: 9.7ms, max_proc_keys: 7136, p95_proc_keys: 7136, tot_proc: 18.3ms, tot_wait: 131.8µs, copr_cache: disabled, build_task_duration: 593.2µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:3, total_time:19.9ms}} | data:Limit_24 | 13.1 KB | N/A |
| └─Limit_24 | 500.00 | 932 | cop[tikv] | | tikv_task:{proc max:9ms, min:3ms, avg: 6ms, p80:9ms, p95:9ms, iters:25, tasks:3}, scan_detail: {total_process_keys: 13216, total_process_keys_size: 7083776, total_keys: 13219, get_snapshot_time: 70µs, rocksdb: {key_skipped_count: 26429, block: {cache_hit_count: 264}}}, time_detail: {total_process_time: 18.3ms, total_suspend_time: 54.8µs, total_wait_time: 131.8µs, total_kv_read_wall_time: 13ms, tikv_wall_time: 18.9ms} | offset:0, count:500 | N/A | N/A |
| └─Selection_23 | 500.00 | 949 | cop[tikv] | | tikv_task:{proc max:9ms, min:3ms, avg: 6ms, p80:9ms, p95:9ms, iters:25, tasks:3} | lt(ttl_test.t_pk_bigint_t_timestamp_150d_200m.t, 2024-01-16 00:00:00.000000) | N/A | N/A |
| └─TableFullScan_22 | 1504.51 | 13216 | cop[tikv] | table:t_pk_bigint_t_timestamp_150d_200M | tikv_task:{proc max:7ms, min:2ms, avg: 4.33ms, p80:7ms, p95:7ms, iters:25, tasks:3} | keep order:true, stats:partial[t:unInitialized] | N/A | N/A |
+----------------------------+---------+---------+-----------+-----------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+---------+------+
6 rows in set
Time: 0.049s
Without paging:
> set @@tidb_enable_paging='OFF';
Query OK, 0 rows affected
Time: 0.013s
> explain analyze select id from t_pk_bigint_t_timestamp_150d_200M where t < '2024-01-16' order by id asc limit 500;
+----------------------------+---------+---------+-----------+-----------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------+---------+---------+-----------+-----------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+---------+------+
| Projection_7 | 500.00 | 500 | root | | time:10.5ms, open:640.8µs, close:80.8µs, loops:2, RU:62.328424, Concurrency:OFF | ttl_test.t_pk_bigint_t_timestamp_150d_200m.id | 9.67 KB | N/A |
| └─Limit_12 | 500.00 | 500 | root | | time:10.5ms, open:637.5µs, close:79µs, loops:2 | offset:0, count:500 | N/A | N/A |
| └─TableReader_25 | 500.00 | 500 | root | | time:10.5ms, open:631.9µs, close:78.5µs, loops:1, cop_task: {num: 1, max: 9.68ms, proc_keys: 7136, tot_proc: 9.05ms, tot_wait: 45.1µs, copr_cache: disabled, build_task_duration: 590.1µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:9.65ms}} | data:Limit_24 | 8.34 KB | N/A |
| └─Limit_24 | 500.00 | 500 | cop[tikv] | | tikv_task:{time:9ms, loops:11}, scan_detail: {total_process_keys: 7136, total_process_keys_size: 3824896, total_keys: 7137, get_snapshot_time: 23.7µs, rocksdb: {key_skipped_count: 14271, block: {cache_hit_count: 137}}}, time_detail: {total_process_time: 9.05ms, total_suspend_time: 31.5µs, total_wait_time: 45.1µs, total_kv_read_wall_time: 7ms, tikv_wall_time: 9.27ms} | offset:0, count:500 | N/A | N/A |
| └─Selection_23 | 500.00 | 511 | cop[tikv] | | tikv_task:{time:9ms, loops:11} | lt(ttl_test.t_pk_bigint_t_timestamp_150d_200m.t, 2024-01-16 00:00:00.000000) | N/A | N/A |
| └─TableFullScan_22 | 1504.51 | 7136 | cop[tikv] | table:t_pk_bigint_t_timestamp_150d_200M | tikv_task:{time:7ms, loops:11} | keep order:true, stats:partial[t:unInitialized] | N/A | N/A |
+----------------------------+---------+---------+-----------+-----------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+---------+------+
6 rows in set
You can see that with @@tidb_enable_paging='ON', it schedules 3 cop tasks with scanning 13216 rows. But for @@tidb_enable_paging='OFF' , it scan much less rows. That is because paging will separate the requests in a same regions to multiple coptasks according to @@tidb_min_paging_size and tidb_max_paging_size, for the last cop task, it's Limit is still 500 and may consume more rows even it is not necessary.