openGauss 执行计划说明 -- cnDBA.cn

1 执行计划说明

1.1 SQL引擎执行SQL语句步骤

SQL引擎从接受SQL语句到执行SQL语句需要经历的步骤如下。其中，红色字体部分为DBA可以介入实施调优的环节。

语法&词法解析：按照约定的SQL语句规则，把输入的SQL语句从字符串转化为格式化结构（Stmt）。
语义解析：将“语法&词法解析”输出的格式化结构转化为数据库可以识别的对象。
查询重写：根据规则把“语义解析”的输出等价转化为执行上更为优化的结构。
查询优化：根据“查询重写”的输出和数据库内部的统计信息规划SQL语句具体的执行方式，也就是执行计划。
查询执行：根据“查询优化”规划的执行路径执行SQL查询语句。底层存储方式的选择合理性，将影响查询执行效率。详见调优手段之底层存储。

1.2 执行计划内容

1.2.1 表访问方式

Seq Scan：全表顺序扫描。
Index Scan：优化器决定使用两步的规划：最底层的规划节点访问一个索引，找出匹配索引条件的行的位置，然后上层规划节点真实地从表中抓取出那些行。独立地抓取数据行比顺序地读取它们的开销高很多，但是因为并非所有表的页面都被访问了，这么做实际上仍然比一次顺序扫描开销要少。使用两层规划的原因是，上层规划节点在读取索引标识出来的行位置之前，会先将它们按照物理位置排序，这样可以最小化独立抓取的开销。

索引扫描可以分为以下几类，他们之间的差异在于索引的排序机制。

Bitmap Index Scan：使用位图索引抓取数据页。
Index Scan using index_name：使用简单索引搜索，该方式表的数据行是以索引顺序抓取的，这样就令读取它们的开销更大，但是这里的行少得可怜，因此对行位置的额外排序并不值得。最常见的就是看到这种规划类型只抓取一行，以及那些要求ORDER BY条件匹配索引顺序的查询。因为那时候没有多余的排序步骤是必要的以满足ORDER BY。

1.2.2 表连接方式

Nested Loop：嵌套循环，适用于被连接的数据子集较小的查询。在嵌套循环中，外表驱动内表，外表返回的每一行都要在内表中检索找到它匹配的行，因此整个查询返回的结果集不能太大（不能大于10000），要把返回子集较小的表作为外表，而且在内表的连接字段上建议要有索引。http://www.cndba.cn/dave/article/116559
(Sonic) Hash Join：哈希连接，适用于数据量大的表的连接方式。优化器使用两个表中较小的表，利用连接键在内存中建立hash表，然后扫描较大的表并探测散列，找到与散列匹配的行。Sonic和非Sonic的Hash Join的区别在于所使用hash表结构不同，不影响执行的结果集。
Merge Join：归并连接，通常情况下执行性能差于哈希连接。如果源数据已经被排序过，在执行融合连接时，并不需要再排序，此时融合连接的性能优于哈希连接。

1.2.3 运算符

sort：对结果集进行排序。
filter：EXPLAIN输出显示WHERE子句当作一个“filter”条件附属于顺序扫描计划节点。这意味着规划节点为它扫描的每一行检查该条件，并且只输出符合条件的行。预计的输出行数降低了，因为有WHERE子句。不过，扫描仍将必须访问所有 10000 行，因此开销没有降低；实际上它还增加了一些（确切的说，通过10000 * cpu_operator_cost）以反映检查WHERE条件的额外CPU时间。
LIMIT：LIMIT限定了执行结果的输出记录数。如果增加了LIMIT，那么不是所有的行都会被检索到。

2 操作示例

2.1 执行计划显示信息

除了设置不同的执行计划显示格式外，还可以通过不同的EXPLAIN用法，显示不同详细程度的执行计划信息。http://www.cndba.cn/dave/article/116559

常见有如下几种：

EXPLAIN statement：只生成执行计划，不实际执行。其中statement代表SQL语句。
EXPLAIN ANALYZE statement：生成执行计划，进行执行，并显示执行的概要信息。显示中加入了实际的运行时间统计，包括在每个规划节点内部花掉的总时间（以毫秒计）和它实际返回的行数。
EXPLAIN PERFORMANCE statement：生成执行计划，进行执行，并显示执行期间的全部信息。

为了测量运行时在执行计划中每个节点的开销，EXPLAIN ANALYZE或EXPLAIN PERFORMANCE会在当前查询执行上增加性能分析的开销。在一个查询上运行EXPLAIN ANALYZE或EXPLAIN PERFORMANCE有时会比普通查询明显的花费更多的时间。超支的数量依赖于查询的本质和使用的平台。

因此，当定位SQL运行慢问题时，如果SQL长时间运行未结束，建议通过EXPLAIN命令查看执行计划，进行初步定位。如果SQL可以运行出来，则推荐使用EXPLAIN ANALYZE或EXPLAIN PERFORMANCE查看执行计划及其实际的运行信息，以便更精准地定位问题原因。

2.2 语法说明

2.2.1 显示SQL语句的执行计划，支持多种选项，对选项顺序无要求。

EXPLAIN [ (  option  [, ...] )  ] statement;

其中选项option子句的语法为。
ANALYZE [ boolean ] |
    ANALYSE [ boolean ] |
    VERBOSE [ boolean ] |
    COSTS [ boolean ] |
    CPU [ boolean ] |
    DETAIL [ boolean ] |(不可用)
    NODES [ boolean ] |(不可用)
    NUM_NODES [ boolean ] |(不可用)
    BUFFERS [ boolean ] |
    TIMING [ boolean ] |
    PLAN [ boolean ] |
    FORMAT { TEXT | XML | JSON | YAML }

2.2.2 显示SQL语句的执行计划，且要按顺序给出选项。

EXPLAIN { [ { ANALYZE | ANALYSE } ] [ VERBOSE ] | PERFORMANCE } statement;
http://www.cndba.cn/dave/article/116559

一般针对select SQL 查看执行计划，如果用户想使用EXPLAIN分析INSERT、UPDATE、DELETE、CREATE TABLE AS或EXECUTE语句，而不想改动数据（执行这些语句会影响数据），请使用如下方法。http://www.cndba.cn/dave/article/116559

START TRANSACTION;
EXPLAIN ANALYZE ...;
ROLLBACK;

2.2.3 EXPLAIN PLAN语法

通过EXPLAIN PLAN命令可以将查询执行的计划信息存储于PLAN_TABLE表中。与EXPLAIN命令不同的是，EXPLAIN PLAN仅将计划信息进行存储，而不会打印到屏幕。http://www.cndba.cn/dave/article/116559

EXPLAIN PLAN
[ SET STATEMENT_ID = string ]
FOR statement ;

STATEMENT_ID用户可以对查询设置标签，输入的标签信息也将存储于PLAN_TABLE中。

PLAN_TABLE中的数据是session级生命周期并且session隔离和用户隔离，用户只能看到当前session、当前用户的数据。

2.3 操作示例

[dave@www.cndba.cn ~]$ gsql -p 15500 -d postgres  -r 
gsql ((openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:07:56 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# select * from cndba;
 id |       website        
----+----------------------
  1 | https://www.cndba.cn
(1 row)

查看SQL 执行计划：
openGauss=# explain select * from cndba;
                       QUERY PLAN                        
---------------------------------------------------------
 Seq Scan on cndba  (cost=0.00..18.01 rows=801 width=72)
(1 row)

指定输出格式
注意：只有当 explain_perf_mode 为 normal 时，才支持 json 格式
openGauss=# set explain_perf_mode=normal;
SET
openGauss=# explain (format json) select * from cndba;
           QUERY PLAN            
---------------------------------
 [                              +
   {                            +
     "Plan": {                  +
       "Node Type": "Seq Scan", +
       "Relation Name": "cndba",+
       "Alias": "cndba",        +
       "Startup Cost": 0.00,    +
       "Total Cost": 18.01,     +
       "Plan Rows": 801,        +
       "Plan Width": 72         +
     }                          +
   }                            +
 ]
(1 row)

openGauss=# create index idx_cndba_id on cndba(id);
CREATE INDEX
openGauss=# explain select * from cndba where id=1;
                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on cndba  (cost=0.00..1.01 rows=1 width=72)
   Filter: (id = 1)
(2 rows)

openGauss=# insert into cndba values(generate_series(1, 200), 'https://www.cndba.cn');
INSERT 0 200
openGauss=# explain select * from cndba where id=1;
                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on cndba  (cost=0.00..2.02 rows=1 width=72)
   Filter: (id = 1)
(2 rows)

openGauss=# explain select * from cndba where id=100;
                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on cndba  (cost=0.00..2.02 rows=1 width=72)
   Filter: (id = 100)
(2 rows)

openGauss=# explain select id,website from cndba where id=100;
                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on cndba  (cost=0.00..2.02 rows=1 width=72)
   Filter: (id = 100)
(2 rows)

可以通过 costs 选项，指定是否显示开销
openGauss=# explain (costs false)select id,website from cndba where id=100;
      QUERY PLAN      
----------------------
 Seq Scan on cndba
   Filter: (id = 100)
(2 rows)

openGauss=# explain analyze select id,website from cndba where id=100;
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Seq Scan on cndba  (cost=0.00..2.02 rows=1 width=72) (actual time=0.075..0.111 rows=1 loops=1)
   Filter: (id = 100)
   Rows Removed by Filter: 200
 Total runtime: 0.194 ms
(4 rows)

openGauss=# analyze cndba;
ANALYZE
openGauss=# explain select id,website from cndba where id=100;
                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on cndba  (cost=0.00..4.51 rows=1 width=25)
   Filter: (id = 100)
(2 rows)

openGauss=# 

EXPLAIN PLAN将计划信息进行存储，而不会打印到屏幕：
openGauss=# explain plan set statement_id='cndba' for select id,website from cndba where id=100;
EXPLAIN SUCCESS
openGauss=# select * from plan_table;
 statement_id |     plan_id      | id |  operation   | options  | object_name | object_type | object_owner | projection  |  cost  | cardinality 
--------------+------------------+----+--------------+----------+-------------+-------------+--------------+-------------+--------+-------------
 cndba        | 3659174697403414 |  1 | TABLE ACCESS | SEQ SCAN | cndba       | TABLE       | public       | id, website | 4.5125 |           1
(1 row)

openGauss=# 
PLAN_TABLE中的数据是session级生命周期，我们重新开个窗口，就看不到执行计划数据了：
[dave@www.cndba.cn ~]$ gsql  -p 15500 -d postgres -U omm -W omm@123456 -r 
gsql ((openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:07:56 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# select * from plan_table;
 statement_id | plan_id | id | operation | options | object_name | object_type | object_owner | projection | cost | cardinality 
--------------+---------+----+-----------+---------+-------------+-------------+--------------+------------+------+-------------
(0 rows)

openGauss=#

签到成功

CNDBA社区