A Simple Query Execution Plan Goes Awry

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

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

| username: porpoiselxj

【TiDB Usage Environment】Production Environment
【TiDB Version】V7.1.3
【Encountered Problem: Issue Phenomenon and Impact】Execution plan anomaly, adding a filter condition results in a full table scan. Tried hints like leading, no_hash_join, use_index, force_index, but none worked. The table has also been manually analyzed.

PS: tt1 primary key OB_OBJECT_ID
tt2 opdate field has an index, can quickly filter out data when queried alone

mysql> explain
    ->   SELECT tt1.*
    ->     FROM wind.tb_object_6757 tt1,
    ->          (select rec_id
    ->             from wind_replicator.wind_tb_object_6757_oplog
    ->            where opdate > date_sub(NOW(), INTERVAL 1 DAY) limit 100) tt2
    ->    where tt1.ob_object_id = tt2.rec_id;
+--------------------------------------+---------+-----------+-----------------------------------------------------------------------------------+
| id                                   | estRows | task      | access object                                                                     |
+--------------------------------------+---------+-----------+-----------------------------------------------------------------------------------+
| IndexJoin_18                         | 100.00  | root      |                                                                                   |
| ├─Projection_39(Build)               | 100.00  | root      |                                                                                   |
| │ └─IndexLookUp_38                   | 100.00  | root      | partition:P202403,P202404                                                         |
| │   ├─Limit_37(Build)                | 100.00  | cop[tikv] |                                                                                   |
| │   │ └─IndexRangeScan_35            | 100.00  | cop[tikv] | table:WIND_TB_OBJECT_6757_OPLOG, index:idx_WINDTBOBJECT6757OPLOG_TIME(OPDATE, ID) |
| │   └─TableRowIDScan_36(Probe)       | 100.00  | cop[tikv] | table:WIND_TB_OBJECT_6757_OPLOG                                                   |
| └─IndexLookUp_17(Probe)              | 100.00  | root      |                                                                                   |
|   ├─IndexRangeScan_15(Build)         | 100.00  | cop[tikv] | table:tt1, index:PRIMARY(OB_OBJECT_ID)                                            |
|   └─TableRowIDScan_16(Probe)         | 100.00  | cop[tikv] | table:tt1                                                                         |
+--------------------------------------+---------+-----------+-----------------------------------------------------------------------------------+
9 rows in set (0.01 sec)

mysql> explain
    ->   SELECT tt1.*
    ->     FROM wind.tb_object_6757 tt1,
    ->          (select rec_id
    ->             from wind_replicator.wind_tb_object_6757_oplog
    ->            where opdate > date_sub(NOW(), INTERVAL 1 DAY) limit 100) tt2
    ->    where tt1.ob_object_id = tt2.rec_id
    ->
    ->      and instr(tt1.f5_6757, 'xxxx') <> 0;
+--------------------------------------+--------------+-----------+-----------------------------------------------------------------------------------+
| id                                   | estRows      | task      | access object                                                                     |
+--------------------------------------+--------------+-----------+-----------------------------------------------------------------------------------+
| HashJoin_17                          | 100.00       | root      |                                                                                   |
| ├─Projection_28(Build)               | 100.00       | root      |                                                                                   |
| │ └─IndexLookUp_27                   | 100.00       | root      | partition:P202403,P202404                                                         |
| │   ├─Limit_26(Build)                | 100.00       | cop[tikv] |                                                                                   |
| │   │ └─IndexRangeScan_24            | 100.00       | cop[tikv] | table:WIND_TB_OBJECT_6757_OPLOG, index:idx_WINDTBOBJECT6757OPLOG_TIME(OPDATE, ID) |
| │   └─TableRowIDScan_25(Probe)       | 100.00       | cop[tikv] | table:WIND_TB_OBJECT_6757_OPLOG                                                   |
| └─Selection_29(Probe)                | 190620123.20 | root      |                                                                                   |
|   └─TableReader_31                   | 238275154.00 | root      |                                                                                   |
|     └─TableFullScan_30               | 238275154.00 | cop[tikv] | table:tt1                                                                         |
+--------------------------------------+--------------+-----------+-----------------------------------------------------------------------------------+
9 rows in set, 1 warning (0.01 sec)

| username: 像风一样的男子 | Original post link

According to the documentation, data needs to be filtered based on the predicate instr()<>0 before probing tt1, which will result in a full table scan of tt1.

| username: porpoiselxj | Original post link

Here, tt2 has effective filter conditions and indexes on those filter conditions. Additionally, I have limited the tt2 table to only 100 records, and the association between tt1 and tt2 can use the primary key field ob_object_id of tt1. However, it still chooses to perform a full table scan on tt1, which I cannot understand.

| username: 像风一样的男子 | Original post link

There are two methods:

  1. Use the first SQL as a subquery and place instr()<>0 outside.
  2. Add STRAIGHT_JOIN to specify the join order.
| username: porpoiselxj | Original post link

I tried both methods you mentioned, but the problem remains.
Now it’s not a matter of order, but rather that it uses hash_join, which is not much related to the order anymore.

| username: forever | Original post link

What about changing it to a subquery with IN?

| username: porpoiselxj | Original post link

Initially, it was written with “in”, but it didn’t work, so it was changed to “join”.

| username: zhaokede | Original post link

Check if SELECT tt1.* FROM wind.tb_object_6757 tt1 WHERE tt1.ob_object_id=? AND INSTR(f5_6757, 'xxxx')<>0 uses an index or a full table scan.

| username: porpoiselxj | Original post link

ob_object_id is the primary key, using point_get.

| username: porpoiselxj | Original post link

The issue lies with this condition: and instr(tt1.f5_6757, ‘xxxx’) <> 0
If the condition is directly changed to tt1.f5_6757 like ‘%xxxx%’, the execution plan is fine.

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

Try specifying tt1 and tt2 to use the PRIMARY(OB_OBJECT_ID) and idx_WINDTBOBJECT6757OPLOG_TIME(OPDATE, ID) indexes respectively.

| username: porpoiselxj | Original post link

I tried it, but it’s still doing a full table scan.

The issue lies with this condition: and instr(tt1.f5_6757, 'xxxx') <> 0
If you change the condition directly to tt1.f5_6757 like '%xxxx%', the execution plan is fine.

| username: buddyyuan | Original post link

Try using /*+inl_join(tt1) */.

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

It seems that instr cannot be pushed down to TiKV, resulting in a full table scan of the tt1 table. LIKE can be pushed down.

| username: IanWong | Original post link

When testing this solution, please note that it will affect the entire system, so proceed with caution.

| username: mono | Original post link

I also have doubts about this execution plan in version 6.5.7. The table health index value is 93. But without adding a hint, it doesn’t use the index. With the hint, the query returns results in milliseconds. Without it, it takes a few seconds.

| username: porpoiselxj | Original post link

In your case, manually analyze the table you want to query and take a look.

| username: porpoiselxj | Original post link

The problem persists.

| username: IanWong | Original post link

Set @@tidb_enable_outer_join_reorder=off; then check the execution plan.

| username: porpoiselxj | Original post link

Continue with full table scan.