你的JoinHint为什么不生效-低调大师

你的JoinHint为什么不生效

2023-12-18 318

本文分享自华为云社区《你的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)

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

微信关注我们

原文链接：https://my.oschina.net/u/4526289/blog/10322251

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

你知道 MySQL 函数 sysdate() 与 now() 的区别么？

作者对两个与时间相关的函数在运行机制和运维技巧上进行了全面的对比。作者：余振兴，爱可生 DBA 团队成员，热衷技术分享、编写技术文档。作者：陈伟，爱可生 DBA 团队成员，负责 MySQL 日常维护及故障处理。爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。本文约 1400 字，预计阅读需要 4 分钟。背景在客户现场优化一批监控 SQL 时，发现一批 SQL 使用 sysdate() 作为统计数据的查询范围值，执行效率十分低下，查看执行计划发现不能使用到索引，而改为 now() 函数后则可以正常使用索引，以下是对该现象的分析。内心小 ps 一下：sysdate() 的和 now() 的区别这是个⽼问题了。函数 sysdate 与 now 的区别下面我们来详细了解一下函数 sysdate() 与 now() 的区别，我们可以去官方文档查找他们两者之间的详细说明。根据官方说明如下： now() 函数返回的是一个常量时间，该时间为语句开始执行的时间。即当存储函数或触发器中调用到 now() 函数时，now() 会返回存储函数或触发器语句开始执...

2023-12-18

296

大家好，我是来自阿里云云原生应用平台的肖长军，花名穹谷，我此次分享的主题是《可观测加持的下一代性能测试服务》。提到性能测试大家并不陌生，性能测试已成为评估系统能力、识别系统弱点、进行系统调优，验证系统稳定性等的重要手段。我们一般进行性能测试的大概流程就是构造数据，配置场景，发起压测，出压测结果，但测试同学也清楚性能测试并不是这么简单，我们还面临着以下问题：一、压测前的影响范围评估，如何准确地掌控压测的爆炸半径。二、压测和业务系统的相关指标监控，分析当前系统性能。三、如果压测不满足预期，还需要分析性能瓶颈点。四、需要根据当前的压测结果给出可以支撑的最大容量或当前性能。这些问题是每个测试团队都需要面对的，在当前技术发展下，如何更好的解决这些问题？针对以上挑战，我们提出性能压测可观测化能力，分别针对以上问题提出压测链路可观测：首先，在实施压测前，先执行一次拨测，通过拨测发起一次请求来构建整个压测链路拓扑，通过链路拓扑全局来看整个压测的影响范围。其次，性能指标可观测，获取压测链路所涉及的监控指标，自动生成压测及各业务各实例水位大盘，边压边观测。再次，聚合压测请求各指标和链路事...

2023-12-18

328

资源下载

更多资源

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。