Ignore index optimizer hint doesn't work with sub queries

[sykp241095]

原帖作者：Lucas_Sky\n原帖时间：2023-01-27 09:14:28.397414\n原帖ID/Slug：130 / ignore-index-optimizer-hint-doesnt-work-with-sub-queries\n\n

Application environment:

There is none. Accessing TiDB directly via mysql-client.

TiDB version:

select @@version;
+--------------------+
| @@version          |
+--------------------+
| 5.7.25-TiDB-v6.5.0 |
+--------------------+

Problem:

ignore index used in queries with sub queries does not work as expected.

When using ignore index with sub-query it is ignored/does not nothing.

EXPLAIN SELECT COUNT(DISTINCT u0_.device_id) AS sclr_0 FROM   daily_traffic_stats d1_  ignore index(ping_at_idx) LEFT JOIN user_vid_devices u0_ ON d1_.vid = u0_.vid WHERE   d1_.vid IN (     SELECT       u2_.vid   FROM       user_vid_devices u2_ force index(user_id_vid_idx)     WHERE       u2_.user_id = '0ec7d9f4-0c07-4c8b-a5d5-f8ad5d84cb51'   )   AND d1_.ping_at > '2023-01-27 08:11:54' LIMIT   1;
+---------------------------------------------+----------+-----------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                          | estRows  | task      | access object                             | operator info                                                                                                                                                                                                               |
+---------------------------------------------+----------+-----------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Limit_22                                    | 1.00     | root      |                                           | offset:0, count:1                                                                                                                                                                                                           |
| └─StreamAgg_24                              | 1.00     | root      |                                           | funcs:count(distinct hourly_stats.user_vid_devices.device_id)->Column#33                                                                                                                                                    |
|   └─IndexJoin_120                           | 48.96    | root      |                                           | left outer join, inner:IndexLookUp_119, outer key:hourly_stats.daily_traffic_stats.vid, inner key:hourly_stats.user_vid_devices.vid, equal cond:eq(hourly_stats.daily_traffic_stats.vid, hourly_stats.user_vid_devices.vid) |
|     ├─HashJoin_100(Build)                   | 48.65    | root      |                                           | inner join, equal:[eq(hourly_stats.user_vid_devices.vid, hourly_stats.daily_traffic_stats.vid)]                                                                                                                             |
|     │ ├─IndexLookUp_106(Build)              | 1.93     | root      |                                           |                                                                                                                                                                                                                             |
|     │ │ ├─IndexRangeScan_104(Build)         | 1.93     | cop[tikv] | table:u2_, index:user_id_vid_idx(user_id) | range:["0ec7d9f4-0c07-4c8b-a5d5-f8ad5d84cb51","0ec7d9f4-0c07-4c8b-a5d5-f8ad5d84cb51"], keep order:false                                                                                                                     |
|     │ │ └─TableRowIDScan_105(Probe)         | 1.93     | cop[tikv] | table:u2_                                 | keep order:false                                                                                                                                                                                                            |
|     │ └─IndexLookUp_112(Probe)              | 43154.06 | root      |                                           |                                                                                                                                                                                                                             |
|     │   ├─IndexRangeScan_110(Build)         | 43154.06 | cop[tikv] | table:d1_, index:ping_at_idx(ping_at)     | range:(2023-01-27 08:11:54,+inf], keep order:false                                                                                                                                                                          |
|     │   └─TableRowIDScan_111(Probe)         | 43154.06 | cop[tikv] | table:d1_                                 | keep order:false                                                                                                                                                                                                            |
|     └─IndexLookUp_119(Probe)                | 48.65    | root      |                                           |                                                                                                                                                                                                                             |
|       ├─IndexRangeScan_117(Build)           | 48.65    | cop[tikv] | table:u0_, index:PRIMARY(vid)             | range: decided by [eq(hourly_stats.user_vid_devices.vid, hourly_stats.daily_traffic_stats.vid)], keep order:false                                                                                                           |
|       └─TableRowIDScan_118(Probe)           | 48.65    | cop[tikv] | table:u0_                                 | keep order:false                                                                                                                                                                                                            |
+---------------------------------------------+----------+-----------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.001 sec)

When sub query is removed ignore index is applied.

 EXPLAIN SELECT  COUNT(DISTINCT vid) AS sclr_0 FROM   daily_traffic_stats d1_  ignore index(ping_at_idx) WHERE d1_.ping_at > '2023-01-27 08:11:54' LIMIT   1;
+------------------------------+---------------+-----------+---------------+--------------------------------------------------------------------------+
| id                           | estRows       | task      | access object | operator info                                                            |
+------------------------------+---------------+-----------+---------------+--------------------------------------------------------------------------+
| Limit_11                     | 1.00          | root      |               | offset:0, count:1                                                        |
| └─StreamAgg_13               | 1.00          | root      |               | funcs:count(distinct hourly_stats.daily_traffic_stats.vid)->Column#21    |
|   └─TableReader_19           | 43872.08      | root      |               | data:Selection_18                                                        |
|     └─Selection_18           | 43872.08      | cop[tikv] |               | gt(hourly_stats.daily_traffic_stats.ping_at, 2023-01-27 08:11:54.000000) |
|       └─TableFullScan_17     | 1117734277.00 | cop[tikv] | table:d1_     | keep order:false                                                         |
+------------------------------+---------------+-----------+---------------+--------------------------------------------------------------------------+
5 rows in set (0.001 sec)

The same goes for other optimizer hints such as force index, use index

[sykp241095]

原帖作者：winkyao\n原帖时间：2023-01-27 09:35:46.904788\n\n

Could you try

EXPLAIN SELECT /*+ IGNORE_INDEX(d1_, ping_at_idx) */ COUNT(DISTINCT u0_.device_id) AS sclr_0 FROM   daily_traffic_stats d1_  LEFT JOIN user_vid_devices u0_ ON d1_.vid = u0_.vid WHERE   d1_.vid IN (     SELECT       u2_.vid   FROM       user_vid_devices u2_ force index(user_id_vid_idx)     WHERE       u2_.user_id = '0ec7d9f4-0c07-4c8b-a5d5-f8ad5d84cb51'   )   AND d1_.ping_at > '2023-01-27 08:11:54' LIMIT   1;

[sykp241095]

原帖作者：Lucas_Sky\n原帖时间：2023-01-27 09:38:41.91615\n\n

Thanks for fast reply.

EXPLAIN SELECT /*+ IGNORE_INDEX(d1_, ping_at_idx) */ COUNT(DISTINCT u0_.device_id) AS sclr_0 FROM   daily_traffic_stats d1_  LEFT JOIN user_vid_devices u0_ ON d1_.vid = u0_.vid WHERE   d1_.vid IN (     SELECT       u2_.vid   FROM       user_vid_devices u2_ force index(user_id_vid_idx)     WHERE       u2_.user_id = '0ec7d9f4-0c07-4c8b-a5d5-f8ad5d84cb51'   )   AND d1_.ping_at > '2023-01-27 08:11:54' LIMIT   1;
+---------------------------------------------+----------+-----------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                          | estRows  | task      | access object                             | operator info                                                                                                                                                                                                               |
+---------------------------------------------+----------+-----------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Limit_22                                    | 1.00     | root      |                                           | offset:0, count:1                                                                                                                                                                                                           |
| └─StreamAgg_24                              | 1.00     | root      |                                           | funcs:count(distinct hourly_stats.user_vid_devices.device_id)->Column#33                                                                                                                                                    |
|   └─IndexJoin_120                           | 48.96    | root      |                                           | left outer join, inner:IndexLookUp_119, outer key:hourly_stats.daily_traffic_stats.vid, inner key:hourly_stats.user_vid_devices.vid, equal cond:eq(hourly_stats.daily_traffic_stats.vid, hourly_stats.user_vid_devices.vid) |
|     ├─HashJoin_100(Build)                   | 48.65    | root      |                                           | inner join, equal:[eq(hourly_stats.user_vid_devices.vid, hourly_stats.daily_traffic_stats.vid)]                                                                                                                             |
|     │ ├─IndexLookUp_106(Build)              | 1.93     | root      |                                           |                                                                                                                                                                                                                             |
|     │ │ ├─IndexRangeScan_104(Build)         | 1.93     | cop[tikv] | table:u2_, index:user_id_vid_idx(user_id) | range:["0ec7d9f4-0c07-4c8b-a5d5-f8ad5d84cb51","0ec7d9f4-0c07-4c8b-a5d5-f8ad5d84cb51"], keep order:false                                                                                                                     |
|     │ │ └─TableRowIDScan_105(Probe)         | 1.93     | cop[tikv] | table:u2_                                 | keep order:false                                                                                                                                                                                                            |
|     │ └─IndexLookUp_112(Probe)              | 53666.27 | root      |                                           |                                                                                                                                                                                                                             |
|     │   ├─IndexRangeScan_110(Build)         | 53666.27 | cop[tikv] | table:d1_, index:ping_at_idx(ping_at)     | range:(2023-01-27 08:11:54,+inf], keep order:false                                                                                                                                                                          |
|     │   └─TableRowIDScan_111(Probe)         | 53666.27 | cop[tikv] | table:d1_                                 | keep order:false                                                                                                                                                                                                            |
|     └─IndexLookUp_119(Probe)                | 48.65    | root      |                                           |                                                                                                                                                                                                                             |
|       ├─IndexRangeScan_117(Build)           | 48.65    | cop[tikv] | table:u0_, index:PRIMARY(vid)             | range: decided by [eq(hourly_stats.user_vid_devices.vid, hourly_stats.daily_traffic_stats.vid)], keep order:false                                                                                                           |
|       └─TableRowIDScan_118(Probe)           | 48.65    | cop[tikv] | table:u0_                                 | keep order:false                                                                                                                                                                                                            |
+---------------------------------------------+----------+-----------+-------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.001 sec)

[sykp241095]

原帖作者：winkyao\n原帖时间：2023-01-27 09:40:14.913291\n\n

Thanks for your feedback, we’ll take a closer look, and get back to you when we figure it out.

[sykp241095]

原帖作者：timeandfate\n原帖时间：2023-01-30 19:22:40.581433\n\n

The SQL bindings also affect the optimizer hints, you can run show global bindings and show session bindings to check if there are bindings on this SQL.

[sykp241095]

原帖作者：Misaka\n原帖时间：2023-01-31 07:12:20.695903\n\n

image2690×1350 365 KB

I’ve created a simple case, and it shows the hint works.
Would you please give us the result of Use PLAN REPLAYER to Save and Restore the On-Site Information of a Cluster | PingCAP Docs to help us to find the reason?
You can send the dump result via privately way.

[sykp241095]

原帖作者：Lucas_Sky\n原帖时间：2023-01-31 09:22:19.69227\n\n

Hello,

Thanks @timeandfate, it seems that we’ve left a global binding that interfered with optimizer hints. Removed global binding and now use index/ignore index works as expected.

Thank you @Misaka @timeandfate

[sykp241095]

原帖作者：winkyao\n原帖时间：2023-01-31 10:09:22.801007\n\n

If you encounter the similar situation in the future, you can use

explain format='verbose' select xxxx

to find out if there are any binding with this SQL statement, it will show in the warning section.