本文分享自华为云社区《【SQL优化】为什么有时候无法走执行性能更优的hashjoin》，作者： leapdb。
1. hash join 通常优于 nestloop join
通常 nestloop join 的复杂度是 O(N 方)，hash join 时间复杂度是 O(N)，所以我们一般倾向于使用 hash join。
在 SQL 脚本调优过程中通常有两种方式，强制走 hash join 方式：
1. 在 session 级关闭 nestloop 方式，set enable_nestloop to off;
2. 在 SQL 中通过 /*+ hashjoin(a b) */ 方式，让 a 和 b 表走 hash join;
CREATE DATABASE test_td WITH DBCOMPATIBILITY='td';
create table dim_day(day_code char(8));create table dwr_rpo as select current_date - 1 as day_code; --返回了date类型
test_td=# \d+ dwr_rpo Table "public.dwr_rpo" Column | Type | Modifiers | Storage | Stats target | Description ----------+------+-----------+---------+--------------+------------- day_code | date | | plain | | Has OIDs: noDistribute By: ROUND ROBINLocation Nodes: ALL DATANODESOptions: orientation=row, compression=no
explain select *from dwr_rpo aleft join dim_day con c.day_code = a.day_code;
id | operation | E-rows | E-distinct | E-memory | E-width | E-costs ---+----------------------------------------------+---------+------------+----------+---------+-------------- 1 | -> Streaming (type: GATHER) | 1310148 | | | 1694 | 279235196.70 2 | -> Nested Loop Left Join (3, 4) | 1310148 | | 1MB | 1694 | 279229682.93 3 | -> Seq Scan on dwr_rpo a | 1310148 | | 1MB | 1676 | 46589.16 4 | -> Materialize | 109575 | | 16MB | 22 | 3747.76 5 | -> Streaming(type: BROADCAST) | 109575 | | 2MB | 22 | 3565.14 6 | -> Seq Scan on dim_day c | 36525 | | 1MB | 22 | 272.75
Predicate Information (identified by plan id) ----------------------------------------------------------------------------- 2 --Nested Loop Left Join (3, 4) Join Filter: ((c.day_code)::timestamp without time zone = a.day_code)
复制代码
可是，以上 SQL 无论用哪种方式都走不上 hash join。我们需要看一下，join 两端的数据类型是否支持 hash 比较。
1. 为什么有时候无法走执行性能更优的 hashjoin
不同数据类型计算 hash 函数不同，互不兼容的数据类型无法进行 hash 比较。
2. 为什么 hashjoin 秒级，nestloop 需要两个小时
nestloop 复杂度：131w * 10w = 1310 亿
hashjoin 复杂度：131w
所以两种方式性能差距很大。
3. 为什么有类型转换，还不能 hash join
看似类型相近，但由于两端的精度，格式，有无时区等不一样，无法认为直接相等。
4. 都哪些数据类型间的 join 不支持 hash?
select oprname,oprkind,oprcanhash, (select typname from pg_type where oid=oprleft) oprleft, (select typname from pg_type where oid=oprright) oprright from pg_operator where oprname='=' and oprcanhash='f';
oprname | oprkind | oprcanhash | oprleft | oprright ---------+---------+------------+---------------+--------------- = | b | f | xid | int8 = | b | f | xid32 | int4 = | b | f | tid | tid = | b | f | box | box = | b | f | path | path = | b | f | tinterval | tinterval = | b | f | money | money = | b | f | circle | circle = | b | f | lseg | lseg = | b | f | line | line = | b | f | bit | bit = | b | f | varbit | varbit = | b | f | date | timestamp = | b | f | date | timestamptz = | b | f | timestamp | date = | b | f | timestamptz | date = | b | f | timestamp | timestamptz = | b | f | timestamptz | timestamp = | b | f | tsvector | tsvector = | b | f | tsquery | tsquery = | b | f | record | record = | b | f | hll | hll = | b | f | hll_hashval | hll_hashval = | b | f | roaringbitmap | roaringbitmap(24 rows)
复制代码
主要是 timestamp, timestamptz, date 间互相 join 是无法走 hash。其它数据类型不常见。
开发建议：join 两端的数据类型尽量一致或互相兼容。
5. 为什么 oracle 兼容模式没有问题，td 兼容模式有问题？
current_date 在 TD 兼容模式下为 date 类型；
current_date 在 Oracle 兼容模式下为 timestamp 类型；
点击关注，第一时间了解华为云新鲜技术~
评论