你的JoinHint为什么不生效
本文分享自华为云社区《你的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); 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 = 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 | 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 = d.mm)或(c.createtime = d.mm::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)
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
你知道 MySQL 函数 sysdate() 与 now() 的区别么?
作者对两个与时间相关的函数在运行机制和运维技巧上进行了全面的对比。 作者:余振兴,爱可生 DBA 团队成员,热衷技术分享、编写技术文档。 作者:陈伟,爱可生 DBA 团队成员,负责 MySQL 日常维护及故障处理。 爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。 本文约 1400 字,预计阅读需要 4 分钟。 背景 在客户现场优化一批监控 SQL 时,发现一批 SQL 使用 sysdate() 作为统计数据的查询范围值,执行效率十分低下,查看执行计划发现不能使用到索引,而改为 now() 函数后则可以正常使用索引,以下是对该现象的分析。 内心小 ps 一下:sysdate() 的和 now() 的区别这是个⽼问题了。 函数 sysdate 与 now 的区别 下面我们来详细了解一下函数 sysdate() 与 now() 的区别,我们可以去官方文档 查找他们两者之间的详细说明。 根据官方说明如下: now() 函数返回的是一个常量时间,该时间为语句开始执行的时间。即当存储函数或触发器中调用到 now() 函数时,now() 会返回存储函数或触发器语句开始执...
- 下一篇
PTS 3.0:可观测加持的下一代性能测试服务
大家好,我是来自阿里云云原生应用平台的肖长军,花名穹谷,我此次分享的主题是《可观测加持的下一代性能测试服务》。提到性能测试大家并不陌生,性能测试已成为评估系统能力、识别系统弱点、进行系统调优,验证系统稳定性等的重要手段。 我们一般进行性能测试的大概流程就是构造数据,配置场景,发起压测,出压测结果,但测试同学也清楚性能测试并不是这么简单,我们还面临着以下问题: 一、压测前的影响范围评估,如何准确地掌控压测的爆炸半径。 二、压测和业务系统的相关指标监控,分析当前系统性能。 三、如果压测不满足预期,还需要分析性能瓶颈点。 四、需要根据当前的压测结果给出可以支撑的最大容量或当前性能。这些问题是每个测试团队都需要面对的,在当前技术发展下,如何更好的解决这些问题? 针对以上挑战,我们提出性能压测可观测化能力,分别针对以上问题提出压测链路可观测: 首先,在实施压测前,先执行一次拨测,通过拨测发起一次请求来构建整个压测链路拓扑,通过链路拓扑全局来看整个压测的影响范围。 其次,性能指标可观测,获取压测链路所涉及的监控指标,自动生成压测及各业务各实例水位大盘,边压边观测。 再次,聚合压测请求各指标和链路事...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- 2048小游戏-低调大师作品
- Linux系统CentOS6、CentOS7手动修改IP地址
- CentOS8安装Docker,最新的服务器搭配容器使用
- Docker使用Oracle官方镜像安装(12C,18C,19C)
- Windows10,CentOS7,CentOS8安装Nodejs环境
- CentOS8安装MyCat,轻松搞定数据库的读写分离、垂直分库、水平分库
- SpringBoot2更换Tomcat为Jetty,小型站点的福音
- 设置Eclipse缩进为4个空格,增强代码规范
- CentOS7,8上快速安装Gitea,搭建Git服务器
- Windows10,CentOS7,CentOS8安装MongoDB4.0.16