Issues with SQL Execution Plan

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

Original topic: sql执行计划问题

| username: 胡杨树旁

When executing the above SQL, the execution plan is displayed.


Question: The condition on table c is very good, and table c is executed first. Why doesn’t it directly use the condition on table c to access the index on table m’s topId, but instead uses the index: idx_plc_main_bustype_actualId(businesstype, classcode, lsteffverflag, lstverflag, actualid) on table m?

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

You should first query the conditions inside the “in” and replace the statements after “in”, then the index should be used…

| username: 胡杨树旁 | Original post link

It is possible to use the index when the specific data is placed in the IN condition, but I am curious why it does not directly use the index of the topId in the m table through the condition of the c table. It feels a bit counterintuitive.

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

The subquery is like this, try changing it to an inner join.

| username: 胡杨树旁 | Original post link

Changing it to an inner join would work, but the business logic requires a left join. Not sure if there are other optimization methods.

| username: 人如其名 | Original post link

What I understand should be related to join reorder. In version 5.4, there should not be outer join reorder yet, so after encountering your left join here, there is no reorder.

  1. Try modifying the statement, from plc_main m where m.toopId in (xxx) left outer join plp_endorse e on to see if it can reach the desired execution plan;

  2. Upgrade to version 6.1 or later:

tidb_enable_outer_join_reorder introduced from version v6.1.0

  • Scope: SESSION | GLOBAL
  • Persisted to cluster: Yes
  • Default value: ON in v6.1.0, i.e., enabled by default. OFF in versions after v6.1.0, i.e., disabled by default.
  • From v6.1.0, TiDB’s Join Reorder algorithm starts to support Outer Join. This variable is used to control this support behavior. Disabled by default, i.e., Join Reorder for Outer Join is not enabled.
  • For upgrades from versions before v6.1.0 to v6.1.0 and later versions, the default value of this variable is OFF. For upgrades from v6.1.0 to later versions, the default value is ON.

Try using a newer version, such as 6.5.4 or 7.1.1, and enable this parameter to see if it can reach the desired execution plan?
However, currently, join reorder is rule-based, so it might still not reach what you want.

| username: 人如其名 | Original post link

I did a simple test, and both solutions are feasible. You can try them out. Below is an example using tpch1.

Table structure:

mysql> show create table customer \G
*************************** 1. row ***************************
       Table: customer
Create Table: CREATE TABLE `customer` (
  `C_CUSTKEY` bigint(20) NOT NULL,
  `C_NAME` varchar(25) NOT NULL,
  `C_ADDRESS` varchar(40) NOT NULL,
  `C_NATIONKEY` bigint(20) NOT NULL,
  `C_PHONE` char(15) NOT NULL,
  `C_ACCTBAL` decimal(15,2) NOT NULL,
  `C_MKTSEGMENT` char(10) NOT NULL,
  `C_COMMENT` varchar(117) NOT NULL,
  PRIMARY KEY (`C_CUSTKEY`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> show create table nation \G
*************************** 1. row ***************************
       Table: nation
Create Table: CREATE TABLE `nation` (
  `N_NATIONKEY` bigint(20) NOT NULL,
  `N_NAME` char(25) NOT NULL,
  `N_REGIONKEY` bigint(20) NOT NULL,
  `N_COMMENT` varchar(152) DEFAULT NULL,
  PRIMARY KEY (`N_NATIONKEY`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> show create table orders \G
*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `O_ORDERKEY` bigint(20) NOT NULL,
  `O_CUSTKEY` bigint(20) NOT NULL,
  `O_ORDERSTATUS` char(1) NOT NULL,
  `O_TOTALPRICE` decimal(15,2) NOT NULL,
  `O_ORDERDATE` date NOT NULL,
  `O_ORDERPRIORITY` char(15) NOT NULL,
  `O_CLERK` char(15) NOT NULL,
  `O_SHIPPRIORITY` bigint(20) NOT NULL,
  `O_COMMENT` varchar(79) NOT NULL,
  PRIMARY KEY (`O_ORDERKEY`) /*T![clustered_index] CLUSTERED */,
  KEY `idx1` (`O_CUSTKEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)

By rewriting the SQL (moving the subquery before the left join) or enabling tidb_enable_outer_join_reorder in higher versions, you can achieve the desired execution plan with significant performance improvement. The execution results are as follows:

set tidb_enable_outer_join_reorder=OFF;
mysql> explain analyze select a.* ,b.* from customer a left join nation b on a.C_NATIONKEY=b.N_NATIONKEY where a.C_CUSTKEY in (select O_CUSTKEY from orders where O_ORDERKEY=1 limit 1);
+-------------------------------+-----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------+---------+---------+
| id                            | estRows   | actRows | task      | access object | execution info                                                                                                                                                                                                                                                                                        | operator info                                                                                   | memory  | disk    |
+-------------------------------+-----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------+---------+---------+
| HashJoin_19                   | 1.00      | 1       | root      |               | time:100.1ms, loops:2, RU:176.777568, build_hash_table:{total:917.8µs, fetch:905.9µs, build:12µs}, probe:{concurrency:5, total:499.1ms, max:99.9ms, probe:8.13ms, fetch:490.9ms}                                                                                                                      | inner join, equal:[eq(tpch1.orders.o_custkey, tpch1.customer.c_custkey)]                        | 9.46 KB | 0 Bytes |
| ├─HashAgg_20(Build)           | 1.00      | 1       | root      |               | time:872.2µs, loops:2                                                                                                                                                                                                                                                                                 | group by:tpch1.orders.o_custkey, funcs:firstrow(tpch1.orders.o_custkey)->tpch1.orders.o_custkey | 8.99 KB | 0 Bytes |
| │ └─Limit_21                  | 1.00      | 1       | root      |               | time:857.1µs, loops:2                                                                                                                                                                                                                                                                                 | offset:0, count:1                                                                               | N/A     | N/A     |
| │   └─Point_Get_23            | 1.00      | 1       | root      | table:orders  | time:852.1µs, loops:1, Get:{num_rpc:1, total_time:809.5µs}, total_process_time: 80.3µs, total_wait_time: 163.1µs, tikv_wall_time: 334.1µs, scan_detail: {total_process_keys: 1, total_process_keys_size: 134, total_keys: 1, get_snapshot_time: 13.7µs, rocksdb: {block: {cache_hit_count: 2}}}       | handle:1                                                                                        | N/A     | N/A     |
| └─HashJoin_33(Probe)          | 150000.00 | 150000  | root      |               | time:99.8ms, loops:149, build_hash_table:{total:901.6µs, fetch:882.1µs, build:19.6µs}, probe:{concurrency:5, total:496.7ms, max:99.7ms, probe:92.5ms, fetch:404.2ms}                                                                                                                                  | left outer join, equal:[eq(tpch1.customer.c_nationkey, tpch1.nation.n_nationkey)]               | 50.2 KB | 0 Bytes |
|   ├─TableReader_38(Build)     | 25.00     | 25      | root      |               | time:827.7µs, loops:2, cop_task: {num: 1, max: 927.1µs, proc_keys: 25, tot_proc: 124.6µs, tot_wait: 114.6µs, rpc_num: 1, rpc_time: 901.1µs, copr_cache_hit_ratio: 0.00, build_task_duration: 9.15µs, max_distsql_concurrency: 1}                                                                      | data:TableFullScan_37                                                                           | 3.05 KB | N/A     |
|   │ └─TableFullScan_37        | 25.00     | 25      | cop[tikv] | table:b       | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 25, total_process_keys_size: 3109, total_keys: 26, get_snapshot_time: 31.8µs, rocksdb: {key_skipped_count: 25, block: {cache_hit_count: 1}}}                                                                                          | keep order:false, stats:pseudo                                                                  | N/A     | N/A     |
|   └─TableReader_36(Probe)     | 150000.00 | 150000  | root      |               | time:83ms, loops:149, cop_task: {num: 11, max: 64.4ms, min: 547.4µs, avg: 7.02ms, p95: 64.4ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 44.2ms, tot_wait: 1.25ms, rpc_num: 11, rpc_time: 77ms, copr_cache_hit_ratio: 0.91, build_task_duration: 12.5µs, max_distsql_concurrency: 1}      | data:TableFullScan_35                                                                           | 18.9 MB | N/A     |
|     └─TableFullScan_35        | 150000.00 | 150000  | cop[tikv] | table:a       | tikv_task:{proc max:31ms, min:2ms, avg: 12ms, p80:25ms, p95:31ms, iters:190, tasks:11}, scan_detail: {total_process_keys: 50144, total_process_keys_size: 10206946, total_keys: 50145, get_snapshot_time: 263.3µs, rocksdb: {key_skipped_count: 50144, block: {cache_hit_count: 335}}}                | keep order:false                                                                                | N/A     | N/A     |
+-------------------------------+-----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------+---------+---------+
9 rows in set (0.10 sec)


mysql> explain analyze select a.* ,b.* from (select * from customer a where a.C_CUSTKEY in (select O_CUSTKEY from orders where O_ORDERKEY=1 limit 1)) a left join nation b on a.C_NATIONKEY=b.N_NATIONKEY ;
+---------------------------------+---------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| id                              | estRows | actRows | task      | access object | execution info                                                                                                                                                                                                                                                                                         | operator info                                                                                                                                                                        | memory   | disk    |
+---------------------------------+---------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| IndexJoin_22                    | 1.25    | 1       | root      |               | time:3.09ms, loops:2, RU:1.510949, inner:{total:792µs, concurrency:5, task:1, construct:4.06µs, fetch:778.7µs, build:3.34µs}, probe:7.12µs                                                                                                                                                             | left outer join, inner:TableReader_19, outer key:tpch1.customer.c_nationkey, inner key:tpch1.nation.n_nationkey, equal cond:eq(tpch1.customer.c_nationkey, tpch1.nation.n_nationkey) | 54.9 KB  | N/A     |
| ├─IndexHashJoin_36(Build)       | 1.00    | 1       | root      |               | time:2.01ms, loops:3, inner:{total:909.3µs, concurrency:5, task:1, construct:4.79µs, fetch:891.2µs, build:5.53µs, join:7.2µs}                                                                                                                                                                          | inner join, inner:TableReader_31, outer key:tpch1.orders.o_custkey, inner key:tpch1.customer.c_custkey, equal cond:eq(tpch1.orders.o_custkey, tpch1.customer.c_custkey)              | 147.5 KB | N/A     |
| │ ├─HashAgg_41(Build)           | 1.00    | 1       | root      |               | time:994.4µs, loops:3                                                                                                                                                                                                                                                                                  | group by:tpch1.orders.o_custkey, funcs:firstrow(tpch1.orders.o_custkey)->tpch1.orders.o_custkey                                                                                      | 8.97 KB  | 0 Bytes |
| │ │ └─Limit_42                  | 1.00    | 1       | root      |               | time:973.8µs, loops:2                                                                                                                                                                                                                                                                                  | offset:0, count:1                                                                                                                                                                    | N/A      | N/A     |
| │ │   └─Point_Get_44            | 1.00    | 1       | root      | table:orders  | time:967.7µs, loops:1, Get:{num_rpc:1, total_time:891.1µs}, total_process_time: 107.1µs, total_wait_time: 194.5µs, tikv_wall_time: 355.7µs, scan_detail: {total_process_keys: 1, total_process_keys_size: 134, total_keys: 1, get_snapshot_time: 23.8µs, rocksdb: {block: {cache_hit_count: 2}}}       | handle:1                                                                                                                                                                             | N/A      | N/A     |
| │ └─TableReader_31(Probe)       | 1.00    | 1       | root      |               | time:753.7µs, loops:2, cop_task: {num: 1, max: 628.8µs, proc_keys: 1, tot_proc: 71.8µs, tot_wait: 102µs, rpc_num: 1, rpc_time: 607.9µs, copr_cache_hit_ratio: 0.00, build_task_duration: 31.1µs, max_distsql_concurrency: 1, max_extra_concurrency: 1}                                                 | data:TableRangeScan_30                                                                                                                                                               | N/A      | N/A     |
| │   └─TableRangeScan_30         | 1.00    | 1       | cop[tikv] | table:a       | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 242, total_keys: 1, get_snapshot_time: 19.8µs, rocksdb: {block: {cache_hit_count: 1}}}                                                                                                                     | range: decided by [tpch1.orders.o_custkey], keep order:false                                                                                                                         | N/A      | N/A     |
| └─TableReader_19(Probe)         | 1.00    | 1       | root      |               | time:692.6µs, loops:2, cop_task: {num: 1, max: 589.3µs, proc_keys: 1, tot_proc: 57µs, tot_wait: 88.8µs, rpc_num: 1, rpc_time: 571.3µs, copr_cache_hit_ratio: 0.00, build_task_duration: 22.6µs, max_distsql_concurrency: 1, max_extra_concurrency: 1}                                                  | data:TableRangeScan_18                                                                                                                                                               | N/A      | N/A     |
|   └─TableRangeScan_18           | 1.00    | 1       | cop[tikv] | table:b       | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 104, total_keys: 1, get_snapshot_time: 14.4µs, rocksdb: {block: {cache_hit_count: 1}}}                                                                                                                     | range: decided by [tpch1.customer.c_nationkey], keep order:false, stats:pseudo                                                                                                       | N/A      | N/A     |
+---------------------------------+---------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
9 rows in set (0.00 sec)



set tidb_enable_outer_join_reorder=ON;
mysql> explain analyze select a.* ,b.* from customer a left join nation b on a.C_NATIONKEY=b.N_NATIONKEY where a.C_CUSTKEY in (select O_CUSTKEY from orders where O_ORDERKEY=1 limit 1);
+---------------------------------+---------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| id                              | estRows | actRows | task      | access object | execution info                                                                                                                                                                                                                                                                                         | operator info                                                                                                                                                                        | memory   | disk    |
+---------------------------------+---------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| IndexJoin_23                    | 1.25    | 1       | root      |               | time:3.01ms, loops:2, RU:1.514662, inner:{total:803.9µs, concurrency:5, task:1, construct:6.21µs, fetch:783.3µs, build:4.67µs}, probe:5.8µs                                                                                                                                                            | left outer join, inner:TableReader_20, outer key:tpch1.customer.c_nationkey, inner key:tpch1.nation.n_nationkey, equal cond:eq(tpch1.customer.c_nationkey, tpch1.nation.n_nationkey) | 54.9 KB  | N/A     |
| ├─IndexHashJoin_36(Build)       | 1.00    | 1       | root      |               | time:1.99ms, loops:3, inner:{total:965µs, concurrency:5, task:1, construct:4.7µs, fetch:950.2µs, build:5.78µs, join:6.77µs}                                                                                                                                                                            | inner join, inner:TableReader_31, outer key:tpch1.orders.o_custkey, inner key:tpch1.customer.c_custkey, equal cond:eq(tpch1.orders.o_custkey, tpch1.customer.c_custkey)              | 162.5 KB | N/A     |
| │ ├─HashAgg_41(Build)           | 1.00    | 1       | root      |               | time:895µs, loops:3                                                                                                                                                                                                                                                                                    | group by:tpch1.orders.o_custkey, funcs:firstrow(tpch1.orders.o_custkey)->tpch1.orders.o_custkey                                                                                      | 8.97 KB  | 0 Bytes |
| │ │ └─Limit_42                  | 1.00    | 1       | root      |               | time:876.2µs, loops:2                                                                                                                                                                                                                                                                                  | offset:0, count:1                                                                                                                                                                    | N/A      | N/A     |
| │ │   └─Point_Get_44            | 1.00    | 1       | root      | table:orders  | time:869.1µs, loops:1, Get:{num_rpc:1, total_time:790.5µs}, total_process_time: 104.2µs, total_wait_time: 113.8µs, tikv_wall_time: 277.6µs, scan_detail: {total_process_keys
| username: redgame | Original post link

Subquery types

| username: 胡杨树旁 | Original post link

Okay, it’s unlikely that the production environment will upgrade the version. I’ll first see if modifying the SQL can achieve the desired result.

| username: TiDBer_5cwU0ltE | Original post link

Such a long SQL doesn’t seem to fit MySQL’s applicable scenarios. It might be better if it can be changed to another database.