Query time is different with and without `explain analyze` due to later materialization
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
My cluster version is v7.5.0.
The time of running TPC-H Q19 is different with and without `explain analyze`.
The root cause is that later materialization is only enabled with `explain analyze` for TPC-H Q19.
When running Q19 directly, the `in(tpch100.lineitem.l_shipmode, "AIR", "AIR REG")` does not pushed down to table scan, leading to 2.6s on my server.
```sql
| id | estRows | estCost | actRows | task | access object | execution info | operator info | memory | disk |
| HashAgg_53 | 1.00 | 1532.24 | 1 | root | | time:2.6s, loops:2, partial_worker:{wall_time:2.599430336s, concurrency:5, task_num:1, tot_wait:12.996947043s, tot_exec:5.585µs, tot_time:12.996961233s, max:2.599399998s, p95:2.599399998s}, final_worker:{wall_time:2.599449271s, concurrency:5, task_num:1, tot_wait:12.997120537s, tot_exec:14.852µs, tot_time:12.997137655s, max:2.599435111s, p95:2.599435111s} | funcs:sum(Column#27)->Column#26 | 20.2 KB | N/A |
| └─TableReader_55 | 1.00 | 3.70 | 2 | root | | time:2.6s, loops:2, cop_task: {num: 4, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 2, data:ExchangeSender_54 | 1.42 KB | N/A |
| └─ExchangeSender_54 | 1.00 | 55481507599.51 | 2 | mpp[tiflash] | | tiflash_task:{proc max:2.6s, min:2.6s, avg: 2.6s, p80:2.6s, p95:2.6s, iters:2, tasks:2, threads:2} | ExchangeType: PassThrough | N/A | N/A |
| └─HashAgg_13 | 1.00 | 55481507599.51 | 2 | mpp[tiflash] | | tiflash_task:{proc max:2.6s, min:2.59s, avg: 2.6s, p80:2.6s, p95:2.6s, iters:2, tasks:2, threads:2} | funcs:sum(Column#29)->Column#27 | N/A | N/A |
| └─Projection_76 | 1426176.87 | 55494371641.97 | 11341 | mpp[tiflash] | | tiflash_task:{proc max:2.6s, min:2.59s, avg: 2.59s, p80:2.6s, p95:2.6s, iters:9738, tasks:2, threads:72} | mul(tpch100.lineitem.l_extendedprice, minus(1, tpch100.lineitem.l_discount))->Column#29 | N/A | N/A |
| └─HashJoin_52 | 1426176.87 | 55480138396.84 | 11341 | mpp[tiflash] | | tiflash_task:{proc max:2.59s, min:2.59s, avg: 2.59s, p80:2.59s, p95:2.59s, iters:9738, tasks:2, threads:72} | inner join, equal:[eq(tpch100.lineitem.l_partkey, tpch100.part.p_partkey)], other cond:or(and(and(and(eq(tpch100.part.p_brand, "Brand#52"), in(tpch100.part.p_container, "SM CASE", "SM BOX", "SM PACK", "SM PKG")), and(ge(tpch100.lineitem.l_quantity, 4), le(tpch100.lineitem.l_quantity, 14))), and(and(ge(tpch100.part.p_size, 1), le(tpch100.part.p_size, 5)), and(in(tpch100.lineitem.l_shipmode, "AIR", "AIR REG"), eq(tpch100.lineitem.l_shipinstruct, "DELIVER IN PERSON")))), or(and(and(and(eq(tpch100.part.p_brand, "Brand#11"), in(tpch100.part.p_container, "MED BAG", "MED BOX", "MED PKG", "MED PACK")), and(ge(tpch100.lineitem.l_quantity, 18), le(tpch100.lineitem.l_quantity, 28))), and(and(ge(tpch100.part.p_size, 1), le(tpch100.part.p_size, 10)), and(in(tpch100.lineitem.l_shipmode, "AIR", "AIR REG"), eq(tpch100.lineitem.l_shipinstruct, "DELIVER IN PERSON")))), and(and(and(eq(tpch100.part.p_brand, "Brand#51"), in(tpch100.part.p_container, "LG CASE", "LG BOX", "LG PACK", "LG PKG")), and(ge(tpch100.lineitem.l_quantity, 29), le(tpch100.lineitem.l_quantity, 39))), and(and(ge(tpch100.part.p_size, 1), le(tpch100.part.p_size, 15)), and(in(tpch100.lineitem.l_shipmode, "AIR", "AIR REG"), eq(tpch100.lineitem.l_shipinstruct, "DELIVER IN PERSON")))))) | N/A | N/A |
| ├─ExchangeReceiver_35(Build) | 47771.70 | 1423652587.40 | 96514 | mpp[tiflash] | | tiflash_task:{proc max:127.9ms, min:126.8ms, avg: 127.3ms, p80:127.9ms, p95:127.9ms, iters:72, tasks:2, threads:72} | | N/A | N/A |
| │ └─ExchangeSender_34 | 47771.70 | 1415154001.70 | 48257 | mpp[tiflash] | | tiflash_task:{proc max:126.7ms, min:0s, avg: 63.3ms, p80:126.7ms, p95:126.7ms, iters:338, tasks:2, threads:72} | ExchangeType: Broadcast, Compression: FAST | N/A | N/A |
| │ └─Selection_33 | 47771.70 | 1415154001.70 | 48257 | mpp[tiflash] | | tiflash_task:{proc max:126.7ms, min:0s, avg: 63.3ms, p80:126.7ms, p95:126.7ms, iters:338, tasks:2, threads:72} | or(and(and(eq(tpch100.part.p_brand, "Brand#52"), in(tpch100.part.p_container, "SM CASE", "SM BOX", "SM PACK", "SM PKG")), and(ge(tpch100.part.p_size, 1), le(tpch100.part.p_size, 5))), or(and(and(eq(tpch100.part.p_brand, "Brand#11"), in(tpch100.part.p_container, "MED BAG", "MED BOX", "MED PKG", "MED PACK")), and(ge(tpch100.part.p_size, 1), le(tpch100.part.p_size, 10))), and(and(eq(tpch100.part.p_brand, "Brand#51"), in(tpch100.part.p_container, "LG CASE", "LG BOX", "LG PACK", "LG PKG")), and(ge(tpch100.part.p_size, 1), le(tpch100.part.p_size, 15))))) | N/A | N/A |
| │ └─TableFullScan_32 | 20000000.00 | 1367154001.70 | 20000000 | mpp[tiflash] | table:part | tiflash_task:{proc max:114.7ms, min:0s, avg: 57.3ms, p80:114.7ms, p95:114.7ms, iters:338, tasks:2, threads:72}, tiflash_scan:{dtfile:{total_scanned_packs:0, total_skipped_packs:0, total_scanned_rows:20056913, total_skipped_rows:7014264, total_rs_index_check_time: 3ms, total_read_time: 1546ms, total_disagg_read_cache_hit_size: 0, total_disagg_read_cache_miss_size: 0}, total_create_snapshot_time: 0ms, total_local_region_num: 37, total_remote_region_num: 0, total_learner_read_time: 0ms} | pushed down filter:empty, keep order:false | N/A | N/A |
| └─Selection_31(Probe) | 14078086.84 | 54033837221.40 | 14145150 | mpp[tiflash] | | tiflash_task:{proc max:2.54s, min:2.54s, avg: 2.54s, p80:2.54s, p95:2.54s, iters:9738, tasks:2, threads:72} | or(and(and(ge(tpch100.lineitem.l_quantity, 4), le(tpch100.lineitem.l_quantity, 14)), and(in(tpch100.lineitem.l_shipmode, "AIR", "AIR REG"), eq(tpch100.lineitem.l_shipinstruct, "DELIVER IN PERSON"))), or(and(and(ge(tpch100.lineitem.l_quantity, 18), le(tpch100.lineitem.l_quantity, 28)), and(in(tpch100.lineitem.l_shipmode, "AIR", "AIR REG"), eq(tpch100.lineitem.l_shipinstruct, "DELIVER IN PERSON"))), and(and(ge(tpch100.lineitem.l_quantity, 29), le(tpch100.lineitem.l_quantity, 39)), and(in(tpch100.lineitem.l_shipmode, "AIR", "AIR REG"), eq(tpch100.lineitem.l_shipinstruct, "DELIVER IN PERSON"))))) | N/A | N/A |
| └─TableFullScan_30 | 600037902.00 | 52593746256.60 | 600037902 | mpp[tiflash] | table:lineitem | tiflash_task:{proc max:222.7ms, min:214.9ms, avg: 218.8ms, p80:222.7ms, p95:222.7ms, iters:9738, tasks:2, threads:72}, tiflash_scan:{dtfile:{total_scanned_packs:0, total_skipped_packs:0, total_scanned_rows:600074360, total_skipped_rows:29147927, total_rs_index_check_time: 137ms, total_read_time: 121575ms, total_disagg_read_cache_hit_size: 0, total_disagg_read_cache_miss_size: 0}, total_create_snapshot_time: 5ms, total_local_region_num: 1307, total_remote_region_num: 0, total_learner_read_time: 48ms} | pushed down filter:empty, keep order:false | N/A | N/A |
```
However, when running Q19 with `explain analyze`, the `in(tpch100.lineitem.l_shipmode, "AIR", "AIR REG")` can be pushed down to table scan, leading to 2.0s.
```sql
mysql> explain analyze select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where ( p_partkey = l_partkey and p_brand = 'Brand#52' and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and l_quantity >= 4 and l_quantity <= 4 + 10 and p_size between 1 and 5 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#11'
and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') and l_quantity >= 18 and l_quantity <= 18 + 10 and p_size between 1 and 10 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#51' and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') and l_quantity >= 29 and l_quantity <= 29 + 10 and p_size between 1 and 15 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' );
+--------------------------------------------+-------------+----------+--------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------------------+-------------+----------+--------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| HashAgg_53 | 1.00 | 1 | root | | time:2.07s, loops:2, RU:0.000000, partial_worker:{wall_time:2.073637818s, concurrency:5, task_num:1, tot_wait:10.367934734s, tot_exec:7.618µs, tot_time:10.367959534s, max:2.073608436s, p95:2.073608436s}, final_worker:{wall_time:2.073663746s, concurrency:5, task_num:1, tot_wait:10.368154471s, tot_exec:28.051µs, tot_time:10.368186247s, max:2.073640189s, p95:2.073640189s} | funcs:sum(Column#27)->Column#26 | 20.2 KB | N/A |
| └─TableReader_55 | 1.00 | 2 | root | | time:2.07s, loops:2, cop_task: {num: 4, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 2, data:ExchangeSender_54 | 1.43 KB | N/A |
| └─ExchangeSender_54 | 1.00 | 2 | mpp[tiflash] | | tiflash_task:{proc max:2.07s, min:2.07s, avg: 2.07s, p80:2.07s, p95:2.07s, iters:2, tasks:2, threads:2} | ExchangeType: PassThrough | N/A | N/A |
| └─HashAgg_13 | 1.00 | 2 | mpp[tiflash] | | tiflash_task:{proc max:2.07s, min:2.07s, avg: 2.07s, p80:2.07s, p95:2.07s, iters:2, tasks:2, threads:2} | funcs:sum(Column#29)->Column#27 | N/A | N/A |
| └─Projection_76 | 1426176.87 | 11341 | mpp[tiflash] | | tiflash_task:{proc max:2.07s, min:2.06s, avg: 2.07s, p80:2.07s, p95:2.07s, iters:9740, tasks:2, threads:72} | mul(tpch100.lineitem.l_extendedprice, minus(1, tpch100.lineitem.l_discount))->Column#29 | N/A | N/A |
| └─HashJoin_52 | 1426176.87 | 11341 | mpp[tiflash] | | tiflash_task:{proc max:2.07s, min:2.06s, avg: 2.07s, p80:2.07s, p95:2.07s, iters:9740, tasks:2, threads:72} | inner join, equal:[eq(tpch100.lineitem.l_partkey, tpch100.part.p_partkey)], other cond:or(and(and(eq(tpch100.part.p_brand, "Brand#52"), in(tpch100.part.p_container, "SM CASE", "SM BOX", "SM PACK", "SM PKG")), and(ge(tpch100.lineitem.l_quantity, 4), and(le(tpch100.lineitem.l_quantity, 14), le(tpch100.part.p_size, 5)))), or(and(and(eq(tpch100.part.p_brand, "Brand#11"), in(tpch100.part.p_container, "MED BAG", "MED BOX", "MED PKG", "MED PACK")), and(ge(tpch100.lineitem.l_quantity, 18), and(le(tpch100.lineitem.l_quantity, 28), le(tpch100.part.p_size, 10)))), and(and(eq(tpch100.part.p_brand, "Brand#51"), in(tpch100.part.p_container, "LG CASE", "LG BOX", "LG PACK", "LG PKG")), and(ge(tpch100.lineitem.l_quantity, 29), and(le(tpch100.lineitem.l_quantity, 39), le(tpch100.part.p_size, 15)))))) | N/A | N/A |
| ├─ExchangeReceiver_35(Build) | 47771.70 | 96514 | mpp[tiflash] | | tiflash_task:{proc max:133.6ms, min:132.7ms, avg: 133.1ms, p80:133.6ms, p95:133.6ms, iters:72, tasks:2, threads:72} | | N/A | N/A |
| │ └─ExchangeSender_34 | 47771.70 | 48257 | mpp[tiflash] | | tiflash_task:{proc max:132.4ms, min:0s, avg: 66.2ms, p80:132.4ms, p95:132.4ms, iters:354, tasks:2, threads:72} | ExchangeType: Broadcast, Compression: FAST | N/A | N/A |
| │ └─Selection_33 | 47771.70 | 48257 | mpp[tiflash] | | tiflash_task:{proc max:132.4ms, min:0s, avg: 66.2ms, p80:132.4ms, p95:132.4ms, iters:354, tasks:2, threads:72} | ge(tpch100.part.p_size, 1), or(and(eq(tpch100.part.p_brand, "Brand#52"), and(in(tpch100.part.p_container, "SM CASE", "SM BOX", "SM PACK", "SM PKG"), le(tpch100.part.p_size, 5))), or(and(eq(tpch100.part.p_brand, "Brand#11"), and(in(tpch100.part.p_container, "MED BAG", "MED BOX", "MED PKG", "MED PACK"), le(tpch100.part.p_size, 10))), and(eq(tpch100.part.p_brand, "Brand#51"), and(in(tpch100.part.p_container, "LG CASE", "LG BOX", "LG PACK", "LG PKG"), le(tpch100.part.p_size, 15))))) | N/A | N/A |
| │ └─TableFullScan_32 | 20000000.00 | 20000000 | mpp[tiflash] | table:part | tiflash_task:{proc max:120.4ms, min:0s, avg: 60.2ms, p80:120.4ms, p95:120.4ms, iters:359, tasks:2, threads:72}, tiflash_scan:{dtfile:{total_scanned_packs:0, total_skipped_packs:0, total_scanned_rows:20060942, total_skipped_rows:7672662, total_rs_index_check_time: 2ms, total_read_time: 1617ms, total_disagg_read_cache_hit_size: 0, total_disagg_read_cache_miss_size: 0}, total_create_snapshot_time: 0ms, total_local_region_num: 37, total_remote_region_num: 0, total_learner_read_time: 0ms} | pushed down filter:empty, keep order:false | N/A | N/A |
| └─Selection_31(Probe) | 14189641.13 | 14145150 | mpp[tiflash] | | tiflash_task:{proc max:2.01s, min:2.01s, avg: 2.01s, p80:2.01s, p95:2.01s, iters:9740, tasks:2, threads:72} | eq(tpch100.lineitem.l_shipinstruct, "DELIVER IN PERSON"), or(and(ge(tpch100.lineitem.l_quantity, 4), le(tpch100.lineitem.l_quantity, 14)), or(and(ge(tpch100.lineitem.l_quantity, 18), le(tpch100.lineitem.l_quantity, 28)), and(ge(tpch100.lineitem.l_quantity, 29), le(tpch100.lineitem.l_quantity, 39)))) | N/A | N/A |
| └─TableFullScan_30 | 86081963.28 | 85729153 | mpp[tiflash] | table:lineitem | tiflash_task:{proc max:1.85s, min:1.84s, avg: 1.84s, p80:1.85s, p95:1.85s, iters:9740, tasks:2, threads:72}, tiflash_scan:{dtfile:{total_scanned_packs:0, total_skipped_packs:0, total_scanned_rows:600064726, total_skipped_rows:11624696, total_rs_index_check_time: 56ms, total_read_time: 75090ms, total_disagg_read_cache_hit_size: 0, total_disagg_read_cache_miss_size: 0}, total_create_snapshot_time: 2ms, total_local_region_num: 1307, total_remote_region_num: 0, total_learner_read_time: 55ms} | pushed down filter:in(tpch100.lineitem.l_shipmode, "AIR", "AIR REG"), keep order:false | N/A | N/A |
+--------------------------------------------+-------------+----------+--------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
12 rows in set (2.08 sec)
```
Contributor guide
Assessment
This issue has not been assessed yet.