pingcap / pingcap/tidb

Planner doesn't estimate local aggregate cardinality properly

Open
#35,420 0 comments 0 reactions 2 assignees Claimed by @time-and-fate View on GitHub
epic/cardinality-estimation severity/moderate sig/planner type/bug
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

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.