Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 咨询 两个 SQl 执行计划差异
|
username: 等一分钟
Production environment
TiDB version: v5.2.3
Details: The execution plans of two SQL queries differ only in the topmost part. I would like to consult about this.
The image is not visible. Please provide the text you need translated.
Sorry, I can’t translate the content from the image. Please provide the text you need translated.
The amount of data returned by the two SQL queries is significantly related to the execution time.
However, the part with the largest amount of queried data is at the bottom of the execution plan, and both parts are displayed the same.
Please format the SQL and convert the execution plan to text. This will make it easier to review.
So, what is your question? Are you asking why the results of the two SQL queries are different, or do you not understand the execution plan?
The image is not visible. Please provide the text you need translated.
The two execution plans are the same in this part, which should also be the slowest part, but the overall execution plan is that one is very fast and the other is very slow.
These two SQL statements only differ in the values of lb.classcode.
| Projection_16 | 296350.54 | 9113650 | root | time:5m21.4s, loops:8902, Concurrency:5 | greentown.atdpersonpaycode.personid, greentown.atdpersonpaycode.timecarddate, Late->Column#127, F01->Column#128, case(gt(ifnull(if(eq(greentown.atd_attendance_class.classcode, H01), Column#122, 0), 0), 0), 0, gt(ifnull(if(eq(greentown.atd_attendance_class.classcode, F01), Column#122, 0), 0), 0), if(eq(greentown.atd_attendance_class.classcode, F01), Column#122, 0), if(eq(greentown.atd_attendance_class.classcode, F02), Column#122, 0))->Column#129, Column#123 | 1.09 MB | N/A | |
|---|---|---|---|---|---|---|---|---|
| └─HashAgg_17 | 296350.54 | 9113650 | root | time:5m21.4s, loops:8902 | group by:greentown.atd_attendance_class.classcode, greentown.atdpersonpaycode.personid, greentown.atdpersonpaycode.timecarddate, funcs:sum(distinct greentown.atdpersonpaycode.payhours)->Column#122, funcs:max(greentown.edf_process_task.endprocesstime)->Column#123, funcs:firstrow(greentown.atdpersonpaycode.personid)->greentown.atdpersonpaycode.personid, funcs:firstrow(greentown.atdpersonpaycode.timecarddate)->greentown.atdpersonpaycode.timecarddate, funcs:firstrow(greentown.atd_attendance_class.classcode)->greentown.atd_attendance_class.classcode | 435.3 MB | 4.44 GB | |
| └─Projection_18 | 358411207.00 | 9364843 | root | time:24.3s, loops:9149, Concurrency:5 | greentown.edf_process_task.endprocesstime, greentown.atdpersonpaycode.personid, greentown.atdpersonpaycode.payhours, greentown.atdpersonpaycode.timecarddate, greentown.atd_attendance_class.classcode | 1.01 MB | N/A | |
| └─HashJoin_20 | 358411207.00 | 9364843 | root | time:25.7s, loops:9149, build_hash_table:{total:3.48ms, fetch:3.46ms, build:26.9µs}, probe:{concurrency:5, total:2m50.3s, max:34.1s, probe:39.9s, fetch:2m10.5s} | inner join, equal:[eq(greentown.atd_attendance_class.id, greentown.atdpersonpaycode.paycode)] | 32.5 KB | 0 Bytes | |
| ├─Batch_Point_Get_21(Build) | 33.00 | 33 | root | table:atd_attendance_class, index:uk_atd_attendance_class_ClassCode(ClassCode) | time:3.35ms, loops:2, BatchGet:{num_rpc:2, total_time:3.12ms}, scan_detail: {total_process_keys: 66, total_keys: 66, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 604, read_count: 1, read_byte: 63.8 KB}}} | keep order:false, desc:false | N/A | N/A |
| └─TableReader_50(Probe) | 663230578.58 | 10612180 | root | time:26.6s, loops:10575, cop_task: {num: 387, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_49 | N/A | N/A | |
| └─ExchangeSender_49 | 663230578.58 | 10612180 | cop[tiflash] | tiflash_task:{time:32.1s, loops:2670, threads:8} | ExchangeType: PassThrough | N/A | N/A | |
| └─HashJoin_22 | 663230578.58 | 10612180 | cop[tiflash] | tiflash_task:{time:32.1s, loops:2670, threads:8} | inner join, equal:[eq(greentown.attendance_submit_form.personid, greentown.atdpersonpaycode.personid)], other cond:ge(greentown.atdpersonpaycode.timecarddate, date_format(cast(greentown.attendance_submit_form.startdate, datetime(6) BINARY), %Y-%m-%d)), le(greentown.atdpersonpaycode.timecarddate, date_format(cast(greentown.attendance_submit_form.enddate, datetime(6) BINARY), %Y-%m-%d)) | N/A | N/A | |
| ├─ExchangeReceiver_45(Build) | 1158664.23 | 112624 | cop[tiflash] | tiflash_task:{time:1.35s, loops:3, threads:8} | N/A | N/A | ||
| │ └─ExchangeSender_44 | 1158664.23 | 112624 | cop[tiflash] | tiflash_task:{time:1.3s, loops:39, threads:8} | ExchangeType: HashPartition, Hash Cols: [name: greentown.attendance_submit_form.personid, collate: utf8_bin] | N/A | N/A | |
| │ └─HashJoin_38 | 1158664.23 | 112624 | cop[tiflash] | tiflash_task:{time:1.3s, loops:39, threads:8} | inner join, equal:[eq(greentown.edf_process_task.processinstanceid, greentown.attendance_submit_form.processinstanceid)] | N/A | N/A | |
| │ ├─ExchangeReceiver_42(Build) | 55110.02 | 5148 | cop[tiflash] | tiflash_task:{time:1.23s, loops:1, threads:8} | N/A | N/A | ||
| │ │ └─ExchangeSender_41 | 55110.02 | 5148 | cop[tiflash] | tiflash_task:{time:1.23s, loops:3, threads:8} | ExchangeType: Broadcast | N/A | N/A | |
| │ │ └─Selection_40 | 55110.02 | 5148 | cop[tiflash] | tiflash_task:{time:1.23s, loops:3, threads:8} | eq(greentown.edf_process_task.prsid, ProcessAttendanceSubmitForm), eq(greentown.edf_process_task.status, COMPLETED), ge(ifnull(cast(greentown.edf_process_task.endprocesstime, var_string(23)), ), 2022-12-01 17:00:27.507), not(isnull(greentown.edf_process_task.processinstanceid)) | N/A | N/A | |
| │ │ └─TableFullScan_39 | 4313827.00 | 4334797 | cop[tiflash] | table:pro | tiflash_task:{time:679.2ms, loops:70, threads:8} | keep order:false | N/A | N/A |
| │ └─TableFullScan_43(Probe) | 1818913.00 | 1818821 | cop[tiflash] | table:sub | tiflash_task:{time:535.8ms, loops:39, threads:8} | keep order:false | N/A | N/A |
| └─ExchangeReceiver_48(Probe) | 169633907.00 | 169622421 | cop[tiflash] | tiflash_task:{time:1.72s, loops:2670, threads:8} | N/A | N/A | ||
| └─ExchangeSender_47 | 169633907.00 | 169622421 | cop[tiflash] | tiflash_task:{time:5.86s, loops:2670, threads:8} | ExchangeType: HashPartition, Hash Cols: [name: greentown.atdpersonpaycode.personid, collate: utf8_bin] | N/A | N/A | |
| └─TableFullScan_46 | 169633907.00 | 169622421 | cop[tiflash] | table:P | tiflash_task:{time:5.86s, loops:2670, threads:8} | keep order:false | N/A | N/A |
Trace the two SQLs to see where the specific time difference lies. It seems that sending data to the client is more time-consuming.
After changing to count(*), one takes 50 seconds, and the other takes 30 seconds.
The count one is 50 seconds, and the other one is 30 seconds.
Previously, it took 50 seconds to be slow, and another fast one took 30 seconds.
So, is there a preliminary conclusion? Both of your SQL queries are performing full table scans, so the execution time of the lower-level operators should be roughly the same. However, the difference in the amount of data returned to the upper-level hash join by the two SQL queries leads to some time differences. When the data is sent to the client later, there is a huge time difference. Overall, it is normal for the time difference to be quite large.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: 
|
username: 
Yes, I think so. Thanks!
|
username: