Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 给表加入tiflash造成语句变慢是什么原因,tiflash用在什么情况比较好,也有可能语句执行时间本身就时快时慢,希望大佬们通过执行计划把把脉
【TiDB Usage Environment】Production Environment
【TiDB Version】
【Reproduction Path】Operations performed that led to the issue
【Encountered Issue: Issue Phenomenon and Impact】
【Resource Configuration】
【Attachments: Screenshots/Logs/Monitoring】
test4.sql (13.2 KB)
Execution Plan Before.xlsx (12.3 KB)
Execution Plan After.xlsx (12.3 KB)
Is the statement used to query a specific column?
Moreover, the execution of this statement is sometimes slow and sometimes fast, and I don’t know why.
Can you open the Dashboard? Find this SQL and see how many execution plans were generated.
I feel that adding TiFlash to this table might not have a significant impact; the main issue is the instability of the query.
Could you please take a look at the execution plan? I noticed that the CPU usage on our TiDB is not very high.
I see that after adding it, it’s faster. Without adding, it was 3 minutes 0.3 seconds, and after adding, it’s 1 minute 2.6 seconds. 
So it’s unstable. It didn’t add 50 seconds before.
I also don’t understand, the TiDB CPU isn’t maxed out, so why does running a query take 3 minutes?
Is there a problem with the SQL? It’s sometimes fast and sometimes slow.
The execution plan hasn’t changed, but there are two points where the time consumption differs significantly:
-
The time taken for aa and bb hash join:
1min 14.5s and 43.1s
-
The time taken for hashjoin_108:
1min and 3min
Check if the memory on several nodes is exhausted.
It seems there are still more.
What version of TiDB are you using?
The image you provided is not accessible. Please provide the text content you need translated.

The main thing is that I can’t understand why there’s such a big difference between the two SQLs in hash join.
└─HashJoin_108(Probe)
238266.09
511515
root
time:1m1.7s, loops:503, build_hash_table:{total:2.85s, fetch:2.81s, build:40.8ms}, probe:{concurrency:5, total:5m8.3s, max:1m1.7s, probe:2m12.8s, fetch:2m55.6s}
inner join, equal:[eq(lbx.atdpersonpaycode.personid, lbx.atdemployeecalendar.personid) eq(lbx.atdpersonpaycode.timecarddate, lbx.atdemployeecalendar.calendardate)]
43.6 MB
34.6 MB
└─HashJoin_108(Probe)
238266.09
511519
root
time:2m59.4s, loops:503, build_hash_table:{total:4.45s, fetch:4.28s, build:169.3ms}, probe:{concurrency:5, total:14m57.2s, max:2m59.4s, probe:11m11.5s, fetch:3m45.6s}
inner join, equal:[eq(lbx.atdpersonpaycode.personid, lbx.atdemployeecalendar.personid) eq(lbx.atdpersonpaycode.timecarddate, lbx.atdemployeecalendar.calendardate)]
43.8 MB
34.6 MB
I don’t understand either, it’s very strange.
You said that if the CPU is very slow, adding another machine would work, but there isn’t one either.