本文分享自华为云社区《【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: no

Distribute By: ROUND ROBIN

Location Nodes: ALL DATANODES

Options: orientation=row, compression=no

explain select *

from dwr_rpo a

left join dim_day c

on 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. 为什么有时候无法走执行性能更优的hash join

不同数据类型计算hash函数不同，互不兼容的数据类型无法进行hash比较。

2. 为什么hash join秒级，nest loop需要两个小时

Nest loop复杂度：131w * 10w = 1310亿

Hash join复杂度：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类型。

点击关注，第一时间了解华为云新鲜技术~