pingcap / pingcap/tidb

wrong estimation causing wrong plan for CH workload

Open
#39,492 1 comment 0 reactions 1 assignee Claimed by @qw4990 View on GitHub
epic/cardinality-estimation sig/planner type/enhancement
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!

### 1. Minimal reproduce step (Required)

run ch workload mater version

### 2. What did you expect to see? (Required)
the cop request is pushdowned to tikv

### 3. What did you see instead (Required)

wrong estimation causing the cop request is pushdown to tiflash causing wrong performance.
est rows: 24988.05
actual rows: 100
```
SELECT
ol_d_id,
SUM(ol_amount)
FROM
order_line
WHERE
(ol_w_id, ol_d_id, ol_o_id) IN (
(?, ?, ?),
(?, ?, ?),
(?, ?, ?),
(?, ?, ?),
(?, ?, ?),
(?, ?, ?),
(?, ?, ?),
(?, ?, ?),
(?, ?, ?),
(?, ?, ?)
)
GROUP BY
ol_d_id [arguments: (444, 1, 2250, 444, 2, 2250, 444, 3, 2250, 444, 4, 2250, 444, 5, 2250, 444, 6, 2250, 444, 7, 2250, 444, 8, 2250, 444, 9, 2250, 444, 10, 2250)]

id task estRows operator info actRows execution info memory disk
Projection_6 root 1 tpcc.order_line.ol_d_id, Column#11 10 time:116.8ms, loops:6, Concurrency:OFF 13.7 KB N/A
└─HashAgg_7 root 1 group by:tpcc.order_line.ol_d_id, funcs:sum(tpcc.order_line.ol_amount)->Column#11, funcs:firstrow(tpcc.order_line.ol_d_id)->tpcc.order_line.ol_d_id 10 time:116.8ms, loops:6, partial_worker:{wall_time:116.720253ms, concurrency:5, task_num:4, tot_wait:582.978001ms, tot_exec:80.141µs, tot_time:583.095762ms, max:116.663593ms, p95:116.663593ms}, final_worker:{wall_time:116.781574ms, concurrency:5, task_num:9, tot_wait:583.476143ms, tot_exec:130.744µs, tot_time:583.614447ms, max:116.744353ms, p95:116.744353ms} 211.1 KB N/A
└─UnionScan_8 root 19764.17 or(or(or(and(eq(tpcc.order_line.ol_w_id, 444), and(eq(tpcc.order_line.ol_d_id, 1), eq(tpcc.order_line.ol_o_id, 2250))), and(eq(tpcc.order_line.ol_w_id, 444), and(eq(tpcc.order_line.ol_d_id, 2), eq(tpcc.order_line.ol_o_id, 2250)))), or(and(eq(tpcc.order_line.ol_w_id, 444), and(eq(tpcc.order_line.ol_d_id, 3), eq(tpcc.order_line.ol_o_id, 2250))), or(and(eq(tpcc.order_line.ol_w_id, 444), and(eq(tpcc.order_line.ol_d_id, 4), eq(tpcc.order_line.ol_o_id, 2250))), and(eq(tpcc.order_line.ol_w_id, 444), and(eq(tpcc.order_line.ol_d_id, 5), eq(tpcc.order_line.ol_o_id, 2250)))))), or(or(and(eq(tpcc.order_line.ol_w_id, 444), and(eq(tpcc.order_line.ol_d_id, 6), eq(tpcc.order_line.ol_o_id, 2250))), and(eq(tpcc.order_line.ol_w_id, 444), and(eq(tpcc.order_line.ol_d_id, 7), eq(tpcc.order_line.ol_o_id, 2250)))), or(and(eq(tpcc.order_line.ol_w_id, 444), and(eq(tpcc.order_line.ol_d_id, 8), eq(tpcc.order_line.ol_o_id, 2250))), or(and(eq(tpcc.order_line.ol_w_id, 444), and(eq(tpcc.order_line.ol_d_id, 9), eq(tpcc.order_line.ol_o_id, 2250))), and(eq(tpcc.order_line.ol_w_id, 444), and(eq(tpcc.order_line.ol_d_id, 10), eq(tpcc.order_line.ol_o_id, 2250))))))) 100 time:116.6ms, loops:5 N/A N/A
└─Selection_14 root 19764.17 or(or(or(and(eq(tpcc.order_line.ol_w_id, 444), and(eq(tpcc.order_line.ol_d_id, 1), eq(tpcc.order_line.ol_o_id, 2250))), and(eq(tpcc.order_line.ol_w_id, 444), and(eq(tpcc.order_line.ol_d_id, 2), eq(tpcc.order_line.ol_o_id, 2250)))), or(and(eq(tpcc.order_line.ol_w_id, 444), and(eq(tpcc.order_line.ol_d_id, 3), eq(tpcc.order_line.ol_o_id, 2250))), or(and(eq(tpcc.order_line.ol_w_id, 444), and(eq(tpcc.order_line.ol_d_id, 4), eq(tpcc.order_line.ol_o_id, 2250))), and(eq(tpcc.order_line.ol_w_id, 444), and(eq(tpcc.order_line.ol_d_id, 5), eq(tpcc.order_line.ol_o_id, 2250)))))), or(or(and(eq(tpcc.order_line.ol_w_id, 444), and(eq(tpcc.order_line.ol_d_id, 6), eq(tpcc.order_line.ol_o_id, 2250))), and(eq(tpcc.order_line.ol_w_id, 444), and(eq(tpcc.order_line.ol_d_id, 7), eq(tpcc.order_line.ol_o_id, 2250)))), or(and(eq(tpcc.order_line.ol_w_id, 444), and(eq(tpcc.order_line.ol_d_id, 8), eq(tpcc.order_line.ol_o_id, 2250))), or(and(eq(tpcc.order_line.ol_w_id, 444), and(eq(tpcc.order_line.ol_d_id, 9), eq(tpcc.order_line.ol_o_id, 2250))), and(eq(tpcc.order_line.ol_w_id, 444), and(eq(tpcc.order_line.ol_d_id, 10), eq(tpcc.order_line.ol_o_id, 2250))))))) 100 time:116.3ms, loops:103 39.5 KB N/A
└─TableReader_13 root 24988.05 data:TableRangeScan_12 100 time:115.7ms, loops:104, cop_task: {num: 2, max: 117.4ms, min: 114.7ms, avg: 116ms, p95: 117.4ms, rpc_num: 2, rpc_time: 232ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} 7.39 KB N/A
└─TableRangeScan_12 cop[tiflash] 24988.05 table:order_line, range:[444 1 2250,444 1 2250], [444 2 2250,444 2 2250], [444 3 2250,444 3 2250], [444 4 2250,444 4 2250], [444 5 2250,444 5 2250], [444 6 2250,444 6 2250], [444 7 2250,444 7 2250], [444 8 2250,444 8 2250], [444 9 2250,444 9 2250], [444 10 2250,444 10 2250], keep order:false 100 tiflash_task:{proc max:109.5ms, min:103.7ms, avg: 106.6ms, p80:109.5ms, p95:109.5ms, iters:2, tasks:2, threads:2}, tiflash_scan:{dmfile:{total_scanned_packs:124, total_skipped_packs:214, total_scanned_rows:975137, total_skipped_rows:1685683, total_rough_set_index_load_time: 0ms, total_read_time: 43ms}, total_create_snapshot_time: 0ms} N/A N/A
```

### 4. What is your TiDB version? (Required)

tidb: 2c0c13053acad7ebbf8405f9be004dffaa5e462c
tikv: c1aceb003b9da06b75b70a1e545d52b994ab67dc

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.