pingcap / pingcap/tidb

HashJoin and IndexHashJoin has the similar cost estimation which caused plan changed

Open
#38,641 0 comments 0 reactions 0 assignees View on GitHub
epic/cost-model severity/moderate sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

HashJoin and IndexHashJoin has the similar cost estimation which caused plan changed

The former plan uses the `├─IndexHashJoin_145` while the later uses `├─HashJoin_149`

### 1. Minimal reproduce step (Required)

Job 11B

```sql

mysql> explain format='verbose' SELECT MIN(cn.name) AS from_company, MIN(lt.link) AS movie_link_type, MIN(t.title) AS sequel_movie FROM company_name AS cn, company_type AS ct, keyword AS k, link_type AS lt, movie_companies AS mc, movie_keyword AS mk, movie_link AS ml, title AS t WHERE cn.country_code !='[pl]' AND (cn.name LIKE '%Film%' OR cn.name LIKE '%Warner%') AND ct.kind ='production companies' AND k.keyword ='sequel' AND lt.link LIKE '%follows%' AND mc.note IS NULL AND t.production_year = 1998 and t.title like '%Money%' AND lt.id = ml.link_type_id AND ml.movie_id = t.id AND t.id = mk.movie_id AND mk.keyword_id = k.id AND t.id = mc.movie_id AND mc.company_type_id = ct.id AND mc.company_id = cn.id AND ml.movie_id = mk.movie_id AND ml.movie_id = mc.movie_id AND mk.movie_id = mc.movie_id;
+---------------------------------------------------------+------------+--------------+-----------+------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | estCost | task | access object | operator info |
+---------------------------------------------------------+------------+--------------+-----------+------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_35 | 1.00 | 101703178.14 | root | | funcs:min(imdb.company_name.name)->Column#39, funcs:min(imdb.link_type.link)->Column#40, funcs:min(imdb.title.title)->Column#41 |
| └─Projection_37 | 148.83 | 101702877.25 | root | | imdb.company_name.name, imdb.link_type.link, imdb.title.title |
| └─HashJoin_50 | 148.83 | 101702769.96 | root | | inner join, equal:[eq(imdb.movie_keyword.keyword_id, imdb.keyword.id)] |
| ├─IndexLookUp_249(Build) | 5.18 | 112.35 | root | | |
| │ ├─IndexRangeScan_246(Build) | 5.18 | 371.64 | cop[tikv] | table:k, index:keyword_idx_keyword(keyword) | range:["seque","seque"], keep order:false |
| │ └─Selection_248(Probe) | 5.18 | 369.70 | cop[tikv] | | eq(imdb.keyword.keyword, "sequel") |
| │ └─TableRowIDScan_247 | 5.18 | 354.15 | cop[tikv] | table:k | keep order:false |
| └─HashJoin_64(Probe) | 6726938.16 | 101702534.75 | root | | inner join, equal:[eq(imdb.movie_companies.movie_id, imdb.movie_keyword.movie_id) eq(imdb.movie_link.movie_id, imdb.movie_keyword.movie_id) eq(imdb.title.id, imdb.movie_keyword.movie_id)] |
| ├─HashJoin_77(Build) | 660397.78 | 59156740.60 | root | | inner join, equal:[eq(imdb.movie_link.link_type_id, imdb.link_type.id)] |
| │ ├─TableReader_240(Build) | 14.40 | 88.02 | root | | data:Selection_239 |
| │ │ └─Selection_239 | 14.40 | 945.00 | cop[tikv] | | like(imdb.link_type.link, "%follows%", 92) |
| │ │ └─TableFullScan_238 | 18.00 | 891.00 | cop[tikv] | table:lt | keep order:false |
| │ └─IndexHashJoin_84(Probe) | 762274.16 | 58760352.70 | root | | inner join, inner:IndexLookUp_81, outer key:imdb.movie_companies.movie_id, inner key:imdb.movie_link.movie_id, equal cond:eq(imdb.movie_companies.movie_id, imdb.movie_link.movie_id), eq(imdb.title.id, imdb.movie_link.movie_id) |
| │ ├─IndexHashJoin_101(Build) | 95357.47 | 43473786.44 | root | | inner join, inner:TableReader_95, outer key:imdb.movie_companies.company_id, inner key:imdb.company_name.id, equal cond:eq(imdb.movie_companies.company_id, imdb.company_name.id) |
| │ │ ├─HashJoin_149(Build) | 95357.47 | 41071345.67 | root | | inner join, equal:[eq(imdb.movie_companies.movie_id, imdb.title.id)] |
| │ │ │ ├─IndexLookUp_232(Build) | 46472.00 | 1748990.44 | root | | |
| │ │ │ │ ├─IndexRangeScan_229(Build) | 58090.00 | 3267964.09 | cop[tikv] | table:t, index:title_idx_year(production_year) | range:[1998,1998], keep order:false |
| │ │ │ │ └─Selection_231(Probe) | 46472.00 | 10178239.35 | cop[tikv] | | like(imdb.title.title, "%Money%", 92) |
| │ │ │ │ └─TableRowIDScan_230 | 58090.00 | 10003969.35 | cop[tikv] | table:t | keep order:false |
| │ │ │ └─HashJoin_203(Probe) | 2435454.00 | 39125660.28 | root | | inner join, equal:[eq(imdb.company_type.id, imdb.movie_companies.company_type_id)] |
| │ │ │ ├─TableReader_218(Build) | 1.00 | 21.12 | root | | data:Selection_217 |
| │ │ │ │ └─Selection_217 | 1.00 | 264.00 | cop[tikv] | | eq(imdb.company_type.kind, "production companies") |
| │ │ │ │ └─TableFullScan_216 | 4.00 | 252.00 | cop[tikv] | table:ct | keep order:false |
| │ │ │ └─TableReader_225(Probe) | 2669657.00 | 37664345.75 | root | | data:Selection_224 |
| │ │ │ └─Selection_224 | 2669657.00 | 457030933.80 | cop[tikv] | | isnull(imdb.movie_companies.note) |
| │ │ │ └─TableFullScan_223 | 4958296.00 | 442156045.80 | cop[tikv] | table:mc | keep order:false |
| │ │ └─TableReader_95(Probe) | 85421.86 | 13.44 | root | | data:Selection_94 |
| │ │ └─Selection_94 | 85421.86 | 144.27 | cop[tikv] | | ne(imdb.company_name.country_code, "[pl]"), or(like(imdb.company_name.name, "%Film%", 92), like(imdb.company_name.name, "%Warner%", 92)) |
| │ │ └─TableRangeScan_93 | 95357.47 | 141.27 | cop[tikv] | table:cn | range: decided by [imdb.movie_companies.company_id], keep order:false |
| │ └─IndexLookUp_81(Probe) | 762274.16 | 142.97 | root | | |
| │ ├─IndexRangeScan_79(Build) | 762274.16 | 455.65 | cop[tikv] | table:ml, index:movie_link_idx_mid(movie_id) | range: decided by [eq(imdb.movie_link.movie_id, imdb.movie_companies.movie_id)], keep order:false |
| │ └─TableRowIDScan_80(Probe) | 762274.16 | 563.57 | cop[tikv] | table:ml | keep order:false |
| └─TableReader_242(Probe) | 7480087.00 | 36527759.52 | root | | data:TableFullScan_241 |
| └─TableFullScan_241 | 7480087.00 | 426364959.00 | cop[tikv] | table:mk | keep order:false |
+---------------------------------------------------------+------------+--------------+-----------+------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
34 rows in set, 5 warnings (0.00 sec)

mysql> explain format='verbose' SELECT MIN(cn.name) AS from_company, MIN(lt.link) AS movie_link_type, MIN(t.title) AS sequel_movie FROM company_name AS cn, company_type AS ct, keyword AS k, link_type AS lt, movie_companies AS mc, movie_keyword AS mk, movie_link AS ml, title AS t WHERE cn.country_code !='[pl]' AND (cn.name LIKE '%Film%' OR cn.name LIKE '%Warner%') AND ct.kind ='production companies' AND k.keyword ='sequel' AND lt.link LIKE '%follows%' AND mc.note IS NULL AND t.production_year = 1998 and t.title like '%Money%' AND lt.id = ml.link_type_id AND ml.movie_id = t.id AND t.id = mk.movie_id AND mk.keyword_id = k.id AND t.id = mc.movie_id AND mc.company_type_id = ct.id AND mc.company_id = cn.id AND ml.movie_id = mk.movie_id AND ml.movie_id = mc.movie_id AND mk.movie_id = mc.movie_id;
+---------------------------------------------------------+------------+--------------+-----------+------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | estCost | task | access object | operator info |
+---------------------------------------------------------+------------+--------------+-----------+------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_35 | 1.00 | 102817001.40 | root | | funcs:min(imdb.company_name.name)->Column#39, funcs:min(imdb.link_type.link)->Column#40, funcs:min(imdb.title.title)->Column#41 |
| └─Projection_37 | 159.90 | 102816680.58 | root | | imdb.company_name.name, imdb.link_type.link, imdb.title.title |
| └─HashJoin_50 | 159.90 | 102816566.64 | root | | inner join, equal:[eq(imdb.movie_keyword.keyword_id, imdb.keyword.id)] |
| ├─IndexLookUp_249(Build) | 5.18 | 112.34 | root | | |
| │ ├─IndexRangeScan_246(Build) | 5.18 | 371.60 | cop[tikv] | table:k, index:keyword_idx_keyword(keyword) | range:["seque","seque"], keep order:false |
| │ └─Selection_248(Probe) | 5.18 | 369.65 | cop[tikv] | | eq(imdb.keyword.keyword, "sequel") |
| │ └─TableRowIDScan_247 | 5.18 | 354.10 | cop[tikv] | table:k | keep order:false |
| └─HashJoin_64(Probe) | 7228423.50 | 102816324.80 | root | | inner join, equal:[eq(imdb.movie_companies.movie_id, imdb.movie_keyword.movie_id) eq(imdb.movie_link.movie_id, imdb.movie_keyword.movie_id) eq(imdb.title.id, imdb.movie_keyword.movie_id)] |
| ├─HashJoin_77(Build) | 709629.66 | 59821894.58 | root | | inner join, equal:[eq(imdb.movie_link.link_type_id, imdb.link_type.id)] |
| │ ├─TableReader_240(Build) | 14.40 | 88.02 | root | | data:Selection_239 |
| │ │ └─Selection_239 | 14.40 | 945.00 | cop[tikv] | | like(imdb.link_type.link, "%follows%", 92) |
| │ │ └─TableFullScan_238 | 18.00 | 891.00 | cop[tikv] | table:lt | keep order:false |
| │ └─HashJoin_91(Probe) | 797273.11 | 59395967.55 | root | | inner join, equal:[eq(imdb.movie_companies.movie_id, imdb.movie_link.movie_id) eq(imdb.title.id, imdb.movie_link.movie_id)] |
| │ ├─IndexHashJoin_101(Build) | 99735.70 | 43667493.34 | root | | inner join, inner:TableReader_95, outer key:imdb.movie_companies.company_id, inner key:imdb.company_name.id, equal cond:eq(imdb.movie_companies.company_id, imdb.company_name.id) |
| │ │ ├─HashJoin_149(Build) | 99735.70 | 41157879.92 | root | | inner join, equal:[eq(imdb.movie_companies.movie_id, imdb.title.id)] |
| │ │ │ ├─IndexLookUp_232(Build) | 48336.80 | 1819172.08 | root | | |
| │ │ │ │ ├─IndexRangeScan_229(Build) | 60421.00 | 3399098.96 | cop[tikv] | table:t, index:title_idx_year(production_year) | range:[1998,1998], keep order:false |
| │ │ │ │ └─Selection_231(Probe) | 48336.80 | 10586665.52 | cop[tikv] | | like(imdb.title.title, "%Money%", 92) |
| │ │ │ │ └─TableRowIDScan_230 | 60421.00 | 10405402.52 | cop[tikv] | table:t | keep order:false |
| │ │ │ └─HashJoin_203(Probe) | 2449003.00 | 39133789.68 | root | | inner join, equal:[eq(imdb.company_type.id, imdb.movie_companies.company_type_id)] |
| │ │ │ ├─TableReader_218(Build) | 1.00 | 21.12 | root | | data:Selection_217 |
| │ │ │ │ └─Selection_217 | 1.00 | 264.00 | cop[tikv] | | eq(imdb.company_type.kind, "production companies") |
| │ │ │ │ └─TableFullScan_216 | 4.00 | 252.00 | cop[tikv] | table:ct | keep order:false |
| │ │ │ └─TableReader_225(Probe) | 2669657.00 | 37664345.75 | root | | data:Selection_224 |
| │ │ │ └─Selection_224 | 2669657.00 | 457030933.80 | cop[tikv] | | isnull(imdb.movie_companies.note) |
| │ │ │ └─TableFullScan_223 | 4958296.00 | 442156045.80 | cop[tikv] | table:mc | keep order:false |
| │ │ └─TableReader_95(Probe) | 89331.74 | 13.44 | root | | data:Selection_94 |
| │ │ └─Selection_94 | 89331.74 | 144.27 | cop[tikv] | | ne(imdb.company_name.country_code, "[pl]"), or(like(imdb.company_name.name, "%Film%", 92), like(imdb.company_name.name, "%Warner%", 92)) |
| │ │ └─TableRangeScan_93 | 99735.70 | 141.27 | cop[tikv] | table:cn | range: decided by [imdb.movie_companies.company_id], keep order:false |
| │ └─TableReader_237(Probe) | 2585150.00 | 14950785.50 | root | | data:TableFullScan_236 |
| │ └─TableFullScan_236 | 2585150.00 | 182253075.00 | cop[tikv] | table:ml | keep order:false |
| └─TableReader_242(Probe) | 7480087.00 | 36527759.52 | root | | data:TableFullScan_241 |
| └─TableFullScan_241 | 7480087.00 | 426364959.00 | cop[tikv] | table:mk | keep order:false |
+---------------------------------------------------------+------------+--------------+-----------+------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
33 rows in set, 6 warnings (0.02 sec)
```

[11b_2022-10-24-18:53:28_650YpEeEBF2H88Z88idG6Q==.zip](https://github.com/pingcap/tidb/files/9866447/11b_2022-10-24-18.53.28_650YpEeEBF2H88Z88idG6Q.zip)

[11b_2022-10-24-18:53:28_650YpEeEBF2H88Z88idG6Q==.zip](https://github.com/pingcap/tidb/files/9866462/11b_2022-10-24-18.53.28_650YpEeEBF2H88Z88idG6Q.zip)

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

### 3. What did you see instead (Required)

### 4. What is your TiDB version? (Required)

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.