你的joinhint为什么不生效【绽放吧！gaussdb(dws)云原生数仓】-4008云顶国际网站
【摘要】 提起数据库的hint，几乎每个dba都知道这一强大功能。在gaussdb(dws)中，hint可以被用来干预sql的执行计划，但是在日常工作中，很多开发人员对hint的缺乏深入了解，经常遇到hint失效的情况却又束手无策。现在一起来看看，你的joinhint为什么不生效？
引言
提起数据库的hint，几乎每个dba都知道这一强大功能。在gaussdb(dws)中，hint可以被用来干预sql的执行计划，但是在日常工作中，很多开发人员对hint的缺乏深入了解，经常遇到hint失效的情况却又束手无策。
本次针对joinhint从案例着手深入解析joinhint不生效的原因，以便读者能“知其所以然”。（本文不讨论hint的基础语法问题）。
问题案例
内核版本 gaussdb 8.1.3
问题描述 两表关联查询，使用hashjoin hint干预join方式，但hint不生效
问题用例
create table workitem (
language character varying(10),
userid character varying(240),
opiontype character varying(240),
processinstid character varying(240),
workitemid character varying(240),
type_name character varying(240),
type_code character varying(240),
createtime timestamp without time zone,
endtime timestamp without time zone,
notrejecttotal numeric,
dws_created_time timestamp without time zone
)
with (orientation=column, compression=low, colversion=2.0, enable_delta=false)
distribute by hash(workitemid);
create table workday (
mm timestamp with time zone,
rn numeric
)
with (orientation=column, compression=low, colversion=2.0, enable_delta=false)
distribute by hash(mm);
explain
select /* hashjoin(c d) */
c.userid,c.type_name,c.type_code,count(1) num
from workitem c inner join workday d on c.createtime = d.mm
where c.createtime >= '2023-09-01' and c.endtime < '2023-10-01'
group by c.userid,c.type_name,c.type_code;
warning: unused hint: hashjoin(c d)
query plan
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | operation | e-rows | e-memory | e-width | e-costs
---- ------------------------------------------------------ -------- ---------- --------- ---------
1 | -> row adapter | 2 | | 1502 | 33.12
2 | -> vector sonic hash aggregate | 2 | | 1502 | 33.12
3 | -> vector streaming (type: gather) | 4 | | 1502 | 33.12
4 | -> vector sonic hash aggregate | 4 | 16mb | 1502 | 27.12
5 | -> vector nest loop (6,8) | 5 | 1mb | 1494 | 27.08
6 | -> vector streaming(type: broadcast) | 14 | 2mb | 8 | 13.68
7 | -> cstore scan on workday d | 7 | 1mb | 8 | 13.05
8 | -> vector materialize | 5 | 16mb | 1502 | 13.09
9 | -> cstore scan on workitem c | 5 | 1mb | 1502 | 13.08
runtime analyze information
-------------------------------------------
"public.workitem" runtime: 25.794ms
"public.workday" runtime: 18.098ms
predicate information (identified by plan id)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 --vector nest loop (6,8)
join filter: (c.createtime = d.mm)
7 --cstore scan on workday d
filter: (mm >= '2023-09-01 00:00:00'::timestamp without time zone)
pushdown predicate filter: (mm >= '2023-09-01 00:00:00'::timestamp without time zone)
9 --cstore scan on workitem c
filter: ((createtime >= '2023-09-01 00:00:00'::timestamp without time zone) and (endtime < '2023-10-01 00:00:00'::timestamp without time zone))
pushdown predicate filter: ((createtime >= '2023-09-01 00:00:00'::timestamp without time zone) and (endtime < '2023-10-01 00:00:00'::timestamp without time zone))
====== query summary =====
-------------------------------
system available mem: 4710400kb
query max mem: 4710400kb
query estimated mem: 5271kb
(33 rows)
问题定位
- 尝试关闭nestloop路径，来验证是否可以生成hash计划
set enable_nestloop = off;
set enable_mergejoin = off;
set enable_hashjoin = on;
warning: unused hint: hashjoin(c d)
query plan
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | operation | e-rows | e-memory | e-width | e-costs
---- ------------------------------------------------------ -------- ---------- --------- ---------
1 | -> row adapter | 2 | | 1502 | 33.12
2 | -> vector sonic hash aggregate | 2 | | 1502 | 33.12
3 | -> vector streaming (type: gather) | 4 | | 1502 | 33.12
4 | -> vector sonic hash aggregate | 4 | 16mb | 1502 | 27.12
5 | -> vector nest loop (6,8) | 5 | 1mb | 1494 | 27.08
6 | -> vector streaming(type: broadcast) | 14 | 2mb | 8 | 13.68
7 | -> cstore scan on workday d | 7 | 1mb | 8 | 13.05
8 | -> vector materialize | 5 | 16mb | 1502 | 13.09
9 | -> cstore scan on workitem c | 5 | 1mb | 1502 | 13.08
predicate information (identified by plan id)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 --vector nest loop (6,8)
join filter: (c.createtime = d.mm)
7 --cstore scan on workday d
filter: (mm >= '2023-09-01 00:00:00'::timestamp without time zone)
pushdown predicate filter: (mm >= '2023-09-01 00:00:00'::timestamp without time zone)
9 --cstore scan on workitem c
filter: ((createtime >= '2023-09-01 00:00:00'::timestamp without time zone) and (endtime < '2023-10-01 00:00:00'::timestamp without time zone))
pushdown predicate filter: ((createtime >= '2023-09-01 00:00:00'::timestamp without time zone) and (endtime < '2023-10-01 00:00:00'::timestamp without time zone))
====== query summary =====
-------------------------------
system available mem: 4710400kb
query max mem: 4710400kb
query estimated mem: 5271kb
(28 rows)
关闭nestloop路径后，仍然生成nestloop计划，且e-costs代价中未添加惩罚代价，说明该场景语句本身不支持hashjoin。
检查关联表达式 (c.createtime = )，确认是否支持hashjoin。
- 关联表达式为字段关联，不存在函数嵌套
- 关联表达式两边数据类型为 timestamp without time zone 和 timestamp with time zone，通过系统表pg_operator确认是否支持hashjoin。
postgres=# select * from pg_operator where oprname = '=' and oprleft = 'timestamp'::regtype and oprright = 'timestamptz'::regtype;
-[ record 1 ]-------------------------
oprname | =
oprnamespace | 11
oprowner | 10
oprkind | b
oprcanmerge | t
oprcanhash | f
oprleft | 1114
oprright | 1184
oprresult | 16
oprcom | 2542
oprnegate | 2539
oprcode | timestamp_eq_timestamptz
oprrest | eqsel
oprjoin | eqjoinsel
- 通过结果确认oprcanhash为false，代表该操作符不支持hash连接；原因是，左边数据不带时区，右边数据带，在比较时要先处理时区问题，不能直接拿存储值进行判断。
改善办法
- 通过系统表确认timestamp类型的等值关联和timestamptz的等值关联均支持hash连接。
postgres=# select * from pg_operator where oprname = '=' and oprleft = oprright and oprleft in('timestamp'::regtype,'timestamptz'::regtype);
-[ record 1 ]---------------
oprname | =
oprnamespace | 11
oprowner | 10
oprkind | b
oprcanmerge | t
oprcanhash | t
oprleft | 1184
oprright | 1184
oprresult | 16
oprcom | 1320
oprnegate | 1321
oprcode | timestamptz_eq
oprrest | eqsel
oprjoin | eqjoinsel
-[ record 2 ]---------------
oprname | =
oprnamespace | 11
oprowner | 10
oprkind | b
oprcanmerge | t
oprcanhash | t
oprleft | 1114
oprright | 1114
oprresult | 16
oprcom | 2060
oprnegate | 2061
oprcode | timestamp_eq
oprrest | eqsel
oprjoin | eqjoinsel
- 在关联条件上添加类型转换，保证两边类型一致，即 (c.createtime：：timestamptz = ) 或 (c.createtime = ：：timestamp)。
postgres=# explain
postgres-# select /* hashjoin(c d) */
postgres-# c.userid,c.type_name,c.type_code,count(1) num
postgres-# from workitem c inner join workday d on c.createtime::timestamptz = d.mm
postgres-# where c.createtime >= '2023-09-01' and c.endtime < '2023-10-01'
postgres-# group by c.userid,c.type_name,c.type_code;
query plan
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | operation | e-rows | e-memory | e-width | e-costs
---- ------------------------------------------------------ -------- ---------- --------- ---------
1 | -> row adapter | 2 | | 1502 | 34.29
2 | -> vector sonic hash aggregate | 2 | | 1502 | 34.29
3 | -> vector streaming (type: gather) | 4 | | 1502 | 34.29
4 | -> vector sonic hash aggregate | 4 | 16mb | 1502 | 28.29
5 | -> vector sonic hash join (6,8) | 5 | 16mb | 1494 | 28.25
6 | -> vector streaming(type: broadcast) | 40 | 2mb | 8 | 15.06
7 | -> cstore scan on workday d | 20 | 1mb | 8 | 13.01
8 | -> cstore scan on workitem c | 5 | 1mb | 1502 | 13.08
predicate information (identified by plan id)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 --vector sonic hash join (6,8)
hash cond: (d.mm = (c.createtime)::timestamp with time zone)
8 --cstore scan on workitem c
filter: ((createtime >= '2023-09-01 00:00:00'::timestamp without time zone) and (endtime < '2023-10-01 00:00:00'::timestamp without time zone))
pushdown predicate filter: ((createtime >= '2023-09-01 00:00:00'::timestamp without time zone) and (endtime < '2023-10-01 00:00:00'::timestamp without time zone))
====== query summary =====
-------------------------------
system available mem: 4710400kb
query max mem: 4710400kb
query estimated mem: 5530kb
(24 rows)
postgres=# explain
select /* hashjoin(c d) */
c.userid,c.type_name,c.type_code,count(1) num
from workitem c inner join workday d on c.createtime = d.mm::timestamp
where c.createtime >= '2023-09-01' and c.endtime < '2023-10-01'
group by c.userid,c.type_name,c.type_code;
query plan
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | operation | e-rows | e-memory | e-width | e-costs
---- ------------------------------------------------------ -------- ---------- --------- ---------
1 | -> row adapter | 2 | | 1502 | 32.91
2 | -> vector sonic hash aggregate | 2 | | 1502 | 32.91
3 | -> vector streaming (type: gather) | 4 | | 1502 | 32.91
4 | -> vector sonic hash aggregate | 4 | 16mb | 1502 | 26.91
5 | -> vector sonic hash join (6,8) | 5 | 16mb | 1494 | 26.87
6 | -> vector streaming(type: broadcast) | 14 | 2mb | 8 | 13.71
7 | -> cstore scan on workday d | 7 | 1mb | 8 | 13.08
8 | -> cstore scan on workitem c | 5 | 1mb | 1502 | 13.08
predicate information (identified by plan id)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 --vector sonic hash join (6,8)
hash cond: ((d.mm)::timestamp without time zone = c.createtime)
7 --cstore scan on workday d
filter: ((mm)::timestamp without time zone >= '2023-09-01 00:00:00'::timestamp without time zone)
8 --cstore scan on workitem c
filter: ((createtime >= '2023-09-01 00:00:00'::timestamp without time zone) and (endtime < '2023-10-01 00:00:00'::timestamp without time zone))
pushdown predicate filter: ((createtime >= '2023-09-01 00:00:00'::timestamp without time zone) and (endtime < '2023-10-01 00:00:00'::timestamp without time zone))
====== query summary =====
-------------------------------
system available mem: 4710400kb
query max mem: 4710400kb
query estimated mem: 5530kb
(26 rows)
知识小结
实际使用过程中导致hint生效的原因很多，这里总结排查hashjoin hint步骤以供参考:
检查hint中的表名是否正确、是否存在重名、是否在当前层可见，此类场景通常在explain中会给出提示，自行排查即可。
判断关联hint中的表名是否被提升导致表名不存在，此类场景通常在explain中会给出提示
postgres=# explain
select /* hashjoin(c d) */
c.userid,c.type_name,c.type_code,count(1) num
from workitem c inner join (select * from workday where mm >= '2023-09-01') d on c.createtime = d.mm::timestamp
where c.createtime >= '2023-09-01' and c.endtime < '2023-10-01'
group by c.userid,c.type_name,c.type_code;
warning: error hint: hashjoin(c d), relation name "d" is not found.
query plan
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | operation | e-rows | e-memory | e-width | e-costs
---- ------------------------------------------------------ -------- ---------- --------- ---------
1 | -> row adapter | 2 | | 1502 | 32.78
2 | -> vector sonic hash aggregate | 2 | | 1502 | 32.78
3 | -> vector streaming (type: gather) | 4 | | 1502 | 32.78
4 | -> vector sonic hash aggregate | 4 | 16mb | 1502 | 26.78
5 | -> vector sonic hash join (6,8) | 5 | 16mb | 1494 | 26.74
6 | -> vector streaming(type: broadcast) | 10 | 2mb | 8 | 13.58
7 | -> cstore scan on workday | 5 | 1mb | 8 | 13.11
8 | -> cstore scan on workitem c | 5 | 1mb | 1502 | 13.08
predicate information (identified by plan id)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 --vector sonic hash join (6,8)
hash cond: ((workday.mm)::timestamp without time zone = c.createtime)
7 --cstore scan on workday
filter: ((mm >= '2023-09-01 00:00:00 08'::timestamp with time zone) and ((mm)::timestamp without time zone >= '2023-09-01 00:00:00'::timestamp without time zone))
pushdown predicate filter: (mm >= '2023-09-01 00:00:00 08'::timestamp with time zone)
8 --cstore scan on workitem c
filter: ((createtime >= '2023-09-01 00:00:00'::timestamp without time zone) and (endtime < '2023-10-01 00:00:00'::timestamp without time zone))
pushdown predicate filter: ((createtime >= '2023-09-01 00:00:00'::timestamp without time zone) and (endtime < '2023-10-01 00:00:00'::timestamp without time zone))
====== query summary =====
-------------------------------
system available mem: 4710400kb
query max mem: 4710400kb
query estimated mem: 5530kb
(27 rows)
针对此种情况，8.2.0及以上版本可以通过添加no merge hint来禁用子查询提升从而规避hint失效问题。
- 通过join路径参数验证目标路径是否可生效。
--如通过关闭其他路径参数来验证某一路径是否可达
set enable_nestloop = off;
set enable_mergejoin = off;
set enable_hashjoin = on;
- 检查关联条件中是否存在volatile函数。
postgres=# create or replace function gettimediff(timestamp) returns interval language sql as 'select $1-timeofday()::timestamp' volatile;
create function
postgres=# explain
select /* hashjoin(c d) */
c.userid,c.type_name,c.type_code,count(1) num
from workitem c inner join workday d on gettimediff(c.createtime) = gettimediff(d.mm::timestamp)
where c.createtime >= '2023-09-01' and c.endtime < '2023-10-01'
group by c.userid,c.type_name,c.type_code;
warning: unused hint: hashjoin(c d)
query plan
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | operation | e-rows | e-width | e-costs
---- ------------------------------------------------------------- -------- --------- ---------
1 | -> hashaggregate | 5 | 1502 | 3.10
2 | -> nested loop (3,4) | 5 | 1494 | 3.00
3 | -> data node scan on workitem "_remote_table_query_" | 5 | 1502 | 0.00
4 | -> data node scan on workday "_remote_table_query_" | 20 | 8 | 0.00
predicate information (identified by plan id)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 --nested loop (3,4)
join filter: ((c.createtime - (timeofday())::timestamp without time zone) = ((d.mm)::timestamp without time zone - (timeofday())::timestamp without time zone))
(11 rows)
- 检查关联条件中两表字段是否在等号两侧，若不是则进行调整。
postgres=# explain
select /* hashjoin(c d) */
c.userid,c.type_name,c.type_code,count(1) num
from workitem c inner join workday d on ifnull(c.createtime,d.mm) = now()
where c.createtime >= '2023-09-01' and c.endtime < '2023-10-01'
group by c.userid,c.type_name,c.type_code;
warning: unused hint: hashjoin(c d)
query plan
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | operation | e-rows | e-memory | e-width | e-costs
---- ------------------------------------------------------ -------- ---------- --------- ---------
1 | -> row adapter | 1 | | 1502 | 35.37
2 | -> vector sonic hash aggregate | 1 | | 1502 | 35.37
3 | -> vector streaming (type: gather) | 2 | | 1502 | 35.37
4 | -> vector sonic hash aggregate | 2 | 16mb | 1502 | 29.37
5 | -> vector nest loop (6,8) | 2 | 1mb | 1494 | 29.35
6 | -> vector streaming(type: broadcast) | 40 | 2mb | 8 | 15.06
7 | -> cstore scan on workday d | 20 | 1mb | 8 | 13.01
8 | -> vector materialize | 5 | 16mb | 1502 | 13.09
9 | -> cstore scan on workitem c | 5 | 1mb | 1502 | 13.08
predicate information (identified by plan id)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 --vector nest loop (6,8)
join filter: (coalesce((c.createtime)::timestamp with time zone, d.mm) = now())
9 --cstore scan on workitem c
filter: ((createtime >= '2023-09-01 00:00:00'::timestamp without time zone) and (endtime < '2023-10-01 00:00:00'::timestamp without time zone))
pushdown predicate filter: ((createtime >= '2023-09-01 00:00:00'::timestamp without time zone) and (endtime < '2023-10-01 00:00:00'::timestamp without time zone))
====== query summary =====
-------------------------------
system available mem: 4710400kb
query max mem: 4710400kb
query estimated mem: 5275kb
(25 rows)
- 检查关联条件是否为等值关联，若不是则进行调整。
postgres=# explain
select /* hashjoin(c d) */
c.userid,c.type_name,c.type_code,count(1) num
from workitem c inner join workday d on c.createtime::timestamptz > d.mm
where c.createtime >= '2023-09-01' and c.endtime < '2023-10-01'
group by c.userid,c.type_name,c.type_code;
warning: unused hint: hashjoin(c d)
query plan
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | operation | e-rows | e-memory | e-width | e-costs
---- ------------------------------------------------------ -------- ---------- --------- ---------
1 | -> row adapter | 5 | | 1502 | 35.41
2 | -> vector sonic hash aggregate | 5 | | 1502 | 35.41
3 | -> vector streaming (type: gather) | 10 | | 1502 | 35.41
4 | -> vector sonic hash aggregate | 10 | 16mb | 1502 | 29.41
5 | -> vector nest loop (6,8) | 33 | 1mb | 1494 | 29.20
6 | -> vector streaming(type: broadcast) | 40 | 2mb | 8 | 15.06
7 | -> cstore scan on workday d | 20 | 1mb | 8 | 13.01
8 | -> vector materialize | 5 | 16mb | 1502 | 13.09
9 | -> cstore scan on workitem c | 5 | 1mb | 1502 | 13.08
predicate information (identified by plan id)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 --vector nest loop (6,8)
join filter: ((c.createtime)::timestamp with time zone > d.mm)
9 --cstore scan on workitem c
filter: ((createtime >= '2023-09-01 00:00:00'::timestamp without time zone) and (endtime < '2023-10-01 00:00:00'::timestamp without time zone))
pushdown predicate filter: ((createtime >= '2023-09-01 00:00:00'::timestamp without time zone) and (endtime < '2023-10-01 00:00:00'::timestamp without time zone))
====== query summary =====
-------------------------------
system available mem: 4710400kb
query max mem: 4710400kb
query estimated mem: 5281kb
(25 rows)
- 检查关联关系两侧的数据类型，并通过pg_operator.oprcanhash确认是否支持hash连接，若不支持则需改写为支持的操作符。
postgres=# select * from pg_operator where oprname = '=' and oprleft = 'timestamp'::regtype and oprright = 'timestamptz'::regtype;
-[ record 1 ]-------------------------
oprname | =
oprnamespace | 11
oprowner | 10
oprkind | b
oprcanmerge | t
oprcanhash | f
oprleft | 1114
oprright | 1184
oprresult | 16
oprcom | 2542
oprnegate | 2539
oprcode | timestamp_eq_timestamptz
oprrest | eqsel
oprjoin | eqjoinsel
- 如果是指定join顺序的hint，如leading(c e),会存在逻辑本身冲突导致hint失败的情况。
postgres=# explain select /* leading(c e) */
c.userid,c.type_name,c.type_code,count(1) num
from workitem c left join workday d on c.createtime = d.mm left join workday e on d.mm = e.mm
where c.createtime >= '2023-09-01' and c.endtime < '2023-10-01'
group by c.userid,c.type_name,c.type_code;
warning: unused hint: leading(c e)
query plan
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | operation | e-rows | e-memory | e-width | e-costs
---- --------------------------------------------------------- -------- ---------- --------- ---------
1 | -> row adapter | 2 | | 1502 | 47.97
2 | -> vector sonic hash aggregate | 2 | | 1502 | 47.97
3 | -> vector streaming (type: gather) | 4 | | 1502 | 47.97
4 | -> vector sonic hash aggregate | 4 | 16mb | 1502 | 41.97
5 | -> vector nest loop left join (6, 7) | 5 | 1mb | 1494 | 41.93
6 | -> cstore scan on workitem c | 5 | 1mb | 1502 | 13.08
7 | -> vector materialize | 40 | 16mb | 8 | 28.00
8 | -> vector streaming(type: broadcast) | 40 | 2mb | 8 | 27.90
9 | -> vector hash left join (10, 11) | 20 | 16mb | 8 | 26.32
10 | -> cstore scan on workday d | 20 | 1mb | 8 | 13.01
11 | -> cstore scan on workday e | 20 | 1mb | 8 | 13.01
predicate information (identified by plan id)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 --vector nest loop left join (6, 7)
join filter: (c.createtime = d.mm)
6 --cstore scan on workitem c
filter: ((createtime >= '2023-09-01 00:00:00'::timestamp without time zone) and (endtime < '2023-10-01 00:00:00'::timestamp without time zone))
pushdown predicate filter: ((createtime >= '2023-09-01 00:00:00'::timestamp without time zone) and (endtime < '2023-10-01 00:00:00'::timestamp without time zone))
9 --vector hash left join (10, 11)
hash cond: (d.mm = e.mm)
====== query summary =====
-------------------------------
system available mem: 4710400kb
query max mem: 4710400kb
query estimated mem: 5274kb
(29 rows)
- 检查查询语句中，from表数量是否超出from_collapse_limit，以及join表数量是否超出join_collapse_limit。超出时存在一定概率使hint无法生效。
比默认值小的数值将降低规划时间，但是可能生成差的执行计划。
postgres=# show from_collapse_limit;
from_collapse_limit
---------------------
8
(1 row)
postgres=# show join_collapse_limit;
join_collapse_limit
---------------------
8
(1 row)
- 检查查询语句中，from表数量是否超出geqo_threshold（geqo开启的前提下），如果超出会使用基因查询优化来生成计划，存在很大概率使hint无法生效。
对于简单的查询，通常用详尽搜索方法，当涉及多个表的查询的时候，用geqo可以更好的管理查询。
postgres=# show geqo_threshold;
geqo_threshold
----------------
12
(1 row)
【4008云顶国际集团的版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
- 点赞
- 收藏
- 关注作者
评论（0）