你的 JoinHint 为什么不生效
作者：华为云开发者联盟
- 2023-12-18 广东
本文字数：14236 字
阅读完需：约 47 分钟
本文分享自华为云社区《你的JoinHint为什么不生效【绽放吧！GaussDB(DWS)云原生数仓】》，作者：你是猴子请来的救兵吗 。
引言
提起数据库的 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);
explainSELECT /*+ hashjoin(c d) */ c.userid,c.type_name,c.type_code,count(1) numFROM 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 = d.mm)，确认是否支持 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 | 11oprowner | 10oprkind | boprcanmerge | toprcanhash | foprleft | 1114oprright | 1184oprresult | 16oprcom | 2542oprnegate | 2539oprcode | timestamp_eq_timestamptzoprrest | eqseloprjoin | 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 | 11oprowner | 10oprkind | boprcanmerge | toprcanhash | toprleft | 1184oprright | 1184oprresult | 16oprcom | 1320oprnegate | 1321oprcode | timestamptz_eqoprrest | eqseloprjoin | eqjoinsel-[ RECORD 2 ]+---------------oprname | =oprnamespace | 11oprowner | 10oprkind | boprcanmerge | toprcanhash | toprleft | 1114oprright | 1114oprresult | 16oprcom | 2060oprnegate | 2061oprcode | timestamp_eqoprrest | eqseloprjoin | eqjoinsel复制代码
在关联条件上添加类型转换，保证两边类型一致，即(c.createtime：：timestamptz = d.mm)或(c.createtime = d.mm：：timestamp)。
postgres=# explainpostgres-# SELECT /*+ hashjoin(c d) */postgres-# c.userid,c.type_name,c.type_code,count(1) numpostgres-# FROM workitem c INNER JOIN workday d ON c.createtime::timestamptz = d.mmpostgres-# 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=# explainSELECT /*+ hashjoin(c d) */ c.userid,c.type_name,c.type_code,count(1) numFROM 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=# explainSELECT /*+ hashjoin(c d) */ c.userid,c.type_name,c.type_code,count(1) numFROM 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 FUNCTIONpostgres=# explainSELECT /*+ hashjoin(c d) */ c.userid,c.type_name,c.type_code,count(1) numFROM 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=# explainSELECT /*+ hashjoin(c d) */ c.userid,c.type_name,c.type_code,count(1) numFROM 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=# explainSELECT /*+ hashjoin(c d) */ c.userid,c.type_name,c.type_code,count(1) numFROM 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 | 11oprowner | 10oprkind | boprcanmerge | toprcanhash | foprleft | 1114oprright | 1184oprresult | 16oprcom | 2542oprnegate | 2539oprcode | timestamp_eq_timestamptzoprrest | eqseloprjoin | eqjoinsel复制代码
如果是指定 join 顺序的 hint，如 leading(c e),会存在逻辑本身冲突导致 hint 失败的情况。
postgres=# explain SELECT /*+ leading(c e) */ c.userid,c.type_name,c.type_code,count(1) numFROM 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)复制代码
划线
评论
复制
发布于: 6 小时前阅读数: 2
版权声明: 本文为 InfoQ 作者【华为云开发者联盟】的原创文章。
原文链接:【http://xie.infoq.cn/article/39cc68563a38acb268e17fa1f】。文章转载请联系作者。
华为云开发者联盟
关注
提供全面深入的云计算技术干货 2020-07-14 加入
生于云，长于云，让开发者成为决定性力量
评论