TiFLASH: Different Execution Plans for Identical Table Structures and Identical SQL Queries in Different Clusters

Translated
1

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

Original topic: TiFLASH 相同表结构相同sql不同集群执行计划不同

| username: Hacker_ojLJ8Ndr

Version: 7.1.1

Environment One:

id                     |estRows   |actRows|task        |access object           |execution info                                                                                                                                                                                                                                                 |operator info                                                                                                                                                                                                                                                  |memory |disk|
-----------------------+----------+-------+------------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----+
TableReader_12         |16702.27  |5548   |root        |                        |time:126.5ms, loops:7, RU:0.000000, cop_task: {num: 19, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00}                                                                                                                                        |MppVersion: 1, data:ExchangeSender_11                                                                                                                                                                                                                          |80.4 KB|N/A |
└─ExchangeSender_11    |16702.27  |5548   |mpp[tiflash]|                        |tiflash_task:{proc max:120.6ms, min:90.2ms, avg: 109.8ms, p80:120.6ms, p95:120.6ms, iters:16, tasks:3, threads:96}                                                                                                                                             |ExchangeType: PassThrough                                                                                                                                                                                                                                      |N/A    |N/A |
  └─Projection_5       |16702.27  |5548   |mpp[tiflash]|                        |tiflash_task:{proc max:120.6ms, min:90.2ms, avg: 109.5ms, p80:120.6ms, p95:120.6ms, iters:16, tasks:3, threads:96}                                                                                                                                             |mbase.rel_xhs_brand_note.noteid, cast(mbase.rel_xhs_brand_note.industryid, var_string(11))->Column#18, cast(mbase.rel_xhs_brand_note.categoryid, var_string(11))->Column#19, cast(mbase.rel_xhs_brand_note.brandid, var_string(11))->Column#20, cast(mbase.rel_|N/A    |N/A |
    └─Selection_10     |16702.27  |5548   |mpp[tiflash]|                        |tiflash_task:{proc max:120.6ms, min:90.2ms, avg: 109.5ms, p80:120.6ms, p95:120.6ms, iters:16, tasks:3, threads:96}                                                                                                                                             |eq(mbase.rel_xhs_brand_note.categoryid, 0), eq(mbase.rel_xhs_brand_note.filtered, 1), ge(mbase.rel_xhs_brand_note.updatetime, 2023-09-20 06:54:37.000000), lt(mbase.rel_xhs_brand_note.updatetime, 2023-09-21 06:39:26.207000), not(isnull(mbase.rel_xhs_brand_|N/A    |N/A |
      └─TableFullScan_9|2178551.73|6204   |mpp[tiflash]|table:rel_xhs_brand_note|tiflash_task:{proc max:120.6ms, min:90.2ms, avg: 109.5ms, p80:120.6ms, p95:120.6ms, iters:32, tasks:3, threads:96}, tiflash_scan:{dtfile:{total_scanned_packs:1604, total_skipped_packs:85524, total_scanned_rows:13080590, total_skipped_rows:692340548, total|pushed down filter:in(mbase.rel_xhs_brand_note.brandid, 88, 344, 472, 600, 728, 856, 984, 1112, 1368, 690136, 690264, 690392, 690648, 690776, 630232, 960088, 960344, 960472, 960856, 960984, 961112, 961368, 961496, 961624, 961752, 961880, 962008, 962136, 9|N/A    |N/A |

Environment Two:

id                   |estRows   |actRows|task        |access object           |execution info                                                                                                                                                                                                                                                 |operator info                                                                                                                                                                                                                                                  |memory  |disk|
---------------------+----------+-------+------------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+----+
Projection_4         |0.00      |5548   |root        |                        |time:2.18s, loops:7, RU:303.886963, Concurrency:OFF                                                                                                                                                                                                            |mbase.rel_xhs_brand_note.noteid, cast(mbase.rel_xhs_brand_note.industryid, var_string(11))->Column#17, cast(mbase.rel_xhs_brand_note.categoryid, var_string(11))->Column#18, cast(mbase.rel_xhs_brand_note.brandid, var_string(11))->Column#19, cast(mbase.rel_|440.9 KB|N/A |
└─TableReader_7      |0.00      |5548   |root        |                        |time:2.18s, loops:7, cop_task: {num: 1165, max: 0s, min: 0s, avg: 57.6ms, p95: 113.3ms, rpc_num: 1167, rpc_time: 1m7.1s, copr_cache_hit_ratio: 0.00, build_task_duration: 4.9ms, max_distsql_concurrency: 32}, backoff{regionMiss: 2ms}                        |data:Selection_6                                                                                                                                                                                                                                               |112.6 KB|N/A |
  └─Selection_6      |0.00      |5548   |cop[tiflash]|                        |tiflash_task:{proc max:205.8ms, min:5.76ms, avg: 51.8ms, p80:76.5ms, p95:108.2ms, iters:97, tasks:1165, threads:1165}                                                                                                                                          |eq(mbase.rel_xhs_brand_note.categoryid, 0), eq(mbase.rel_xhs_brand_note.filtered, 1), ge(mbase.rel_xhs_brand_note.updatetime, 2023-09-20 06:54:37.000000), lt(mbase.rel_xhs_brand_note.updatetime, 2023-09-21 06:39:26.207000), not(isnull(mbase.rel_xhs_brand_|N/A     |N/A |
    └─TableFullScan_5|2062771.00|1433979|cop[tiflash]|table:rel_xhs_brand_note|tiflash_task:{proc max:205.8ms, min:5.76ms, avg: 51.7ms, p80:76.5ms, p95:107.6ms, iters:236, tasks:1165, threads:1165}, tiflash_scan:{dtfile:{total_scanned_packs:50597, total_skipped_packs:342897, total_scanned_rows:411503365, total_skipped_rows:277851415|pushed down filter:in(mbase.rel_xhs_brand_note.brandid, 88, 344, 472, 600, 728, 856, 984, 1112, 1368, 690136, 690264, 690392, 690648, 690776, 630232, 960088, 960344, 960472, 960856, 960984, 961112, 961368, 961496, 961624, 961752, 961880, 962008, 962136, 9|N/A     |N/A |

In Environment One, TiFlash is used without any warnings when not enforcing execution. In Environment Two, TiFlash is enforced but not used, and a warning is reported as follows:

Scalar function 'cast'(signature: CastIntAsString, return type: var_string(20)) is not supported to push down to tiflash now.

What is the reason for this, and how should the issue in Environment Two be addressed?

2
| username: zhanggame1 | Original post link

Are the database version numbers the same?

3
| username: Hacker_ojLJ8Ndr | Original post link

The same.

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

Could you please share the SQL? Let’s check the error.

Scalar function 'cast' (signature: CastIntAsString, return type: var_string(20)) is not supported to push down to TiFlash now.

It seems that casting an integer to a string is not supported for pushdown to TiFlash, but your version is the same, so it shouldn’t be the case…

5
| username: Hacker_ojLJ8Ndr | Original post link

The versions of the two environments:

image
image963×316 15.3 KB

SQL:

SELECT
  noteid,
  cast(industryid AS char) AS industryid,
  cast(categoryid AS char) AS categoryid,
  cast(brandid AS char) AS brandid,
  cast(commodityid AS char),
  uid
FROM
  rel_xhs_brand_note
WHERE
  updatetime >= '2023-09-20T06:54:37'
  AND updatetime < '2023-09-21T06:39:26.207'
  AND filtered = 1
  AND pos IS NOT NULL
  AND categoryid = 0
  AND brandid IN (
    88,
    344,
    472,
    600,
    728,
    856,
    984,
    1112,
    1368,
    690136,
    690264,
    690392,
    690648,
    690776,
    630232,
    960088,
    960344,
    960472,
    960856,
    960984,
    961112,
    961368,
    961496,
    961624,
    961752,
    961880,
    962008,
    962136,
    962264,
    962520,
    962648,
    962776,
    963288,
    963416,
    963544,
    963672,
    964056,
    964312,
    964440,
    964568,
    964696,
    964952,
    965336,
    965464,
    900056,
    900184,
    900312,
    965848,
    900568,
    966104,
    900696,
    966232,
    966360,
    966488,
    966744,
    966872,
    967384,
    967640,
    967768,
    967896,
    968152,
    968536,
    968664,
    968920,
    969176,
    969304,
    969432,
    969560,
    970072,
    970200,
    970456,
    970712,
    970840,
    970968,
    971096,
    971224,
    971352,
    971480,
    971608,
    971864,
    971992,
    972120,
    972376,
    972504,
    972632,
    972760,
    972888,
    973144,
    973272,
    973400,
    973528,
    973656,
    973784,
    973912,
    974040,
    974168,
    974296,
    450008,
    974424,
    974552,
    974680,
    974808,
    974936,
    975576,
    975704,
    975960,
    976088,
    976216,
    976344,
    976472,
    976600,
    976728,
    976856,
    976984,
    977112,
    977240,
    977496,
    977624,
    977752,
    977880,
    978264,
    978392,
    978520,
    978648,
    978776,
    978904,
    979032,
    979160,
    979288,
    979416,
    979544
  );
6
| username: 像风一样的男子 | Original post link

Try running an ANALYZE on the table.

7
| username: Hacker_ojLJ8Ndr | Original post link

Done it, same result.

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

Are the tidb_enforce_mpp parameters the same on both sides?

9
| username: Hacker_ojLJ8Ndr | Original post link

Similarly, tidb_enforce_mpp=off, tidb_allow_mpp=on

10
| username: Billmay表妹 | Original post link

Check if all components of both versions are 7.1.1. Is it possible that some components in a cluster might fail to upgrade successfully?

11
| username: Hacker_ojLJ8Ndr | Original post link

Environment 1:

image
image779×251 9.12 KB

Environment 2:
image

All components in both environments are version 7.1.1.

12
| username: zhanggame1 | Original post link

Is there a difference in the amount of data in the queried tables?

13
| username: Hacker_ojLJ8Ndr | Original post link

The data volume queried is consistent on both sides, both showing 5548 in the execution plan.

14
| username: zhanggame1 | Original post link

How about restarting the TiDB server node? Could there be an issue with the cached execution plan?

15
| username: Hacker007 | Original post link

Try executing ANALYZE TABLE on both sides.

16
| username: Hacker_ojLJ8Ndr | Original post link

I have done it, and the result is the same.

17
| username: xingzhenxiang | Original post link

There’s no way to try upgrading the version, the latest one.

18
| username: 有猫万事足 | Original post link

I agree with @tidb菜鸟一只’s statement. There shouldn’t be warnings on one side and not on the other. If there are no issues with the parameters and the analysis has been done, why not compare the table structures? Are there any fields with the same name but different types?

19
| username: ShawnYan | Original post link

Let’s first take a look at tiup cluster display.

20
| username: 像风一样的男子 | Original post link

You still need to find the differences between the two clusters, whether there are different parameter settings.