了解更多Greenplum技术干货,欢迎访问Greenplum中文社区网站
2019年10月15日,Pivotal中国研发中心副总经理兼Greenplum中文社区发起人姚延栋出席了于意大利举行的PostgreSQL Conference Europe并发表了精彩的演讲《How does Hash Join work in PostgreSQL and its derivates》。本文根据演讲内容整理而成,供大家学习交流。
今天我将详细介绍PostgreSQL和Greenplum的Hashjoin。之所以会选择Hashjoin这个话题,是因为HashJoin是处理OLAP或者是分析型查询(analytics queries)的重要武器。首先,我们来看看 PostgreSQL 中的 Hashjoin 实现。
在介绍HashJoin实现之前,首先了解下什么是 JOIN。根据维基百科(WIKIPedia),JOIN是关系数据库中组合一个或者多个表中的columns的算子。
而JOIN 有多种类型, SQL 标准中定义了 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN 等四种类型,用集合论里的操作非常容易理解。 我们在下图直观的解释了这四种JOIN类型的效用。
此外还有其中JOIN类型,譬如 SEMI JOIN和 ANTI JOIN。 这两种JOIN不是 SQL 语法,然而通常用来实现某些 SQL 功能,后面会详细介绍他们。
本文将使用这样一个例子。 例子里面包含两张表:student表和score表,每张表都有几条记录。
首先我们看下图中JOIN对应的 SQL语句。 通过 explain 可以查看这6个SQL例子的JOIN类型。 (需要设置 enable_mergejoin, enable_hashagg 为 OFF,否则优化器可能会选择其他查询计划)
下图展示了查询结果,让我们对图中JOIN的作用有个直观的认知。
JOIN 有三种经典的实现算法:Nested Loop、Merge JOIN、Hash Join。他们各有优缺点,譬如 Nest loop 通常性能不好,但是适用于任何类型的JOIN;Merge join对预排序的数据性能非常好;HashJoin对大数据量通常性能最好,但是只能处理equijoin,而不能处理譬如 c1 > c2 这样的join条件。
Hashjoin 是一个经典算法,它包含2个phases,一个是 build phase,理想情况下对小表构建 hash table,该表通常也称为 inner table;第二个phase为 probe phase,扫描关联的另一张表的内容,并通过hash table 探测是否有匹配的行/元组,该表通常称为 outer table。