Improve the performance when late materialization is enable but filter can not help filter out packs
Open
@Lloyd-Pottiger is already working on this.
Since Aug 8, 2023.
component/storage
type/enhancement
type/performance
- Dominant language
- C++
- Stars
- 1k
- Forks
- 423
- Avg merge
- 1d 15h
- Merged PRs (30d)
- 24
Description
Enhancement
tpch50, q6
mysql> set @@tidb_opt_enable_late_materialization=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> explain analyze select sum(l_extendedprice * l_discount) as revenue from lineitem where l_shipdate >= '1994-01-01' and l_shipdate < date_add('1994-01-01', interval '1' year) and l_discount between 0.06 - 0.01 and
0.06 + 0.01 and l_quantity < 24;
+----------------------------------+--------------+-----------+--------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------+--------------+-----------+--------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| HashAgg_27 | 1.00 | 1 | root | | time:2.95s, loops:2, RU:0.000000, partial_worker:{wall_time:2.951720179s, concurrency:5, task_num:1, tot_wait:14.75837484s, tot_exec:4.169µs, tot_time:14.758389486s, max:2.951693329s, p95:2.951693329s}, final_worker:{wall_time:0s, concurrency:5, task_num:1, tot_wait:14.758555377s, tot_exec:12.647µs, tot_time:14.75857039s, max:2.951726214s, p95:2.951726214s} | funcs:sum(Column#20)->Column#18 | 20.2 KB | N/A |
| └─TableReader_29 | 1.00 | 1 | root | | time:2.95s, loops:2, cop_task: {num: 2, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 2, data:ExchangeSender_28 | 414 Bytes | N/A |
| └─ExchangeSender_28 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{time:2.95s, loops:1, threads:4} | ExchangeType: PassThrough | N/A | N/A |
| └─HashAgg_10 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{time:2.95s, loops:1, threads:4} | funcs:sum(Column#23)->Column#20 | N/A | N/A |
| └─Projection_46 | 5833741.99 | 5707314 | mpp[tiflash] | | tiflash_task:{time:2.93s, loops:5113, threads:4} | mul(test.lineitem.l_extendedprice, test.lineitem.l_discount)->Column#23 | N/A | N/A |
| └─Selection_26 | 5833741.99 | 5707314 | mpp[tiflash] | | tiflash_task:{time:2.9s, loops:5113, threads:4} | ge(test.lineitem.l_discount, 0.05), ge(test.lineitem.l_shipdate, 1994-01-01 00:00:00.000000), le(test.lineitem.l_discount, 0.07), lt(test.lineitem.l_quantity, 24), lt(test.lineitem.l_shipdate, 1995-01-01 00:00:00.000000) | N/A | N/A |
| └─TableFullScan_25 | 300005811.00 | 300005811 | mpp[tiflash] | table:lineitem | tiflash_task:{time:517.3ms, loops:5113, threads:4}, tiflash_scan:{dtfile:{total_scanned_packs:36931, total_skipped_packs:0, total_scanned_rows:300005811, total_skipped_rows:0, total_rs_index_load_time: 7ms, total_read_time: 10252ms}, total_create_snapshot_time: 1ms, total_local_region_num: 570, total_remote_region_num: 0} | keep order:false | N/A | N/A |
+----------------------------------+--------------+-----------+--------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
7 rows in set (2.96 sec)
mysql> set @@tidb_opt_enable_late_materialization=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> explain analyze select sum(l_extendedprice * l_discount) as revenue from lineitem where l_shipdate >= '1994-01-01' and l_shipdate < date_add('1994-01-01', interval '1' year) and l_discount between 0.06 - 0.01 and
0.06 + 0.01 and l_quantity < 24;
+----------------------------------+-------------+----------+--------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------+-------------+----------+--------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| HashAgg_27 | 1.00 | 1 | root | | time:3.44s, loops:2, RU:0.000000, partial_worker:{wall_time:3.443967983s, concurrency:5, task_num:1, tot_wait:17.219604154s, tot_exec:3.714µs, tot_time:17.219618574s, max:3.443927144s, p95:3.443927144s}, final_worker:{wall_time:3.443994178s, concurrency:5, task_num:1, tot_wait:17.219830145s, tot_exec:17.848µs, tot_time:17.219850373s, max:3.443977064s, p95:3.443977064s} | funcs:sum(Column#20)->Column#18 | 20.2 KB | N/A |
| └─TableReader_29 | 1.00 | 1 | root | | time:3.44s, loops:2, cop_task: {num: 2, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 2, data:ExchangeSender_28 | 413 Bytes | N/A |
| └─ExchangeSender_28 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{time:3.44s, loops:1, threads:4} | ExchangeType: PassThrough | N/A | N/A |
| └─HashAgg_10 | 1.00 | 1 | mpp[tiflash] | | tiflash_task:{time:3.44s, loops:1, threads:4} | funcs:sum(Column#23)->Column#20 | N/A | N/A |
| └─Projection_46 | 5833741.99 | 5707314 | mpp[tiflash] | | tiflash_task:{time:3.42s, loops:5113, threads:4} | mul(test.lineitem.l_extendedprice, test.lineitem.l_discount)->Column#23 | N/A | N/A |
| └─Selection_26 | 5833741.99 | 5707314 | mpp[tiflash] | | tiflash_task:{time:3.39s, loops:5113, threads:4} | ge(test.lineitem.l_discount, 0.05), le(test.lineitem.l_discount, 0.07), lt(test.lineitem.l_quantity, 24) | N/A | N/A |
| └─TableFullScan_25 | 46321747.11 | 45517940 | mpp[tiflash] | table:lineitem | tiflash_task:{time:2.97s, loops:5113, threads:4}, tiflash_scan:{dtfile:{total_scanned_packs:73862, total_skipped_packs:0, total_scanned_rows:600011622, total_skipped_rows:0, total_rs_index_load_time: 7ms, total_read_time: 8161ms}, total_create_snapshot_time: 1ms, total_local_region_num: 570, total_remote_region_num: 0} | pushed down filter:ge(test.lineitem.l_shipdate, 1994-01-01 00:00:00.000000), lt(test.lineitem.l_shipdate, 1995-01-01 00:00:00.000000), keep order:false | N/A | N/A |
+----------------------------------+-------------+----------+--------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
7 rows in set (3.44 sec)
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Assessment
This issue has not been assessed yet.