mogdb 优化大全（落落）-摩杜云开发者社区

/+ set(enable_nestloop off) set(enable_index_nestloop off) /

关闭嵌套查询，走hash

/+ use_cplan /

常量和绑定变量执行计划一致

Custom Plan和Generic Plan选择的Hint

语法格式：

指定使用Custom Plan：
use_cplan

指定使用Generic Plan：
use_gplan

示例：

//强制使用Custom Plan,可以看到过滤条件为入参的实际值
openGauss=# prepare p as select /*+ use_cplan*/ * from test_bak where id = $1;
PREPARE
openGauss=# explain execute p(1);
                             QUERY PLAN                              
---------------------------------------------------------------------
 Index Scan using unq1 on test_bak  (cost=0.00..8.27 rows=1 width=8)
   Index Cond: (id = 1)
(2 rows)
//释放语句p
openGauss=# deallocate p;
DEALLOCATE
//强制使用Generic Plan,可以看到过滤条件为待填充的入参
openGauss=# prepare p as select /*+ use_gplan*/ * from test_bak where id = $1;
PREPARE
openGauss=# explain execute p(1);
                             QUERY PLAN                              
---------------------------------------------------------------------
 [Bypass]
 Index Scan using unq1 on test_bak  (cost=0.00..8.27 rows=1 width=8)
   Index Cond: (id = $1)
(3 rows)

/+ set(enable_nestloop off) set(enable_index_nestloop off) set(enable_indexscan off) set(enable_bitmapscan off) set(query_dop 3)/

with as 改写为 as not materialized

pg12之前，WITH中的每一个CTE（common table express），都是直接进行物化的，也就是说外层的条件是不会影响到CTE语句里面的内容。但是对于select CTE来说，外面的条件如果可以推到CTE里面，那可能能够大幅降低扫描，尤其是在有索引的情况下。

从pg12开始，CTE语句支持了not materialized选项，即不使用物化，允许外面条件推进去。

ROWNUM RN 改写为 row_number() over(order by null) rn 这样可以开启并行使 set(query_dop 3) 能生效

/+ leading((t2 t)) nestloop(t2 t) no tablescan(t) /

仅指定join顺序，不指定内外表顺序。

leading(t1 t2 t3) t1 t2 t3先join，3张表join顺序和内外表不限同时指定join顺序和内外表顺序，内外表顺序仅在最外层生效。

leading((t1 t2 t3)) t1和t2先join，t2作内表。再和t3join，t3作内表

结论，通过上述实践，在连接时，如果关联列没有索引，那么选择行数比较少的表做内表，可以提升查询效率；如果关联列有索引，选择建了索引的表做内表，可以提升查询效率。

/+ no_expand/

对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展。

/+ set(try_vector_engine_strategy force) /

参数说明：设置行存表走向量化执行引擎的策略。通过设置该参数，可以使包含行存表的查询可以转换为向量化的执行计划执行计算，从而提升类AP场景的复杂查询的执行性能。

该参数属于USERSET类型参数，请参考表1中对应设置方法进行设置。

取值范围：枚举型

off，为默认取值，表示关闭本功能，即行存表不会转换为向量的执行计划执行。
force，表示只要查询中不包含向量化引擎不支持的类型或者表达式，则不论查询的基表为行存表、列存表，还是行列混合存储的，强制将查询转换为向量化的执行计划执行计算。在这种情况下，针对不同的查询场景可能出现性能下降。
optimal，表示在force的基础上，由优化器根据查询的复杂度进行选择是否将查询语句转换为向量化的执行计划，尽可能避免转换为向量化的执行计划后出现性能下降。

/+ indexonlyscan(t1) /

/+ hashjoin(t t1) nestloop(t c)/

/+ rows(a #11842) /

/+ no tablescan(t1) no indexscan(t1) /

一、Plan Hint调优概述

Plan Hint为用户提供了直接影响执行计划生成的手段，用户可以通过指定join顺序、join、scan方法、指定结果行数等多个手段来进行执行计划的调优，以提升查询的性能。
Plan Hint支持在SELECT关键字后通过如下形式指定：

/*+ <plan hint>*/

可以同时指定多个hint，之间使用空格分隔。hint只能hint当前层的计划，对于子查询
计划的hint，需要在子查询的select关键字后指定hint。

支持范围：
指定Join顺序的Hint - leading hint
指定Join方式的Hint，仅支持除semi/anti join、unique plan之外的常用hint。
指定结果集行数的Hint
指定Scan方式的Hint，仅支持常用的tablescan、indexscan和indexonlyscan的hint。
指定子链接块名的Hint
不支持：
不支持Agg、Sort、Setop和Subplan的hint。

二、实践操作

1. Join顺序的Hint

指明join的顺序，包括内外表顺序。
语法：

仅指定join顺序，不指定内外表顺序。
leading(t1 t2 t3)
t1 t2 t3先join，3张表join顺序和内外表不限

同时指定join顺序和内外表顺序，内外表顺序仅在最外层生效。
leading((t1 t2 t3))
t1和t2先join，t2作内表。再和t3join，t3作内表

示例：

//score作内表
openGauss=# explain select /*+ leading((test_bak score))*/ * from test_bak natural join score;
                              QUERY PLAN                              
----------------------------------------------------------------------
 Hash Join  (cost=1.07..2204.52 rows=3 width=14)
   Hash Cond: (test_bak.id = score.id)
   ->  Seq Scan on test_bak  (cost=0.00..1875.99 rows=129999 width=8)
   ->  Hash  (cost=1.03..1.03 rows=3 width=10)
         ->  Seq Scan on score  (cost=0.00..1.03 rows=3 width=10)
(5 rows)

Time: 0.720 ms
//test_bak做内表，test_bak在连接关联列有索引
openGauss=# explain select /*+ leading((score test_bak))*/ * from test_bak natural join score;
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Nested Loop  (cost=0.00..25.87 rows=3 width=14)
   ->  Seq Scan on score  (cost=0.00..1.03 rows=3 width=10)
   ->  Index Scan using unq1 on test_bak  (cost=0.00..8.27 rows=1 width=8)
         Index Cond: (id = score.id)
(4 rows)

//test2做内表，9行数据
openGauss=# explain select /*+ leading((test1 test2)) */ * from test1 join test2 on test1.name = test2.name;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Hash Join  (cost=1.20..9.25 rows=579 width=16)
   Hash Cond: ((test1.name)::text = (test2.name)::text)
   ->  Seq Scan on test1  (cost=0.00..1.99 rows=99 width=8)
   ->  Hash  (cost=1.09..1.09 rows=9 width=8)
         ->  Seq Scan on test2  (cost=0.00..1.09 rows=9 width=8)
(5 rows)

Time: 3.065 ms

//test1做内表，连接关联列没有索引，99行数据
openGauss=# explain select /*+ leading((test2 test1)) */ * from test1 join test2 on test1.name = test2.name;
                            QUERY PLAN                            
------------------------------------------------------------------
 Hash Join  (cost=3.23..10.25 rows=579 width=16)
   Hash Cond: ((test2.name)::text = (test1.name)::text)
   ->  Seq Scan on test2  (cost=0.00..1.09 rows=9 width=8)
   ->  Hash  (cost=1.99..1.99 rows=99 width=8)
         ->  Seq Scan on test1  (cost=0.00..1.99 rows=99 width=8)
(5 rows)

Time: 0.770 ms

2. Join方式的Hint：

指明Join使用的方法，可以为Nested Loop、Hash Join和Merge Join。
语法格式：

[no] nestloop|hashjoin|mergejoin(table_list)

参数说明：
no：hint的join方式不使用
table_list:中间不允许出现括号指定join的优先级。
示例：

//使用hashjoin
openGauss=# explain select /*+ hashjoin(test_bak score)*/ * from test_bak natural join score;
                              QUERY PLAN                              
----------------------------------------------------------------------
 Hash Join  (cost=1.07..2204.52 rows=3 width=14)
   Hash Cond: (test_bak.id = score.id)
   ->  Seq Scan on test_bak  (cost=0.00..1875.99 rows=129999 width=8)
   ->  Hash  (cost=1.03..1.03 rows=3 width=10)
         ->  Seq Scan on score  (cost=0.00..1.03 rows=3 width=10)
(5 rows)

Time: 0.861 ms
//使用mergejoin
openGauss=# explain select /*+ mergejoin(test_bak score)*/ * from test_bak natural join score;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Merge Join  (cost=10000000001.07..100000000011.65 rows=3 width=14)
   Merge Cond: (test_bak.id = score.id)
   ->  Index Scan using unq1 on test_bak  (cost=0.00..3973.23 rows=129999 width=8)
   ->  Sort  (cost=1.05..1.06 rows=3 width=10)
         Sort Key: score.id
         ->  Seq Scan on score  (cost=0.00..1.03 rows=3 width=10)
(6 rows)

Time: 0.904 ms
//使用nestloop
openGauss=# explain select /*+ nestloop(test_bak score)*/ * from test_bak natural join score;
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Nested Loop  (cost=0.00..25.87 rows=3 width=14)
   ->  Seq Scan on score  (cost=0.00..1.03 rows=3 width=10)
   ->  Index Scan using unq1 on test_bak  (cost=0.00..8.27 rows=1 width=8)
         Index Cond: (id = score.id)
(4 rows)

Time: 0.858 ms

结论：当数据量大时，对于连接的表的关联列中存在索引时，选择nestloop方式进行连接，可以提升查询效率，默认会将关联列上建了索引的表作为内表；hashjoin方式连接时，会对连接的表都进行顺序扫描，会将行数少的表作为内表（先扫描的表），查询效率远高于mergejoin方式。当需要进行连接查询的表在关联列上都没有建立索引时，选择hashjoin方式进行连接，查询性能更高。

3. 行数的Hint

指明中间结果集的大小，支持绝对值和相对值的hint。
语法格式：

rows(table_list #|+|-|* const)

参数说明：
#、+、-、*，进行行数估算hint的四种操作符号。#表示直接使用后面的行数进行
hint。+、-、*表示对原来估算的行数进行加、减、乘操作，运算后的行数最小值为1
行
const可以是任意非负数，支持科学计数法

rows(t1 #5)表示：指定t1表的结果集为5行。
rows(t1 t2 t3 *1000)表示：指定t1、 t2、t3 join完的结果集的行数乘以1000。
示例：

//没有使用行数的Hint
openGauss=# explain select * from test1,test2 where test1.id = test2.id;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Hash Join  (cost=1.20..3.53 rows=9 width=16)
   Hash Cond: (test1.id = test2.id)
   ->  Seq Scan on test1  (cost=0.00..1.99 rows=99 width=8)
   ->  Hash  (cost=1.09..1.09 rows=9 width=8)
         ->  Seq Scan on test2  (cost=0.00..1.09 rows=9 width=8)
(5 rows)

Time: 0.878 ms
//指定test1与test2连接之后的结果集为9
openGauss=# explain select /*+ rows(test1 test2 #9)*/ * from test1,test2 where test1.id = test2.id;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Hash Join  (cost=1.20..3.53 rows=9 width=16)
   Hash Cond: (test1.id = test2.id)
   ->  Seq Scan on test1  (cost=0.00..1.99 rows=99 width=8)
   ->  Hash  (cost=1.09..1.09 rows=9 width=8)
         ->  Seq Scan on test2  (cost=0.00..1.09 rows=9 width=8)
(5 rows)

Time: 0.942 ms
//指定扫描test1后的结果集为9
openGauss=# explain select /*+ rows(test1 #9)*/ * from test1,test2 where test1.id = test2.id;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Hash Join  (cost=1.20..3.31 rows=9 width=16)
   Hash Cond: (test1.id = test2.id)
   ->  Seq Scan on test1  (cost=0.00..1.99 rows=9 width=8)
   ->  Hash  (cost=1.09..1.09 rows=9 width=8)
         ->  Seq Scan on test2  (cost=0.00..1.09 rows=9 width=8)
(5 rows)

Time: 0.823 ms

4. Scan方式的Hint：

指明scan使用的方法，可以是tablescan、indexscan和indexonlyscan。
语法格式：

[no] tablescan|indexscan|indexonlyscan(table [index])

参数说明：
no:表示hint的scan方式不使用。
table:表示hint指定的表，只能指定一个表，如果表存在别名应优先使用别名进行hint。
index:表示使用indexscan或indexonlyscan的hint时，指定的索引名称，当前只能指定一个。
示例：

//在表test1中创建一个普通索引，表test1有99条记录
openGauss=# create index test1_id_idx on test1(id);
CREATE INDEX
Time: 3.503 ms
//查看点查询执行计划
openGauss=# explain select * from test1 where id = 1;
                     QUERY PLAN                      
-----------------------------------------------------
 Seq Scan on test1  (cost=0.00..2.24 rows=1 width=8)
   Filter: (id = 1)
(2 rows)

Time: 0.914 ms
//指定扫描表的方式为indexscan
openGauss=# explain select /*+ indexscan(test1 test1_id_idx)*/ * from test1 where id = 1;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 [Bypass]
 Index Scan using test1_id_idx on test1  (cost=0.00..8.27 rows=1 width=8)
   Index Cond: (id = 1)
(3 rows)

Time: 0.723 ms

//test_bak表有129999条记录
openGauss=# explain select * from test_bak where id = 5067;
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on test_bak  (cost=0.00..2200.99 rows=1 width=8)
   Filter: (id = 5067)
(2 rows)

Time: 0.687 ms
openGauss=# create index unq1 on test_bak(id);
CREATE INDEX
Time: 483.102 ms
openGauss=# explain select * from test_bak where id = 5067;
                             QUERY PLAN                              
---------------------------------------------------------------------
 [Bypass]
 Index Scan using unq1 on test_bak  (cost=0.00..8.27 rows=1 width=8)
   Index Cond: (id = 5067)
(3 rows)

Time: 0.762 ms

结论：当表中数据比较少时，全表扫描的查询性能高于使用索引扫描，当不指定scan类型时，系统会自动选择seq scan，因为这种扫描方式代价小。当数据量很大时，为条件列建立索引可以大幅提升查询性能。

5. 子链接块名的Hint：

指明子链接块的名称
语法格式：

blockname (table)

示例：

openGauss=# explain select /*+ nestloop(test_bak t2)*/ * from test_bak where id in (select /*+ blockname(t2)*/ count(*) from test2 group by name);
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Nested Loop  (cost=1.23..34.38 rows=4 width=8)
   ->  HashAggregate  (cost=1.23..1.27 rows=4 width=8)
         Group By Key: t2.count
         ->  Subquery Scan on t2  (cost=1.14..1.22 rows=4 width=8)
               ->  HashAggregate  (cost=1.14..1.18 rows=4 width=12)
                     Group By Key: test2.name
                     ->  Seq Scan on test2  (cost=0.00..1.09 rows=9 width=4)
   ->  Index Scan using unq1 on test_bak  (cost=0.00..8.27 rows=1 width=8)
         Index Cond: (id = t2.count)
(9 rows)

Time: 1.144 ms

6. 优化器GUC参数的Hint

设置本次查询执行内生效的查询优化相关GUC参数
语法格式：

set(param value)

7. Custom Plan和Generic Plan选择的Hint

语法格式：

指定使用Custom Plan：
use_cplan

指定使用Generic Plan：
use_gplan

示例：

//强制使用Custom Plan,可以看到过滤条件为入参的实际值
openGauss=# prepare p as select /*+ use_cplan*/ * from test_bak where id = $1;
PREPARE
openGauss=# explain execute p(1);
                             QUERY PLAN                              
---------------------------------------------------------------------
 Index Scan using unq1 on test_bak  (cost=0.00..8.27 rows=1 width=8)
   Index Cond: (id = 1)
(2 rows)
//释放语句p
openGauss=# deallocate p;
DEALLOCATE
//强制使用Generic Plan,可以看到过滤条件为待填充的入参
openGauss=# prepare p as select /*+ use_gplan*/ * from test_bak where id = $1;
PREPARE
openGauss=# explain execute p(1);
                             QUERY PLAN                              
---------------------------------------------------------------------
 [Bypass]
 Index Scan using unq1 on test_bak  (cost=0.00..8.27 rows=1 width=8)
   Index Cond: (id = $1)
(3 rows)

8. 指定子查询不扩展的Hint

示例：

openGauss=# \timing on
Timing is on.
//正常的查询
openGauss=# explain select * from test1 where test1.id in (select id from test2);
                           QUERY PLAN                            
-----------------------------------------------------------------
 Hash Semi Join  (cost=1.20..3.53 rows=9 width=8)
   Hash Cond: (test1.id = test2.id)
   ->  Seq Scan on test1  (cost=0.00..1.99 rows=99 width=8)
   ->  Hash  (cost=1.09..1.09 rows=9 width=4)
         ->  Seq Scan on test2  (cost=0.00..1.09 rows=9 width=4)
(5 rows)

Time: 1.622 ms

//加入no_expand
openGauss=# explain select * from test1 where test1.id in (select /*+ no_expand*/ id from test2);
                         QUERY PLAN                          
-------------------------------------------------------------
 Seq Scan on test1  (cost=1.11..3.35 rows=50 width=8)
   Filter: (hashed SubPlan 1)
   SubPlan 1
     ->  Seq Scan on test2  (cost=0.00..1.09 rows=9 width=4)
(4 rows)

Time: 0.613 ms

9. 指定不使用全局计划缓存的Hint

全局计划缓存打开时，可以通过no_gpc Hint来强制单个查询语句不在全局共享计划缓存，只保留会话生命周期的计划缓存。
语法格式：

no_gpc

说明：本参数仅在enable_global_plancache=on时对PBE执行的语句生效。
dbe_perf.global_plancache_status视图中无结果即没有计划被全局缓存。

示例：

openGauss=# alter system set enable_global_plancache = on;
NOTICE:  please restart the database for the POSTMASTER level parameter to take effect.
ALTER SYSTEM SET
然后重启数据库
openGauss=# deallocate all;
DEALLOCATE ALL
openGauss=# prepare insert_test as insert /*+ no_gpc*/ into test2 select * from test1 where id = 11;
PREPARE
openGauss=# execute insert_test ;
INSERT 0 1
openGauss=# select * from dbe_perf.global_plancache_status;
 nodename | query | refcount | valid | databaseid | schema_name | params_num | func_id 
----------+-------+----------+-------+------------+-------------+------------+---------
(0 rows)

本文主要对plan hint中的一些语法进行简单的实践操作。

/*+ set(enable_nestloop off) set(enable_index_nestloop off) */

/*+ use_cplan */