分区智能连接
KingbaseES 有两个参数 enable_partitionwise_join ， enable_partitionwise_aggregate 用于分区智能连接和聚集，默认这两个参数是 off 的。开启这两个参数，对于采用hash join 或 hash aggregate 的执行方式有性能提升。
我们来看以下例子：
1、准备数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | drop table t1;create table t1(id1 integer,name1 text) partition by hash(id1);create table t1_p1 partition of t1 for values with (MODULUS 4, REMAINDER 0); create table t1_p2 partition of t1 for values with (MODULUS 4, REMAINDER 1); create table t1_p3 partition of t1 for values with (MODULUS 4, REMAINDER 2); create table t1_p4 partition of t1 for values with (MODULUS 4, REMAINDER 3); insert into t1 select generate_series(1,10000000),'abc'||generate_series(1,10000000);drop table t2;create table t2(id2 integer,name1 text) partition by hash(id2);create table t2_p1 partition of t2 for values with (MODULUS 4, REMAINDER 0); create table t2_p2 partition of t2 for values with (MODULUS 4, REMAINDER 1); create table t2_p3 partition of t2 for values with (MODULUS 4, REMAINDER 2); create table t2_p4 partition of t2 for values with (MODULUS 4, REMAINDER 3); insert into t2 select generate_series(1,3000000),'abc'||generate_series(1,3000000); |
2、查看执行计划
不使用智能连接：把所有分区的数据取回，以整个表为单位进行hash 及 hash join。因为hash操作是整表，需要的内存更大。下例红色部分 Batches : 8，说明hash 的结果被临时存放在。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | test=# show enable_partitionwise_join ; enable_partitionwise_join --------------------------- off(1 row)test=# explain analyze select count(*) from t1,t2 where id1=id2; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=234428.54..234428.55 rows=1 width=8) (actual time=16961.762..17036.424 rows=1 loops=1) -> Gather (cost=234428.32..234428.53 rows=2 width=8) (actual time=16953.417..17036.393 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=233428.32..233428.33 rows=1 width=8) (actual time=16948.908..16949.002 rows=1 loops=3) -> Parallel Hash Join (cost=55477.00..230303.32 rows=1250000 width=0) (actual time=2828.860..16859.289 rows=1000000 loops=3) Hash Cond: (t1_p4.id1 = t2_p4.id2) -> Parallel Append (cost=0.00..116556.00 rows=4166666 width=4) (actual time=0.030..781.854 rows=3333333 loops=3) -> Parallel Seq Scan on t1_p4 (cost=0.00..23946.36 rows=1042336 width=4) (actual time=0.049..413.020 rows=2501606 loops=1) -> Parallel Seq Scan on t1_p2 (cost=0.00..23934.28 rows=1041828 width=4) (actual time=0.028..415.877 rows=2500388 loops=1) -> Parallel Seq Scan on t1_p1 (cost=0.00..23923.50 rows=1041350 width=4) (actual time=0.027..138.329 rows=833080 loops=3) -> Parallel Seq Scan on t1_p3 (cost=0.00..23918.52 rows=1041152 width=4) (actual time=0.012..391.160 rows=2498765 loops=1) -> Parallel Hash (cost=34969.00..34969.00 rows=1250000 width=4) (actual time=467.790..467.792 rows=1000000 loops=3) Buckets: 1048576 Batches: 8 Memory Usage: 22880kB -> Parallel Append (cost=0.00..34969.00 rows=1250000 width=4) (actual time=0.046..239.138 rows=1000000 loops=3) -> Parallel Seq Scan on t2_p4 (cost=0.00..7190.62 rows=312962 width=4) (actual time=0.097..128.951 rows=751110 loops=1) -> Parallel Seq Scan on t2_p2 (cost=0.00..7188.90 rows=312890 width=4) (actual time=0.026..138.163 rows=750935 loops=1) -> Parallel Seq Scan on t2_p1 (cost=0.00..7169.82 rows=312082 width=4) (actual time=0.009..41.807 rows=249665 loops=3) -> Parallel Seq Scan on t2_p3 (cost=0.00..7169.66 rows=312066 width=4) (actual time=0.010..115.019 rows=748959 loops=1) Planning Time: 0.211 ms Execution Time: 17036.510 ms(21 rows) |
使用智能连接：因为两张表的分区模式是一样的（分区类型、分区列的数据类型、分区数量），能够保证相同的数据在同一分区。因此，只要对应的分区进行连接就可以。由于单个分区的数据量更少，更有可能使用内存排序。本例红色部分batches : 1，也就是所有的hash 的结果都在内存。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | test=# show enable_partitionwise_join ; enable_partitionwise_join --------------------------- on(1 row)test=# explain analyze select count(*) from t1,t2 where id1=id2; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=171275.21..171275.22 rows=1 width=8) (actual time=2432.577..2446.396 rows=1 loops=1) -> Gather (cost=171274.99..171275.20 rows=2 width=8) (actual time=2429.590..2446.387 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=170274.99..170275.00 rows=1 width=8) (actual time=2423.596..2423.618 rows=1 loops=3) -> Parallel Append (cost=11100.02..167149.99 rows=1250000 width=0) (actual time=377.736..2351.993 rows=1000000 loops=3) -> Parallel Hash Join (cost=11102.65..40261.78 rows=312962 width=0) (actual time=393.036..1741.121 rows=751110 loops=1) Hash Cond: (t1_p4.id1 = t2_p4.id2) -> Parallel Seq Scan on t1_p4 (cost=0.00..23946.36 rows=1042336 width=4) (actual time=0.044..406.850 rows=2501606 loops=1) -> Parallel Hash (cost=7190.62..7190.62 rows=312962 width=4) (actual time=387.994..387.995 rows=751110 loops=1) Buckets: 1048576 Batches: 1 Memory Usage: 37600kB -> Parallel Seq Scan on t2_p4 (cost=0.00..7190.62 rows=312962 width=4) (actual time=0.064..140.704 rows=751110 loops=1) -> Parallel Hash Join (cost=11100.02..40244.87 rows=312890 width=0) (actual time=381.391..1729.623 rows=750935 loops=1) Hash Cond: (t1_p2.id1 = t2_p2.id2) -> Parallel Seq Scan on t1_p2 (cost=0.00..23934.28 rows=1041828 width=4) (actual time=0.046..408.713 rows=2500388 loops=1) -> Parallel Hash (cost=7188.90..7188.90 rows=312890 width=4) (actual time=375.787..375.787 rows=750935 loops=1) Buckets: 1048576 Batches: 1 Memory Usage: 37600kB -> Parallel Seq Scan on t2_p2 (cost=0.00..7188.90 rows=312890 width=4) (actual time=0.054..136.383 rows=750935 loops=1) -> Parallel Hash Join (cost=11070.84..40199.75 rows=312082 width=0) (actual time=120.170..558.199 rows=249665 loops=3) Hash Cond: (t1_p1.id1 = t2_p1.id2) -> Parallel Seq Scan on t1_p1 (cost=0.00..23923.50 rows=1041350 width=4) (actual time=0.037..139.094 rows=833080 loops=3) -> Parallel Hash (cost=7169.82..7169.82 rows=312082 width=4) (actual time=117.353..117.369 rows=249665 loops=3) Buckets: 1048576 Batches: 1 Memory Usage: 37536kB -> Parallel Seq Scan on t2_p1 (cost=0.00..7169.82 rows=312082 width=4) (actual time=0.034..41.733 rows=249665 loops=3) -> Parallel Hash Join (cost=11070.49..40193.60 rows=312066 width=0) (actual time=358.778..1698.493 rows=748959 loops=1) Hash Cond: (t1_p3.id1 = t2_p3.id2) -> Parallel Seq Scan on t1_p3 (cost=0.00..23918.52 rows=1041152 width=4) (actual time=0.015..406.430 rows=2498765 loops=1) -> Parallel Hash (cost=7169.66..7169.66 rows=312066 width=4) (actual time=350.709..350.709 rows=748959 loops=1) Buckets: 1048576 Batches: 1 Memory Usage: 37504kB -> Parallel Seq Scan on t2_p3 (cost=0.00..7169.66 rows=312066 width=4) (actual time=0.020..113.071 rows=748959 loops=1) Planning Time: 0.201 ms Execution Time: 2446.453 ms(32 rows) |
3、注意事项
- 关联表的hash分区模式必须完全相同：列类型、长度必须相同；hash 分区数量必须相同；
- 对于hash 分区表，分区数量越多，每个分区数据量越少，在连接时，越可能采用内存hash。
KINGBASE研究院
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单，K8s 太复杂？w7panel 让容器管理更轻松！