openGauss源码学习（一）执行计划分析

汇总链接：

---

---

前言

执行计划可以说是排查SQL执行慢的必备手段，迁移或者业务中遇到烂SQL或者慢SQL，执行计划可以更好地帮助排查问题根因。

---

一、执行计划简介

很多数据库基础算子的主要逻辑都是类似的，比如全表扫描、索引扫描、Nestloop Join、HashJoin、Group、Order等等，我们还是以openGauss为例，对于主要的算子做一些说明。

1. 几种EXPLAIN方式

通过EXPLAIN语句可以查看语句的执行计划，主要语法如下：

openGauss=# \h EXPLAIN
Command:     EXPLAIN
Description: show the execution plan of a statement
Syntax:
EXPLAIN [ (  option  [, ...] )  ] statement;
EXPLAIN  { [  { ANALYZE  | ANALYSE  }  ] [ VERBOSE  ]  | PERFORMANCE  } statement;

where option can be:
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 }

其中analyze、buffers和performance选项比较重要，analyze可以帮助查看语句实际执行时间和行数，buffers可以看到实际io情况，而performance可以看作是一个汇总，提供了多种执行信息。

2. 字段说明

下面用一个简单查询的执行计划说明一下

CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 SELECT x,x FROM generate_series(1,10) x; -- 插入10条数据
ANALYZE t1;
EXPLAIN ANALYZE SELECT * FROM t1 WHERE a=1;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..1.12 rows=1 width=8) (actual time=0.037..0.044 rows=1 loops=1)
   Filter: (a = 1)
   Rows Removed by Filter: 9
 Total runtime: 0.238 ms
(4 rows)

Seq Scan对应的是全表扫描算子，说明t1表是通过全表扫描的方式获取tuple。后面的部分则是说明了算子的一些估算信息，cost代表算子的代价，其中0.00为启动代价（返回第一条数据的代价，limit场景会更看重这个代价），1.12为算子全部代价（选择计划时一般都是选择total cost更小的计划），rows则是算子估算的结果集大小。

而后面的括号则是ANALYZE统计到的实际执行数据，actual time是算子的实际执行时间，0.037是返回第一条记录的时间，0.044是整个算子的执行时间，rows是实际行数，loops则是重复了多少次（多数在Nestloop Join中会出现loops大于1的情况）。

下面的Rows Removed by Filter则说明了过滤条件筛选掉的行数。

Total runtime这个字段是整个执行器实际的执行时间，PG新版本中则是还额外增加了一个字段统计优化器的执行时间。

二、慢SQL初步排查

1. 排查非数据库因素

一个SQL执行慢可能由多方面的原因导致的，在遇到问题的时候，可以先尝试排查下是否是非数据库因素导致的，比如是否服务器CPU负载过高，网络是否存在波动（分布式和远程连接情况下），以及IO是否正常。如果这些原因都已经排查过，都不是瓶颈所在，那么就需要从执行计划进一步分析是否是SQL的原因了。

2. 如何从计划分析性能问题

还是手动构造个例子，举例说明下我的一般排查思路

CREATE TABLE t2(a INT, b INT);
INSERT INTO t2 SELECT 1, x FROM generate_series(1,100000) x;
EXPLAIN
SELECT tt2.*
FROM t2 tt2
	,(
		SELECT t1.*
		FROM t2 t1
			,(
				SELECT a
					,max(b) b
				FROM t2
				WHERE a > 0
				GROUP BY a
				) s1
		WHERE t1.a = s1.a
			AND t1.b = s1.b
		) s2
WHERE tt2.a = s2.a;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Nested Loop  (cost=2109.42..6804.31 rows=95222 width=8)
   Join Filter: (t1.a = tt2.a)
   ->  Hash Join  (cost=2109.42..4218.82 rows=1 width=8)
         Hash Cond: ((t1.a = t2.a) AND (t1.b = (max(t2.b))))
         ->  Seq Scan on t2 t1  (cost=0.00..1395.22 rows=95222 width=8)
         ->  Hash  (cost=2109.41..2109.41 rows=1 width=8)
               ->  HashAggregate  (cost=2109.39..2109.40 rows=1 width=12)
                     Group By Key: t2.a
                     ->  Seq Scan on t2  (cost=0.00..1633.28 rows=95222 width=8)
                           Filter: (a > 0)
   ->  Seq Scan on t2 tt2  (cost=0.00..1395.22 rows=95222 width=8)
(11 rows)

这个SQL看起来比较复杂，但是拆分看可以发现只用到了一个表，并且是表重复在做自关联。插入的数据也没有重复值，结果集也只有10w行数据，为什么NestLoop会很长时间都跑不出来呢？

这种情况一般我会先排查是否有SubPlan（类似于NL），是否数据量很大，或者条件估算是否准确。结合实际计划，并没有发现上面所说的几点共性问题，但是最上层选择了NL，这里有点可疑。

先看下关掉了NL以后的执行结果，只需要500ms就可以跑出来，实际结果和估算的结果好像也差不太多。上面计划说明优化器最终认为NL的代价更小，但实际HashJoin的执行耗时明显更小。

EXPLAIN ANALYZE
SELECT tt2.*
FROM t2 tt2
	,(
		SELECT t1.*
		FROM t2 t1
			,(
				SELECT a
					,max(b) b
				FROM t2
				WHERE a > 0
				GROUP BY a
				) s1
		WHERE t1.a = s1.a
			AND t1.b = s1.b
		) s2
WHERE tt2.a = s2.a;
                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=4218.83..6804.92 rows=95222 width=8) (actual time=458.117..545.957 rows=100000 loops=1)
   Hash Cond: (tt2.a = t1.a)
   ->  Seq Scan on t2 tt2  (cost=0.00..1395.22 rows=95222 width=8) (actual time=0.021..18.469 rows=100000 loops=1)
   ->  Hash  (cost=4218.82..4218.82 rows=1 width=8) (actual time=454.481..454.481 rows=100000 loops=1)
          Buckets: 131072  Batches: 1  Memory Usage: 4931kB
         ->  Hash Join  (cost=2109.42..4218.82 rows=1 width=8) (actual time=274.756..402.119 rows=100000 loops=1)
               Hash Cond: ((t1.a = t2.a) AND (t1.b = (max(t2.b))))
               ->  Seq Scan on t2 t1  (cost=0.00..1395.22 rows=95222 width=8) (actual time=0.012..18.798 rows=100000 loops=1)
               ->  Hash  (cost=2109.41..2109.41 rows=1 width=8) (actual time=265.746..265.746 rows=100000 loops=1)
                      Buckets: 131072  Batches: 1  Memory Usage: 4931kB
                     ->  HashAggregate  (cost=2109.39..2109.40 rows=1 width=12) (actual time=131.397..204.098 rows=100000 loops=1)
                           Group By Key: t2.a
                           ->  Seq Scan on t2  (cost=0.00..1633.28 rows=95222 width=8) (actual time=0.028..30.604 rows=100000 loops=1)
                                 Filter: (a > 0)
 Total runtime: 562.418 ms
(15 rows)

现在可以确定是优化器选择了错误的计划（NL）导致执行时间很长，接下来从行数估算开始排查原因和场景。NL内外表分别为基表的全表扫描和HashJoin。全表扫描的估算信息看起来和实际出入不大，而另一侧的s1和t1表做join的结果集估算和实际执行结果有很大的差距！HashJoin的结果集估算有1行，但实际却有10w行，导致优化器误认为NestLoop仅需执行1*10w次计算即可得到结果，而选择了NL。但实际执行的时候，NL需要执行10w*10w次才可以计算出结果，这个计算量就很恐怖了。

那么到这里解决方案就很清晰了，可以通过guc参数关掉NestLoop或者通过Hint对语句设置Join方式来调优。

---

总结

优化器大多选择估算模型，有的时候会出现这种很明显的bad case。本篇文章主要介绍了一下EXPLAIN的语法和字段含义，还有遇到慢SQL如何从计划尝试分析原因。当然实际情况会复杂的多，还是要对内核有更深入的了解才能更快地发现更深层次的原因。
下一篇博客计划从内核代码的层面介绍下OG是如何做行数估算的，以及分析下上面的SQL为什么会出现估算行数和实际行数有如此之大差距的原因。