Planner doesn't estimate local aggregate cardinality properly
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
### Minimal reproduce step (Required)
Use TPCH 100 supplier table
```sql
explain analyze select count(S_SUPPKEY), count(distinct S_NAME) from supplier;
+------------------------------------+------------+---------+-------------------+----------------+-----------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------+--------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------+------------+---------+-------------------+----------------+-----------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------+--------+------+
| TableReader_18 | 1.00 | 1 | root | | time:480.1ms, loops:2, cop_task: {num: 2, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_17 | N/A | N/A |
| └─ExchangeSender_17 | 1.00 | 1 | batchCop[tiflash] | | tiflash_task:{time:476.3ms, loops:1, threads:40} | ExchangeType: PassThrough | N/A | N/A |
| └─Projection_13 | 1.00 | 1 | batchCop[tiflash] | | tiflash_task:{time:476.3ms, loops:1, threads:40} | Column#8, Column#9 | N/A | N/A |
| └─HashAgg_14 | 1.00 | 1 | batchCop[tiflash] | | tiflash_task:{time:476.3ms, loops:1, threads:1} | funcs:sum(Column#10)->Column#8, funcs:count(distinct tpch_100.supplier.s_name)->Column#9 | N/A | N/A |
| └─ExchangeReceiver_16 | 1.00 | 1000000 | batchCop[tiflash] | | tiflash_task:{time:346.3ms, loops:2, threads:40} | | N/A | N/A |
| └─ExchangeSender_15 | 1.00 | 1000000 | batchCop[tiflash] | | tiflash_task:{time:274.1ms, loops:2, threads:2} | ExchangeType: PassThrough | N/A | N/A |
| └─HashAgg_6 | 1.00 | 1000000 | batchCop[tiflash] | | tiflash_task:{time:245.4ms, loops:2, threads:2} | group by:tpch_100.supplier.s_name, funcs:count(tpch_100.supplier.s_suppkey)->Column#10 | N/A | N/A |
| └─TableFullScan_12 | 1000000.00 | 1000000 | batchCop[tiflash] | table:supplier | tiflash_task:{time:23.4ms, loops:16, threads:2} | keep order:false | N/A | N/A |
+------------------------------------+------------+---------+-------------------+----------------+-----------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------+--------+------+
8 rows in set (56.00 sec)
```
The cardinality of local aggregate missed so much with the real row number, it always use the cardinality of final aggregate as the cardinality of local aggregate, we end up with always choosing 2 phase aggregate for this kind of query.
Similarly, query with group by aggregate, and group by distinct aggregate may have the same kind of issue.
```sql
explain analyze
select S_NATIONKEY, count(S_SUPPKEY),
count(distinct S_NAME) as cnt_sname
from supplier
group by S_NATIONKEY;
+--------------------------------------+------------+---------+-------------------+----------------+-----------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------------+------------+---------+-------------------+----------------+-----------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| Projection_4 | 25.00 | 25 | root | | time:455.7ms, loops:2, Concurrency:OFF | tpch_100.supplier.s_nationkey, Column#8, Column#9 | 1.11 KB | N/A |
| └─TableReader_25 | 25.00 | 25 | root | | time:455.7ms, loops:2, cop_task: {num: 2, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_24 | N/A | N/A |
| └─ExchangeSender_24 | 25.00 | 25 | batchCop[tiflash] | | tiflash_task:{proc max:453.2ms, min:448.8ms, p80:453.2ms, p95:453.2ms, iters:2, tasks:2, threads:80} | ExchangeType: PassThrough | N/A | N/A |
| └─Projection_20 | 25.00 | 25 | batchCop[tiflash] | | tiflash_task:{proc max:453.2ms, min:448.8ms, p80:453.2ms, p95:453.2ms, iters:2, tasks:2, threads:80} | Column#8, Column#9, tpch_100.supplier.s_nationkey | N/A | N/A |
| └─HashAgg_21 | 25.00 | 25 | batchCop[tiflash] | | tiflash_task:{proc max:452.2ms, min:447.8ms, p80:452.2ms, p95:452.2ms, iters:2, tasks:2, threads:2} | group by:tpch_100.supplier.s_nationkey, funcs:sum(Column#10)->Column#8, funcs:count(distinct tpch_100.supplier.s_name)->Column#9, funcs:firstrow(tpch_100.supplier.s_nationkey)->tpch_100.supplier.s_nationkey | N/A | N/A |
| └─ExchangeReceiver_23 | 25.00 | 1000000 | batchCop[tiflash] | | tiflash_task:{proc max:377.2ms, min:360.8ms, p80:377.2ms, p95:377.2ms, iters:4, tasks:2, threads:80} | | N/A | N/A |
| └─ExchangeSender_22 | 25.00 | 1000000 | batchCop[tiflash] | | tiflash_task:{proc max:339.9ms, min:0s, p80:339.9ms, p95:339.9ms, iters:2, tasks:2, threads:2} | ExchangeType: HashPartition, Hash Cols: [name: tpch_100.supplier.s_nationkey, collate: binary] | N/A | N/A |
| └─HashAgg_7 | 25.00 | 1000000 | batchCop[tiflash] | | tiflash_task:{proc max:289.9ms, min:0s, p80:289.9ms, p95:289.9ms, iters:2, tasks:2, threads:2} | group by:tpch_100.supplier.s_name, tpch_100.supplier.s_nationkey, funcs:count(tpch_100.supplier.s_suppkey)->Column#10 | N/A | N/A |
| └─TableFullScan_19 | 1000000.00 | 1000000 | batchCop[tiflash] | table:supplier | tiflash_task:{proc max:24.8ms, min:0s, p80:24.8ms, p95:24.8ms, iters:16, tasks:2, threads:2} | keep order:false | N/A | N/A |
+--------------------------------------+------------+---------+-------------------+----------------+-----------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
9 rows in set (0.61 sec)
```
Contributor guide
Assessment
This issue has not been assessed yet.