pingcap / pingcap/tidb

est_row for TableScan on a single partition is over-estiamted

Open
#35,132 0 comments 0 reactions 1 assignee Claimed by @time-and-fate 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

## Enhancement
Prepare data:
```
set @@session.tidb_enable_list_partition = ON;
CREATE TABLE t (
a int
) partition by list(a) (
partition p0 values in (0),
partition p1 values in (1),
partition p2 values in (2),
partition p3 values in (3),
partition p4 values in (4),
partition p5 values in (5),
partition p6 values in (6),
partition p7 values in (7),
partition p8 values in (8),
partition p9 values in (9),
);
insert into t values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
insert into t select * from t;
...
insert into t select * from t;
set @@session.tidb_partition_prune_mode = 'dynamic';
analyze table t;

mysql> select a, count(*) from t group by a;
+------+----------+
| a | count(*) |
+------+----------+
| 0 | 512 |
| 1 | 512 |
| 2 | 512 |
| 3 | 512 |
| 4 | 512 |
| 5 | 512 |
| 6 | 512 |
| 7 | 512 |
| 8 | 512 |
| 9 | 512 |
+------+----------+
```

In the case above, the `est_row` of TableScan on a single partition should be `512`, but actually, it is over-estimated by 10 times:
```
mysql> explain format=verbose select * from t where a=0;
+-------------------------+---------+-----------+-----------+---------------+--------------------------------+
| id | estRows | estCost | task | access object | operator info |
+-------------------------+---------+-----------+-----------+---------------+--------------------------------+
| TableReader_7 | 512.00 | 188500.00 | root | partition:p0 | data:Selection_6, row_size: 16 |
| └─Selection_6 | 512.00 | 184320.00 | cop[tikv] | | eq(test.t.a, 0), row_size: 16 |
| └─TableFullScan_5 | 5120.00 | 168960.00 | cop[tikv] | table:t | keep order:false, row_size: 16 |
+-------------------------+---------+-----------+-----------+---------------+--------------------------------+
```

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.