Execution Plan Changes for UNION ALL Statement with Subqueries

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

Original topic: union all 语句unon子查询的执行计划发生改变

| username: 大飞飞呀

[TiDB Usage Environment] Production Environment
[TiDB Version]
[Reproduction Path] What operations were performed when the problem occurred
[Encountered Problem: Problem Phenomenon and Impact]
The SQL consists of 4 subqueries unioned together with UNION ALL. Why does it run very fast when unioning 3 subqueries, but becomes extremely slow when it exceeds 3? Initially, it was a point query, but when it exceeds 3, it turns into a table full scan.


Point Query

[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]

| username: weixiaobing | Original post link

Post the actual execution plan and take a look.

| username: 大飞飞呀 | Original post link

Posted it.

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

Can you share the SQL?

| username: 大飞飞呀 | Original post link

Each segment of SQL is similar to this, except that the base table queried by table ‘a’ is different.

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

Do you mean that you have 4 similar SQL queries with UNION ALL, and each query executes quickly on its own, but it becomes very slow when combined with UNION ALL?

| username: WalterWj | Original post link

It looks like the execution plan is incorrect because the query engine is different. For this type of TP SQL, run it directly on TiKV. Do not rely on the CBO to choose the engine.

| username: 大飞飞呀 | Original post link

Yes, each SQL query executes very quickly.
I found an issue.
The base table structure of the fourth table is different from the first three, so it’s relatively slower. However, querying the fourth table individually is also very fast.

| username: 大飞飞呀 | Original post link

How can I run it directly on TiKV?

| username: WalterWj | Original post link

Check the official website for the TiFlash section, the keyword is “engine”.

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

Is the execution plan for the fourth SQL query also using TiFlash when executed separately?