Enhancement
Background
When the outer table is small, IN (SELECT DISTINCT ...) subqueries are often better executed as a correlated EXISTS semi-join so the optimizer can drive the inner table by the outer rows and short-circuit on the first match. This is especially safe in NOT NULL scenarios (e.g. PK/UK), where IN vs EXISTS NULL semantics divergence does not apply.
Currently, for IN (SELECT DISTINCT ...), TiDB tends to materialize the subquery result via HashAgg(DISTINCT) and then join to the outer table, which can be suboptimal when the outer table is small.
Proposal
We can add an optimization rule (or planner preference) to rewrite / plan: into an equivalent semi-join / correlated EXISTS, when:
-
The compared columns are guaranteed NOT NULL (e.g. PK/UK/explicit NOT NULL)
-
Subquery is a simple projection of the join key (no aggregates other than DISTINCT, no LIMIT/OFFSET that changes semantics).
This would allow the plan to be driven by the smaller outer input and avoid unnecessary HashAgg materialization in common OLTP patterns.
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
mysql> CREATE TABLE `t1` ( `id` bigint not NULL, `k` int DEFAULT NULL, KEY `idx_k` (`k`) );
mysql> CREATE TABLE `t2` ( `id` bigint not NULL, `k` int DEFAULT NULL, KEY `idx_k` (`k`) );
mysql> explain select * from t1 where t1.id in (select /*+ NO_DECORRELATE() */ distinct id from t2 where k=1);
2. What did you expect to see? (Required)
+------------------------------------+-----------+-----------+--------------------------+---------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------+-----------+-----------+--------------------------+---------------------------------------------+
| Apply_13 | 10000.00 | root | | CARTESIAN semi join |
| ├─TableReader_15(Build) | 10000.00 | root | | data:TableFullScan_14 |
| │ └─TableFullScan_14 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
| └─Limit_17(Probe) | 100.00 | root | | offset:0, count:1 |
| └─IndexLookUp_22 | 100.00 | root | | |
| ├─IndexRangeScan_18(Build) | 100000.00 | cop[tikv] | table:t2, index:idx_k(k) | range:[1,1], keep order:false, stats:pseudo |
| └─Limit_21(Probe) | 100.00 | cop[tikv] | | offset:0, count:1 |
| └─Selection_20 | 100.00 | cop[tikv] | | eq(test2.t1.id, test2.t2.id) |
| └─TableRowIDScan_19 | 100000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
+------------------------------------+-----------+-----------+--------------------------+---------------------------------------------+
9 rows in set (0.01 sec)
3. What did you see instead (Required)
mysql> explain select * from t1 where t1.id in (select /*+ NO_DECORRELATE() */ distinct id from t2 where k=1);
+--------------------------------------+----------+-----------+--------------------------+----------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------------+----------+-----------+--------------------------+----------------------------------------------------------------+
| HashJoin_16 | 10.00 | root | | inner join, equal:[eq(test2.t2.id, test2.t1.id)] |
| ├─HashAgg_22(Build) | 8.00 | root | | group by:test2.t2.id, funcs:firstrow(test2.t2.id)->test2.t2.id |
| │ └─IndexLookUp_23 | 8.00 | root | | |
| │ ├─IndexRangeScan_20(Build) | 10.00 | cop[tikv] | table:t2, index:idx_k(k) | range:[1,1], keep order:false, stats:pseudo |
| │ └─HashAgg_18(Probe) | 8.00 | cop[tikv] | | group by:test2.t2.id, |
| │ └─TableRowIDScan_21 | 10.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─TableReader_28(Probe) | 10000.00 | root | | data:TableFullScan_27 |
| └─TableFullScan_27 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+--------------------------------------+----------+-----------+--------------------------+----------------------------------------------------------------+
8 rows in set, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1815 | NO_DECORRELATE() is inapplicable because there are no correlated columns. |
+---------+------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)
4. What is your TiDB version? (Required)
Enhancement
Background
When the outer table is small, IN (SELECT DISTINCT ...) subqueries are often better executed as a correlated EXISTS semi-join so the optimizer can drive the inner table by the outer rows and short-circuit on the first match. This is especially safe in NOT NULL scenarios (e.g. PK/UK), where IN vs EXISTS NULL semantics divergence does not apply.
Currently, for IN (SELECT DISTINCT ...), TiDB tends to materialize the subquery result via HashAgg(DISTINCT) and then join to the outer table, which can be suboptimal when the outer table is small.
Proposal
We can add an optimization rule (or planner preference) to rewrite / plan: into an equivalent semi-join / correlated EXISTS, when:
The compared columns are guaranteed NOT NULL (e.g. PK/UK/explicit NOT NULL)
Subquery is a simple projection of the join key (no aggregates other than DISTINCT, no LIMIT/OFFSET that changes semantics).
This would allow the plan to be driven by the smaller outer input and avoid unnecessary HashAgg materialization in common OLTP patterns.
Please answer these questions before submitting your issue. Thanks!
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)