A Simple Query Execution Plan Deviation-2

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

Original topic: 一个简单的查询执行计划走偏-2

| username: porpoiselxj

[TiDB Usage Environment] Production Environment
[TiDB Version] v7.1.3
[Encountered Problem: Problem Phenomenon and Impact]
Primary key of table t: ob_object_id
Table t2 has a unique index F1_6511 + F2_6511

Abnormal execution plan:
When a specific data is specified for table t, the execution plan is normal, and the join between t and t2 uses index_join.
When a data is specified for table t (from a subquery), the execution plan is abnormal, and the join between t and t2 uses hash_join.

index_join:

mysql> explain analyze
    ->   SELECT t.* FROM
    ->   WIND.TB_OBJECT_6757 t,wind.tb_object_6511 t2
    -> WHERE t.ob_object_id in('5efcb925-d53b-4676-ac90-7353391ac49e')
    ->   AND f9_6757 = 1
    ->   and f1_6511 = f1_6757 
    ->   AND f3_6511 = '2010400017';
+---------------------------------+------------+---------+-----------+----------------------------------------------------+
| id                              | estRows    | actRows | task      | access object                                      |
+---------------------------------+------------+---------+-----------+----------------------------------------------------+
| IndexJoin_12                    | 0.87       | 0       | root      |                                                    |
| ├─Selection_25(Build)           | 0.84       | 0       | root      |                                                   
| │ └─Point_Get_24                | 1.00       | 1       | root      | table:TB_OBJECT_6757, index:PRIMARY(OB_OBJECT_ID)
| └─IndexLookUp_11(Probe)         | 0.87       | 0       | root      |                                                   
|   ├─IndexRangeScan_8(Build)     | 5006121.81 | 0       | cop[tikv] | table:t2, index:UIDX_TB6511_F1F2(F1_6511, F2_6511)
|   └─Selection_10(Probe)         | 0.87       | 0       | cop[tikv] |                                                   
|     └─TableRowIDScan_9          | 5006121.81 | 0       | cop[tikv] | table:t2                                          
+---------------------------------+------------+---------+-----------+----------------------------------------------------+
**7 rows in set (0.00 sec)**

hash_join causes full table scan:

mysql>   explain analyze
    ->   SELECT t.* FROM
    ->   WIND.TB_OBJECT_6757 t,wind.tb_object_6511 t2
    -> WHERE t.ob_object_id in(SELECT REC_ID FROM wind_replicator.wind_tb_object_6757_oplog limit 1)
    ->   AND f9_6757 = 1
    ->   and f1_6511 = f1_6757
    ->   AND f3_6511 = '2010400017';
+------------------------------------+--------------+-----------+-----------+--------------------------------------------------------------------------------+
| id                                 | estRows      | actRows   | task      | access object                                                                  |
+------------------------------------+--------------+-----------+-----------+--------------------------------------------------------------------------------+
| HashJoin_18                        | 1.00         | 0         | root      |                                                                                |
| ├─HashAgg_72(Build)                | 1.00         | 1         | root      |                                                                               
| │ └─Limit_73                       | 1.00         | 1         | root      |                                                                              
| │   └─IndexReader_78               | 1.00         | 1         | root      | partition:all                                                                
| │     └─Limit_77                   | 1.00         | 1         | cop[tikv] |                                                                              
| │       └─IndexFullScan_76         | 1.00         | 1         | cop[tikv] | table:WIND_TB_OBJECT_6757_OPLOG, index:idx_WINDTBOBJECT6757OPLOG_RECID(REC_ID
| └─IndexHashJoin_27(Probe)          | 115.71       | 2275499   | root      |                                                                               
|   ├─TableReader_68(Build)          | 44.35        | 663505    | root      |                                                                               
|   │ └─Selection_67                 | 44.35        | 663505    | cop[tikv] |                                                                              
|   │   └─TableFullScan_66           | 254688690.00 | 254669900 | cop[tikv] | table:t2                                                                     
|   └─IndexLookUp_24(Probe)          | 115.71       | 2275499   | root      |                                                                               
|     ├─IndexRangeScan_21(Build)     | 137.79       | 5290761   | cop[tikv] | table:t, index:IDX_TB6757_F1(F1_6757)                                         
|     └─Selection_23(Probe)          | 115.71       | 2275499   | cop[tikv] |                                                                               
|       └─TableRowIDScan_22          | 137.79       | 5290761   | cop[tikv] | table:t                                                                       
+------------------------------------+--------------+-----------+-----------+--------------------------------------------------------------------------------+
**14 rows in set (18.99 sec)**

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

You definitely need to use “in” to indicate association.

| username: IanWong | Original post link

– Using NO_DECORRELATE() works
explain select * from t1 where t1.a < (select /*+ NO_DECORRELATE() */ sum(t2.a) from t2 where t2.b = t1.b);

| username: porpoiselxj | Original post link

The “in” is used to define the data range of t, and then associate it with t2. The current problem is that the association between t and t2 uses hash_join, even though t2 clearly has an index.

| username: porpoiselxj | Original post link

No, it’s still hash_join, t2 full table scan.

| username: zhanggame1 | Original post link

Try removing LIMIT 1 and replacing it with MAX(Id).

| username: caiyfc | Original post link

Optimize according to this: Subquery Optimization | PingCAP Documentation Center

| username: porpoiselxj | Original post link

The subquery was changed to select max(rec_id) from wind_replicator.wind_tb_object_6757_oplog, but the issue persists.

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

Rewrite it using join, don’t use in.

| username: porpoiselxj | Original post link

Flatten the subquery and change it to a join, t2 continues to perform a full table scan.
If only t is associated with t1, there is no problem.

| username: porpoiselxj | Original post link

Flatten the subquery and change it to a join, t2 continues to perform a full table scan.

| username: WalterWj | Original post link

Take a look at what the statistics model is.
If it is 1, try changing it to 2 and collect the statistics again.

| username: porpoiselxj | Original post link

Can these be changed casually in a production environment? Will it cause performance bottlenecks? Can we try it with these few tables first?

| username: WalterWj | Original post link

It’s best to test it.

| username: porpoiselxj | Original post link

I tried setting tidb_cost_model_version to 2 and reanalyzed the table, but the issue persists.

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

Try using a hint to make the table t join with the subquery table first.

| username: porpoiselxj | Original post link

There is no problem directly associating the t table with the subquery, no hint is needed.

| username: 小龙虾爱大龙虾 | Original post link

Please post the detailed execution plan text and table structure for review. You can use code blocks,

like this
| username: TiDBer_iLonNMYE | Original post link

Does TiDB not have a hint like no_merge?

| username: porpoiselxj | Original post link

Added it.