The execution plan is not optimal because of multiple join group

Enhancement

1. Minimal reproduce step (Required)

2. What did you expect to see? (Required)

mysql> explain format='verbose' SELECT *
    -> FROM TEST1 A
    ->          INNER JOIN TEST2 B ON A.BILL = B.BILL
    ->          LEFT JOIN TEST3 C ON B.BIZ = C.OPC_PRES
    ->          INNER JOIN TEST4 D ON B.BIZ = D.PRES
    ->          LEFT JOIN TEST5 E ON D.PRES_CLA = E.ID
    ->          LEFT JOIN TEST6 F ON D.OPC = F.OPC_ID
    ->          LEFT JOIN TEST7 G ON D.CHARGE = G.CHARGE_SN
    ->          LEFT JOIN TEST8 H ON F.FEE_NATURE = H.ID
    -> where (E.MEDDELIV is null OR E.MEDDELIV = 0)
    ->   AND B.BIZ_TYPE = 1
    ->   AND D.IS_PAY = 1
    ->   AND D.INVALID_PRES_SN IS NULL
    ->   AND B.STATE = 1
    ->   AND A.STATE != 99
    ->   AND A.STO_ID = 1010003
    ->   AND A.BILL < 230609101999999
    ->   AND A.BILL > 230608101000000;

+----------------------------------------------------------+---------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                                       | estRows | estCost | task      | access object | operator info                                                                                                                                                  |
+----------------------------------------------------------+---------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| IndexJoin_27                                             | 0.00    | 134.98  | root      |               | left outer join, inner:TableReader_24, outer key:wdhis.test6.fee_nature, inner key:wdhis.test8.id, equal cond:eq(wdhis.test6.fee_nature, wdhis.test8.id)       |
| ├─IndexJoin_39(Build)                                    | 0.00    | 116.98  | root      |               | left outer join, inner:TableReader_36, outer key:wdhis.test4.charge, inner key:wdhis.test7.charge_sn, equal cond:eq(wdhis.test4.charge, wdhis.test7.charge_sn) |
| │ ├─IndexJoin_49(Build)                                  | 0.00    | 98.98   | root      |               | left outer join, inner:TableReader_46, outer key:wdhis.test4.opc, inner key:wdhis.test6.opc_id, equal cond:eq(wdhis.test4.opc, wdhis.test6.opc_id)             |
| │ │ ├─Selection_54(Build)                                | 0.00    | 80.98   | root      |               | or(isnull(wdhis.test5.meddeliv), eq(wdhis.test5.meddeliv, 0))                                                                                                  |
| │ │ │ └─IndexJoin_59                                     | 0.00    | 80.98   | root      |               | left outer join, inner:TableReader_56, outer key:wdhis.test4.pres_cla, inner key:wdhis.test5.id, equal cond:eq(wdhis.test4.pres_cla, wdhis.test5.id)           |
| │ │ │   ├─IndexJoin_71(Build)                            | 0.00    | 62.98   | root      |               | inner join, inner:TableReader_67, outer key:wdhis.test2.biz, inner key:wdhis.test4.pres, equal cond:eq(wdhis.test2.biz, wdhis.test4.pres)                      |
| │ │ │   │ ├─IndexJoin_81(Build)                          | 0.00    | 44.98   | root      |               | left outer join, inner:TableReader_78, outer key:wdhis.test2.biz, inner key:wdhis.test3.opc_pres, equal cond:eq(wdhis.test2.biz, wdhis.test3.opc_pres)         |
| │ │ │   │ │ ├─IndexJoin_94(Build)                        | 0.00    | 26.98   | root      |               | inner join, inner:TableReader_90, outer key:wdhis.test1.bill, inner key:wdhis.test2.bill, equal cond:eq(wdhis.test1.bill, wdhis.test2.bill)                    |
| │ │ │   │ │ │ ├─TableReader_112(Build)                   | 0.20    | 6.78    | root      |               | data:Selection_111                                                                                                                                             |
| │ │ │   │ │ │ │ └─Selection_111                          | 0.20    | 43.38   | cop[tikv] |               | eq(wdhis.test1.sto_id, 1010003), ne(wdhis.test1.state, 99)                                                                                                     |
| │ │ │   │ │ │ │   └─TableRangeScan_110                   | 0.45    | 42.01   | cop[tikv] | table:A       | range:(230608101000000,230609101999999), keep order:false                                                                                                      |
| │ │ │   │ │ │ └─TableReader_90(Probe)                    | 0.16    | 6.90    | root      |               | data:Selection_89                                                                                                                                              |
| │ │ │   │ │ │   └─Selection_89                           | 0.16    | 51.00   | cop[tikv] |               | eq(wdhis.test2.biz_type, 1), eq(wdhis.test2.state, 1), gt(wdhis.test2.bill, 230608101000000), lt(wdhis.test2.bill, 230609101999999)                            |
| │ │ │   │ │ │     └─TableRangeScan_88                    | 0.20    | 48.00   | cop[tikv] | table:B       | range: decided by [eq(wdhis.test2.bill, wdhis.test1.bill) eq(wdhis.test2.biz_type, 1)], keep order:false                                                       |
| │ │ │   │ │ └─TableReader_78(Probe)                      | 0.00    | 15.16   | root      |               | data:TableRangeScan_77                                                                                                                                         |
| │ │ │   │ │   └─TableRangeScan_77                        | 0.00    | 101.59  | cop[tikv] | table:C       | range: decided by [wdhis.test2.biz], keep order:false                                                                                                          |
| │ │ │   │ └─TableReader_67(Probe)                        | 0.00    | 12.12   | root      |               | data:Selection_66                                                                                                                                              |
| │ │ │   │   └─Selection_66                               | 0.00    | 161.10  | cop[tikv] |               | eq(wdhis.test4.is_pay, 1), isnull(wdhis.test4.invalid_pres_sn)                                                                                                 |
| │ │ │   │     └─TableRangeScan_65                        | 0.00    | 158.10  | cop[tikv] | table:D       | range: decided by [wdhis.test2.biz], keep order:false                                                                                                          |
| │ │ │   └─TableReader_56(Probe)                          | 0.00    | 32.45   | root      |               | data:TableRangeScan_55                                                                                                                                         |
| │ │ │     └─TableRangeScan_55                            | 0.00    | 184.45  | cop[tikv] | table:E       | range: decided by [wdhis.test4.pres_cla], keep order:false                                                                                                     |
| │ │ └─TableReader_46(Probe)                              | 0.00    | 42.58   | root      |               | data:TableRangeScan_45                                                                                                                                         |
| │ │   └─TableRangeScan_45                                | 0.00    | 229.76  | cop[tikv] | table:F       | range: decided by [wdhis.test4.opc], keep order:false                                                                                                          |
| │ └─TableReader_36(Probe)                                | 0.00    | 19.98   | root      |               | data:TableRangeScan_35                                                                                                                                         |
| │   └─TableRangeScan_35                                  | 0.00    | 144.41  | cop[tikv] | table:G       | range: decided by [wdhis.test4.charge], keep order:false                                                                                                       |
| └─TableReader_24(Probe)                                  | 0.00    | 20.08   | root      |               | data:TableRangeScan_23                                                                                                                                         |
|   └─TableRangeScan_23                                    | 0.00    | 119.98  | cop[tikv] | table:H       | range: decided by [wdhis.test6.fee_nature], keep order:false                                                                                                   |
+----------------------------------------------------------+---------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
27 rows in set, 1 warning (0.02 sec)

3. What did you see instead (Required)

Table H and F shouldn't be as a join group in HashJoin_187(Probe) because estrows too large.


+----------------------------------------------------------+------------+---------------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                                       | estRows    | estCost       | task      | access object | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+----------------------------------------------------------+------------+---------------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_34                                            | 0.00       | 415737168.48  | root      |               | wdhis.test1.bill, wdhis.test1.opc_no, wdhis.test1.sto_id, wdhis.test1.state, wdhis.test1.dsps_win_id, wdhis.test1.activation_time, wdhis.test1.arrange_empid, wdhis.test1.arrange_time, wdhis.test1.mult_arrange_empid, wdhis.test1.dsps_empid, wdhis.test1.dsps_time, wdhis.test1.exec_times, wdhis.test1.next_bill_sn, wdhis.test1.next_exec_seq, wdhis.test1.audit_empid, wdhis.test1.mobile_state, wdhis.test1.mobile_arrange_empid, wdhis.test1.mobile_arrange_time, wdhis.test1.dsps_times, wdhis.test1.type, wdhis.test1.last_dsps_time, wdhis.test1.arrange_begin_time, wdhis.test1.dispense_win_id, wdhis.test2.bill, wdhis.test2.biz_type, wdhis.test2.biz, wdhis.test2.state, wdhis.test2.biz_group, wdhis.test3.opc_pres, wdhis.test3.return_reason, wdhis.test3.req_empid, wdhis.test3.req_time, wdhis.test3.audi_empid, wdhis.test3.audi_time, wdhis.test3.return_empid, wdhis.test3.return_time, wdhis.test3.cancle_empid, wdhis.test3.cancle_time, wdhis.test3.plugs_state, wdhis.test4.pres, wdhis.test4.branch_code, wdhis.test4.sto_id, wdhis.test4.pres_cla, wdhis.test4.ois_pres_sn, wdhis.test4.pres_type, wdhis.test4.charge, wdhis.test4.opc, wdhis.test4.server_way, wdhis.test4.pres_dept_id, wdhis.test4.pres_empid, wdhis.test4.pres_work_group, wdhis.test4.invalid_pres_sn, wdhis.test4.inherit_pres_sn, wdhis.test4.audit_result, wdhis.test4.audit_sn, wdhis.test4.dsps_bill_sn, wdhis.test4.mach_task_sn, wdhis.test4.remark, wdhis.test4.is_pay, wdhis.test4.reg_dept, wdhis.test4.auto_prn_time, wdhis.test4.today_num, wdhis.test5.id, wdhis.test5.name, wdhis.test5.short_name, wdhis.test5.title, wdhis.test5.hint, wdhis.test5.pres_type, wdhis.test5.assos_reg_dept, wdhis.test5.assos_nature, wdhis.test5.is_default, wdhis.test5.state, wdhis.test5.branch_code, wdhis.test5.med_limit, wdhis.test5.type, wdhis.test5.code, wdhis.test5.tag, wdhis.test5.independent_seq, wdhis.test5.sign_grade, wdhis.test5.pres_med_num, wdhis.test5.meddeliv, wdhis.test5.use_stock, wdhis.test5.stock_id, wdhis.test5.return_all, wdhis.test5.force_filter, wdhis.test5.ord, wdhis.test5.multi_sto, wdhis.test5.assos_med_type, wdhis.test5.assos_custom_field, wdhis.test5.prnt_times, wdhis.test5.province_code, wdhis.test6.opc_id, wdhis.test6.branch_code, wdhis.test6.pat_from, wdhis.test6.card_data, wdhis.test6.card_type, wdhis.test6.opc_no, wdhis.test6.fee_nature, wdhis.test6.pat_class, wdhis.test6.ratio_classify, wdhis.test6.insurance_no, wdhis.test6.schedule_sn, wdhis.test6.reg_date, wdhis.test6.reg_type, wdhis.test6.is_eme, wdhis.test6.ie_observe, wdhis.test6.reg_dept, wdhis.test6.reg_empid, wdhis.test6.ois_reg_no, wdhis.test6.msg, wdhis.test6.doc_empid, wdhis.test6.reception_times, wdhis.test6.charge_times, wdhis.test6.charge_no, wdhis.test6.resv_sn, wdhis.test6.reg_time, wdhis.test6.reg_input_empid, wdhis.test6.invalid_empid, wdhis.test6.invalid_time, wdhis.test6.reg_source_type, wdhis.test6.reg_client_no, wdhis.test6.serve_way, wdhis.test6.allergen, wdhis.test6.last_clinic, wdhis.test6.in_dept_id, wdhis.test6.reg_time_period, wdhis.test6.resource_id, wdhis.test6.is_observe, wdhis.test6.obs_start_time, wdhis.test6.obs_end_time, wdhis.test6.is_venerism, wdhis.test6.special_pat_kind, wdhis.test6.reg_source, wdhis.test6.is_sign, wdhis.test6.remark, wdhis.test7.charge_sn, wdhis.test7.branch_code, wdhis.test7.opc_id, wdhis.test7.fee_nature, wdhis.test7.name, wdhis.test7.charge_empid, wdhis.test7.charge_time, wdhis.test7.charge_source, wdhis.test7.settle_sn, wdhis.test7.invalid_charge_sn, wdhis.test7.invalid_settle_sn, wdhis.test7.pay_sn, wdhis.test7.pay_state, wdhis.test7.reg_dept, wdhis.test7.serve_way, wdhis.test8.id, wdhis.test8.id_c, wdhis.test8.branch_code, wdhis.test8.code, wdhis.test8.name, wdhis.test8.def_ratio_classify, wdhis.test8.ord, wdhis.test8.out_used, wdhis.test8.in_used, wdhis.test8.other_used, wdhis.test8.inputcode1, wdhis.test8.inputcode2, wdhis.test8.state, wdhis.test8.kind, wdhis.test8.on_acct_fee_type, wdhis.test8.settle_id, wdhis.test8.dept_type |
| └─IndexJoin_40                                           | 0.00       | 415737150.48  | root      |               | left outer join, inner:TableReader_37, outer key:wdhis.test4.charge, inner key:wdhis.test7.charge_sn, equal cond:eq(wdhis.test4.charge, wdhis.test7.charge_sn)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|   ├─HashJoin_50(Build)                                   | 0.00       | 415737132.48  | root      |               | left outer join, equal:[eq(wdhis.test4.opc, wdhis.test6.opc_id)]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|   │ ├─Selection_62(Build)                                | 0.00       | 98.98         | root      |               | or(isnull(wdhis.test5.meddeliv), eq(wdhis.test5.meddeliv, 0))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|   │ │ └─Projection_63                                    | 0.00       | 98.98         | root      |               | wdhis.test1.bill, wdhis.test1.opc_no, wdhis.test1.sto_id, wdhis.test1.state, wdhis.test1.dsps_win_id, wdhis.test1.activation_time, wdhis.test1.arrange_empid, wdhis.test1.arrange_time, wdhis.test1.mult_arrange_empid, wdhis.test1.dsps_empid, wdhis.test1.dsps_time, wdhis.test1.exec_times, wdhis.test1.next_bill_sn, wdhis.test1.next_exec_seq, wdhis.test1.audit_empid, wdhis.test1.mobile_state, wdhis.test1.mobile_arrange_empid, wdhis.test1.mobile_arrange_time, wdhis.test1.dsps_times, wdhis.test1.type, wdhis.test1.last_dsps_time, wdhis.test1.arrange_begin_time, wdhis.test1.dispense_win_id, wdhis.test2.bill, wdhis.test2.biz_type, wdhis.test2.biz, wdhis.test2.state, wdhis.test2.biz_group, wdhis.test3.opc_pres, wdhis.test3.return_reason, wdhis.test3.req_empid, wdhis.test3.req_time, wdhis.test3.audi_empid, wdhis.test3.audi_time, wdhis.test3.return_empid, wdhis.test3.return_time, wdhis.test3.cancle_empid, wdhis.test3.cancle_time, wdhis.test3.plugs_state, wdhis.test4.pres, wdhis.test4.branch_code, wdhis.test4.sto_id, wdhis.test4.pres_cla, wdhis.test4.ois_pres_sn, wdhis.test4.pres_type, wdhis.test4.charge, wdhis.test4.opc, wdhis.test4.server_way, wdhis.test4.pres_dept_id, wdhis.test4.pres_empid, wdhis.test4.pres_work_group, wdhis.test4.invalid_pres_sn, wdhis.test4.inherit_pres_sn, wdhis.test4.audit_result, wdhis.test4.audit_sn, wdhis.test4.dsps_bill_sn, wdhis.test4.mach_task_sn, wdhis.test4.remark, wdhis.test4.is_pay, wdhis.test4.reg_dept, wdhis.test4.auto_prn_time, wdhis.test4.today_num, wdhis.test5.id, wdhis.test5.name, wdhis.test5.short_name, wdhis.test5.title, wdhis.test5.hint, wdhis.test5.pres_type, wdhis.test5.assos_reg_dept, wdhis.test5.assos_nature, wdhis.test5.is_default, wdhis.test5.state, wdhis.test5.branch_code, wdhis.test5.med_limit, wdhis.test5.type, wdhis.test5.code, wdhis.test5.tag, wdhis.test5.independent_seq, wdhis.test5.sign_grade, wdhis.test5.pres_med_num, wdhis.test5.meddeliv, wdhis.test5.use_stock, wdhis.test5.stock_id, wdhis.test5.return_all, wdhis.test5.force_filter, wdhis.test5.ord, wdhis.test5.multi_sto, wdhis.test5.assos_med_type, wdhis.test5.assos_custom_field, wdhis.test5.prnt_times, wdhis.test5.province_code |
|   │ │   └─IndexJoin_68                                   | 0.00       | 80.98         | root      |               | left outer join, inner:TableReader_65, outer key:wdhis.test4.pres_cla, inner key:wdhis.test5.id, equal cond:eq(wdhis.test4.pres_cla, wdhis.test5.id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|   │ │     ├─IndexJoin_82(Build)                          | 0.00       | 62.98         | root      |               | inner join, inner:TableReader_78, outer key:wdhis.test2.biz, inner key:wdhis.test4.pres, equal cond:eq(wdhis.test2.biz, wdhis.test4.pres)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|   │ │     │ ├─IndexJoin_126(Build)                       | 0.00       | 44.98         | root      |               | left outer join, inner:TableReader_123, outer key:wdhis.test2.biz, inner key:wdhis.test3.opc_pres, equal cond:eq(wdhis.test2.biz, wdhis.test3.opc_pres)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|   │ │     │ │ ├─IndexJoin_152(Build)                     | 0.00       | 26.98         | root      |               | inner join, inner:TableReader_148, outer key:wdhis.test1.bill, inner key:wdhis.test2.bill, equal cond:eq(wdhis.test1.bill, wdhis.test2.bill)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|   │ │     │ │ │ ├─TableReader_170(Build)                 | 0.20       | 6.78          | root      |               | data:Selection_169                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|   │ │     │ │ │ │ └─Selection_169                        | 0.20       | 43.38         | cop[tikv] |               | eq(wdhis.test1.sto_id, 1010003), ne(wdhis.test1.state, 99)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|   │ │     │ │ │ │   └─TableRangeScan_168                 | 0.45       | 42.01         | cop[tikv] | table:A       | range:(230608101000000,230609101999999), keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|   │ │     │ │ │ └─TableReader_148(Probe)                 | 0.16       | 6.90          | root      |               | data:Selection_147                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
|   │ │     │ │ │   └─Selection_147                        | 0.16       | 51.00         | cop[tikv] |               | eq(wdhis.test2.biz_type, 1), eq(wdhis.test2.state, 1), gt(wdhis.test2.bill, 230608101000000), lt(wdhis.test2.bill, 230609101999999)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|   │ │     │ │ │     └─TableRangeScan_146                 | 0.20       | 48.00         | cop[tikv] | table:B       | range: decided by [eq(wdhis.test2.bill, wdhis.test1.bill) eq(wdhis.test2.biz_type, 1)], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|   │ │     │ │ └─TableReader_123(Probe)                   | 0.00       | 15.16         | root      |               | data:TableRangeScan_122                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|   │ │     │ │   └─TableRangeScan_122                     | 0.00       | 101.59        | cop[tikv] | table:C       | range: decided by [wdhis.test2.biz], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|   │ │     │ └─TableReader_78(Probe)                      | 0.00       | 12.12         | root      |               | data:Selection_77                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|   │ │     │   └─Selection_77                             | 0.00       | 161.10        | cop[tikv] |               | eq(wdhis.test4.is_pay, 1), isnull(wdhis.test4.invalid_pres_sn)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|   │ │     │     └─TableRangeScan_76                      | 0.00       | 158.10        | cop[tikv] | table:D       | range: decided by [wdhis.test2.biz], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
|   │ │     └─TableReader_65(Probe)                        | 0.00       | 32.45         | root      |               | data:TableRangeScan_64                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|   │ │       └─TableRangeScan_64                          | 0.00       | 184.45        | cop[tikv] | table:E       | range: decided by [wdhis.test4.pres_cla], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|   │ └─HashJoin_187(Probe)                                | 9935103.00 | 415737015.50  | root      |               | left outer join, equal:[eq(wdhis.test6.fee_nature, wdhis.test8.id)]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|   │   ├─TableReader_192(Build)                           | 120.00     | 2251.01       | root      |               | data:TableFullScan_191                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|   │   │ └─TableFullScan_191                              | 120.00     | 14398.20      | cop[tikv] | table:H       | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|   │   └─TableReader_190(Probe)                           | 9935103.00 | 409773324.57  | root      |               | data:TableFullScan_189                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|   │     └─TableFullScan_189                              | 9935103.00 | 2282639589.77 | cop[tikv] | table:F       | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|   └─TableReader_37(Probe)                                | 0.00       | 19.98         | root      |               | data:TableRangeScan_36                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
|     └─TableRangeScan_36                                  | 0.00       | 144.41        | cop[tikv] | table:G       | range: decided by [wdhis.test4.charge], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+----------------------------------------------------------+------------+---------------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
29 rows in set, 2 warnings (0.02 sec)

4. What is your TiDB version? (Required)

v6.5

pingcap / tidb

The execution plan is not optimal because of multiple join group #44814

Enhancement

1. Minimal reproduce step (Required)

2. What did you expect to see? (Required)

3. What did you see instead (Required)

4. What is your TiDB version? (Required)