Author: 卓刀
在使用数据库时，经常会有开发者有这样的疑问：“我的表对应字段已经创建了索引，为什么这个SQL 语句执行还是这么慢？” 虽然数据库SQL 执行慢有很多原因，但是对于PostgreSQL DBA 来说，好像有个共识，遇到用户慢SQL优化的问题，先拿EXPLAIN 命令查看下对应的查询计划，从而可以快速定位慢在哪里。这就引出了本文的主角—PostgreSQL 的EXPLAIN 命令。
在PostgreSQL 中，EXPLAIN 命令可以输出SQL 语句的查询计划，具体语法如下：
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
where option can be one of:
ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS [ boolean ]
BUFFERS [ boolean ]
TIMING [ boolean ]
SUMMARY [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }
其中：
阅读到这里，我们已经知道了如何使用EXPLAIN 命令。接下来，我们将学习如何理解EXPLAIN 的输出，从而快速地定位问题。
以下面的输出为例（该例子选自PostgreSQL官方文档），我们来分析下EXPLAIN 命令输出的结构：
EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)
Sort Key: t1.fivethous
Sort Method: quicksort Memory: 77kB
-> Hash Join (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)
Hash Cond: (t2.unique2 = t1.unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)
-> Hash (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 28kB
-> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)
Index Cond: (unique1 < 100)
Planning time: 0.194 ms
Execution time: 8.008 ms
从上面的例子来看，EXPLAIN 命令的输出可以看做是一个树形结构，我们称之为查询计划树，树的每个节点包括对应的节点类型，作用对象以及其他属性例如cost、rows、width 等。如果只显示节点类型，上面的例子可以简化为如下结构：
Sort
└── Hash Join
├── Seq Scan
└── Hash
└── Bitmap Heap Scan
└── Bitmap Index Scan
为了帮助大家更好地理解，我们简要介绍下PostgreSQL中SQL 执行的一些特点：
在了解了这些特点之后，我们会对EXPLAIN 命令的输出有一个整体结构的概念。其实EXPLAIN 输出的就是一个用户可视化的查询计划树，可以告诉我们执行了哪些节点（操作），并且每个节点（操作）的代价预估是什么样的。接下来，我们将详细去阐述每个节点的类型和具体干了哪些事情。
在EXPLAIN 命令的输出结果中可能包含多种类型的执行节点，我们可以大体分为几大类（参考彭智勇、彭煜玮编著的《PostgreSQL 数据库内核分析》）：
为了更有针对性，本文只介绍扫描节点。
扫描节点，简单来说就是为了扫描表的元组，每次获取一条元组（Bitmap Index Scan除外）作为上层节点的输入。当然严格的说，不光可以扫描表，还可以扫描函数的结果集、链表结构、子查询结果集等。
目前在PostgreSQL 中支持：
其中，我们将重点介绍最常用的几个：Seq Scan、Index Scan、IndexOnly Scan、BitmapIndex Scan、BitmapHeap Scan。
Seq Scan 是全表顺序扫描，一般查询没有索引的表需要全表顺序扫描，例如下面的EXPLAIN 输出：
postgres=> explain(ANALYZE,VERBOSE,BUFFERS) select * from class where st_no=2;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Seq Scan on public.class (cost=0.00..26.00 rows=1 width=35) (actual time=0.136..0.141 rows=1 loops=1)
Output: st_no, name
Filter: (class.st_no = 2)
Rows Removed by Filter: 1199
Buffers: shared hit=11
Planning time: 0.066 ms
Execution time: 0.160 ms
其中：
Index Scan 是索引扫描，主要用来在WHERE 条件中存在索引列时的扫描，如上面Seq Scan 中的查询如果在st_no 上创建索引，则EXPLAIN 输出如下：
postgres=> explain(ANALYZE,VERBOSE,BUFFERS) select * from class where st_no=2;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Index Scan using no_index on public.class (cost=0.28..8.29 rows=1 width=35) (actual time=0.022..0.023 rows=1 loops=1)
Output: st_no, name
Index Cond: (class.st_no = 2)
Buffers: shared hit=3
Planning time: 0.119 ms
Execution time: 0.060 ms
(6 rows)
其中：
可以看出，使用了索引之后，对相同表的相同条件的扫描速度变快了。这是因为从全表扫描变为索引扫描，通过Buffers: shared hit=3 可以看出，需要扫描的BLOCK（或者说元组）少了，所以需要的代价也就小了，速度也就快了。
IndexOnly Scan 是覆盖索引扫描，所需的返回结果能被所扫描的索引全部覆盖，例如上面Index Scan中的SQL 把“select * ” 修改为“select st_no” ，其EXPLAIN 结果输出如下：
postgres=> explain(ANALYZE,VERBOSE,BUFFERS) select st_no from class where st_no=2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Only Scan using no_index on public.class (cost=0.28..4.29 rows=1 width=4) (actual time=0.015..0.016 rows=1 loops=1)
Output: st_no
Index Cond: (class.st_no = 2)
Heap Fetches: 0
Buffers: shared hit=3
Planning time: 0.058 ms
Execution time: 0.036 ms
(7 rows)
其中：
虽然Index Only Scan 可以从索引直接输出结果。但是因为PostgreSQL MVCC 机制的实现，需要对扫描的元组进行可见性判断，即检查visibility MAP 文件。当新建表之后，如果没有进行过vacuum和autovacuum操作，这时还没有VM文件，而索引并没有保存记录的版本信息，索引Index Only Scan 还是需要扫描数据块（Heap Fetches 代表需要扫描的数据块个数）来获取版本信息，这个时候可能会比Index Scan 慢。
BitmapIndex Scan 与Index Scan 很相似，都是基于索引的扫描，但是BitmapIndex Scan 节点每次执行返回的是一个位图而不是一个元组，其中位图中每位代表了一个扫描到的数据块。而BitmapHeap Scan一般会作为BitmapIndex Scan 的父节点，将BitmapIndex Scan 返回的位图转换为对应的元组。这样做最大的好处就是把Index Scan 的随机读转换成了按照数据块的物理顺序读取，在数据量比较大的时候，这会大大提升扫描的性能。
我们可以运行set enable_indexscan =off; 来指定关闭Index Scan ，上文中Index Scan 中SQL 的EXPLAIN 输出结果则变为：
postgres=> explain(ANALYZE,VERBOSE,BUFFERS) select * from class where st_no=2;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.class (cost=4.29..8.30 rows=1 width=35) (actual time=0.025..0.025 rows=1 loops=1)
Output: st_no, name
Recheck Cond: (class.st_no = 2)
Heap Blocks: exact=1
Buffers: shared hit=3
-> Bitmap Index Scan on no_index (cost=0.00..4.29 rows=1 width=0) (actual time=0.019..0.019 rows=1 loops=1)
Index Cond: (class.st_no = 2)
Buffers: shared hit=2
Planning time: 0.088 ms
Execution time: 0.063 ms
(10 rows)
其中：
至此，我们对这几种主要的扫描节点有了一些认识。一般来说：
上面的结论都是基于理论分析得到的结果，但是其实PostgreSQL 的EXPLAIN 命令中输出的cost，rows，width 等代价估计信息中已经展示了这些扫描节点或者其他节点的预估代价，通过对预估代价的比较，可以选择出最小代价的查询计划树。
从上文可知，EXPLAIN 命令会在每个节点后面显示代价估计信息，包括cost、rows、width，这里将一一介绍。
在PostgreSQL 中，执行优化器会基于代价估计自动选择代价最小的查询计划树。而在EXPLAIN 命令的输出结果中每个cost 就是该执行节点的代价估计。它的格式是xxx..xxx，在.. 之前的是预估的启动代价，即找到符合该节点条件的第一个结果预估所需要的代价，在..之后的是预估的总代价。而父节点的启动代价包含子节点的总代价。
而在本文开头讲述PostgreSQL DBA 对慢SQL 的常见诊断方法就是使用EXPLAIN 命令，分析其中哪个节点cost （或者下文的 actual time ）最大，通过快速优化它达到优化慢SQL 的目的。
那cost 是怎么计算而来的呢？简单来说，是PostgreSQL 根据周期性收集到的统计信息（参考PostgreSQL · 特性分析 · 统计信息计算方法），按照一个代价估计模型计算而来的。其中会根据以下几个参数来作为代价估计的单位（详见PostgreSQL 官方文档）：
其中，seq_page_cost 和random_page_cost 可以使用ALTER TABLESPACE 对每个TABLESPACE 进行修改。
代价估计信息中的其他两个，rows 代表预估的行数，width 代表预估的结果宽度，单位为字节。两者都是根据表的统计信息预估而来的。
当EXPLAIN 命令中ANALYZE 选项为on时，会在代价估计信息之后输出真实执行信息，包括：
这里需要注意的是，代价估计信息一般是和真实执行信息比较相近的，即预估代价和实际时间成正比且返回结果集的行数相近。但是由于统计信息的时效性，有可能找到的预估代价最小的性能却很差，这就需要开发者调整参数或者主动执行vacuum analyze 命令对表的统计信息进行及时更新，保证PostgreSQL 的执行优化器能够找到相对较优的查询计划树。
至此，我们分析了EXPLAIN 命令的语法和输出的整个结构，可以发现它的输出实际上是PostgreSQL 查询计划树的一个可视化的摘要。在执行优化器内部，就是依靠这些信息找到最优的查询计划树并执行。但是执行优化器不是万能的，由于统计信息不准确或者代价估计模型的问题，PostgreSQL 有可能得到一个性能比较差的查询计划树并执行。这需要做一些case by case 的优化，感兴趣的同学可以参考《WHY IS MY INDEX NOT BEING USED》 和《RDS for PG加入Plan Hint功能》 等相关文章。