pingcap / pingcap/tidb

count(*) on a cross join costs too much time.

Open
#35,101 0 comments 0 reactions 0 assignees View on GitHub
type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement

3 nodes, TPC-H SF 100.

```
mysql> explain analyze SELECT /*+ hash_agg */ count(*) FROM orders join lineitem on l_orderkey != o_orderkey and l_partkey<100 and o_custkey <10000;
+----------------------------------------+--------------+-----------+--------------+----------------+-------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------------+--------------+-----------+--------------+----------------+-------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------+---------+------+
| StreamAgg_16 | 1.00 | 1 | root | | time:9.91s, loops:2 | funcs:count(1)->Column#27 | 33.5 KB | N/A |
| └─TableReader_27 | 0.00 | 300967031 | root | | time:605.4ms, loops:295036, cop_task: {num: 2575, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_26 | N/A | N/A |
| └─ExchangeSender_26 | 0.00 | 300967031 | cop[tiflash] | | tiflash_task:{proc max:8.96s, min:8.21s, p80:8.96s, p95:8.96s, iters:2572, tasks:3, threads:60} | ExchangeType: PassThrough | N/A | N/A |
| └─HashJoin_17 | 0.00 | 300967031 | cop[tiflash] | | tiflash_task:{proc max:597.6ms, min:509.9ms, p80:597.6ms, p95:597.6ms, iters:2572, tasks:3, threads:60} | CARTESIAN inner join, other cond:ne(tpch_100.lineitem.l_orderkey, tpch_100.orders.o_orderkey) | N/A | N/A |
| ├─ExchangeReceiver_23(Build) | 0.00 | 9054 | cop[tiflash] | | tiflash_task:{proc max:86.9ms, min:85.6ms, p80:86.9ms, p95:86.9ms, iters:60, tasks:3, threads:60} | | N/A | N/A |
| │ └─ExchangeSender_22 | 0.00 | 3018 | cop[tiflash] | | tiflash_task:{proc max:78.1ms, min:0s, p80:78.1ms, p95:78.1ms, iters:2847, tasks:3, threads:60} | ExchangeType: Broadcast | N/A | N/A |
| │ └─Selection_21 | 0.00 | 3018 | cop[tiflash] | | tiflash_task:{proc max:76.1ms, min:0s, p80:76.1ms, p95:76.1ms, iters:2847, tasks:3, threads:60} | lt(tpch_100.lineitem.l_partkey, 100) | N/A | N/A |
| │ └─TableFullScan_20 | 600037902.00 | 24237064 | cop[tiflash] | table:lineitem | tiflash_task:{proc max:74.1ms, min:0s, p80:74.1ms, p95:74.1ms, iters:2847, tasks:3, threads:60} | keep order:false | N/A | N/A |
| └─Selection_25(Probe) | 106018.84 | 99724 | cop[tiflash] | | tiflash_task:{proc max:83.9ms, min:60.8ms, p80:83.9ms, p95:83.9ms, iters:2572, tasks:3, threads:60} | lt(tpch_100.orders.o_custkey, 10000) | N/A | N/A |
| └─TableFullScan_24 | 150000000.00 | 149437771 | cop[tiflash] | table:orders | tiflash_task:{proc max:78.9ms, min:50.8ms, p80:78.9ms, p95:78.9ms, iters:2572, tasks:3, threads:60} | keep order:false | N/A | N/A |
+----------------------------------------+--------------+-----------+--------------+----------------+-------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------+---------+------+
10 rows in set, 1 warning (9.97 sec)

mysql> explain analyze SELECT /*+ hash_agg() */ count(*) FROM orders join lineitem on l_orderkey != o_orderkey and l_partkey<100 and o_custkey <10000;
+----------------------------------------+--------------+-----------+--------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------------+--------------+-----------+--------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------+----------+------+
| HashAgg_13 | 1.00 | 1 | root | | time:9.44s, loops:2, partial_worker:{wall_time:9.439879194s, concurrency:5, task_num:295035, tot_wait:18.143971508s, tot_exec:28.962648303s, tot_time:47.199047123s, max:9.439833447s, p95:9.439833447s}, final_worker:{wall_time:9.43990018s, concurrency:5, task_num:5, tot_wait:47.199268049s, tot_exec:46.374µs, tot_time:47.199319801s, max:9.439873943s, p95:9.439873943s} | funcs:count(1)->Column#27 | 416.9 KB | N/A |
| └─TableReader_25 | 0.00 | 300967031 | root | | time:3.85s, loops:295036, cop_task: {num: 2575, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_24 | N/A | N/A |
| └─ExchangeSender_24 | 0.00 | 300967031 | cop[tiflash] | | tiflash_task:{proc max:9.09s, min:7.94s, p80:9.09s, p95:9.09s, iters:2572, tasks:3, threads:60} | ExchangeType: PassThrough | N/A | N/A |
| └─HashJoin_15 | 0.00 | 300967031 | cop[tiflash] | | tiflash_task:{proc max:611.1ms, min:543.1ms, p80:611.1ms, p95:611.1ms, iters:2572, tasks:3, threads:60} | CARTESIAN inner join, other cond:ne(tpch_100.lineitem.l_orderkey, tpch_100.orders.o_orderkey) | N/A | N/A |
| ├─ExchangeReceiver_21(Build) | 0.00 | 9054 | cop[tiflash] | | tiflash_task:{proc max:86.1ms, min:84.6ms, p80:86.1ms, p95:86.1ms, iters:60, tasks:3, threads:60} | | N/A | N/A |
| │ └─ExchangeSender_20 | 0.00 | 3018 | cop[tiflash] | | tiflash_task:{proc max:73.8ms, min:0s, p80:73.8ms, p95:73.8ms, iters:2847, tasks:3, threads:60} | ExchangeType: Broadcast | N/A | N/A |
| │ └─Selection_19 | 0.00 | 3018 | cop[tiflash] | | tiflash_task:{proc max:71.1ms, min:0s, p80:71.1ms, p95:71.1ms, iters:2847, tasks:3, threads:60} | lt(tpch_100.lineitem.l_partkey, 100) | N/A | N/A |
| │ └─TableFullScan_18 | 600037902.00 | 24237064 | cop[tiflash] | table:lineitem | tiflash_task:{proc max:70.1ms, min:0s, p80:70.1ms, p95:70.1ms, iters:2847, tasks:3, threads:60} | keep order:false | N/A | N/A |
| └─Selection_23(Probe) | 106018.84 | 99724 | cop[tiflash] | | tiflash_task:{proc max:82.1ms, min:65.1ms, p80:82.1ms, p95:82.1ms, iters:2572, tasks:3, threads:60} | lt(tpch_100.orders.o_custkey, 10000) | N/A | N/A |
| └─TableFullScan_22 | 150000000.00 | 149437771 | cop[tiflash] | table:orders | tiflash_task:{proc max:69.1ms, min:53.1ms, p80:69.1ms, p95:69.1ms, iters:2572, tasks:3, threads:60} | keep order:false | N/A | N/A |
+----------------------------------------+--------------+-----------+--------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------+----------+------+
10 rows in set (9.49 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.