Issue of Suboptimal Execution Plan Caused by Parentheses

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

Original topic: 圆括号导致执行计划非最优问题

| username: wzf0072

[TiDB Usage Environment] Production Environment
[TiDB Version] v6.5.2
[Reproduction Path] Operations performed that led to the issue
[Encountered Issue: Phenomenon and Impact] When using parentheses in the WHERE clause or join condition, it uses the TiKV engine and takes 108 seconds.

Removing the parentheses in the WHERE clause uses TiFlash.


Removing the parentheses in the join condition uses TiFlash.

Removing the “shell” of count(0) and executing the subquery (keeping the parentheses in the WHERE clause and join condition) uses TiFlash and takes 0.99 seconds.

[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page

[Attachments: Screenshots/Logs/Monitoring]

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

If parentheses are added, can the hint be applied? /*+ read_from_storage(tiflash[alias_a,alias_b]) */

| username: wzf0072 | Original post link

The HINT is also not effective.

| username: wzf0072 | Original post link

PLAN REPLAYER DUMP EXPLAIN ANALYZE execution plan
sql0.txt (9.2 KB)

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

It should be placed here.

| username: wzf0072 | Original post link

Both methods have bound the execution plan, but it did not take effect.

| username: zhaokede | Original post link

It didn’t take effect, but the query time has decreased.

| username: wzf0072 | Original post link

It hasn’t been reduced, it’s just that the production execution plan has been made but not executed.

| username: redsong | Original post link

Anything is fine.

| username: TIDB-Learner | Original post link

If a subquery involves a join between tables, and parentheses are added after on or where, TiFlash will not be used?? Awaiting resolution…

| username: wzf0072 | Original post link

@Billmay Is it a bug?

| username: TiDBer_JUi6UvZm | Original post link

The first execution won’t fetch data from the disk, right?

| username: TiDBer_JUi6UvZm | Original post link

Could you try running the first image again?

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

Generally, setting it at the session level like this will tell you the reason why MPP cannot be used.

| username: wzf0072 | Original post link

Removing the parentheses in the WHERE clause eliminates the warning.

| username: zhang_2023 | Original post link

Why does it feel like a bug?

| username: Billmay表妹 | Original post link

I’ll provide some feedback and ask the development team to take a look.

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

This should be a bug. No hint was found that prevents MPP.

| username: 这里介绍不了我 | Original post link

Mark it.

| username: aytrack | Original post link

Check whether a binding has been added before for the cases with and without parentheses above.