Estimation info of planner is seriously wrong under ch-benchmark
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
Please answer these questions before submitting your issue. Thanks!
Similar issue #17185
### 1. Minimal reproduce step (Required)
Run daily ch-benchmark regression tests.
### 2. What did you expect to see? (Required)
Estimation info is similar to the real.
### 3. What did you see instead (Required)
ch-benchmark Q5
tidb will choose the unexpected join method. The estimate row count of `HashJoin_56` is far more bigger than the real one. It may cause the chosen join algorithm(Broadcast Join) is not the best one because the size of hash table when using broadcast join is bigger than the shuffle way, which means it may bring worse performance.
```
id task estRows operator info actRows execution info memory disk
TableReader_271 root 5 MppVersion: 1, data:ExchangeSender_270 5 time:1m6.2s, loops:2, cop_task: {num: 4, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} 2.16 KB N/A
└─ExchangeSender_270 cop[tiflash] 5 ExchangeType: PassThrough 5 tiflash_task:{proc max:1m6.2s, min:1m6.2s, avg: 1m6.2s, p80:1m6.2s, p95:1m6.2s, iters:2, tasks:2, threads:8} N/A N/A
└─Projection_28 cop[tiflash] 5 tpcc.nation.n_name, Column#75 5 tiflash_task:{proc max:1m6.2s, min:1m6.2s, avg: 1m6.2s, p80:1m6.2s, p95:1m6.2s, iters:2, tasks:2, threads:8} N/A N/A
└─Projection_266 cop[tiflash] 5 Column#75, tpcc.nation.n_name 5 tiflash_task:{proc max:1m6.2s, min:1m6.2s, avg: 1m6.2s, p80:1m6.2s, p95:1m6.2s, iters:2, tasks:2, threads:8} N/A N/A
└─HashAgg_267 cop[tiflash] 5 group by:tpcc.nation.n_name, funcs:sum(Column#142)->Column#75, funcs:firstrow(tpcc.nation.n_name)->tpcc.nation.n_name, stream_count: 16 5 tiflash_task:{proc max:1m6.2s, min:1m6.2s, avg: 1m6.2s, p80:1m6.2s, p95:1m6.2s, iters:2, tasks:2, threads:8} N/A N/A
└─ExchangeReceiver_269 cop[tiflash] 5 stream_count: 16 10 tiflash_task:{proc max:1m6.2s, min:1m6.2s, avg: 1m6.2s, p80:1m6.2s, p95:1m6.2s, iters:2, tasks:2, threads:8} N/A N/A
└─ExchangeSender_268 cop[tiflash] 5 ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpcc.nation.n_name, collate: N/A], stream_count: 16 10 tiflash_task:{proc max:1m13.6s, min:0s, avg: 36.8s, p80:1m13.6s, p95:1m13.6s, iters:2, tasks:2, threads:8} N/A N/A
└─HashAgg_264 cop[tiflash] 5 group by:tpcc.nation.n_name, funcs:sum(tpcc.order_line.ol_amount)->Column#142 10 tiflash_task:{proc max:1m13.6s, min:0s, avg: 36.8s, p80:1m13.6s, p95:1m13.6s, iters:2, tasks:2, threads:8} N/A N/A
└─Projection_253 cop[tiflash] 2307121986.87 tpcc.order_line.ol_amount, tpcc.nation.n_name 2484662 tiflash_task:{proc max:1m13.4s, min:0s, avg: 36.7s, p80:1m13.4s, p95:1m13.4s, iters:14870, tasks:2, threads:11} N/A N/A
└─HashJoin_243 cop[tiflash] 2307121986.87 inner join, equal:[eq(tpcc.nation.n_nationkey, tpcc.supplier.s_nationkey) eq(tpcc.nation.n_nationkey, Column#94)] 2484662 tiflash_task:{proc max:1m13.4s, min:0s, avg: 36.7s, p80:1m13.4s, p95:1m13.4s, iters:14870, tasks:2, threads:11} N/A N/A
├─ExchangeReceiver_47(Build) cop[tiflash] 5 10 tiflash_task:{proc max:60.6ms, min:0s, avg: 30.3ms, p80:60.6ms, p95:60.6ms, iters:2, tasks:2, threads:8} N/A N/A
│ └─ExchangeSender_46 cop[tiflash] 5 ExchangeType: Broadcast, Compression: FAST 5 tiflash_task:{proc max:29.2ms, min:0s, avg: 14.6ms, p80:29.2ms, p95:29.2ms, iters:1, tasks:2, threads:4} N/A N/A
│ └─HashJoin_40 cop[tiflash] 5 inner join, equal:[eq(tpcc.region.r_regionkey, tpcc.nation.n_regionkey)] 5 tiflash_task:{proc max:29.2ms, min:0s, avg: 14.6ms, p80:29.2ms, p95:29.2ms, iters:1, tasks:2, threads:4} N/A N/A
│ ├─ExchangeReceiver_44(Build) cop[tiflash] 1 1 tiflash_task:{proc max:29.2ms, min:0s, avg: 14.6ms, p80:29.2ms, p95:29.2ms, iters:1, tasks:2, threads:4} N/A N/A
│ │ └─ExchangeSender_43 cop[tiflash] 1 ExchangeType: Broadcast, Compression: FAST 1 tiflash_task:{proc max:13.9ms, min:0s, avg: 6.93ms, p80:13.9ms, p95:13.9ms, iters:1, tasks:2, threads:1} N/A N/A
│ │ └─Selection_42 cop[tiflash] 1 eq(tpcc.region.r_name, "EUROPE") 1 tiflash_task:{proc max:13.9ms, min:0s, avg: 6.93ms, p80:13.9ms, p95:13.9ms, iters:1, tasks:2, threads:1} N/A N/A
│ │ └─TableFullScan_41 cop[tiflash] 5 table:region, keep order:false 5 tiflash_task:{proc max:13.9ms, min:0s, avg: 6.93ms, p80:13.9ms, p95:13.9ms, iters:1, tasks:2, threads:1}, tiflash_scan:{dtfile:{total_scanned_packs:1, total_skipped_packs:0, total_scanned_rows:5, total_skipped_rows:0, total_rs_index_load_time: 0ms, total_read_time: 0ms}, total_create_snapshot_time: 0ms, total_local_region_num: 0, total_remote_region_num: 0} N/A N/A
│ └─TableFullScan_45(Probe) cop[tiflash] 25 table:nation, keep order:false 25 tiflash_task:{proc max:29.2ms, min:0s, avg: 14.6ms, p80:29.2ms, p95:29.2ms, iters:1, tasks:2, threads:1}, tiflash_scan:{dtfile:{total_scanned_packs:1, total_skipped_packs:0, total_scanned_rows:25, total_skipped_rows:0, total_rs_index_load_time: 0ms, total_read_time: 0ms}, total_create_snapshot_time: 0ms, total_local_region_num: 0, total_remote_region_num: 0} N/A N/A
└─Projection_48(Probe) cop[tiflash] 311922903022.71 tpcc.order_line.ol_amount, tpcc.supplier.s_nationkey, minus(ascii(substr(tpcc.customer.c_state, 1, 1)), 65)->Column#94 12373201 tiflash_task:{proc max:1m13.3s, min:0s, avg: 36.6s, p80:1m13.3s, p95:1m13.3s, iters:14870, tasks:2, threads:11} N/A N/A
└─Projection_49 cop[tiflash] 311922903022.71 tpcc.customer.c_state, tpcc.order_line.ol_amount, tpcc.supplier.s_nationkey 12373201 tiflash_task:{proc max:1m13.2s, min:0s, avg: 36.6s, p80:1m13.2s, p95:1m13.2s, iters:14870, tasks:2, threads:11} N/A N/A
└─HashJoin_50 cop[tiflash] 311922903022.71 inner join, equal:[eq(tpcc.supplier.s_suppkey, Column#76) eq(tpcc.supplier.s_suppkey, Column#77) eq(tpcc.supplier.s_suppkey, Column#78) eq(tpcc.supplier.s_suppkey, Column#79) eq(tpcc.supplier.s_suppkey, Column#80) eq(tpcc.supplier.s_suppkey, Column#81) eq(tpcc.supplier.s_suppkey, Column#82) eq(tpcc.supplier.s_nationkey, Column#83) eq(tpcc.supplier.s_suppkey, Column#84)] 12373201 tiflash_task:{proc max:1m13.2s, min:0s, avg: 36.6s, p80:1m13.2s, p95:1m13.2s, iters:14870, tasks:2, threads:11} N/A N/A
├─ExchangeReceiver_53(Build) cop[tiflash] 10000 20000 tiflash_task:{proc max:60.6ms, min:0s, avg: 30.3ms, p80:60.6ms, p95:60.6ms, iters:2, tasks:2, threads:8} N/A N/A
│ └─ExchangeSender_52 cop[tiflash] 10000 ExchangeType: Broadcast, Compression: FAST 10000 tiflash_task:{proc max:30.3ms, min:0s, avg: 15.2ms, p80:30.3ms, p95:30.3ms, iters:1, tasks:2, threads:1} N/A N/A
│ └─TableFullScan_51 cop[tiflash] 10000 table:supplier, keep order:false 10000 tiflash_task:{proc max:30.3ms, min:0s, avg: 15.2ms, p80:30.3ms, p95:30.3ms, iters:1, tasks:2, threads:1}, tiflash_scan:{dtfile:{total_scanned_packs:2, total_skipped_packs:0, total_scanned_rows:10000, total_skipped_rows:0, total_rs_index_load_time: 0ms, total_read_time: 0ms}, total_create_snapshot_time: 0ms, total_local_region_num: 0, total_remote_region_num: 0} N/A N/A
└─Projection_54(Probe) cop[tiflash] 3320661750495.92 tpcc.customer.c_state, tpcc.order_line.ol_amount, mod(mul(tpcc.order_line.ol_w_id, tpcc.order_line.ol_i_id), 10000)->Column#76, mod(mul(tpcc.orders.o_w_id, tpcc.order_line.ol_i_id), 10000)->Column#77, mod(mul(tpcc.customer.c_w_id, tpcc.order_line.ol_i_id), 10000)->Column#78, mod(mul(tpcc.stock.s_w_id, tpcc.order_line.ol_i_id), 10000)->Column#79, mod(mul(tpcc.order_line.ol_w_id, tpcc.stock.s_i_id), 10000)->Column#80, mod(mul(tpcc.orders.o_w_id, tpcc.stock.s_i_id), 10000)->Column#81, mod(mul(tpcc.customer.c_w_id, tpcc.stock.s_i_id), 10000)->Column#82, minus(ascii(substr(tpcc.customer.c_state, 1, 1)), 65)->Column#83, mod(mul(tpcc.stock.s_w_id, tpcc.stock.s_i_id), 10000)->Column#84 322010529 tiflash_task:{proc max:1m1.4s, min:0s, avg: 30.7s, p80:1m1.4s, p95:1m1.4s, iters:14870, tasks:2, threads:11} N/A N/A
└─HashJoin_55 cop[tiflash] 3320661750495.92 inner join, equal:[eq(tpcc.order_line.ol_w_id, tpcc.stock.s_w_id) eq(tpcc.order_line.ol_i_id, tpcc.stock.s_i_id)] 322010529 tiflash_task:{proc max:58.2s, min:0s, avg: 29.1s, p80:58.2s, p95:58.2s, iters:14870, tasks:2, threads:11} N/A N/A
├─ExchangeReceiver_68(Build) cop[tiflash] 100000000 200000000 tiflash_task:{proc max:2.87s, min:0s, avg: 1.44s, p80:2.87s, p95:2.87s, iters:4068, tasks:2, threads:8} N/A N/A
│ └─ExchangeSender_67 cop[tiflash] 100000000 ExchangeType: Broadcast, Compression: FAST 100000000 tiflash_task:{proc max:13.7s, min:0s, avg: 6.87s, p80:13.7s, p95:13.7s, iters:7877, tasks:2, threads:16} N/A N/A
│ └─TableFullScan_66 cop[tiflash] 100000000 table:stock, keep order:false 100000000 tiflash_task:{proc max:2.78s, min:0s, avg: 1.39s, p80:2.78s, p95:2.78s, iters:7877, tasks:2, threads:16}, tiflash_scan:{dtfile:{total_scanned_packs:77338, total_skipped_packs:24815, total_scanned_rows:617235697, total_skipped_rows:198069923, total_rs_index_load_time: 6ms, total_read_time: 6953ms}, total_create_snapshot_time: 0ms, total_local_region_num: 0, total_remote_region_num: 13} N/A N/A
└─HashJoin_56(Probe) cop[tiflash] 3214290062856.99 inner join, equal:[eq(tpcc.orders.o_id, tpcc.order_line.ol_o_id) eq(tpcc.orders.o_w_id, tpcc.order_line.ol_w_id) eq(tpcc.orders.o_d_id, tpcc.order_line.ol_d_id)] 322010529 tiflash_task:{proc max:21.6s, min:0s, avg: 10.8s, p80:21.6s, p95:21.6s, iters:14870, tasks:2, threads:11} N/A N/A
├─ExchangeReceiver_64(Build) cop[tiflash] 32143056 64402506 tiflash_task:{proc max:5.23s, min:0s, avg: 2.62s, p80:5.23s, p95:5.23s, iters:1343, tasks:2, threads:8} N/A N/A
│ └─ExchangeSender_63 cop[tiflash] 32143056 ExchangeType: Broadcast, Compression: FAST 32201253 tiflash_task:{proc max:7.08s, min:0s, avg: 3.54s, p80:7.08s, p95:7.08s, iters:1441, tasks:2, threads:13} N/A N/A
│ └─HashJoin_57 cop[tiflash] 32143056 inner join, equal:[eq(tpcc.customer.c_id, tpcc.orders.o_c_id) eq(tpcc.customer.c_w_id, tpcc.orders.o_w_id) eq(tpcc.customer.c_d_id, tpcc.orders.o_d_id)] 32201253 tiflash_task:{proc max:6.31s, min:0s, avg: 3.16s, p80:6.31s, p95:6.31s, iters:1441, tasks:2, threads:13} N/A N/A
│ ├─ExchangeReceiver_60(Build) cop[tiflash] 30000000 60000000 tiflash_task:{proc max:1.21s, min:0s, avg: 605.2ms, p80:1.21s, p95:1.21s, iters:1308, tasks:2, threads:8} N/A N/A
│ │ └─ExchangeSender_59 cop[tiflash] 30000000 ExchangeType: Broadcast, Compression: FAST 30000000 tiflash_task:{proc max:3.24s, min:0s, avg: 1.62s, p80:3.24s, p95:3.24s, iters:2954, tasks:2, threads:16} N/A N/A
│ │ └─TableFullScan_58 cop[tiflash] 30000000 table:customer, keep order:false 30000000 tiflash_task:{proc max:1.22s, min:0s, avg: 610.3ms, p80:1.22s, p95:1.22s, iters:2954, tasks:2, threads:16}, tiflash_scan:{dtfile:{total_scanned_packs:21576, total_skipped_packs:7755, total_scanned_rows:172361588, total_skipped_rows:61820394, total_rs_index_load_time: 2ms, total_read_time: 2608ms}, total_create_snapshot_time: 0ms, total_local_region_num: 0, total_remote_region_num: 18} N/A N/A
│ └─Selection_62(Probe) cop[tiflash] 32143056 ge(tpcc.orders.o_entry_d, 2007-01-02 00:00:00.000000), not(isnull(tpcc.orders.o_c_id)) 32201253 tiflash_task:{proc max:650.5ms, min:0s, avg: 325.3ms, p80:650.5ms, p95:650.5ms, iters:1441, tasks:2, threads:13} N/A N/A
│ └─TableFullScan_61 cop[tiflash] 32143056 table:orders, keep order:false 32201253 tiflash_task:{proc max:550.5ms, min:0s, avg: 275.3ms, p80:550.5ms, p95:550.5ms, iters:1441, tasks:2, threads:13}, tiflash_scan:{dtfile:{total_scanned_packs:11775, total_skipped_packs:282, total_scanned_rows:95837584, total_skipped_rows:2287305, total_rs_index_load_time: 0ms, total_read_time: 1346ms}, total_create_snapshot_time: 0ms, total_local_region_num: 0, total_remote_region_num: 5} N/A N/A
└─TableFullScan_65(Probe) cop[tiflash] 321429017 table:order_line, keep order:false 322010529 tiflash_task:{proc max:3.55s, min:0s, avg: 1.78s, p80:3.55s, p95:3.55s, iters:14870, tasks:2, threads:11}, tiflash_scan:{dtfile:{total_scanned_packs:123328, total_skipped_packs:6835, total_scanned_rows:1006583553, total_skipped_rows:55787408, total_rs_index_load_time: 8ms, total_read_time: 11812ms}, total_create_snapshot_time: 2ms, total_local_region_num: 0, total_remote_region_num: 3} N/A N/A
```
### 4. What is your TiDB version? (Required)
master
Contributor guide
Assessment
This issue has not been assessed yet.