Projection of column pruning may include useless columns
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
See TPC-H 100 Q9 plan
```sql
mysql> set tidb_prefer_broadcast_join_by_exchange_data_size = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> set tidb_default_string_match_selectivity = 0.1;
Query OK, 0 rows affected (0.00 sec)
mysql> set tidb_opt_join_reorder_threshold = 20;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select nation, o_year, sum(amount) as sum_profit from ( select n_name as nation, extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount from part, supplier, lineitem, partsupp, orders, nation where s_suppkey = l_suppkey and ps_suppkey = l_suppkey and ps_partkey = l_partkey and p_partkey = l_partkey and o_orderkey = l_orderkey and s_nationkey = n_nationkey and p_name like '%dim%' ) as profit group by nation, o_year order by nation, o_year desc;
+-----------------------------------------------------------------------------------+--------------+--------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-----------------------------------------------------------------------------------+--------------+--------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Sort_79 | 2406.00 | root | | tpch100.nation.n_name, Column#51:desc |
| └─TableReader_275 | 2406.00 | root | | MppVersion: 2, data:ExchangeSender_274 |
| └─ExchangeSender_274 | 2406.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─Projection_82 | 2406.00 | mpp[tiflash] | | tpch100.nation.n_name, Column#51, Column#53 |
| └─Projection_270 | 2406.00 | mpp[tiflash] | | Column#53, tpch100.nation.n_name, Column#51 |
| └─HashAgg_271 | 2406.00 | mpp[tiflash] | | group by:Column#64, tpch100.nation.n_name, funcs:sum(Column#65)->Column#53, funcs:firstrow(tpch100.nation.n_name)->tpch100.nation.n_name, funcs:firstrow(Column#64)->Column#51, stream_count: 36 |
| └─ExchangeReceiver_273 | 2406.00 | mpp[tiflash] | | stream_count: 36 |
| └─ExchangeSender_272 | 2406.00 | mpp[tiflash] | | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.nation.n_name, collate: utf8mb4_bin], stream_count: 36 |
| └─HashAgg_268 | 2406.00 | mpp[tiflash] | | group by:Column#69, Column#70, funcs:sum(Column#68)->Column#65 |
| └─Projection_278 | 59788376.13 | mpp[tiflash] | | minus(mul(tpch100.lineitem.l_extendedprice, minus(1, tpch100.lineitem.l_discount)), mul(tpch100.partsupp.ps_supplycost, tpch100.lineitem.l_quantity))->Column#68, tpch100.nation.n_name->Column#69, extract(YEAR, tpch100.orders.o_orderdate)->Column#70 |
| └─Projection_257 | 59788376.13 | mpp[tiflash] | | tpch100.lineitem.l_quantity, tpch100.lineitem.l_extendedprice, tpch100.lineitem.l_discount, tpch100.partsupp.ps_supplycost, tpch100.orders.o_orderdate, tpch100.nation.n_name |
| └─Projection_247 | 59788376.13 | mpp[tiflash] | | tpch100.lineitem.l_quantity, tpch100.lineitem.l_extendedprice, tpch100.lineitem.l_discount, tpch100.partsupp.ps_supplycost, tpch100.orders.o_orderdate, tpch100.nation.n_name, tpch100.orders.o_orderkey |
| └─HashJoin_246 | 59788376.13 | mpp[tiflash] | | inner join, equal:[eq(tpch100.lineitem.l_suppkey, tpch100.supplier.s_suppkey)] |
| ├─ExchangeReceiver_122(Build) | 1000000.00 | mpp[tiflash] | | |
| │ └─ExchangeSender_121 | 1000000.00 | mpp[tiflash] | | ExchangeType: Broadcast, Compression: FAST |
| │ └─Projection_120 | 1000000.00 | mpp[tiflash] | | tpch100.supplier.s_suppkey, tpch100.nation.n_name |
| │ └─HashJoin_115 | 1000000.00 | mpp[tiflash] | | inner join, equal:[eq(tpch100.supplier.s_nationkey, tpch100.nation.n_nationkey)] |
| │ ├─ExchangeReceiver_119(Build) | 25.00 | mpp[tiflash] | | |
| │ │ └─ExchangeSender_118 | 25.00 | mpp[tiflash] | | ExchangeType: Broadcast, Compression: FAST |
| │ │ └─TableFullScan_117 | 25.00 | mpp[tiflash] | table:nation | keep order:false |
| │ └─TableFullScan_116(Probe) | 1000000.00 | mpp[tiflash] | table:supplier | keep order:false |
| └─Projection_114(Probe) | 59708020.55 | mpp[tiflash] | | tpch100.lineitem.l_suppkey, tpch100.lineitem.l_quantity, tpch100.lineitem.l_extendedprice, tpch100.lineitem.l_discount, tpch100.partsupp.ps_supplycost, tpch100.orders.o_orderdate, tpch100.orders.o_orderkey |
| └─HashJoin_94 | 59708020.55 | mpp[tiflash] | | inner join, equal:[eq(tpch100.lineitem.l_orderkey, tpch100.orders.o_orderkey)], stream_count: 36 |
| ├─ExchangeReceiver_110(Build) | 59708020.55 | mpp[tiflash] | | stream_count: 36 |
| │ └─ExchangeSender_109 | 59708020.55 | mpp[tiflash] | | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.lineitem.l_orderkey, collate: binary], stream_count: 36 |
| │ └─Projection_108 | 59708020.55 | mpp[tiflash] | | tpch100.lineitem.l_orderkey, tpch100.lineitem.l_suppkey, tpch100.lineitem.l_quantity, tpch100.lineitem.l_extendedprice, tpch100.lineitem.l_discount, tpch100.partsupp.ps_supplycost, tpch100.partsupp.ps_suppkey, tpch100.partsupp.ps_partkey |
| │ └─HashJoin_95 | 59708020.55 | mpp[tiflash] | | inner join, equal:[eq(tpch100.lineitem.l_suppkey, tpch100.partsupp.ps_suppkey) eq(tpch100.lineitem.l_partkey, tpch100.partsupp.ps_partkey)], stream_count: 36 |
| │ ├─ExchangeReceiver_104(Build) | 59708020.55 | mpp[tiflash] | | stream_count: 36 |
| │ │ └─ExchangeSender_103 | 59708020.55 | mpp[tiflash] | | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.lineitem.l_suppkey, collate: binary], [name: tpch100.lineitem.l_partkey, collate: binary], stream_count: 36 |
| │ │ └─Projection_102 | 59708020.55 | mpp[tiflash] | | tpch100.lineitem.l_orderkey, tpch100.lineitem.l_partkey, tpch100.lineitem.l_suppkey, tpch100.lineitem.l_quantity, tpch100.lineitem.l_extendedprice, tpch100.lineitem.l_discount |
| │ │ └─HashJoin_96 | 59708020.55 | mpp[tiflash] | | inner join, equal:[eq(tpch100.part.p_partkey, tpch100.lineitem.l_partkey)] |
| │ │ ├─ExchangeReceiver_100(Build) | 2000000.00 | mpp[tiflash] | | |
| │ │ │ └─ExchangeSender_99 | 2000000.00 | mpp[tiflash] | | ExchangeType: Broadcast, Compression: FAST |
| │ │ │ └─TableFullScan_97 | 2000000.00 | mpp[tiflash] | table:part | pushed down filter:like(tpch100.part.p_name, "%dim%", 92), keep order:false, stats:partial[p_partkey:allEvicted] |
| │ │ └─TableFullScan_101(Probe) | 600037902.00 | mpp[tiflash] | table:lineitem | keep order:false |
| │ └─ExchangeReceiver_107(Probe) | 80000000.00 | mpp[tiflash] | | |
| │ └─ExchangeSender_106 | 80000000.00 | mpp[tiflash] | | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.partsupp.ps_suppkey, collate: binary], [name: tpch100.partsupp.ps_partkey, collate: binary] |
| │ └─TableFullScan_105 | 80000000.00 | mpp[tiflash] | table:partsupp | keep order:false |
| └─ExchangeReceiver_113(Probe) | 150000000.00 | mpp[tiflash] | | |
| └─ExchangeSender_112 | 150000000.00 | mpp[tiflash] | | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.orders.o_orderkey, collate: binary] |
| └─TableFullScan_111 | 150000000.00 | mpp[tiflash] | table:orders | keep order:false |
+-----------------------------------------------------------------------------------+--------------+--------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
41 rows in set (0.03 sec)
```
In `Projection_108: tpch100.lineitem.l_orderkey, tpch100.lineitem.l_suppkey, tpch100.lineitem.l_quantity, tpch100.lineitem.l_extendedprice, tpch100.lineitem.l_discount, tpch100.partsupp.ps_supplycost, tpch100.partsupp.ps_suppkey, tpch100.partsupp.ps_partkey`, the `ps_suppkey` and `ps_partkey` are not needed for further usage. These useless column data will be used in the exchange, resulting in performance degradation.
Also, in `Projection_114: tpch100.lineitem.l_suppkey, tpch100.lineitem.l_quantity, tpch100.lineitem.l_extendedprice, tpch100.lineitem.l_discount, tpch100.partsupp.ps_supplycost, tpch100.orders.o_orderdate, tpch100.orders.o_orderkey`, `o_orderkey` is not needed.
Contributor guide
Assessment
This issue has not been assessed yet.