unexpected estimate row count using distinct
- 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)
``` SQL
CREATE TABLE `tlbc1a0bbb` (
`col_12` tinyint(1) DEFAULT NULL,
`col_13` date DEFAULT '1995-11-06',
`col_14` timestamp NOT NULL DEFAULT '2027-12-16 00:00:00',
`col_15` tinyint(4) DEFAULT '30',
UNIQUE KEY `idx_3` (`col_15`,`col_14`,`col_12`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY KEY (`col_12`) PARTITIONS 4;
INSERT INTO `tlbc1a0bbb` VALUES(0,'2014-08-19','1973-02-12 00:00:00',107),(1,'1988-11-26','1973-02-12 00:00:00',-46),(0,'2017-10-04','1973-02-12 00:00:00',76),(1,'2018-06-10','1973-02-12 00:00:00',-102),(1,'1991-08-11','1973-02-12 00:00:00',15),(1,'2021-09-23','1973-02-12 00:00:00',112),(0,'1975-10-08','1973-02-12 00:00:00',-120),(0,NULL,'1973-02-12 00:00:00',-16),(0,'2012-06-01','1973-02-12 00:00:00',24),(0,'1998-07-01','1973-02-12 00:00:00',-13),(0,'1994-06-17','1973-02-12 00:00:00',79),(0,'2018-02-13','1973-02-12 00:00:00',-111),(0,'2024-11-18','1973-02-12 00:00:00',-45),(1,'2010-12-12','1973-02-12 00:00:00',1),(0,'1978-01-15','1973-02-12 00:00:00',-113),(0,'1999-12-24','2010-02-10 00:00:00',1),(1,'1971-01-15','1973-02-12 00:00:00',108),(1,'2011-12-11','1998-08-24 00:00:00',-96);
desc format=brief SELECT DISTINCT `tlbc1a0bbb`.`col_12` AS `r0`,`tlbc1a0bbb`.`col_12` AS `r1` FROM `tlbc1a0bbb` L
IMIT 6889;
```
### 2. What did you expect to see? (Required)
The estimated row should be reasonable.
### 3. What did you see instead (Required)
``` SQL
mysql> desc format=brief SELECT DISTINCT `tlbc1a0bbb`.`col_12` AS `r0`,`tlbc1a0bbb`.`col_12` AS `r1` FROM `tlbc1a0bbb` L
IMIT 6889
+---------------------------------+----------+-----------+---------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------------+----------+-----------+---------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Limit | 32028.80 | root | | offset:0, count:6889196 |
| └─HashAgg | 32028.80 | root | | group by:test.tlbc1a0bbb.col_12, test.tlbc1a0bbb.col_12, funcs:firstrow(Column#6)->test.tlbc1a0bbb.col_12, funcs:firstrow(Column#7)->test.tlbc1a0bbb.col_12 |
| └─PartitionUnion | 16014.40 | root | | |
| ├─HashAgg | 8000.00 | root | | group by:test.tlbc1a0bbb.col_12, test.tlbc1a0bbb.col_12, funcs:firstrow(test.tlbc1a0bbb.col_12)->Column#6, funcs:firstrow(test.tlbc1a0bbb.col_12)->Column#7, funcs:firstrow(test.tlbc1a0bbb.col_12)->test.tlbc1a0bbb.col_12, funcs:firstrow(test.tlbc1a0bbb.col_12)->test.tlbc1a0bbb.col_12 |
| │ └─IndexReader | 8000.00 | root | | index:HashAgg |
| │ └─HashAgg | 8000.00 | cop[tikv] | | group by:test.tlbc1a0bbb.col_12, test.tlbc1a0bbb.col_12, |
| │ └─IndexFullScan | 10000.00 | cop[tikv] | table:tlbc1a0bbb, partition:p0, index:idx_3(col_15, col_14, col_12) | keep order:false, stats:pseudo |
| ├─HashAgg | 8.80 | root | | group by:test.tlbc1a0bbb.col_12, test.tlbc1a0bbb.col_12, funcs:firstrow(test.tlbc1a0bbb.col_12)->Column#6, funcs:firstrow(test.tlbc1a0bbb.col_12)->Column#7, funcs:firstrow(test.tlbc1a0bbb.col_12)->test.tlbc1a0bbb.col_12, funcs:firstrow(test.tlbc1a0bbb.col_12)->test.tlbc1a0bbb.col_12 |
| │ └─IndexReader | 8.80 | root | | index:HashAgg |
| │ └─HashAgg | 8.80 | cop[tikv] | | group by:test.tlbc1a0bbb.col_12, test.tlbc1a0bbb.col_12, |
| │ └─IndexFullScan | 11.00 | cop[tikv] | table:tlbc1a0bbb, partition:p1, index:idx_3(col_15, col_14, col_12) | keep order:false, stats:pseudo |
| ├─HashAgg | 8000.00 | root | | group by:test.tlbc1a0bbb.col_12, test.tlbc1a0bbb.col_12, funcs:firstrow(test.tlbc1a0bbb.col_12)->Column#6, funcs:firstrow(test.tlbc1a0bbb.col_12)->Column#7, funcs:firstrow(test.tlbc1a0bbb.col_12)->test.tlbc1a0bbb.col_12, funcs:firstrow(test.tlbc1a0bbb.col_12)->test.tlbc1a0bbb.col_12 |
| │ └─IndexReader | 8000.00 | root | | index:HashAgg |
| │ └─HashAgg | 8000.00 | cop[tikv] | | group by:test.tlbc1a0bbb.col_12, test.tlbc1a0bbb.col_12, |
| │ └─IndexFullScan | 10000.00 | cop[tikv] | table:tlbc1a0bbb, partition:p2, index:idx_3(col_15, col_14, col_12) | keep order:false, stats:pseudo |
| └─HashAgg | 5.60 | root | | group by:test.tlbc1a0bbb.col_12, test.tlbc1a0bbb.col_12, funcs:firstrow(test.tlbc1a0bbb.col_12)->Column#6, funcs:firstrow(test.tlbc1a0bbb.col_12)->Column#7, funcs:firstrow(test.tlbc1a0bbb.col_12)->test.tlbc1a0bbb.col_12, funcs:firstrow(test.tlbc1a0bbb.col_12)->test.tlbc1a0bbb.col_12 |
| └─IndexReader | 7.00 | root | | index:IndexFullScan |
| └─IndexFullScan | 7.00 | cop[tikv] | table:tlbc1a0bbb, partition:p3, index:idx_3(col_15, col_14, col_12) | keep order:false, stats:pseudo |
+---------------------------------+----------+-----------+---------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
18 rows in set, 1 warning (0.03 sec)
```
The estimated row of HashAgg is greater than PartitionUnion.
### 4. What is your TiDB version? (Required)
1fc92b32b05e3f2f04b9d321a9e224b9125a4a82
Contributor guide
Assessment
This issue has not been assessed yet.