search

pingcap / tidb

TiDB is an open-source, cloud-native, distributed, MySQL-Compatible database for elastic scale and real-time analytics. Try AI-powered Chat2Query free at : https://www.pingcap.com/tidb-serverless/
https://pingcap.com
Apache License 2.0
36.87k stars 5.8k forks source link

estimate filter selectively for simple derived columns #55366

Open windtalker opened 1 month ago

windtalker commented 1 month ago

Enhancement

Looks like TiDB does not support estimate filter selectively on derived column, which may cause some sub-optimal plans. Take tpch query 18 as example:

+--------------------------------------------------------------------------------+--------------+-----------+--------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
| id                                                                             | estRows      | actRows   | task         | access object  | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | memory  | disk    |
+--------------------------------------------------------------------------------+--------------+-----------+--------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
| Projection_33                                                                  | 100.00       | 100       | root         |                | time:3.75s, loops:2, RU:565489.639603, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | tpch100.customer.c_name, tpch100.customer.c_custkey, tpch100.orders.o_orderkey, tpch100.orders.o_orderdate, tpch100.orders.o_totalprice, Column#54                                                                                                                                                                                                                                                                                                                                                                                                                    | 14.6 KB | N/A     |
| └─TopN_37                                                                      | 100.00       | 100       | root         |                | time:3.75s, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | tpch100.orders.o_totalprice:desc, tpch100.orders.o_orderdate, offset:0, count:100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | 41.7 KB | 0 Bytes |
|   └─TableReader_240                                                            | 100.00       | 732       | root         |                | time:3.75s, loops:3, cop_task: {num: 111, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache: disabled}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | MppVersion: 2, data:ExchangeSender_239                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | 4.58 KB | N/A     |
|     └─ExchangeSender_239                                                       | 100.00       | 732       | mpp[tiflash] |                | tiflash_task:{proc max:3.75s, min:3.75s, avg: 3.75s, p80:3.75s, p95:3.75s, iters:108, tasks:3, threads:108}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | ExchangeType: PassThrough                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | N/A     | N/A     |
|       └─TopN_238                                                               | 100.00       | 732       | mpp[tiflash] |                | tiflash_task:{proc max:3.75s, min:3.75s, avg: 3.75s, p80:3.75s, p95:3.75s, iters:108, tasks:3, threads:108}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | tpch100.orders.o_totalprice:desc, tpch100.orders.o_orderdate, offset:0, count:100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | N/A     | N/A     |
|         └─Projection_234                                                       | 120704204.80 | 732       | mpp[tiflash] |                | tiflash_task:{proc max:3.75s, min:3.75s, avg: 3.75s, p80:3.75s, p95:3.75s, iters:108, tasks:3, threads:108}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | Column#54, tpch100.customer.c_custkey, tpch100.customer.c_name, tpch100.orders.o_orderkey, tpch100.orders.o_totalprice, tpch100.orders.o_orderdate                                                                                                                                                                                                                                                                                                                                                                                                                    | N/A     | N/A     |
|           └─HashAgg_235                                                        | 120704204.80 | 732       | mpp[tiflash] |                | tiflash_task:{proc max:3.75s, min:3.75s, avg: 3.75s, p80:3.75s, p95:3.75s, iters:108, tasks:3, threads:108}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | group by:tpch100.customer.c_custkey, tpch100.customer.c_name, tpch100.orders.o_orderdate, tpch100.orders.o_orderkey, tpch100.orders.o_totalprice, funcs:sum(Column#88)->Column#54, funcs:firstrow(tpch100.customer.c_custkey)->tpch100.customer.c_custkey, funcs:firstrow(tpch100.customer.c_name)->tpch100.customer.c_name, funcs:firstrow(tpch100.orders.o_orderkey)->tpch100.orders.o_orderkey, funcs:firstrow(tpch100.orders.o_totalprice)->tpch100.orders.o_totalprice, funcs:firstrow(tpch100.orders.o_orderdate)->tpch100.orders.o_orderdate, stream_count: 36 | N/A     | N/A     |
|             └─ExchangeReceiver_237                                             | 120704204.80 | 732       | mpp[tiflash] |                | tiflash_task:{proc max:3.75s, min:3.75s, avg: 3.75s, p80:3.75s, p95:3.75s, iters:108, tasks:3, threads:108}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | stream_count: 36                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | N/A     | N/A     |
|               └─ExchangeSender_236                                             | 120704204.80 | 732       | mpp[tiflash] |                | tiflash_task:{proc max:3.75s, min:0s, avg: 1.25s, p80:3.75s, p95:3.75s, iters:3, tasks:3, threads:3}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.customer.c_name, collate: N/A], [name: tpch100.customer.c_custkey, collate: N/A], [name: tpch100.orders.o_orderkey, collate: N/A], [name: tpch100.orders.o_orderdate, collate: N/A], [name: tpch100.orders.o_totalprice, collate: N/A], stream_count: 36                                                                                                                                                                                                                                    | N/A     | N/A     |
|                 └─HashAgg_232                                                  | 120704204.80 | 732       | mpp[tiflash] |                | tiflash_task:{proc max:3.75s, min:0s, avg: 1.25s, p80:3.75s, p95:3.75s, iters:3, tasks:3, threads:3}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | group by:tpch100.customer.c_custkey, tpch100.customer.c_name, tpch100.orders.o_orderdate, tpch100.orders.o_orderkey, tpch100.orders.o_totalprice, funcs:sum(tpch100.lineitem.l_quantity)->Column#88                                                                                                                                                                                                                                                                                                                                                                   | N/A     | N/A     |
|                   └─Projection_221                                             | 480030321.60 | 5124      | mpp[tiflash] |                | tiflash_task:{proc max:3.71s, min:0s, avg: 1.24s, p80:3.71s, p95:3.71s, iters:27163, tasks:3, threads:216}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | tpch100.customer.c_custkey, tpch100.customer.c_name, tpch100.orders.o_orderkey, tpch100.orders.o_totalprice, tpch100.orders.o_orderdate, tpch100.lineitem.l_quantity                                                                                                                                                                                                                                                                                                                                                                                                  | N/A     | N/A     |
|                     └─Projection_211                                           | 480030321.60 | 5124      | mpp[tiflash] |                | tiflash_task:{proc max:3.68s, min:0s, avg: 1.23s, p80:3.68s, p95:3.68s, iters:27163, tasks:3, threads:216}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | tpch100.lineitem.l_quantity, tpch100.customer.c_custkey, tpch100.customer.c_name, tpch100.orders.o_orderkey, tpch100.orders.o_totalprice, tpch100.orders.o_orderdate, tpch100.lineitem.l_orderkey                                                                                                                                                                                                                                                                                                                                                                     | N/A     | N/A     |
|                       └─HashJoin_210                                           | 480030321.60 | 5124      | mpp[tiflash] |                | tiflash_task:{proc max:3.65s, min:0s, avg: 1.22s, p80:3.65s, p95:3.65s, iters:27163, tasks:3, threads:216}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | inner join, equal:[eq(tpch100.lineitem.l_orderkey, tpch100.orders.o_orderkey)]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | N/A     | N/A     |
|                         ├─ExchangeReceiver_101(Build)                          | 120704204.80 | 732       | mpp[tiflash] |                | tiflash_task:{proc max:3.07s, min:0s, avg: 1.02s, p80:3.07s, p95:3.07s, iters:152, tasks:3, threads:216}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | N/A     | N/A     |
|                         │ └─ExchangeSender_100                                 | 120704204.80 | 732       | mpp[tiflash] |                | tiflash_task:{proc max:3.07s, min:0s, avg: 1.02s, p80:3.07s, p95:3.07s, iters:215, tasks:3, threads:216}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.orders.o_orderkey, collate: N/A]                                                                                                                                                                                                                                                                                                                                                                                                                                                            | N/A     | N/A     |
|                         │   └─Projection_99                                    | 120704204.80 | 732       | mpp[tiflash] |                | tiflash_task:{proc max:3.07s, min:0s, avg: 1.02s, p80:3.07s, p95:3.07s, iters:215, tasks:3, threads:216}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | tpch100.customer.c_custkey, tpch100.customer.c_name, tpch100.orders.o_orderkey, tpch100.orders.o_totalprice, tpch100.orders.o_orderdate, tpch100.orders.o_custkey                                                                                                                                                                                                                                                                                                                                                                                                     | N/A     | N/A     |
|                         │     └─HashJoin_68                                    | 120704204.80 | 732       | mpp[tiflash] |                | tiflash_task:{proc max:3.07s, min:0s, avg: 1.02s, p80:3.07s, p95:3.07s, iters:215, tasks:3, threads:216}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | inner join, equal:[eq(tpch100.customer.c_custkey, tpch100.orders.o_custkey)], stream_count: 36                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | N/A     | N/A     |
|                         │       ├─ExchangeReceiver_71(Build)                   | 15000000.00  | 15000000  | mpp[tiflash] |                | tiflash_task:{proc max:788.3ms, min:0s, avg: 262.8ms, p80:788.3ms, p95:788.3ms, iters:1726, tasks:3, threads:108}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | stream_count: 36                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | N/A     | N/A     |
|                         │       │ └─ExchangeSender_70                          | 15000000.00  | 15000000  | mpp[tiflash] |                | tiflash_task:{proc max:1.31s, min:0s, avg: 437.8ms, p80:1.31s, p95:1.31s, iters:233, tasks:3, threads:216}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.customer.c_custkey, collate: N/A], stream_count: 36                                                                                                                                                                                                                                                                                                                                                                                                                                         | N/A     | N/A     |
|                         │       │   └─TableFullScan_69                         | 15000000.00  | 15000000  | mpp[tiflash] | table:customer | tiflash_task:{proc max:686.5ms, min:0s, avg: 228.8ms, p80:686.5ms, p95:686.5ms, iters:233, tasks:3, threads:216}, tiflash_scan:{mvcc_input_rows:514851, mvcc_input_bytes:8752467, mvcc_output_rows:514851, lm_skip_rows:0, local_regions:30, remote_regions:0, tot_learner_read:46ms, region_balance:{instance_num: 3, max/min: 10/10=1.000000}, delta_rows:0, delta_bytes:0, segments:29, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:144ms, tot_build_inputstream:252ms, min_local_stream:0ms, max_local_stream:657ms, dtfile:{data_scanned_rows:15000000, data_skipped_rows:42584, mvcc_scanned_rows:600019, mvcc_skipped_rows:1072286, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:61ms, tot_read:6339ms}}              | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | N/A     | N/A     |
|                         │       └─ExchangeReceiver_98(Probe)                   | 120704204.80 | 732       | mpp[tiflash] |                | tiflash_task:{proc max:3.07s, min:0s, avg: 1.02s, p80:3.07s, p95:3.07s, iters:215, tasks:3, threads:216}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | N/A     | N/A     |
|                         │         └─ExchangeSender_97                          | 120704204.80 | 732       | mpp[tiflash] |                | tiflash_task:{proc max:3.06s, min:0s, avg: 1.02s, p80:3.06s, p95:3.06s, iters:7062, tasks:3, threads:216}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.orders.o_custkey, collate: N/A]                                                                                                                                                                                                                                                                                                                                                                                                                                                             | N/A     | N/A     |
|                         │           └─Projection_96                            | 120704204.80 | 732       | mpp[tiflash] |                | tiflash_task:{proc max:3.03s, min:0s, avg: 1.01s, p80:3.03s, p95:3.03s, iters:7062, tasks:3, threads:216}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | tpch100.orders.o_orderkey, tpch100.orders.o_custkey, tpch100.orders.o_totalprice, tpch100.orders.o_orderdate                                                                                                                                                                                                                                                                                                                                                                                                                                                          | N/A     | N/A     |
|                         │             └─HashJoin_72                            | 120704204.80 | 732       | mpp[tiflash] |                | tiflash_task:{proc max:3.02s, min:0s, avg: 1.01s, p80:3.02s, p95:3.02s, iters:7062, tasks:3, threads:216}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | inner join, equal:[eq(tpch100.orders.o_orderkey, tpch100.lineitem.l_orderkey)]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | N/A     | N/A     |
|                         │               ├─Selection_76(Build)                  | 120704204.80 | 732       | mpp[tiflash] |                | tiflash_task:{proc max:2.55s, min:0s, avg: 849.9ms, p80:2.55s, p95:2.55s, iters:702, tasks:3, threads:108}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | gt(Column#52, 314)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | N/A     | N/A     |
|                         │               │ └─Projection_85                      | 150880256.00 | 150000000 | mpp[tiflash] |                | tiflash_task:{proc max:2.5s, min:0s, avg: 833.8ms, p80:2.5s, p95:2.5s, iters:27648, tasks:3, threads:108}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | Column#52, tpch100.lineitem.l_orderkey                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | N/A     | N/A     |
|                         │               │   └─HashAgg_86                       | 150880256.00 | 150000000 | mpp[tiflash] |                | tiflash_task:{proc max:2.47s, min:0s, avg: 823.9ms, p80:2.47s, p95:2.47s, iters:27648, tasks:3, threads:108}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | group by:tpch100.lineitem.l_orderkey, funcs:sum(Column#60)->Column#52, funcs:firstrow(tpch100.lineitem.l_orderkey)->tpch100.lineitem.l_orderkey, stream_count: 36                                                                                                                                                                                                                                                                                                                                                                                                     | N/A     | N/A     |
|                         │               │     └─ExchangeReceiver_88            | 150880256.00 | 150000432 | mpp[tiflash] |                | tiflash_task:{proc max:1.96s, min:0s, avg: 652.3ms, p80:1.96s, p95:1.96s, iters:13789, tasks:3, threads:108}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | stream_count: 36                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | N/A     | N/A     |
|                         │               │       └─ExchangeSender_87            | 150880256.00 | 150000432 | mpp[tiflash] |                | tiflash_task:{proc max:2.29s, min:0s, avg: 762.2ms, p80:2.29s, p95:2.29s, iters:2816, tasks:3, threads:216}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.lineitem.l_orderkey, collate: N/A], stream_count: 36                                                                                                                                                                                                                                                                                                                                                                                                                                        | N/A     | N/A     |
|                         │               │         └─HashAgg_79                 | 150880256.00 | 150000432 | mpp[tiflash] |                | tiflash_task:{proc max:2.11s, min:0s, avg: 703.9ms, p80:2.11s, p95:2.11s, iters:2816, tasks:3, threads:216}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | group by:tpch100.lineitem.l_orderkey, funcs:sum(tpch100.lineitem.l_quantity)->Column#60                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | N/A     | N/A     |
|                         │               │           └─TableFullScan_84         | 600037902.00 | 600037902 | mpp[tiflash] | table:lineitem | tiflash_task:{proc max:549ms, min:0s, avg: 183ms, p80:549ms, p95:549ms, iters:10156, tasks:3, threads:216}, tiflash_scan:{mvcc_input_rows:735763, mvcc_input_bytes:12507971, mvcc_output_rows:735763, lm_skip_rows:0, local_regions:1130, remote_regions:0, tot_learner_read:71ms, region_balance:{instance_num: 3, max/min: 382/368=1.038043}, delta_rows:0, delta_bytes:0, segments:1129, stale_read_regions:0, tot_build_snapshot:7ms, tot_build_bitmap:7009ms, tot_build_inputstream:14461ms, min_local_stream:102ms, max_local_stream:516ms, dtfile:{data_scanned_rows:600037902, data_skipped_rows:1151513, mvcc_scanned_rows:735763, mvcc_skipped_rows:1263651, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:6181ms, tot_read:114171ms}} | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | N/A     | N/A     |
|                         │               └─ExchangeReceiver_75(Probe)           | 150000000.00 | 150000000 | mpp[tiflash] |                | tiflash_task:{proc max:2.95s, min:0s, avg: 982.4ms, p80:2.95s, p95:2.95s, iters:7062, tasks:3, threads:216}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | N/A     | N/A     |
|                         │                 └─ExchangeSender_74                  | 150000000.00 | 150000000 | mpp[tiflash] |                | tiflash_task:{proc max:2.95s, min:0s, avg: 984.8ms, p80:2.95s, p95:2.95s, iters:2354, tasks:3, threads:216}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.orders.o_orderkey, collate: N/A]                                                                                                                                                                                                                                                                                                                                                                                                                                                            | N/A     | N/A     |
|                         │                   └─TableFullScan_73                 | 150000000.00 | 150000000 | mpp[tiflash] | table:orders   | tiflash_task:{proc max:473.8ms, min:0s, avg: 157.9ms, p80:473.8ms, p95:473.8ms, iters:2354, tasks:3, threads:216}, tiflash_scan:{mvcc_input_rows:702375, mvcc_input_bytes:11940375, mvcc_output_rows:702375, lm_skip_rows:0, local_regions:215, remote_regions:0, tot_learner_read:54ms, region_balance:{instance_num: 3, max/min: 72/71=1.014085}, delta_rows:0, delta_bytes:0, segments:214, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:985ms, tot_build_inputstream:3094ms, min_local_stream:0ms, max_local_stream:440ms, dtfile:{data_scanned_rows:150000000, data_skipped_rows:2765, mvcc_scanned_rows:702375, mvcc_skipped_rows:702375, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:1327ms, tot_read:95759ms}}       | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | N/A     | N/A     |
|                         └─ExchangeReceiver_67(Probe)                           | 600037902.00 | 600037902 | mpp[tiflash] |                | tiflash_task:{proc max:3.53s, min:0s, avg: 1.18s, p80:3.53s, p95:3.53s, iters:27163, tasks:3, threads:216}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | N/A     | N/A     |
|                           └─ExchangeSender_66                                  | 600037902.00 | 600037902 | mpp[tiflash] |                | tiflash_task:{proc max:3.72s, min:0s, avg: 1.24s, p80:3.72s, p95:3.72s, iters:10155, tasks:3, threads:216}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpch100.lineitem.l_orderkey, collate: N/A]                                                                                                                                                                                                                                                                                                                                                                                                                                                          | N/A     | N/A     |
|                             └─TableFullScan_65                                 | 600037902.00 | 600037902 | mpp[tiflash] | table:lineitem | tiflash_task:{proc max:286.4ms, min:0s, avg: 95.5ms, p80:286.4ms, p95:286.4ms, iters:10155, tasks:3, threads:216}, tiflash_scan:{mvcc_input_rows:527888, mvcc_input_bytes:8974096, mvcc_output_rows:527888, lm_skip_rows:0, local_regions:1130, remote_regions:0, tot_learner_read:71ms, region_balance:{instance_num: 3, max/min: 383/367=1.043597}, delta_rows:0, delta_bytes:0, segments:1128, stale_read_regions:0, tot_build_snapshot:8ms, tot_build_bitmap:9142ms, tot_build_inputstream:24575ms, min_local_stream:98ms, max_local_stream:236ms, dtfile:{data_scanned_rows:600037902, data_skipped_rows:623625, mvcc_scanned_rows:527888, mvcc_skipped_rows:0, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:12721ms, tot_read:15203ms}}   | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | N/A     | N/A     |
+--------------------------------------------------------------------------------+--------------+-----------+--------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+

There is a very large deviation for the estimate of Selection_76, which is sum(l_quantity) > 314. The actually result is 732 and the estimate result is 120704204.80. This miscalculation resulted in the subsequent plans not being optimal. For example, HashJoin_72 can be broadcast join by broadcast the result of Selection_76, and for HashJoin_68, it can also be broadcast join and use Projection_96 as the build side. And HashJoin_210 can be broadcast join so there is no need to exchange the entire lineitem table.

fixdb commented 1 month ago

On derived columns, e.g. function call or sum(l_quantity) > 314, optimizer currently just uses hard-coded value. The best we can do, at the moment, is changing to a smaller default value for derived columns.

fixdb commented 1 month ago

stats feedback for optimizer is more feasible to resolve this kind of issue.