Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 指定INL_JOIN，但执行计划并不走
|
username: 人如其名
[TiDB Usage Environment] Poc
[TiDB Version] 6.3.0
[Encountered Problem] Not using the specified INL_JOIN, execution efficiency is too slow
[Problem Phenomenon and Impact]
The table structure is as follows:
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 */,
KEY `customer_idx1` (`C_PHONE`),
KEY `customer_idx2` (`C_NATIONKEY`)
) 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 `orders_idx1` (`O_ORDERDATE`),
KEY `o_custkey` (`O_CUSTKEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
The execution plan of the following SQL statement:
mysql> explain select a.C_NAME,b.price from tpch.customer a left join (select o_custkey,sum(O_TOTALPRICE) as price from tpch.orders group by o_custkey) b on a.C_CUSTKEY=b.O_CUSTKEY where a.C_PHONE='23-768-687-3665';
+------------------------------------+-------------+-----------+---------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------+-------------+-----------+---------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+
| HashJoin_13 | 1.02 | root | | left outer join, equal:[eq(tpch.customer.c_custkey, tpch.orders.o_custkey)] |
| ├─IndexLookUp_31(Build) | 1.02 | root | | |
| │ ├─IndexRangeScan_29(Build) | 1.02 | cop[tikv] | table:a, index:customer_idx1(C_PHONE) | range:["23-768-687-3665","23-768-687-3665"], keep order:false |
| │ └─TableRowIDScan_30(Probe) | 1.02 | cop[tikv] | table:a | keep order:false |
| └─HashAgg_38(Probe) | 1009664.00 | root | | group by:tpch.orders.o_custkey, funcs:sum(Column#21)->Column#18, funcs:firstrow(tpch.orders.o_custkey)->tpch.orders.o_custkey |
| └─TableReader_39 | 1009664.00 | root | | data:HashAgg_32 |
| └─HashAgg_32 | 1009664.00 | cop[tikv] | | group by:tpch.orders.o_custkey, funcs:sum(tpch.orders.o_totalprice)->Column#21 |
| └─TableFullScan_37 | 15000000.00 | cop[tikv] | table:orders | keep order:false |
+------------------------------------+-------------+-----------+---------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)
I understand that the optimal execution plan path is to first filter table a through the customer_idx1 index, then use INL_JOIN to join table b (using the o_custkey index of table b), and then use the o_custkey index of table b to perform a sum(O_TOTALPRICE) using index back table. This would be the optimal execution path, but in reality, it does not follow this path. So I manually added a hint to force it to follow this path:
mysql> explain select /*+ INL_JOIN(a,b) */ a.C_NAME,b.price from tpch.customer a left join (select o_custkey,sum(O_TOTALPRICE) as price from tpch.orders group by o_custkey) b on a.C_CUSTKEY=b.O_CUSTKEY where a.C_PHONE='23-768-687-3665';
+------------------------------------+-------------+-----------+---------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------+-------------+-----------+---------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+
| HashJoin_12 | 1.02 | root | | left outer join, equal:[eq(tpch.customer.c_custkey, tpch.orders.o_custkey)] |
| ├─IndexLookUp_30(Build) | 1.02 | root | | |
| │ ├─IndexRangeScan_28(Build) | 1.02 | cop[tikv] | table:a, index:customer_idx1(C_PHONE) | range:["23-768-687-3665","23-768-687-3665"], keep order:false |
| │ └─TableRowIDScan_29(Probe) | 1.02 | cop[tikv] | table:a | keep order:false |
| └─HashAgg_37(Probe) | 1009664.00 | root | | group by:tpch.orders.o_custkey, funcs:sum(Column#21)->Column#18, funcs:firstrow(tpch.orders.o_custkey)->tpch.orders.o_custkey |
| └─TableReader_38 | 1009664.00 | root | | data:HashAgg_31 |
| └─HashAgg_31 | 1009664.00 | cop[tikv] | | group by:tpch.orders.o_custkey, funcs:sum(tpch.orders.o_totalprice)->Column#21 |
| └─TableFullScan_36 | 15000000.00 | cop[tikv] | table:orders | keep order:false |
+------------------------------------+-------------+-----------+---------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+
8 rows in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1815 | There are no matching table names for (b) in optimizer hint /*+ INL_JOIN(a, b) */ or /*+ TIDB_INLJ(a, b) */ Maybe you can use the table alias name |
| Warning | 1815 | Optimizer Hint /*+ INL_JOIN(a, b) */ or /*+ TIDB_INLJ(a, b) */ is inapplicable |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
As you can see, it does not follow the expected result. The warnings indicate that this execution plan cannot be specified in this way.
Next, we execute to check the time taken (to avoid hash_agg causing OOM, hash_agg is performed in non-parallel mode here):
mysql> set tidb_hashagg_partial_concurrency=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select a.C_NAME,b.price from tpch.customer a left join (select o_custkey,sum(O_TOTALPRICE) as price from tpch.orders group by o_custkey) b on a.C_CUSTKEY=b.O_CUSTKEY where a.C_PHONE='23-768-687-3665';
+--------------------+-----------+
| C_NAME | price |
+--------------------+-----------+
| Customer#000000002 | 481044.54 |
+--------------------+-----------+
1 row in set (10.86 sec)
It can be seen that it took more than 10 seconds to execute.
I imported the same data into DB2 to observe its execution plan and execution time (due to the large output of the execution plan, only partial key information is posted here):
Optimizer Plan:
Rows
Operator
(ID)
Cost
1
RETURN
( 1)
63.7538
|
1
GRPBY
( 2)
63.7531
|
9.95056
NLJOIN
( 3)
63.7507
/--/ \---\
0.995056 10
TBSCAN FETCH
( 4) (--)
20.4589 43.2918
| / \
0.995056 10 1.5e+07
SORT RIDSCN Table:
( 5) ( 9) TPCH
20.4575 13.6895 ORDERS
| |
0.995056 10
FETCH SORT
( 6) (10)
20.455 13.6884
/ \ |
0.995056 1.5e+06 10
IXSCAN Table: IXSCAN
( 7) TPCH (11)
13.6718 CUSTOMER 13.685
| |
1.49996e+06 999982
Index: Index:
TPCH TPCH
CUSTOMER_IDX1 ORDERS_IDX2
[db2inst1@host1 ~]$ time db2 "select a.C_NAME,b.price from tpch.customer a left join (select o_custkey,sum(O_TOTALPRICE) as price from tpch.orders group by o_custkey) b on a.C_CUSTKEY=b.O_CUSTKEY where a.C_PHONE='23-768-687-3665'"
C_NAME PRICE
------------------------- ---------------------------------
Customer#000000002 481044.54
1 record(s) selected.
real 0m0.034s
user 0m0.008s
sys 0m0.018s
It can be seen that DB2 automatically used the correct execution plan and is very efficient.
So the question here is, in the presence of indexes, why does manually specifying the hint to use INL_JOIN not work and show that this join is not applicable?
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: