Aggregate query did not use TiFlash

Translated
1

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

Original topic: 聚合查询没有走到tiflash

| username: TiDBer_zhw

【TiDB Usage Environment】Production Environment or Test Environment or POC
【TiDB Version】
5.4
【Encountered Problem】
Aggregate query does not use TiFlash

This query works

image
image1199×559 26.9 KB

But this one does not

image
image1366×494 23.5 KB

【Reproduction Path】What operations were performed to encounter the problem
【Problem Phenomenon and Impact】

【Attachments】 Related logs and monitoring (https://metricstool.pingcap.com/)

For questions related to performance optimization and fault troubleshooting, please download the script and run it. Please select all and copy-paste the terminal output results and upload them.

2
| username: ddhe9527 | Original post link

The optimizer sees that there is an index available on the paid_douyin_id field in the second SQL, so it uses the index on TiKV to go back to the table. The cost is lower than a full table scan on TiFlash, so it chooses TiKV. Not all aggregate queries will use TiFlash unless forced by methods such as engine isolation. For example, you can add the following hint after the SELECT in the second SQL:

/*+READ_FROM_STORAGE(TIFLASH[a])*/
3
| username: TiDBer_zhw | Original post link

Got it, thanks a lot.

4
| username: system | Original post link

This topic will be automatically closed 60 days after the last reply. No new replies are allowed.