Planner will not consider IndexMerge when an alternative (non selective) index exists

[morgo]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
 id BIGINT NOT NULL AUTO_INCREMENT,
 c_id bigint(20) NOT NULL,
 l_hash varbinary(255) DEFAULT NULL,
 b_hash varbinary(255) DEFAULT NULL,
 s_hash varbinary(255) DEFAULT NULL,
 d varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
 PRIMARY KEY (c_id,id) /*T![clustered_index] CLUSTERED */,
 UNIQUE KEY (id),
 KEY (l_hash),
 KEY (b_hash,d)
);

SET tidb_enable_index_merge = 1;

EXPLAIN SELECT * FROM t1 WHERE id != 107053556 AND (
 (l_hash = '04-8ee493297fb4d9334865fb6be7ef8960a4a20f0745050f14e7d2c69f799bab8c' AND b_hash = '04-aa6e53190af3625018ec86fab7790726f4c918a36ed74084f4ccbbb38b29cedf')
 OR (l_hash = '04-8ee493297fb4d9334865fb6be7ef8960a4a20f0745050f14e7d2c69f799bab8c' AND s_hash = '04-2abea4972adc29134a11d34c52ccd27112eea07cfb4127aaaceda4b333dbc047')
 OR (b_hash = '04-aa6e53190af3625018ec86fab7790726f4c918a36ed74084f4ccbbb38b29cedf' AND s_hash = '04-2abea4972adc29134a11d34c52ccd27112eea07cfb4127aaaceda4b333dbc047')
);

-- But if I remove the id != 107053556 it is able to index merge correctly:

EXPLAIN SELECT * FROM t1 WHERE  (
 (l_hash = '04-8ee493297fb4d9334865fb6be7ef8960a4a20f0745050f14e7d2c69f799bab8c' AND b_hash = '04-aa6e53190af3625018ec86fab7790726f4c918a36ed74084f4ccbbb38b29cedf')
 OR (l_hash = '04-8ee493297fb4d9334865fb6be7ef8960a4a20f0745050f14e7d2c69f799bab8c' AND s_hash = '04-2abea4972adc29134a11d34c52ccd27112eea07cfb4127aaaceda4b333dbc047')
 OR (b_hash = '04-aa6e53190af3625018ec86fab7790726f4c918a36ed74084f4ccbbb38b29cedf' AND s_hash = '04-2abea4972adc29134a11d34c52ccd27112eea07cfb4127aaaceda4b333dbc047')
);

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

I expect more or less the same query plan from both queries.

The column id is a unique index. It seems that because an index is considered for id != 107053556 (but not useful), IndexMerge is not considered at all.

3. What did you see instead (Required)

mysql> EXPLAIN SELECT * FROM t1 WHERE id != 107053556 AND (
    ->  (l_hash = '04-8ee493297fb4d9334865fb6be7ef8960a4a20f0745050f14e7d2c69f799bab8c' AND b_hash = '04-aa6e53190af3625018ec86fab7790726f4c918a36ed74084f4ccbbb38b29cedf')
    ->  OR (l_hash = '04-8ee493297fb4d9334865fb6be7ef8960a4a20f0745050f14e7d2c69f799bab8c' AND s_hash = '04-2abea4972adc29134a11d34c52ccd27112eea07cfb4127aaaceda4b333dbc047')
    ->  OR (b_hash = '04-aa6e53190af3625018ec86fab7790726f4c918a36ed74084f4ccbbb38b29cedf' AND s_hash = '04-2abea4972adc29134a11d34c52ccd27112eea07cfb4127aaaceda4b333dbc047')
    -> );
+-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                      | estRows  | task      | access object | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TableReader_7           | 0.02     | root      |               | data:Selection_6                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| └─Selection_6           | 0.02     | cop[tikv] |               | ne(test.t1.id, 107053556), or(and(eq(test.t1.l_hash, "04-8ee493297fb4d9334865fb6be7ef8960a4a20f0745050f14e7d2c69f799bab8c"), eq(test.t1.b_hash, "04-aa6e53190af3625018ec86fab7790726f4c918a36ed74084f4ccbbb38b29cedf")), or(and(eq(test.t1.l_hash, "04-8ee493297fb4d9334865fb6be7ef8960a4a20f0745050f14e7d2c69f799bab8c"), eq(test.t1.s_hash, "04-2abea4972adc29134a11d34c52ccd27112eea07cfb4127aaaceda4b333dbc047")), and(eq(test.t1.b_hash, "04-aa6e53190af3625018ec86fab7790726f4c918a36ed74084f4ccbbb38b29cedf"), eq(test.t1.s_hash, "04-2abea4972adc29134a11d34c52ccd27112eea07cfb4127aaaceda4b333dbc047")))) |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> 
mysql> -- But if I remove the id != 107053556 it is able to index merge correctly:
mysql> 
mysql> EXPLAIN SELECT * FROM t1 WHERE  (
    ->  (l_hash = '04-8ee493297fb4d9334865fb6be7ef8960a4a20f0745050f14e7d2c69f799bab8c' AND b_hash = '04-aa6e53190af3625018ec86fab7790726f4c918a36ed74084f4ccbbb38b29cedf')
    ->  OR (l_hash = '04-8ee493297fb4d9334865fb6be7ef8960a4a20f0745050f14e7d2c69f799bab8c' AND s_hash = '04-2abea4972adc29134a11d34c52ccd27112eea07cfb4127aaaceda4b333dbc047')
    ->  OR (b_hash = '04-aa6e53190af3625018ec86fab7790726f4c918a36ed74084f4ccbbb38b29cedf' AND s_hash = '04-2abea4972adc29134a11d34c52ccd27112eea07cfb4127aaaceda4b333dbc047')
    -> );
+--------------------------------+---------+-----------+-----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                             | estRows | task      | access object                     | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+--------------------------------+---------+-----------+-----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| IndexMerge_13                  | 0.00    | root      |                                   |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| ├─IndexRangeScan_8(Build)      | 10.00   | cop[tikv] | table:t1, index:l_hash(l_hash)    | range:[0x30342D38656534393332393766623464393333343836356662366265376566383936306134613230663037343530353066313465376432633639663739396261623863,0x30342D38656534393332393766623464393333343836356662366265376566383936306134613230663037343530353066313465376432633639663739396261623863], keep order:false, stats:pseudo                                                                                                                                                                                                                                                               |
| ├─IndexRangeScan_9(Build)      | 10.00   | cop[tikv] | table:t1, index:l_hash(l_hash)    | range:[0x30342D38656534393332393766623464393333343836356662366265376566383936306134613230663037343530353066313465376432633639663739396261623863,0x30342D38656534393332393766623464393333343836356662366265376566383936306134613230663037343530353066313465376432633639663739396261623863], keep order:false, stats:pseudo                                                                                                                                                                                                                                                               |
| ├─IndexRangeScan_10(Build)     | 10.00   | cop[tikv] | table:t1, index:b_hash(b_hash, d) | range:[0x30342D61613665353331393061663336323530313865633836666162373739303732366634633931386133366564373430383466346363626262333862323963656466,0x30342D61613665353331393061663336323530313865633836666162373739303732366634633931386133366564373430383466346363626262333862323963656466], keep order:false, stats:pseudo                                                                                                                                                                                                                                                               |
| └─Selection_12(Probe)          | 0.00    | cop[tikv] |                                   | or(and(eq(test.t1.l_hash, "04-8ee493297fb4d9334865fb6be7ef8960a4a20f0745050f14e7d2c69f799bab8c"), eq(test.t1.b_hash, "04-aa6e53190af3625018ec86fab7790726f4c918a36ed74084f4ccbbb38b29cedf")), or(and(eq(test.t1.l_hash, "04-8ee493297fb4d9334865fb6be7ef8960a4a20f0745050f14e7d2c69f799bab8c"), eq(test.t1.s_hash, "04-2abea4972adc29134a11d34c52ccd27112eea07cfb4127aaaceda4b333dbc047")), and(eq(test.t1.b_hash, "04-aa6e53190af3625018ec86fab7790726f4c918a36ed74084f4ccbbb38b29cedf"), eq(test.t1.s_hash, "04-2abea4972adc29134a11d34c52ccd27112eea07cfb4127aaaceda4b333dbc047")))) |
|   └─TableRowIDScan_11          | 19.99   | cop[tikv] | table:t1                          | keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+--------------------------------+---------+-----------+-----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v5.2.0-alpha-420-gc70662bbe
Edition: Community
Git Commit Hash: c70662bbecd0bd2a4db62e15bc0393f3aaf55906
Git Branch: master
UTC Build Time: 2021-07-31 02:45:02
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[winoros]

This is a limitation of current index merge.

[chrysan]

duplicated with #30409