Greenplum , HAWQ outer join与motion问题讲解

Greenplum , HAWQ outer join与motion问题讲解

作者

digoal

日期

2016-09-05

标签

PostgreSQL , HAWQ , Greenplum , OUTER JOIN , Motion

背景

Greenplum，HAWQ是分布式的数据库，在建表时，我们可以选择分布列，或者选择随机分布。

多个表做等值JOIN时，如果JOIN列为分布列，则不需要进行数据的重分布。

但是，如果使用的是OUTER JOIN，情况就不一样了，你可能会发现多个表进行outer join时，如果JOIN列都是HASH分布列，某些写法就可能导致需要重分布。

下面给大家分析一下原因。

创建几张测试表

其中tab1,tab2,tab3的bucketnum一致，分布列一致。

tab4,tab5,tab6的分布列与前面几张表一致，但是bucketnum不一致（所以显然HASH取模后的值也不一致）。

bucketnum默认是实体segments的6倍（意思是每台实体segment上跑6个虚拟segment），用户可以根据表的大小来调试，例如要发挥最大的计算能力，实际设置时可以设置为主机CPU核数的0.8，小表则建议设置为较小的值。

tab7与tab8为随机分布，对于随机分布的表，bucketnum设置会忽略，在实体segments之间随机分布(查看hdfs对应的文件也能看出端倪)。

postgres=# create table tab1(c1 int, c2 int, c3 text, c4 timestamp) with (bucketnum=6) distributed by(c1,c2);
postgres=# create table tab2(c1 int, c2 int, c3 text, c4 timestamp) with (bucketnum=6) distributed by(c1,c2);
postgres=# create table tab3(c1 int, c2 int, c3 text, c4 timestamp) with (bucketnum=6) distributed by(c1,c2);
postgres=# create table tab4(c1 int, c2 int, c3 text, c4 timestamp) with (bucketnum=10) distributed by(c1,c2);
postgres=# create table tab5(c1 int, c2 int, c3 text, c4 timestamp) with (bucketnum=12) distributed by(c1,c2);
postgres=# create table tab6(c1 int, c2 int, c3 text, c4 timestamp) with (bucketnum=3) distributed by(c1,c2);
postgres=# create table tab7(c1 int, c2 int, c3 text, c4 timestamp) with (bucketnum=3) distributed randomly;
postgres=# create table tab8(c1 int, c2 int, c3 text, c4 timestamp) with (bucketnum=6) distributed randomly;
postgres=# insert into tab7 select generate_series(1,1000000),generate_series(1,1000000),'test',now();
INSERT 0 1000000
postgres=# insert into tab8 select generate_series(1,1000000),generate_series(1,1000000),'test',now();
INSERT 0 1000000
postgres=# analyze tab7;
ANALYZE
postgres=# analyze tab8;
ANALYZE

下面开始几组测试，通过执行计划的motion node，观察query是否需要重分布，以及重分布那张表，重分布的键值是哪些。

测试1

HAWQ的hash分布取模值取决于bucket num，如果bucket num不一致，则JOIN时有一张表需要重分布

postgres=# explain select * from tab1 join tab6 on (tab1.c1=tab6.c1 and tab1.c2=tab6.c2);
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 6:1  (slice2; segments: 6)  (cost=0.00..862.00 rows=1 width=48)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=48)
         Hash Cond: tab1.c1 = tab6.c1 AND tab1.c2 = tab6.c2
         ->  Table Scan on tab1  (cost=0.00..431.00 rows=1 width=24)
         ->  Hash  (cost=431.00..431.00 rows=1 width=24)
               ->  Redistribute Motion 6:6  (slice1; segments: 6)  (cost=0.00..431.00 rows=1 width=24)    重分布tab6
                     Hash Key: tab6.c1, tab6.c2
                     ->  Table Scan on tab6  (cost=0.00..431.00 rows=1 width=24)
 Settings:  default_hash_table_bucket_number=6
 Optimizer status: PQO version 1.638
(10 rows)

测试2

如果JOIN的其中一个表为随机分布式，随机分布的表需要复制或按JOIN列进行重分布

postgres=# explain select * from tab6 join tab7 on (tab6.c1=tab7.c1 and tab6.c2=tab7.c2);
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=0.00..862.00 rows=1 width=48)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=48)
         Hash Cond: tab6.c1 = tab7.c1 AND tab6.c2 = tab7.c2
         ->  Table Scan on tab6  (cost=0.00..431.00 rows=1 width=24)
         ->  Hash  (cost=431.00..431.00 rows=1 width=24)
               ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=24)  重分布tab7
                     Hash Key: tab7.c1, tab7.c2
                     ->  Table Scan on tab7  (cost=0.00..431.00 rows=1 width=24)
 Settings:  default_hash_table_bucket_number=6
 Optimizer status: PQO version 1.638
(10 rows)

测试3

随机分布策略的表，在每个segment上只有一个bucket

postgres=# explain select count(*) from tab7;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Aggregate  (cost=0.00..452.11 rows=1 width=8)
   ->  Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..452.11 rows=1 width=8)
         ->  Aggregate  (cost=0.00..452.11 rows=1 width=8)
               ->  Table Scan on tab7  (cost=0.00..450.25 rows=1000000 width=1)
 Settings:  default_hash_table_bucket_number=6
 Optimizer status: PQO version 1.638
(6 rows)

测试4

如果两个随机分布的表JOIN，数据需要在所有的实体segments(每个datanode对应一个实体segment)上按JOIN列重分布

postgres=# explain select * from tab8 join tab7 on (tab8.c1=tab7.c1 and tab8.c2=tab7.c2);
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Gather Motion 1:1  (slice3; segments: 1)  (cost=0.00..2151.49 rows=10000 width=42)
   ->  Hash Join  (cost=0.00..2148.64 rows=10000 width=42)
         Hash Cond: tab8.c1 = tab7.c1 AND tab8.c2 = tab7.c2
         ->  Redistribute Motion 1:1  (slice1; segments: 1)  (cost=0.00..555.04 rows=1000000 width=21)  重分布tab8
               Hash Key: tab8.c1, tab8.c2
               ->  Table Scan on tab8  (cost=0.00..450.25 rows=1000000 width=21)
         ->  Hash  (cost=555.04..555.04 rows=1000000 width=21)
               ->  Redistribute Motion 1:1  (slice2; segments: 1)  (cost=0.00..555.04 rows=1000000 width=21)  重分布tab7
                     Hash Key: tab7.c1, tab7.c2
                     ->  Table Scan on tab7  (cost=0.00..450.25 rows=1000000 width=21)
 Settings:  default_hash_table_bucket_number=6
 Optimizer status: PQO version 1.638
(12 rows)

测试5

outer join，2张表的outer join，如果JOIN列就是分布列，不需要重分布

postgres=# explain select * from tab1 left join tab2 on (tab1.c1=tab2.c1 and tab1.c2=tab2.c2);
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 6:1  (slice1; segments: 6)  (cost=0.00..862.00 rows=2 width=48)
   ->  Hash Left Join  (cost=0.00..862.00 rows=1 width=48)
         Hash Cond: tab1.c1 = tab2.c1 AND tab1.c2 = tab2.c2
         ->  Table Scan on tab1  (cost=0.00..431.00 rows=1 width=24)
         ->  Hash  (cost=431.00..431.00 rows=1 width=24)
               ->  Table Scan on tab2  (cost=0.00..431.00 rows=1 width=24)
 Settings:  default_hash_table_bucket_number=6
 Optimizer status: PQO version 1.638
(8 rows)

测试6

outer join，3张表的outer join，需要注意JOIN的条件

1.
tab1与tab2 left join后，关联不上时tab2可能会返回一些NULL值

因此再次与tab3 join时，如果JOIN条件是tab2与tab3，则不能圈定在虚拟segment内完成tab2与tab3的JOIN，必须要对tab1与tab2的outer JOIN中间结果进行重分布，对齐tab3的分布策略，再进行JOIN。

但是实际上，并不需要重分布，因为null和null比较返回的还是null，所以null实际上不需要重分布到一起去JOIN，本条SQL，对于HAWQ的优化器来说，是有优化余地的。

postgres=# explain select * from tab1 left join tab2 on (tab1.c1=tab2.c1 and tab1.c2=tab2.c2) left join tab3 on (tab2.c1=tab3.c1 and tab2.c2=tab3.c2);
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Gather Motion 6:1  (slice2; segments: 6)  (cost=0.00..1293.00 rows=3 width=72)
   ->  Hash Left Join  (cost=0.00..1293.00 rows=1 width=72)
         Hash Cond: tab2.c1 = tab3.c1 AND tab2.c2 = tab3.c2
         ->  Redistribute Motion 6:6  (slice1; segments: 6)  (cost=0.00..862.00 rows=1 width=48)
               Hash Key: tab2.c1, tab2.c2
               ->  Hash Left Join  (cost=0.00..862.00 rows=1 width=48)
                     Hash Cond: tab1.c1 = tab2.c1 AND tab1.c2 = tab2.c2
                     ->  Table Scan on tab1  (cost=0.00..431.00 rows=1 width=24)
                     ->  Hash  (cost=431.00..431.00 rows=1 width=24)
                           ->  Table Scan on tab2  (cost=0.00..431.00 rows=1 width=24)
         ->  Hash  (cost=431.00..431.00 rows=1 width=24)
               ->  Table Scan on tab3  (cost=0.00..431.00 rows=1 width=24)
 Settings:  default_hash_table_bucket_number=6
 Optimizer status: PQO version 1.638
(14 rows)

2.
这样的条件下，则不需要重分布，因为第一次LEFT JOIN后，TAB1不会产生空值，使用tab1再与tab3进行join也不需要重分布。

postgres=# explain select * from tab1 left join tab2 on (tab1.c1=tab2.c1 and tab1.c2=tab2.c2) left join tab3 on (tab1.c1=tab3.c1 and tab1.c2=tab3.c2);
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Gather Motion 6:1  (slice1; segments: 6)  (cost=0.00..1293.00 rows=3 width=72)
   ->  Hash Left Join  (cost=0.00..1293.00 rows=1 width=72)
         Hash Cond: tab1.c1 = tab3.c1 AND tab1.c2 = tab3.c2
         ->  Hash Left Join  (cost=0.00..862.00 rows=1 width=48)
               Hash Cond: tab1.c1 = tab2.c1 AND tab1.c2 = tab2.c2
               ->  Table Scan on tab1  (cost=0.00..431.00 rows=1 width=24)
               ->  Hash  (cost=431.00..431.00 rows=1 width=24)
                     ->  Table Scan on tab2  (cost=0.00..431.00 rows=1 width=24)
         ->  Hash  (cost=431.00..431.00 rows=1 width=24)
               ->  Table Scan on tab3  (cost=0.00..431.00 rows=1 width=24)
 Settings:  default_hash_table_bucket_number=6
 Optimizer status: PQO version 1.638
(12 rows)

3.
如果第三张关联表是JOIN条件，而非OUTER JOIN，同样不需要重分布。

postgres=# explain select * from tab1 left join tab2 on (tab1.c1=tab2.c1 and tab1.c2=tab2.c2) join tab3 on (tab2.c1=tab3.c1 and tab2.c2=tab3.c2);
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Gather Motion 6:1  (slice1; segments: 6)  (cost=0.00..1293.00 rows=1 width=72)
   ->  Hash Join  (cost=0.00..1293.00 rows=1 width=72)
         Hash Cond: tab2.c1 = tab3.c1 AND tab2.c2 = tab3.c2 AND tab1.c1 = tab3.c1 AND tab1.c2 = tab3.c2
         ->  Hash Join  (cost=0.00..862.00 rows=1 width=48)
               Hash Cond: tab1.c1 = tab2.c1 AND tab1.c2 = tab2.c2
               ->  Table Scan on tab1  (cost=0.00..431.00 rows=1 width=24)
               ->  Hash  (cost=431.00..431.00 rows=1 width=24)
                     ->  Table Scan on tab2  (cost=0.00..431.00 rows=1 width=24)
         ->  Hash  (cost=431.00..431.00 rows=1 width=24)
               ->  Table Scan on tab3  (cost=0.00..431.00 rows=1 width=24)
 Settings:  default_hash_table_bucket_number=6
 Optimizer status: PQO version 1.638
(12 rows)

4.
只有JOIN时，也不需要考虑重分布。

postgres=# explain select * from tab1 join tab2 on (tab1.c1=tab2.c1 and tab1.c2=tab2.c2) join tab3 on (tab2.c1=tab3.c1 and tab2.c2=tab3.c2);
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Gather Motion 6:1  (slice1; segments: 6)  (cost=0.00..1293.00 rows=1 width=72)
   ->  Hash Join  (cost=0.00..1293.00 rows=1 width=72)
         Hash Cond: tab2.c1 = tab3.c1 AND tab2.c2 = tab3.c2 AND tab1.c1 = tab3.c1 AND tab1.c2 = tab3.c2
         ->  Hash Join  (cost=0.00..862.00 rows=1 width=48)
               Hash Cond: tab1.c1 = tab2.c1 AND tab1.c2 = tab2.c2
               ->  Table Scan on tab1  (cost=0.00..431.00 rows=1 width=24)
               ->  Hash  (cost=431.00..431.00 rows=1 width=24)
                     ->  Table Scan on tab2  (cost=0.00..431.00 rows=1 width=24)
         ->  Hash  (cost=431.00..431.00 rows=1 width=24)
               ->  Table Scan on tab3  (cost=0.00..431.00 rows=1 width=24)
 Settings:  default_hash_table_bucket_number=6
 Optimizer status: PQO version 1.638
(12 rows)

小结

1. 随机分布的表与随机分布的表进行JOIN时，可能无法充分利用计算资源，因为每个物理节点只能用到一个核。
2. 随机分布的表与哈希分布的表JOIN时，会根据实际情况，重分布，并行计算。（如果哈希分布的表bucketnum较多，这种QUERY也能用上多核JOIN）。
3. outer join时，如果多次进行，请注意实际的场景逻辑，建议在JOIN时过滤，而不是JOIN完后过滤NULL，以避免重分布。

Count

时间： 2024-11-08 21:16:15

Greenplum , HAWQ outer join与motion问题讲解的相关文章

PostgreSQL vs Greenplum Hash outer join (hash表的选择)

标签 PostgreSQL , Greenplum , hash outer join , hash table 背景 数据分析.大表JOIN.多表JOIN时,哈希JOIN是比较好的提速手段. hash join会首先扫描其中的一张表(包括需要输出的字段),根据JOIN列生成哈希表.然后扫描另一张表. hash join介绍 https://www.postgresql.org/docs/10/static/planner-optimizer.html the right relation is

HTAP数据库 PostgreSQL 场景与性能测试之 4 - (OLAP) 大表OUTER JOIN统计查询

标签 PostgreSQL , HTAP , OLTP , OLAP , 场景与性能测试 背景 PostgreSQL是一个历史悠久的数据库,历史可以追溯到1973年,最早由2014计算机图灵奖得主,关系数据库的鼻祖Michael_Stonebraker 操刀设计,PostgreSQL具备与Oracle类似的功能.性能.架构以及稳定性. PostgreSQL社区的贡献者众多,来自全球各个行业,历经数年,PostgreSQL 每年发布一个大版本,以持久的生命力和稳定性著称. 2017年10月,Pos

图解SQL的inner join、left join、right join、full outer join、union、union all的区别

SQL的Join语法有很多,inner join(等值连接) 只返回两个表中联结字段相等的行,left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录,right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录,看到一篇图解文章,非常清楚简洁的说明了使用JOIN操作后的结果集是什么格式. 假设我们有两张表.Table A 是左边的表.Table B 是右边的表.其各有四条记录,其中有两条记录name是相同的,如下所示: A表 id name 1 P

SQL Server的Inner Join及Outer Join

当然 Join 如何将不同的数据库的资料结合, 还要看你如何使用它, 一共有四种不同的 Join 的方式, 在这篇文章中我们将为你介绍 Inner Join 及 Outer Join 以及其应用. 在一个正规化的数据库环境中, 我们常会碰到这款情形: 所需的资料并不是放在同一个资料表中, 在这个时候, 你就要用到 Join. 当然 Join 如何将不同的数据库的资料结合, 还要看你如何使用它, 一共有四种不同的 Join 的方式, 在这篇文章中我们将为你介绍 Inner Join 及 Outer

hive left outer join的问题

最近BA用户反馈有两句看似很像的语句返回的结果数不一样,比较奇怪,怀疑是不是Hive的Bug Query 1 返回结果数6071 select count(distinct reviewid) as dis_reviewcnt from (select a.reviewid from bi.dpods_dp_reviewreport a left outer join bi.dpods_dp_reviewlog b on a.reviewid=b.reviewid and b.hp_statda

Greenplum,HAWQ interval parser带来的问题 - TPCH 测试注意啦

Greenplum,HAWQ interval parser带来的问题 - TPCH 测试注意啦 作者 digoal 日期 2016-10-11 标签 Greenplum , PostgreSQL , interval , parser 背景 interval是用来表达时间间隔的数据类型,比如1年,或者1分钟,或者1天零多少小时分钟等. postgres=# select interval '100 year 2 month 1 day 1:00:01.11'; interval -------

SQL Server 2008 R2——使用FULL OUTER JOIN实现多表信息汇总

原文:SQL Server 2008 R2--使用FULL OUTER JOIN实现多表信息汇总 =================================版权声明================================= 版权声明:原创文章 谢绝转载  请通过右侧公告中的"联系邮箱(wlsandwho@foxmail.com)"联系我 勿用于学术性引用. 勿用于商业出版.商业印刷.商业引用以及其他商业用途.       本文不定期修正完善. 本文链接:http://w

sql server Right Outer Join用法

Right Outer Join 运算符返回满足第二个(底端)输入与第一个(顶端)输入的每个匹配行的联接的每行.此外,它还返回第二个输入中在第一个输入中没有匹配行的任何行,即与 NULL 联接.如果 Argument 列内不存在任何联接谓词,则每行都是一个匹配行. 一.联接( join) 通过联接,可以根据各个表之间的逻辑关系从两个或多个表中检索数据.联接表示应如何使用一个表中的数据来选择另一个表中的行. 联接条件通过以下方法定义两个表在查询中的关联方式: 指定每个表中要用于联接的列.典型的联接

[20120223]full outer join.txt

full outer join 也就是包括左连接以及右连接,然后去除重复的记录.11g改进了算法,演示如下: SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionPL/S