pingcap / pingcap/tidb

planner generates bad plan for TPCH Q18 in MPP mode

Open
#38,512 2 comments 0 reactions 0 assignees View on GitHub
type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement
The plan is:
```sql

+------------------------------------------------------------+--------------+-----------+--------------+----------------+--------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| ID | ESTROWS | ACTROWS | TASK | ACCESS OBJECT | EXECUTION INFO | OPERATOR INFO | MEMORY | DISK |
+------------------------------------------------------------+--------------+-----------+--------------+----------------+--------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| Projection_25 | 100.00 | 100 | root | | time:10.2s, loops:2, Concurrency:OFF | test.customer.c_name, test.customer.c_custkey, test.orders.o_orderkey, test.orders.o_orderdate, test.orders.o_totalprice, Column#54 | 14.6 KB | N/A |
| └─TopN_29 | 100.00 | 100 | root | | time:10.2s, loops:2 | test.orders.o_totalprice:desc, test.orders.o_orderdate, offset:0, count:100 | 14.3 KB | N/A |
| └─TableReader_152 | 100.00 | 100 | root | | time:10.2s, loops:2, cop_task: {num: 1, max: 0s, proc_keys: 0, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_151 | N/A | N/A |
| └─ExchangeSender_151 | 100.00 | 100 | mpp[tiflash] | | tiflash_task:{time:10.2s, loops:1, threads:20} | ExchangeType: PassThrough | N/A | N/A |
| └─TopN_150 | 100.00 | 100 | mpp[tiflash] | | tiflash_task:{time:10.2s, loops:1, threads:1} | test.orders.o_totalprice:desc, test.orders.o_orderdate, offset:0, count:100 | N/A | N/A |
| └─Projection_146 | 60000000.00 | 366 | mpp[tiflash] | | tiflash_task:{time:10.2s, loops:1, threads:20} | Column#54, test.customer.c_custkey, test.customer.c_name, test.orders.o_orderkey, test.orders.o_totalprice, test.orders.o_orderdate | N/A | N/A |
| └─HashAgg_147 | 60000000.00 | 366 | mpp[tiflash] | | tiflash_task:{time:10.2s, loops:1, threads:20} | group by:test.customer.c_custkey, test.customer.c_name, test.orders.o_orderdate, test.orders.o_orderkey, test.orders.o_totalprice, funcs:sum(Column#77)->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 | N/A | N/A |
| └─ExchangeReceiver_149 | 60000000.00 | 366 | mpp[tiflash] | | tiflash_task:{time:10.2s, loops:20, threads:20} | | N/A | N/A |
| └─ExchangeSender_148 | 60000000.00 | 366 | mpp[tiflash] | | tiflash_task:{time:16.3s, loops:256, threads:20} | ExchangeType: HashPartition, Hash Cols: [name: test.customer.c_name, collate: N/A], [name: test.customer.c_custkey, collate: N/A], [name: test.orders.o_orderkey, collate: N/A], [name: test.orders.o_orderdate, collate: N/A], [name: test.orders.o_totalprice, collate: N/A] | N/A | N/A |
| └─HashAgg_144 | 60000000.00 | 366 | mpp[tiflash] | | tiflash_task:{time:16.3s, loops:256, threads:20} | 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#77 | N/A | N/A |
| └─Projection_133 | 243550009.54 | 2562 | mpp[tiflash] | | tiflash_task:{time:16.3s, loops:5036, threads:20} | test.customer.c_custkey, test.customer.c_name, test.orders.o_orderkey, test.orders.o_totalprice, test.orders.o_orderdate, test.lineitem.l_quantity | N/A | N/A |
| └─HashJoin_123 | 243550009.54 | 2562 | mpp[tiflash] | | tiflash_task:{time:16.3s, loops:5036, threads:20} | inner join, equal:[eq(test.orders.o_orderkey, test.lineitem.l_orderkey)] | N/A | N/A |
| ├─HashJoin_44(Build) | 60000000.00 | 366 | mpp[tiflash] | | tiflash_task:{time:15s, loops:1179, threads:20} | inner join, equal:[eq(test.orders.o_orderkey, test.lineitem.l_orderkey)] | N/A | N/A |
| │ ├─Selection_55(Build) | 59126579.20 | 366 | mpp[tiflash] | | tiflash_task:{time:6.16s, loops:191, threads:20} | gt(Column#52, 314) | N/A | N/A |
| │ │ └─Projection_64 | 73908224.00 | 75000000 | mpp[tiflash] | | tiflash_task:{time:6.03s, loops:256, threads:20} | Column#52, test.lineitem.l_orderkey | N/A | N/A |
| │ │ └─HashAgg_65 | 73908224.00 | 75000000 | mpp[tiflash] | | tiflash_task:{time:6.03s, loops:256, threads:20} | group by:test.lineitem.l_orderkey, funcs:sum(Column#55)->Column#52, funcs:firstrow(test.lineitem.l_orderkey)->test.lineitem.l_orderkey | N/A | N/A |
| │ │ └─ExchangeReceiver_67 | 73908224.00 | 75000000 | mpp[tiflash] | | tiflash_task:{time:3.86s, loops:256, threads:20} | | N/A | N/A |
| │ │ └─ExchangeSender_66 | 73908224.00 | 75000000 | mpp[tiflash] | | tiflash_task:{time:4.95s, loops:256, threads:20} | ExchangeType: HashPartition, Hash Cols: [name: test.lineitem.l_orderkey, collate: N/A] | N/A | N/A |
| │ │ └─HashAgg_58 | 73908224.00 | 75000000 | mpp[tiflash] | | tiflash_task:{time:4.65s, loops:256, threads:20} | group by:test.lineitem.l_orderkey, funcs:sum(test.lineitem.l_quantity)->Column#55 | N/A | N/A |
| │ │ └─TableFullScan_63 | 300005811.00 | 300005811 | mpp[tiflash] | table:lineitem | tiflash_task:{time:192.5ms, loops:5114, threads:20} | keep order:false | N/A | N/A |
| │ └─ExchangeReceiver_54(Probe) | 75000000.00 | 75000000 | mpp[tiflash] | | tiflash_task:{time:3.18s, loops:1179, threads:20} | | N/A | N/A |
| │ └─ExchangeSender_53 | 75000000.00 | 75000000 | mpp[tiflash] | | tiflash_task:{time:8.82s, loops:1179, threads:20} | ExchangeType: HashPartition, Hash Cols: [name: test.orders.o_orderkey, collate: N/A] | N/A | N/A |
| │ └─HashJoin_46 | 75000000.00 | 75000000 | mpp[tiflash] | | tiflash_task:{time:3.43s, loops:1179, threads:20} | inner join, equal:[eq(test.customer.c_custkey, test.orders.o_custkey)] | N/A | N/A |
| │ ├─ExchangeReceiver_49(Build) | 7500000.00 | 7500000 | mpp[tiflash] | | tiflash_task:{time:113.9ms, loops:117, threads:20} | | N/A | N/A |
| │ │ └─ExchangeSender_48 | 7500000.00 | 7500000 | mpp[tiflash] | | tiflash_task:{time:244.5ms, loops:117, threads:16} | ExchangeType: HashPartition, Hash Cols: [name: test.customer.c_custkey, collate: N/A] | N/A | N/A |
| │ │ └─TableFullScan_47 | 7500000.00 | 7500000 | mpp[tiflash] | table:customer | tiflash_task:{time:64.5ms, loops:117, threads:16} | keep order:false | N/A | N/A |
| │ └─ExchangeReceiver_52(Probe) | 75000000.00 | 75000000 | mpp[tiflash] | | tiflash_task:{time:147.9ms, loops:1179, threads:20} | | N/A | N/A |
| │ └─ExchangeSender_51 | 75000000.00 | 75000000 | mpp[tiflash] | | tiflash_task:{time:8.47s, loops:1179, threads:20} | ExchangeType: HashPartition, Hash Cols: [name: test.orders.o_custkey, collate: N/A] | N/A | N/A |
| │ └─TableFullScan_50 | 75000000.00 | 75000000 | mpp[tiflash] | table:orders | tiflash_task:{time:84.1ms, loops:1179, threads:20} | keep order:false | N/A | N/A |
| └─ExchangeReceiver_77(Probe) | 300005811.00 | 300005811 | mpp[tiflash] | | tiflash_task:{time:736.3ms, loops:5036, threads:20} | | N/A | N/A |
| └─ExchangeSender_76 | 300005811.00 | 300005811 | mpp[tiflash] | | tiflash_task:{time:10.2s, loops:5114, threads:20} | ExchangeType: HashPartition, Hash Cols: [name: test.lineitem.l_orderkey, collate: N/A] | N/A | N/A |
| └─TableFullScan_75 | 300005811.00 | 300005811 | mpp[tiflash] | table:lineitem | tiflash_task:{time:124ms, loops:5114, threads:20} | keep order:false | N/A | N/A |
+------------------------------------------------------------+--------------+-----------+--------------+----------------+--------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+

```

As shown above, cardinality of `Selection_55` missed so much with the real rowcount, planner generates plan which shuffle both sides of join, then causes another shuffle for `TableFullScan_75`.

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.