Inaccurate Execution Plan for Large Tables in TiDB 7.1.0

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

Original topic: tidb 7.1.0 大表执行计划不准确

| username: TiDBer_yyy

[TiDB Usage Environment] Production Environment
[TiDB Version] tidb 7
[Reproduction Path]

  1. There are currently 2 TiDB clusters, tidb5 and tidb7. tidb5 is the production cluster, and tidb7 is the test cluster used for upgrades.

  2. The execution plans of tidb5 and tidb7 differ significantly. The execution time difference is 1-2 hours.

  3. Using SMP /*+ read_from_storage(tiflash[s]) */ and /*+ USE_INDEX(s, IDX_SUMMARY_TYPE) */ does not work.

  4. The large table st_summary_bill_wide_table has about 2.7 billion rows and has been loaded into tiflash.

The SQL is as follows:

SELECT COUNT(DISTINCT(I_UID)) I_USER_COUNT FROM st_summary_bill_wide_table s LEFT JOIN st_region_code_new p ON s.CH_USER_COUNTRY_CODE = p.CH_CODE WHERE (B_IS_POOR=1 AND I_STAT_DEALER_IS_YIYUN=1 AND I_STAT_IS_TEST=0 AND I_SUMMARY_TYPE IN (1,3,5,7,9,11,118,124))

Execution plan for tidb5:

| id                        task      estRows     operator info                                                                                                                                                                                                                                     actRows  execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                memory   disk
 StreamAgg_11              root      1           funcs:count(distinct stat.st_summary_bill_wide_table.i_uid)->Column#124                                                                                                                                                                           1        time:3m18s, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           31.7 MB  N/A
 └─IndexHashJoin_75        root      41121236.71 inner join, inner:IndexLookUp_72, outer key:stat.st_region_code_new.ch_code, inner key:stat.st_summary_bill_wide_table.ch_user_country_code, equal cond:eq(stat.st_region_code_new.ch_code, stat.st_summary_bill_wide_table.ch_user_country_code) 14616977 time:3m16.5s, loops:14276, inner:{total:3m18s, concurrency:8, task:1, construct:65.6µs, fetch:3m9.3s, build:34.6µs, join:8.61s}                                                                                                                                                                                                                                                                                                                                                                                                                                                             615.9 MB N/A
   ├─TableReader_56        root      52          data:Selection_55                                                                                                                                                                                                                                 52       time:702.1µs, loops:3, cop_task: {num: 1, max: 695.4µs, proc_keys: 0, rpc_num: 1, rpc_time: 679.3µs, copr_cache_hit_ratio: 1.00}                                                                                                                                                                                                                                                                                                                                                                                                                                                           1.35 KB  N/A
   │ └─Selection_55        cop[tikv] 52          eq(stat.st_region_code_new.b_is_poor, 1)                                                                                                                                                                                                          52       tikv_task:{time:5ms, loops:11}, scan_detail: {total_process_keys: 0, total_keys: 0, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}}                                                                                                                                                                                                                                                                                                                                                                                 N/A      N/A
   │   └─TableFullScan_54  cop[tikv] 6792        table:p, keep order:false                                                                                                                                                                                                                         6792     tikv_task:{time:5ms, loops:11}, scan_detail: {total_process_keys: 0, total_keys: 0, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}}                                                                                                                                                                                                                                                                                                                                                                                 N/A      N/A
   └─IndexLookUp_72        root      790793.01                                                                                                                                                                                                                                                  14616977 time:3m9.3s, loops:14276, index_task: {total_time: 3m6.4s, fetch_handle: 904.5ms, build: 709.7µs, wait: 3m5.5s}, table_task: {total_time: 25m8.2s, num: 362, concurrency: 8}                                                                                                                                                                                                                                                                                                                                                                                                                 104.0 MB N/A
     ├─IndexRangeScan_69   cop[tikv] 1523035.98  table:s, index:IDX_USER_COUNTY_CODE(CH_USER_COUNTRY_CODE), range: decided by [eq(stat.st_summary_bill_wide_table.ch_user_country_code, stat.st_region_code_new.ch_code)], keep order:false                                                        14617569 time:160.1ms, loops:14296, cop_task: {num: 40, max: 515ms, min: 10.8ms, avg: 201.3ms, p95: 466.4ms, max_proc_keys: 1062909, p95_proc_keys: 903186, tot_proc: 7.49s, tot_wait: 90ms, rpc_num: 40, rpc_time: 8.05s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:463ms, min:9ms, p80:303ms, p95:428ms, iters:14458, tasks:40}, scan_detail: {total_process_keys: 14617569, total_keys: 14617639, rocksdb: {delete_skipped_count: 15, key_skipped_count: 14617581, block: {cache_hit_count: 910, read_count: 8974, read_byte: 159.2 MB}}}                                                    N/A      N/A
     └─Selection_71        cop[tikv] 790793.01   eq(stat.st_summary_bill_wide_table.i_stat_is_test, 0), in(stat.st_summary_bill_wide_table.i_summary_type, 1, 3, 5, 7, 9, 11, 118, 124)                                                                                                            14616977 time:24m41.3s, loops:14653, cop_task: {num: 1294808, max: 7.85s, min: 239.9µs, avg: 12.9ms, p95: 37.4ms, max_proc_keys: 1365, p95_proc_keys: 38, tot_proc: 2h38m17s, tot_wait: 1h45m12.9s, rpc_num: 1294810, rpc_time: 4h38m8.5s, copr_cache_hit_ratio: 0.00}, backoff{regionMiss: 10ms}, tikv_task:{proc max:221ms, min:0s, p80:3ms, p95:9ms, iters:1398539, tasks:1294808}, scan_detail: {total_process_keys: 14533425, total_keys: 14565903, rocksdb: {delete_skipped_count: 3, key_skipped_count: 100220, block: {cache_hit_count: 189212838, read_count: 9736461, read_byte: 97.5 GB}}} N/A      N/A
       └─TableRowIDScan_70 cop[tikv] 1523035.98  table:s, keep order:false

Execution plan for tidb7:

| id                             task      estRows     operator info                                                                                                                                                                                                                                              actRows    execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          memory  disk
 StreamAgg_10                   root      1           funcs:count(distinct stat.st_summary_bill_wide_table.i_uid)->Column#124                                                                                                                                                                                    1          time:2h18m48.9s, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                33.0 MB N/A
 └─IndexJoin_70                 root      22.14       inner join, inner:IndexLookUp_69, outer key:stat.st_summary_bill_wide_table.ch_user_country_code, inner key:stat.st_region_code_new.ch_code, equal cond:eq(stat.st_summary_bill_wide_table.ch_user_country_code, stat.st_region_code_new.ch_code)          12733799   time:2h18m45.1s, loops:12437, inner:{total:3h37m35.7s, concurrency:5, task:49173, construct:23m0.4s, fetch:3h14m34.4s, build:807.6ms}, probe:1m53.4s                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    22.9 MB N/A
   ├─IndexLookUp_61(Build)      root      22.14                                                                                                                                                                                                                                                           1258606479 time:2h10m18.4s, loops:1229113, index_task: {total_time: 2h18m47.2s, fetch_handle: 1m7.2s, build: 102.6ms, wait: 2h17m39.9s}, table_task: {total_time: 11h28m51.8s, num: 62407, concurrency: 5}, next: {wait_index: 47.3ms, wait_table_lookup_build: 2.92ms, wait_table_lookup_resp: 2h8m55.6s}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         93.9 MB N/A
   │ ├─IndexRangeScan_58(Build) cop[tikv] 22135971.82 table:s, index:IDX_SUMMARY_TYPE(I_SUMMARY_TYPE), range:[1,1], [3,3], [5,5], [7,7], [9,9], [11,11], [118,118], [124,124], keep order:false, stats:partial[i_summary_type:unInitialized, i_stat_dealer_is_yiyun:unInitialized, i_stat_is_test:unInitialized] 1277395058 time:2.57s, loops:1252031, cop_task: {num: 36226, max: 1.8s, min: 660.9µs, avg: 35.2ms, p95: 146.2ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 10m15s, tot_wait: 36.6s, rpc_num: 36226, rpc_time: 21m15.1s, copr_cache_hit_ratio: 0.00, build_task_duration: 2.46ms, max_distsql_concurrency: 60}, backoff{regionMiss: 2ms}, tikv_task:{proc max:1.79s, min:0s, avg: 31.6ms, p80:26ms, p95:138ms, iters:1391311, tasks:36226}, scan_detail: {total_process_keys: 1277394834, total_process_keys_size: 58760162364, total_keys: 1277481045, get_snapshot_time: 31.8s, rocksdb: {delete_skipped_count: 2156, key_skipped_count: 1277446291, block: {cache_hit_count: 385420, read_count: 1563890, read_byte: 2.59 GB, read_time: 8.55s}}}                                                                                                                    N/A     N/A
   │ └─Selection_60(Probe)      cop[tikv] 22.14       eq(stat.st_summary_bill_wide_table.i_stat_dealer_is_yiyun, 1), eq(stat.st_summary_bill_wide_table.i_stat_is_test, 0)                                                                                                                                       1258606479 time:11h21m51.7s, loops:1373707, cop_task: {num: 251343, max: 4.78s, min: 0s, avg: 278.9ms, p95: 886.2ms, max_proc_keys: 20992, p95_proc_keys: 19204, tot_proc: 14h7m2.6s, tot_wait: 7m5.4s, rpc_num: 234062, rpc_time: 19h28m12.4s, copr_cache_hit_ratio: 0.01, build_task_duration: 59.4s, max_distsql_concurrency: 60, max_extra_concurrency: 199, store_batch_num: 17403, store_batch_fallback_num: 1764}, backoff{regionMiss: 50ms}, tikv_task:{proc max:4.71s, min:0s, avg: 271.3ms, p80:485ms, p95:865ms, iters:2231739, tasks:251343}, scan_detail: {total_process_keys: 1276853406, total_process_keys_size: 1348452498966, total_keys: 1501760419, get_snapshot_time: 5m49.3s, rocksdb: {delete_skipped_count: 1545, key_skipped_count: 1127346316, block: {cache_hit_count: 9025552284, read_count: 127214601, read_byte: 1417.5 GB, read_time: 5h59m25.9s}}}N/A     N/A
   │   └─TableRowIDScan_59      cop[tikv] 22135971.82 table:s, keep order:false, stats:partial[i_summary_type:unInitialized, i_stat_dealer_is_yiyun:unInitialized, i_stat_is_test:unInitialized]                                                                                                                 1277395058 tikv_task:{proc max:4.71s, min:0s, avg: 271ms, p80:484ms, p95:864ms, iters:2231739, tasks:251343}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       N/A     N/A
   └─IndexLookUp_69(Probe)      root      22.14                                                                                                                                                                                                                                                           1925153    time:3h11m56.1s, loops:98345, index_task: {total_time: 2h46m55.8s, fetch_handle: 2h46m55.2s, build: 111.6ms, wait: 515.1ms}, table_task: {total_time: 48m27.1s, num: 98335, concurrency: 5}, next: {wait_index: 2h23m40.3s, wait_table_lookup_build: 20s, wait_table_lookup_resp: 47m54s}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               8.02 KB N/A
     ├─IndexRangeScan_66(Build) cop[tikv] 22.14       table:p, index:UNIQ_CODE(CH_CODE), range: decided by [eq(stat.st_region_code_new.ch_code, stat.st_summary_bill_wide_table.ch_user_country_code)], keep order:false                                                                                         126747444  time:2h46m49.3s, loops:245625, cop_task: {num: 196576, max: 1.23s, min: 945.3µs, avg: 50.7ms, p95: 166.9ms, max_proc_keys: 1115, p95_proc_keys: 992, tot_proc: 1h13m59.4s, tot_wait: 1m23.3s, rpc_num: 196601, rpc_time: 2h46m5.2s, copr_cache_hit_ratio: 0.00, build_task_duration: 3.38s, max_distsql_concurrency: 1}, backoff{regionMiss: 58ms}, tikv_task:{proc max:1.22s, min:0s, avg: 46.3ms, p80:54ms, p95:160ms, iters:843735, tasks:196576}, scan_detail: {total_process_keys: 126747444, total_process_keys_size: 5576887536, total_keys: 253642294, get_snapshot_time: 32.8s, rocksdb: {key_skipped_count: 207989897, block: {cache_hit_count: 1815330552, read_count: 1284, read_byte: 18.9 MB, read_time: 38.8ms}}}                                                                                                                                        N/A     N/A
     └─Selection_68(Probe)      cop[tikv] 22.14       eq(stat.st_region_code_new.b_is_poor, 1)                                                                                                                                                                                                                   1925153    time:48m3.9s, loops:147531, cop_task: {num: 98335, max: 1.2s, min: 795.9µs, avg: 29.1ms, p95: 112.7ms, max_proc_keys: 1787, p95_proc_keys: 1687, tot_proc: 15m6.5s, tot_wait: 39.8s, rpc_num: 98342, rpc_time: 47m43.8s, copr_cache_hit_ratio: 0.00, build_task_duration: 3.82s, max_distsql_concurrency: 1, max_extra_concurrency: 1}, backoff{regionMiss: 14ms}, tikv_task:{proc max:1.2s, min:0s, avg: 26.7ms, p80:21ms, p95:109ms, iters:589696, tasks:98335}, scan_detail: {total_process_keys: 126747444, total_process_keys_size: 21972068432, total_keys: 145629249, get_snapshot_time: 13.4s, rocksdb: {key_skipped_count: 198152920, block: {cache_hit_count: 328545465, read_count: 1080, read_byte: 148.3 MB, read_time: 162.4ms}}}                                                                                                                         N/A     N/A
       └─TableRowIDScan_67      cop[tikv] 22.14       table:p, keep order:false                                                                                                                                                                                                                                  126747444  tikv_task:{proc max:1.2s, min:0s, avg: 26.6ms, p80:21ms, p95:109ms, iters:589696, tasks:98335}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          N/A     N/A |

Statistics results: No statistics received for the large table, but it was automatically analyzed for one day yesterday.

mysql> SHOW COLUMN_STATS_USAGE where Table_name='st_summary_bill_wide_table';
Empty set (0.06 sec)

mysql> show global variables like '%tidb_an
| username: dba-kit | Original post link

You can execute it to see if the statistics have been collected properly.

| username: dba-kit | Original post link

PS: TiDB allocates very low resources to auto_analyze by default. It’s better to manually analyze some large tables.

| username: TiDBer_yyy | Original post link

Based on historical data, it seems that the statistics are inaccurate. Is there any way to speed it up?

mysql> SHOW STATS_HEALTHY where table_name ='st_summary_bill_wide_table';
+---------+----------------------------+----------------+---------+
| Db_name | Table_name                 | Partition_name | Healthy |
+---------+----------------------------+----------------+---------+
| stat    | st_summary_bill_wide_table |                |      99 |
+---------+----------------------------+----------------+---------+
| username: dba-kit | Original post link

Additionally, could you provide the table structure? Otherwise, it’s impossible to determine which table the I_UID field belongs to. If it’s a field in the st_summary_bill_wide_table, theoretically it should use TiFlash.

| username: dba-kit | Original post link

show stats_meta where Table_name='st_summary_bill_wide_table';

Looking at stats_meta is more accurate. Can you share the results for 7.1?

| username: TiDBer_yyy | Original post link

+---------+----------------------------+----------------+---------------------+--------------+------------+
| Db_name | Table_name                 | Partition_name | Update_time         | Modify_count | Row_count  |
+---------+----------------------------+----------------+---------------------+--------------+------------+
| stat    | st_summary_bill_wide_table |                | 2023-09-14 10:55:23 |      1662949 | 2767549902 |
+---------+----------------------------+----------------+---------------------+--------------+------------+

The table has been recorded in TiFlash

| username: TiDBer_yyy | Original post link

Resolved, the query became faster after manual analyze.

| username: 人如其名 | Original post link

The execution plan for your production environment with TiDB 5 is also not optimal. In this scenario, using /*+ hash_join(s,p) */ should be the optimal choice, and it will also consume less memory. I suggest you give it a try.

| username: oceanzhang | Original post link

Does TiDB automatically collect statistics?

| username: TiDBer_yyy | Original post link

Yes, there are scheduled tasks every day to proactively collect statistical information.

| username: zhanggame1 | Original post link

The execution plan is inaccurate, manually collect statistics. Automatic collection requires more than 50% data change.

| username: system | Original post link

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