Abnormal Query Time in TiDB

Translated
1

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

Original topic: tidb查询耗时异常

| username: SamLi

【TiDB Environment】Production
【TiDB Version】v4.0.10
【Reproduction Path】
【Encountered Problem: Phenomenon and Impact】
Abnormal SQL execution time
Abnormal time consumption when querying partition tables that scan partitions with no data
【Resource Configuration】
【Attachments: Screenshots/Logs/Monitoring】
SQL:
SELECT
xxx,xxx,xxx,xxx,xxx,xxx,xxx,xxx,xxx
FROM
test_table
WHERE
(
aaaa IN (
‘xxx’,‘xxx’,‘xxx’
)
)
AND (
date IN (
‘xxx’,‘xxx’,‘xxx’
)
);

2
| username: SamLi | Original post link

Other partition scans take only tens of milliseconds.
Scanning the above-mentioned empty partition takes 40 minutes.

How should I troubleshoot this?

3
| username: xfworld | Original post link

You can refer to the official documentation:

4
| username: tidb菜鸟一只 | Original post link

The presence of the keyword stats:pseudo in the execution plan indicates that the table’s statistics are inaccurate. Please recollect the statistics.

5
| username: SamLi | Original post link

I don’t understand.

So what practical effect does this suggestion have?

6
| username: SamLi | Original post link

I don’t quite understand whether the execution plan is accurate or not. Why would an empty partition take 40 minutes?

I see that the index selection in the execution plan is as expected.

7
| username: xfworld | Original post link

If you want to master optimization methods, just take your time and check each one slowly.

If there’s anything you’re unsure about, feel free to ask further.

8
| username: SamLi | Original post link

A blank partition. The scan took 40 minutes.

Not quite sure what the issue is. Hope you can help analyze it.

9
| username: xfworld | Original post link

Partition pruning is only supported after version 5.X, and it actually matures in version 6.5 LTS.

Global partitioned indexes are similar. If you have high requirements for partitioning, you might consider upgrading your version (it is recommended to test before upgrading).

10
| username: SamLi | Original post link

I don’t understand…

11
| username: SamLi | Original post link

Are you a TiDB hater?

12
| username: xingzhenxiang | Original post link

aaaa, is date a composite index?

13
| username: SamLi | Original post link

The above fields ‘aaaa’ and ‘date’ form a composite primary key.

14
| username: xingzhenxiang | Original post link

Will this rewrite make it faster?

15
| username: Billmay表妹 | Original post link

Support in version 6.5 will be much better, do you want to give it a try?

16
| username: SamLi | Original post link

So I’m asking about SQL rewriting, right…

17
| username: SamLi | Original post link

Please provide some constructive suggestions, thank you.

18
| username: system | Original post link

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