optimizer: redundant WHERE condition result in poor exeution plan

[Yriuns]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. create table

CREATE TABLE `t` (
  `group_id` bigint(20) NOT NULL,
  `sales_grid_id` bigint(20) NOT NULL DEFAULT '0',
  `rule_id` int(11) NOT NULL DEFAULT '0',
  `version` bigint(20) NOT NULL,
  `measure_id` bigint(20) NOT NULL DEFAULT '0',
  `group_type` smallint(4) NOT NULL DEFAULT '1',
  `channel_id` int(11) NOT NULL DEFAULT '0',
  `istatus` tinyint(4) NOT NULL DEFAULT '1',
  `measure_value` varchar(255) NOT NULL DEFAULT '',
  UNIQUE KEY `uniq_group_grid_rule_ver_measure_gtype` (`group_id`,`sales_grid_id`,`rule_id`,`version`,`measure_id`,`group_type`,`channel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

2. execute 2 queries. query 2 has a redundant AND channel_id = 1001 condition.

-- query 1
explain SELECT *
FROM t
WHERE ((istatus = 1
        AND group_id = 1126213
        AND sales_grid_id = 50000160
        AND group_type = 3
        AND version = 1600264699490963518)
    OR (istatus = 1
        AND group_id = 902599
        AND sales_grid_id = 50000391
        AND group_type = 3
        AND version = 1600264703391563800
    )
AND rule_id IN (128, 160, 129, 161, 131, 165, 38, 166, 168, 169, 170, 148, 149, 150, 151, 155, 156)
AND channel_id = 1001);
-- query 2
explain SELECT *
FROM t
WHERE ((istatus = 1
        AND group_id = 1126213
        AND sales_grid_id = 50000160
        AND group_type = 3
        AND version = 1600264699490963518)
    OR (istatus = 1
        AND group_id = 902599
        AND sales_grid_id = 50000391
        AND group_type = 3
        AND version = 1600264703391563800
    )
AND rule_id IN (128, 160, 129, 161, 131, 165, 38, 166, 168, 169, 170, 148, 149, 150, 151, 155, 156)
AND channel_id = 1001)
AND channel_id = 1001; -- redundant condition

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

two exeution plans are exactly the same.

3. What did you see instead (Required)

the execution plan of query 2 is inefficient.

-- query 1
+-------------------------------+---------+-----------+--------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                            | estRows | task      | access object                                                                                                                        | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+-------------------------------+---------+-----------+--------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| IndexLookUp_11                | 0.08    | root      |                                                                                                                                      |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| ├─IndexRangeScan_8(Build)     | 0.10    | cop[tikv] | table:t, index:uniq_group_grid_rule_ver_measure_gtype(group_id, sales_grid_id, rule_id, version, measure_id, group_type, channel_id) | range:[902599 50000391 38 1600264703391563800,902599 50000391 38 1600264703391563800], [902599 50000391 128 1600264703391563800,902599 50000391 128 1600264703391563800], [902599 50000391 129 1600264703391563800,902599 50000391 129 1600264703391563800], [902599 50000391 131 1600264703391563800,902599 50000391 131 1600264703391563800], [902599 50000391 148 1600264703391563800,902599 50000391 148 1600264703391563800], [902599 50000391 149 1600264703391563800,902599 50000391 149 1600264703391563800], [902599 50000391 150 1600264703391563800,902599 50000391 150 1600264703391563800], [902599 50000391 151 1600264703391563800,902599 50000391 151 1600264703391563800], [902599 50000391 155 1600264703391563800,902599 50000391 155 1600264703391563800], [902599 50000391 156 1600264703391563800,902599 50000391 156 1600264703391563800], [902599 50000391 160 1600264703391563800,902599 50000391 160 1600264703391563800], [902599 50000391 161 1600264703391563800,902599 50000391 161 1600264703391563800], [902599 50000391 165 1600264703391563800,902599 50000391 165 1600264703391563800], [902599 50000391 166 1600264703391563800,902599 50000391 166 1600264703391563800], [902599 50000391 168 1600264703391563800,902599 50000391 168 1600264703391563800], [902599 50000391 169 1600264703391563800,902599 50000391 169 1600264703391563800], [902599 50000391 170 1600264703391563800,902599 50000391 170 1600264703391563800], [1126213 50000160,1126213 50000160], keep order:false, stats:pseudo |
| └─Selection_10(Probe)         | 0.08    | cop[tikv] |                                                                                                                                      | or(and(and(eq(test.t.istatus, 1), eq(test.t.group_id, 1126213)), and(eq(test.t.sales_grid_id, 50000160), and(eq(test.t.group_type, 3), eq(test.t.version, 1600264699490963518)))), and(and(eq(test.t.istatus, 1), and(eq(test.t.group_id, 902599), eq(test.t.sales_grid_id, 50000391))), and(and(eq(test.t.group_type, 3), eq(test.t.version, 1600264703391563800)), and(in(test.t.rule_id, 128, 160, 129, 161, 131, 165, 38, 166, 168, 169, 170, 148, 149, 150, 151, 155, 156), eq(test.t.channel_id, 1001)))))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|   └─TableRowIDScan_9          | 0.10    | cop[tikv] | table:t                                                                                                                              | keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-------------------------------+---------+-----------+--------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

-- query 2
+----------------------------+---------+-----------+--------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                         | estRows | task      | access object                                                                                                                        | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+----------------------------+---------+-----------+--------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| IndexLookUp_12             | 0.00    | root      |                                                                                                                                      |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| ├─Selection_10(Build)      | 0.02    | cop[tikv] |                                                                                                                                      | eq(test.t.channel_id, 1001)                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| │ └─IndexRangeScan_8       | 20.00   | cop[tikv] | table:t, index:uniq_group_grid_rule_ver_measure_gtype(group_id, sales_grid_id, rule_id, version, measure_id, group_type, channel_id) | range:[902599,902599], [1126213,1126213], keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                               |
| └─Selection_11(Probe)      | 0.00    | cop[tikv] |                                                                                                                                      | or(and(and(eq(test.t.istatus, 1), eq(test.t.group_id, 1126213)), and(eq(test.t.sales_grid_id, 50000160), and(eq(test.t.group_type, 3), eq(test.t.version, 1600264699490963518)))), and(and(eq(test.t.istatus, 1), and(eq(test.t.group_id, 902599), eq(test.t.sales_grid_id, 50000391))), and(and(eq(test.t.group_type, 3), eq(test.t.version, 1600264703391563800)), and(in(test.t.rule_id, 128, 160, 129, 161, 131, 165, 38, 166, 168, 169, 170, 148, 149, 150, 151, 155, 156), 1)))) |
|   └─TableRowIDScan_9       | 0.02    | cop[tikv] | table:t                                                                                                                              | keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+----------------------------+---------+-----------+--------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v6.4.0
Edition: Community
Git Commit Hash: cf36a9ce2fe1039db3cf3444d51930b887df18a1
Git Branch: heads/refs/tags/v6.4.0
UTC Build Time: 2022-11-13 05:15:26
GoVersion: go1.19.2
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv

[fixdb]

Nice catch.
We should dedup these conj / disj predicates.

[fixdb]

@seiya-annie Can you label it as improvement instead of bug?

[Yriuns]

@fixdb hi, is there any schedule to improve this query?

[ghazalfamilyusa]

I tried the explain and the redundant condition is actually removed. The plan also listed above shows there is no redundant condition. eq(test.t.channel_id, 1001) only done once. But, q2 has the predicates in a different order and "possibly" that changed the index access path.