Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 执行计划显示hashagg和build的疑问?
[TiDB Usage Environment] Test
[TiDB Version] 6.5.3
[Reproduction Path] What operations were performed to encounter the issue
[Encountered Issue: Problem Phenomenon and Impact]
Hello teachers,
While studying the execution plan of TiDB, I have the following questions. The data source is from TPC-H.
mysql> explain analyze select * from customer where C_CUSTKEY in (select O_CUSTKEY from orders limit 1);
+----------------------------------+---------+---------+-----------+-----------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------+---------+---------+-----------+-----------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| IndexHashJoin_16 | 1.00 | 1 | root | | time:2.73ms, loops:2, inner:{total:1.05ms, concurrency:5, task:1, construct:12.1µs, fetch:1.03ms, build:3.69µs, join:4.19µs} | inner join, inner:TableReader_11, outer key:tpch2.orders.o_custkey, inner key:tpch2.customer.c_custkey, equal cond:eq(tpch2.orders.o_custkey, tpch2.customer.c_custkey) | 146.4 KB | N/A |
| ├─HashAgg_21(Build) | 1.00 | 1 | root | | time:1.53ms, loops:3 | group by:tpch2.orders.o_custkey, funcs:firstrow(tpch2.orders.o_custkey)->tpch2.orders.o_custkey | 898 Bytes | 0 Bytes |
| │ └─Limit_22 | 1.00 | 1 | root | | time:1.52ms, loops:2 | offset:0, count:1 | N/A | N/A |
| │ └─IndexReader_27 | 1.00 | 1 | root | | time:1.51ms, loops:1, cop_task: {num: 1, max: 1.57ms, proc_keys: 1, rpc_num: 1, rpc_time: 1.54ms, copr_cache: disabled, distsql_concurrency: 1} | index:Limit_26 | 241 Bytes | N/A |
| │ └─Limit_26 | 1.00 | 1 | cop[tikv] | | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 46, total_keys: 2, get_snapshot_time: 601.8µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 5}}} | offset:0, count:1 | N/A | N/A |
| │ └─IndexFullScan_25 | 1.00 | 1 | cop[tikv] | table:orders, index:indexO_CUSTKEY(O_CUSTKEY) | tikv_task:{time:0s, loops:1} | keep order:false | N/A | N/A |
| └─TableReader_11(Probe) | 1.00 | 1 | root | | time:951.8µs, loops:2, cop_task: {num: 1, max: 907µs, proc_keys: 1, rpc_num: 1, rpc_time: 888.3µs, copr_cache: disabled, distsql_concurrency: 15} | data:TableRangeScan_10 | N/A | N/A |
| └─TableRangeScan_10 | 1.00 | 1 | cop[tikv] | table:customer | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 184, total_keys: 1, get_snapshot_time: 407.4µs, rocksdb: {block: {cache_hit_count: 5}}} | range: decided by [tpch2.orders.o_custkey], keep order:false | N/A | N/A |
+----------------------------------+---------+---------+-----------+-----------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
8 rows in set (0.00 sec)
I have the following two questions:
- For this kind of
limit 1
statement, why does thehashagg
operator appear? - Why does the
hashagg
operator have abuild
display? Is thisbuild
related to theIndexHashJoin
operator above?
mysql> explain analyze select O_CUSTKEY from orders limit 1;
+--------------------------+---------+---------+-----------+-----------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------+---------+---------+-----------+-----------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+---------+------+
| Limit_6 | 1.00 | 1 | root | | time:1.59ms, loops:2 | offset:0, count:1 | N/A | N/A |
| └─IndexReader_11 | 1.00 | 32 | root | | time:1.59ms, loops:1, cop_task: {num: 1, max: 1.44ms, proc_keys: 224, rpc_num: 1, rpc_time: 1.41ms, copr_cache: disabled, distsql_concurrency: 15} | index:IndexFullScan_10 | 4.19 KB | N/A |
| └─IndexFullScan_10 | 1.00 | 224 | cop[tikv] | table:orders, index:indexO_CUSTKEY(O_CUSTKEY) | tikv_task:{time:0s, loops:3}, scan_detail: {total_process_keys: 224, total_process_keys_size: 10304, total_keys: 225, get_snapshot_time: 611.7µs, rocksdb: {key_skipped_count: 224, block: {cache_hit_count: 6}}} | keep order:false | N/A | N/A |
+--------------------------+---------+---------+-----------+-----------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------+---------+------+
3 rows in set (0.00 sec)