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

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介:

标签

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 first scanned and loaded into a hash table, using its join attributes as hash keys.

Next the left relation is scanned and the appropriate values of every row found are used as hash keys to locate the matching rows in the table.

hash table的选择

理论上应该选择小表作为哈希表。但是2011年以前的版本,对HASH表的选择是有讲究的,并不是自由选择,只支OUTER JOIN时返回可以为空的表生成哈希表。

hash join演进

PostgreSQL在1997年的时候已经支持HashJoin,Greenplum基于PostgreSQL 8.2开发,因此也是天然支持HashJoin的。

在2011年时,PostgreSQL对hashjoin做出了两个改进,支持full outer join,同时支持outer join任意表生成哈希表(原来的版本只支OUTER JOIN时返回可以为空的表生成哈希表):

https://www.postgresql.org/docs/current/static/release-9-1.html

Allow FULL OUTER JOIN to be implemented as a hash join, and allow either side of a LEFT OUTER JOIN or RIGHT OUTER JOIN to be hashed (Tom Lane)

Previously FULL OUTER JOIN could only be implemented as a merge join, and LEFT OUTER JOIN and RIGHT OUTER JOIN could hash only the nullable side of the join.

These changes provide additional query optimization possibilities.

对应patch

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f4e4b3274317d9ce30de7e7e5b04dece7c4e1791

这个改进非常有意义,特别是可以为空的表非常庞大时,作为哈希表是不合适的。后面就来对比一下。

几种JOIN介绍

INNER JOIN

For each row R1 of T1, the joined table has a row for each row in T2 that satisfies the join condition with R1.

LEFT OUTER JOIN

First, an inner join is performed.

Then, for each row in T1 that does not satisfy the join condition with any row in T2,

a joined row is added with null values in columns of T2.

Thus, the joined table always has at least one row for each row in T1.

RIGHT OUTER JOIN

First, an inner join is performed.

Then, for each row in T2 that does not satisfy the join condition with any row in T1,

a joined row is added with null values in columns of T1.

This is the converse of a left join: the result table will always have a row for each row in T2.

FULL OUTER JOIN

First, an inner join is performed.

Then, for each row in T1 that does not satisfy the join condition with any row in T2,

a joined row is added with null values in columns of T2.

Also, for each row of T2 that does not satisfy the join condition with any row in T1,

a joined row with null values in the columns of T1 is added.

PostgreSQL vs Greenplum outer join 对比

left\right outer join

PostgreSQL 9.1+

postgres=# create table t1(id int, info text);  
CREATE TABLE  
postgres=# create table t2(id int, info text);  
CREATE TABLE  
  
t1为小表, t2为大表  
  
postgres=# insert into t1 select generate_series(1,10000);  
INSERT 0 10000  
postgres=# insert into t2 select generate_series(1,10000000);  
INSERT 0 10000000  
postgres=# analyze t1;  
ANALYZE  
postgres=# analyze t2;  
ANALYZE  

PostgreSQL自动选择了小表作为哈希表。

postgres=# explain (analyze,verbose,timing,costs,buffers) select t1.*,t2.* from t1 left outer join t2 on (t1.id=t2.id);  
                                                          QUERY PLAN                                                             
-------------------------------------------------------------------------------------------------------------------------------  
 Hash Right Join  (cost=270.00..182117.68 rows=10000 width=72) (actual time=3.367..2736.484 rows=10000 loops=1)  
   Output: t1.id, t1.info, t2.id, t2.info  
   Hash Cond: (t2.id = t1.id)  
   Buffers: shared hit=16260 read=28033 dirtied=7288 written=5780  
   ->  Seq Scan on public.t2  (cost=0.00..144247.77 rows=9999977 width=36) (actual time=0.014..1262.472 rows=10000000 loops=1)  
         Output: t2.id, t2.info  
         Buffers: shared hit=16228 read=28020 dirtied=7288 written=5780  
   ->  Hash  (cost=145.00..145.00 rows=10000 width=36) (actual time=3.323..3.323 rows=10000 loops=1)  
         Output: t1.id, t1.info  
         Buckets: 16384  Batches: 1  Memory Usage: 480kB  
         Buffers: shared hit=32 read=13  
         ->  Seq Scan on public.t1  (cost=0.00..145.00 rows=10000 width=36) (actual time=0.033..1.501 rows=10000 loops=1)  
               Output: t1.id, t1.info  
               Buffers: shared hit=32 read=13  
 Planning time: 0.076 ms  
 Execution time: 2737.441 ms  
(16 rows)  

Greenplum

greenplum只能选择nullable端的表作为哈希表。即t2.

postgres=# explain analyze select t1.*,t2.* from t1 left outer join t2 on (t1.id=t2.id);  
                                                                      QUERY PLAN                                                                         
-------------------------------------------------------------------------------------------------------------------------------------------------------  
 Gather Motion 48:1  (slice1; segments: 48)  (cost=236070.60..236368.60 rows=10000 width=72)  
   Rows out:  10000 rows at destination with 215 ms to end, start offset by 1.350 ms.  
   ->  Hash Left Join  (cost=236070.60..236368.60 rows=209 width=72)  
         Hash Cond: t1.id = t2.id  
         Rows out:  Avg 208.3 rows x 48 workers.  Max 223 rows (seg17) with 0.043 ms to first row, 81 ms to end, start offset by 15 ms.  
         Executor memory:  6511K bytes avg, 6513K bytes max (seg18).  
         Work_mem used:  6511K bytes avg, 6513K bytes max (seg18). Workfile: (0 spilling, 0 reused)  
         ->  Seq Scan on t1  (cost=0.00..148.00 rows=209 width=36)  
               Rows out:  Avg 208.3 rows x 48 workers.  Max 223 rows (seg17) with 0.006 ms to first row, 0.025 ms to end, start offset by 15 ms.  
         ->  Hash  (cost=111053.60..111053.60 rows=208362 width=36)  
               Rows in:  (No row requested) 0 rows (seg0) with 0 ms to end.  
               ->  Seq Scan on t2  (cost=0.00..111053.60 rows=208362 width=36)  
                     Rows out:  Avg 208333.3 rows x 48 workers.  Max 208401 rows (seg18) with 79 ms to first row, 98 ms to end, start offset by 15 ms.  
 Slice statistics:  
   (slice0)    Executor memory: 283K bytes.  
   (slice1)    Executor memory: 250K bytes avg x 48 workers, 250K bytes max (seg0).  Work_mem: 6513K bytes max.  
 Statement statistics:  
   Memory used: 128000K bytes  
 Settings:  optimizer=off  
 Optimizer status: legacy query optimizer  
 Total runtime: 216.814 ms  
(21 rows)  

full outer join

PostgreSQL 9.1+

PostgreSQL 9.1+ 支持full outer join使用hash join.

postgres=# explain (analyze,verbose,timing,costs,buffers) select t1.*,t2.* from t2 full outer join t1 on (t1.id=t2.id);  
                                                          QUERY PLAN                                                             
-------------------------------------------------------------------------------------------------------------------------------  
 Hash Full Join  (cost=270.00..182117.68 rows=9999977 width=72) (actual time=3.434..3728.277 rows=10000000 loops=1)  
   Output: t1.id, t1.info, t2.id, t2.info  
   Hash Cond: (t2.id = t1.id)  
   Buffers: shared hit=16301 read=27992  
   ->  Seq Scan on public.t2  (cost=0.00..144247.77 rows=9999977 width=36) (actual time=0.246..1187.189 rows=10000000 loops=1)  
         Output: t2.id, t2.info  
         Buffers: shared hit=16256 read=27992  
   ->  Hash  (cost=145.00..145.00 rows=10000 width=36) (actual time=3.157..3.157 rows=10000 loops=1)  
         Output: t1.id, t1.info  
         Buckets: 16384  Batches: 1  Memory Usage: 480kB  
         Buffers: shared hit=45  
         ->  Seq Scan on public.t1  (cost=0.00..145.00 rows=10000 width=36) (actual time=0.013..1.438 rows=10000 loops=1)  
               Output: t1.id, t1.info  
               Buffers: shared hit=45  
 Planning time: 0.095 ms  
 Execution time: 4527.421 ms  
(16 rows)  

Greenplum

Greenplum 8.2版本,不支持full outer join使用hash join.

使用了merge join.

postgres=# explain analyze select t1.*,t2.* from t2 full outer join t1 on (t1.id=t2.id);  
                                                                        QUERY PLAN                                                                           
-----------------------------------------------------------------------------------------------------------------------------------------------------------  
 Gather Motion 48:1  (slice1; segments: 48)  (cost=1274708.75..1324865.55 rows=10001360 width=72)  
   Rows out:  10000000 rows at destination with 2310 ms to end, start offset by 229 ms.  
   ->  Merge Full Join  (cost=1274708.75..1324865.55 rows=208362 width=72)  
         Merge Cond: t2.id = t1.id  
         Rows out:  Avg 208333.3 rows x 48 workers.  Max 208401 rows (seg18) with 0.002 ms to first row, 36 ms to end, start offset by 274 ms.  
         ->  Sort  (cost=1273896.37..1298899.77 rows=208362 width=36)  
               Sort Key: t2.id  
               Rows out:  Avg 208333.3 rows x 48 workers.  Max 208401 rows (seg18) with 0.006 ms to end, start offset by 274 ms.  
               Executor memory:  14329K bytes avg, 14329K bytes max (seg0).  
               Work_mem used:  14329K bytes avg, 14329K bytes max (seg0). Workfile: (0 spilling, 0 reused)  
               ->  Seq Scan on t2  (cost=0.00..111053.60 rows=208362 width=36)  
                     Rows out:  Avg 208333.3 rows x 48 workers.  Max 208401 rows (seg18) with 0.003 ms to first row, 67 ms to end, start offset by 274 ms.  
         ->  Sort  (cost=812.39..837.39 rows=209 width=36)  
               Sort Key: t1.id  
               Rows out:  Avg 208.3 rows x 48 workers.  Max 223 rows (seg17) with 0.002 ms to end, start offset by 273 ms.  
               Executor memory:  58K bytes avg, 58K bytes max (seg0).  
               Work_mem used:  58K bytes avg, 58K bytes max (seg0). Workfile: (0 spilling, 0 reused)  
               ->  Seq Scan on t1  (cost=0.00..148.00 rows=209 width=36)  
                     Rows out:  Avg 208.3 rows x 48 workers.  Max 223 rows (seg17) with 31 ms to first row, 32 ms to end, start offset by 273 ms.  
 Slice statistics:  
   (slice0)    Executor memory: 411K bytes.  
   (slice1)    Executor memory: 14597K bytes avg x 48 workers, 14597K bytes max (seg0).  Work_mem: 14329K bytes max.  
 Statement statistics:  
   Memory used: 128000K bytes  
 Settings:  optimizer=off  
 Optimizer status: legacy query optimizer  
 Total runtime: 2539.416 ms  
(27 rows)  
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
7月前
|
SQL Oracle 关系型数据库
实时计算 Flink版操作报错之往GREENPLUM 6 写数据,用postgresql-42.2.9.jar 报 ON CONFLICT (uuid) DO UPDATE SET 语法有问题。怎么解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
1月前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
134 4
|
2月前
|
关系型数据库 数据库 PostgreSQL
深入理解 PostgreSQL 的 JOIN 连接
深入理解 PostgreSQL 的 JOIN 连接
129 4
|
4月前
|
关系型数据库 MySQL 数据库
探究数据库开源协议:PostgreSQL vs MySQL
探究数据库开源协议:PostgreSQL vs MySQL
|
6月前
|
SQL 关系型数据库 PostgreSQL
PostgreSQL和greenplum的copy命令可以添加字段吗?
【6月更文挑战第5天】PostgreSQL和greenplum的copy命令可以添加字段吗?
96 3
|
6月前
|
监控 关系型数据库 数据库
PostgreSQL和greenplum的copy命令如何使用?
【6月更文挑战第5天】PostgreSQL和greenplum的copy命令如何使用?
172 2
|
7月前
|
存储 关系型数据库 MySQL
【专栏】在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个
【4月更文挑战第27天】MySQL与PostgreSQL是两大主流开源数据库,各有特色。MySQL注重简单、便捷和高效,适合读操作密集场景,而PostgreSQL强调灵活、强大和兼容,擅长并发写入与复杂查询。MySQL支持多种存储引擎和查询缓存,PostgreSQL则具备扩展性、强事务支持和高可用特性。选择时应考虑项目需求、团队技能和预期收益。
102 2
|
7月前
|
关系型数据库 MySQL 数据处理
MySQL vs. PostgreSQL:选择适合你的开源数据库
在当今信息时代,开源数据库成为许多企业和开发者的首选。本文将比较两个主流的开源数据库——MySQL和PostgreSQL,分析它们的特点、优势和适用场景,以帮助读者做出明智的选择。
|
7月前
|
监控 关系型数据库 Java
SpringBoot【集成 01】Druid+Dynamic+Greenplum(实际上用的是PostgreSQL的驱动)及 dbType not support 问题处理(附hikari相关配置)
SpringBoot【集成 01】Druid+Dynamic+Greenplum(实际上用的是PostgreSQL的驱动)及 dbType not support 问题处理(附hikari相关配置)
336 0
|
存储 关系型数据库 MySQL
如何选择最适合你的数据库解决方案:PostgreSQL VS MySQL 技术选型对比
如何选择最适合你的数据库解决方案:PostgreSQL VS MySQL 技术选型对比
317 1

相关产品

  • 云原生数据库 PolarDB