search

pingcap / tidb

TiDB - the open-source, cloud-native, distributed SQL database designed for modern applications.
https://pingcap.com
Apache License 2.0
37.37k stars 5.86k forks source link

TPCH Q18 is slower in v4.0 than v3.0 #16123

Closed SunRunAway closed 5 months ago

SunRunAway commented 4 years ago

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

Workload is tpch 10g It seems that IndexJoin_38 is better than HashRightJoin_48.

MySQL [test]> select tidb_version();
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                       |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.8
Git Commit Hash: 8f13cf1449bd8903ff465a4f12ed89ecbac858a4
Git Branch: HEAD
UTC Build Time: 2019-12-31 11:14:59
GoVersion: go version go1.13 linux/amd64
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [test]> explain analyze select  c_name,  c_custkey,  o_orderkey,  o_orderdate,  o_totalprice,  sum(l_quantity) from  customer,  orders,  lineitem where  o_orderkey in (   select    l_orderkey   from    lineitem   group by    l_orderkey having     sum(l_quantity) > 314  )  and c_custkey = o_custkey  and o_orderkey = l_orderkey group by  c_name,  c_custkey,  o_orderkey,  o_orderdate,  o_totalprice order by  o_totalprice desc,  o_orderdate limit 100;
+--------------------------------------+-------------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------+-----------------------+
| id                                   | count       | task | operator info                                                                                                                                                                                                                                                                                                                                      | execution info                                                                        | memory                |
+--------------------------------------+-------------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------+-----------------------+
| Projection_24                        | 100.00      | root | test.customer.c_name, test.customer.c_custkey, test.orders.o_orderkey, test.orders.o_orderdate, test.orders.o_totalprice, 14_col_0                                                                                                                                                                                                                 | time:27.181922782s, loops:2, rows:84                                                  | N/A                   |
| └─TopN_27                        | 100.00      | root | test.orders.o_totalprice:desc, test.orders.o_orderdate:asc, offset:0, count:100                                                                                                                                                                                                                                                                    | time:27.181914675s, loops:2, rows:84                                                  | 20.328125 KB          |
|   └─HashAgg_33                   | 11926746.63 | root | group by:test.customer.c_custkey, test.customer.c_name, test.orders.o_orderdate, test.orders.o_orderkey, test.orders.o_totalprice, funcs:sum(test.lineitem.l_quantity), firstrow(test.customer.c_custkey), firstrow(test.customer.c_name), firstrow(test.orders.o_orderkey), firstrow(test.orders.o_totalprice), firstrow(test.orders.o_orderdate) | time:27.181825649s, loops:6, rows:84                                                  | N/A                   |
|     └─IndexJoin_38               | 48081876.00 | root | inner join, inner:IndexLookUp_37, outer key:test.orders.o_orderkey, inner key:test.lineitem.l_orderkey                                                                                                                                                                                                                                             | time:27.181153781s, loops:2, rows:588                                                 | 148.234375 KB         |
|       ├─HashLeftJoin_41          | 11926746.63 | root | inner join, inner:Selection_52, equal:[eq(test.orders.o_orderkey, test.lineitem.l_orderkey)]                                                                                                                                                                                                                                                       | time:27.167465693s, loops:7, rows:84                                                  | 48.515625 KB          |
|       │ ├─HashRightJoin_47     | 15164864.00 | root | inner join, inner:TableReader_51, equal:[eq(test.customer.c_custkey, test.orders.o_custkey)]                                                                                                                                                                                                                                                       | time:7.602659928s, loops:14650, rows:15000000                                         | 43.6073112487793 MB   |
|       │ │ ├─TableReader_51   | 1500000.00  | root | data:TableScan_50                                                                                                                                                                                                                                                                                                                                  | time:697.939395ms, loops:1466, rows:1500000                                           | 32.36765193939209 MB  |
|       │ │ │ └─TableScan_50 | 1500000.00  | cop  | table:customer, range:[-inf,+inf], keep order:false                                                                                                                                                                                                                                                                                                | proc max:620ms, min:264ms, p80:620ms, p95:620ms, rows:1500000, iters:1482, tasks:4    | N/A                   |
|       │ │ └─TableReader_49   | 15164864.00 | root | data:TableScan_48                                                                                                                                                                                                                                                                                                                                  | time:5.884732219s, loops:14650, rows:15000000                                         | 476.41050148010254 MB |
|       │ │   └─TableScan_48   | 15164864.00 | cop  | table:orders, range:[-inf,+inf], keep order:false                                                                                                                                                                                                                                                                                                  | proc max:960ms, min:312ms, p80:920ms, p95:932ms, rows:15000000, iters:14788, tasks:31 | N/A                   |
|       │ └─Selection_52         | 11926746.63 | root | gt(sel_agg_2, 314)                                                                                                                                                                                                                                                                                                                                 | time:20.578030314s, loops:2, rows:84                                                  | N/A                   |
|       │   └─HashAgg_59         | 14908433.29 | root | group by:col_2, funcs:sum(col_0), firstrow(col_1)                                                                                                                                                                                                                                                                                                  | time:18.798667477s, loops:14652, rows:15000000                                        | N/A                   |
|       │     └─TableReader_60   | 14908433.29 | root | data:HashAgg_53                                                                                                                                                                                                                                                                                                                                    | time:6.706352859s, loops:14650, rows:15000117                                         | 31.047134399414062 MB |
|       │       └─HashAgg_53     | 14908433.29 | cop  | group by:test.lineitem.l_orderkey, funcs:sum(test.lineitem.l_quantity), firstrow(test.lineitem.l_orderkey)                                                                                                                                                                                                                                         | proc max:948ms, min:24ms, p80:836ms, p95:872ms, rows:15000117, iters:58619, tasks:153 | N/A                   |
|       │         └─TableScan_58 | 60102345.00 | cop  | table:lineitem, range:[-inf,+inf], keep order:false                                                                                                                                                                                                                                                                                                | proc max:780ms, min:16ms, p80:696ms, p95:732ms, rows:59986052, iters:58619, tasks:153 | N/A                   |
|       └─IndexLookUp_37           | 1.00        | root |                                                                                                                                                                                                                                                                                                                                                    | time:21.142408ms, loops:4, rows:588                                                   | 16.90625 KB           |
|         ├─IndexScan_35           | 1.00        | cop  | table:lineitem, index:L_ORDERKEY, L_LINENUMBER, range: decided by [eq(test.lineitem.l_orderkey, test.orders.o_orderkey)], keep order:false                                                                                                                                                                                                         | proc max:0s, min:0s, p80:0s, p95:0s, rows:588, iters:59, tasks:59                     | N/A                   |
|         └─TableScan_36           | 1.00        | cop  | table:lineitem, keep order:false                                                                                                                                                                                                                                                                                                                   | proc max:4ms, min:0s, p80:0s, p95:0s, rows:588, iters:73, tasks:73                    | N/A                   |
+--------------------------------------+-------------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------+-----------------------+
18 rows in set (27.19 sec)
MySQL [test]> select tidb_version();
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.0-beta.2-183-g57db6cec7
Git Commit Hash: 57db6cec7ffad78e74b6ac6c67a2bfe9a6718d17
Git Branch: master
UTC Build Time: 2020-04-04 08:06:18
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [test]> explain analyze select  c_name,  c_custkey,  o_orderkey,  o_orderdate,  o_totalprice,  sum(l_quantity) from  customer,  orders,  lineitem where  o_orderkey in (   select    l_orderkey   from    lineitem   group by    l_orderkey having     sum(l_quantity) > 314  )  and c_custkey = o_custkey  and o_orderkey = l_orderkey group by  c_name,  c_custkey,  o_orderkey,  o_orderdate,  o_totalprice order by  o_totalprice desc,  o_orderdate limit 100;
+--------------------------------------------+-------------+----------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+
| id                                         | estRows     | actRows  | task      | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | execution info                                                                                                                                                                             | memory               | disk    |
+--------------------------------------------+-------------+----------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+
| Projection_24                              | 100.00      | 84       | root      | test.customer.c_name, test.customer.c_custkey, test.orders.o_orderkey, test.orders.o_orderdate, test.orders.o_totalprice, Column#54                                                                                                                                                                                                                                                                                                                                                                                   | time:30.563894025s, loops:2, rows:84, Concurrency:OFF                                                                                                                                      | 14.3671875 KB        | N/A     |
| └─TopN_27                              | 100.00      | 84       | root      | test.orders.o_totalprice:desc, test.orders.o_orderdate:asc, offset:0, count:100                                                                                                                                                                                                                                                                                                                                                                                                                                       | time:30.563870353s, loops:2, rows:84                                                                                                                                                       | 19.109375 KB         | N/A     |
|   └─HashAgg_33                         | 11901337.60 | 84       | root      | group by:test.customer.c_custkey, test.customer.c_name, test.orders.o_orderdate, test.orders.o_orderkey, test.orders.o_totalprice, funcs:sum(test.lineitem.l_quantity)->Column#54, funcs:firstrow(test.customer.c_custkey)->test.customer.c_custkey, funcs:firstrow(test.customer.c_name)->test.customer.c_name, funcs:firstrow(test.orders.o_orderkey)->test.orders.o_orderkey, funcs:firstrow(test.orders.o_totalprice)->test.orders.o_totalprice, funcs:firstrow(test.orders.o_orderdate)->test.orders.o_orderdate | time:30.563692104s, loops:6, rows:84, PartialConcurrency:4, FinalConcurrency:4                                                                                                             | 105.33203125 KB      | N/A     |
|     └─HashRightJoin_48                 | 47988841.60 | 588      | root      | inner join, equal:[eq(test.orders.o_orderkey, test.lineitem.l_orderkey)]                                                                                                                                                                                                                                                                                                                                                                                                                                              | time:30.562864009s, loops:6, rows:588, Concurrency:5, probe collision:0, build:36.196µs                                                                                                   | 15.84375 KB          | 0 Bytes |
|       ├─HashLeftJoin_72(Build)         | 11901337.60 | 84       | root      | inner join, equal:[eq(test.orders.o_orderkey, test.lineitem.l_orderkey)]                                                                                                                                                                                                                                                                                                                                                                                                                                              | time:26.125712078s, loops:6, rows:84, Concurrency:5, probe collision:0, build:118.348µs                                                                                                   | 48.5 KB              | 5.25 KB |
|       │ ├─Selection_89(Build)        | 11901337.60 | 84       | root      | gt(Column#52, 314)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | time:22.443239865s, loops:2, rows:84                                                                                                                                                       | 49.75 KB             | N/A     |
|       │ │ └─HashAgg_96             | 14876672.00 | 15000000 | root      | group by:test.lineitem.l_orderkey, funcs:sum(Column#66)->Column#52, funcs:firstrow(test.lineitem.l_orderkey)->test.lineitem.l_orderkey                                                                                                                                                                                                                                                                                                                                                                                | time:21.732552822s, loops:14652, rows:15000000, PartialConcurrency:4, FinalConcurrency:4                                                                                                   | 36.57133483886719 MB | N/A     |
|       │ │   └─TableReader_97       | 14876672.00 | 15000116 | root      | data:HashAgg_90                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | time:6.601359835s, loops:154, rows:15000116, rpc num: 153, rpc max:2.252442133s, min:25.346102ms, avg:715.503813ms, p80:959.92463ms, p95:1.455997806s, proc keys max:397624, p95:396089    | 58.93570327758789 MB | N/A     |
|       │ │     └─HashAgg_90         | 14876672.00 | 15000116 | cop[tikv] | group by:test.lineitem.l_orderkey, funcs:sum(test.lineitem.l_quantity)->Column#66                                                                                                                                                                                                                                                                                                                                                                                                                                     | proc max:1.008s, min:20ms, p80:612ms, p95:824ms, rows:15000116, iters:58619, tasks:153                                                                                                     | N/A                  | N/A     |
|       │ │       └─TableFullScan_95 | 59986052.00 | 59986052 | cop[tikv] | table:lineitem, keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | proc max:844ms, min:12ms, p80:488ms, p95:688ms, rows:59986052, iters:58619, tasks:153                                                                                                      | N/A                  | N/A     |
|       │ └─HashRightJoin_84(Probe)    | 15000000.00 | 15000000 | root      | inner join, equal:[eq(test.customer.c_custkey, test.orders.o_custkey)]                                                                                                                                                                                                                                                                                                                                                                                                                                                | time:4.674407345s, loops:14652, rows:15000000, Concurrency:5, probe collision:0, build:398.07768ms                                                                                         | 43.61027526855469 MB | 0 Bytes |
|       │   ├─TableReader_88(Build)    | 1500000.00  | 1500000  | root      | data:TableFullScan_87                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | time:622.921448ms, loops:1467, rows:1500000, rpc num: 4, rpc max:777.661384ms, min:577.8898ms, avg:688.453703ms, p80:777.661384ms, p95:777.661384ms, proc keys max:390019, p95:390019      | 37.97313404083252 MB | N/A     |
|       │   │ └─TableFullScan_87     | 1500000.00  | 1500000  | cop[tikv] | table:customer, keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | proc max:616ms, min:504ms, p80:616ms, p95:616ms, rows:1500000, iters:1482, tasks:4                                                                                                         | N/A                  | N/A     |
|       │   └─TableReader_86(Probe)    | 15000000.00 | 15000000 | root      | data:TableFullScan_85                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | time:736.897099ms, loops:14665, rows:15000000, rpc num: 31, rpc max:2.547312036s, min:694.187141ms, avg:1.412478892s, p80:1.875272501s, p95:2.397471184s, proc keys max:490114, p95:490080 | 916.0972852706909 MB | N/A     |
|       │     └─TableFullScan_85       | 15000000.00 | 15000000 | cop[tikv] | table:orders, keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | proc max:1.036s, min:436ms, p80:872ms, p95:936ms, rows:15000000, iters:14788, tasks:31                                                                                                     | N/A                  | N/A     |
|       └─TableReader_101(Probe)         | 59986052.00 | 59986052 | root      | data:TableFullScan_100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | time:2.848675226s, loops:58620, rows:59986052, rpc num: 153, rpc max:1.961604503s, min:20.864551ms, avg:636.088576ms, p80:650.653629ms, p95:1.43948859s, proc keys max:397624, p95:396089  | 537.4070634841919 MB | N/A     |
|         └─TableFullScan_100            | 59986052.00 | 59986052 | cop[tikv] | table:lineitem, keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | proc max:828ms, min:16ms, p80:452ms, p95:660ms, rows:59986052, iters:59231, tasks:153                                                                                                      | N/A                  | N/A     |
+--------------------------------------------+-------------+----------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+
17 rows in set (30.57 sec)

2. What did you expect to see?

3. What did you see instead?

4. What version of TiDB are you using? (tidb-server -V or run select tidb_version(); on TiDB)

SunRunAway commented 4 years ago

@eurekaka PTAL

zz-jason commented 4 years ago 
|       │ ├─Selection_89(Build)        | 11901337.60 | 84       | root      | gt(Column#52, 314)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | time:22.443239865s, loops:2, rows:84                                                                                                                                                       | 49.75 KB             | N/A     |

There is a huge estimation error on Selection_89

zz-jason commented 4 years ago

@XuHuaiyu Could you help us to investigate the root cause of this plan regression?

jebter commented 5 months ago

The issue has not been updated for too long, so I will close it. If there are any updates, you can reopen it.