PG数据库中的EXPLAIN用法

一、EXPLAIN语法

EXPLAIN命令的主要作用是输出一个query的整个查询计划，具体语法如下：

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 }

我只介绍用到的，后面再慢慢补充吧。。。。

1. ANALYZE ：选项默认为true，会把query实际执行一遍。
2. 这篇文章写的挺好的: 文章
3. pg11之后增加了并行功能：并行

二、EXPLAIN的输出结构

explain命令的输出可以看做是一个树形结构-查询计划树，树的每个节点包含对应的节点类型，作用对象以及其他属性如cost,rows,width等。
例如：

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

可化简为：

Sort
└── Hash Join
    ├── Seq Scan
    └── Hash
        └── Bitmap Heap Scan
            └── Bitmap Index Scan

在PG中实际的查询是从下往上执行的，基于火山模型？（每个节点返回一行记录给父节点）。其实EXPLAIN 输出的就是一个用户可视化的查询计划树，可以告诉我们执行了哪些节点（操作），并且每个节点（操作）的代价预估是什么样的。接下来，我们将详细去阐述每个节点的类型和具体干了哪些事情。

节点类型

1. 控制节点
2. 扫描节点
3. 物化节点
4. 连接节点

扫描节点

扫描节点简单来说就是为了扫描表的元组，每次获取一条元组作为上层节点的输入。当然严格的说，不光可以扫描表，还可以扫描函数的结果集、链表结构、子查询结果集等。

Seq Scan，顺序扫描
Index Scan，基于索引扫描，但不只是返回索引列的值
IndexOnly Scan，基于索引扫描，并且只返回索引列的值，简称为覆盖索引
BitmapIndex Scan，利用Bitmap 结构扫描
BitmapHeap Scan，把BitmapIndex Scan 返回的Bitmap 结构转换为元组结构
Tid Scan，用于扫描一个元组TID 数组
Subquery Scan，扫描一个子查询
Function Scan，处理含有函数的扫描
TableFunc Scan，处理tablefunc 相关的扫描
Values Scan，用于扫描Values 链表的扫描
Cte Scan，用于扫描WITH 字句的结果集
NamedTuplestore Scan，用于某些命名的结果集的扫描
WorkTable Scan，用于扫描Recursive Union 的中间数据
Foreign Scan，用于外键扫描
Custom 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

其中：

1. Seq Scan on public.class 表明了这个节点的类型和作用对象，即在class 表上进行了全表扫描
2. (cost=0.00…26.00 rows=1 width=35) 表明了这个节点的代价估计，这部分我们将在下文节点代价估计信息中详细介绍
3. (actual time=0.136…0.141 rows=1 loops=1) 表明了这个节点的真实执行信息，当EXPLAIN 命令中的ANALYZE选项为on时，会输出该项内容，具体的含义我们将在下文节点执行信息中详细介绍
4. Output: st_no, name 表明了query的输出结果集的各个列，当EXPLAIN 命令中的选项VERBOSE 为on时才会显示
5. Filter: (class.st_no = 2) 表明了Seq Scan 节点之上的Filter 操作，即全表扫描时对每行记录进行过滤操作，过滤条件为class.st_no = 2
6. Rows Removed by Filter: 1199 表明了过滤操作过滤了多少行记录，属于Seq Scan 节点的VERBOSE 信息，只有EXPLAIN 命令中的VERBOSE 选项为on 时才会显示
7. Buffers: shared hit=11 表明了从共享缓存中命中了11 个BLOCK，属于Seq Scan 节点的BUFFERS 信息，只有EXPLAIN 命令中的BUFFERS 选项为on 时才会显示
8. Planning time: 0.066 ms 表明了生成查询计划的时间
9. Execution time: 0.160 ms 表明了实际的SQL 执行时间，其中不包括查询计划的生成时间

Index Scan

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)

其中：

1. Index Scan using no_index on public.class 表明是使用的public.class 表的no_index 索引对表进行索引扫描的
2. Index Cond: (class.st_no = 2) 表明索引扫描的条件是class.st_no = 2
   可以看出，使用了索引之后，对相同表的相同条件的扫描速度变快了。这是因为从全表扫描变为索引扫描，通过Buffers: shared hit=3 可以看出，需要扫描的BLOCK（或者说元组）少了，所以需要的代价也就小了，速度也就快了。

IndexOnly Scan

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)

其中：

1. Index Only Scan using no_index on public.class 表明使用public.class 表的no_index 索引对表进行覆盖索引扫描
2. Heap Fetches 表明需要扫描数据块的个数。

BitmapIndex Scan 与BitmapHeap 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)

其中：

1. Bitmap Index Scan on no_index 表明使用no_index 索引进行位图索引扫描
2. Index Cond: (class.st_no = 2) 表明位图索引的条件为class.st_no = 2
3. Bitmap Heap Scan on public.class 表明对public.class 表进行Bitmap Heap 扫描
4. Recheck Cond: (class.st_no = 2) 表明Bitmap Heap Scan 的Recheck操作 的条件是class.st_no = 2，这是因为Bitmap Index Scan 节点返回的是位图，位图中每位代表了一个扫描到的数据块，通过位图可以定位到一些符合条件的数据块（这里是3，Buffers: shared hit=3），而Bitmap Heap Scan 则需要对每个数据块的元组进行Recheck
5. Heap Blocks: exact=1 表明准确扫描到数据块的个数是1

一般来说：

1. 大多数情况下，Index Scan 要比 Seq Scan 快。但是如果获取的结果集占所有数据的比重很大时，这时Index Scan 因为要先扫描索引再读表数据反而不如直接全表扫描来的快。
2. 如果获取的结果集的占比比较小，但是元组数很多时，可能Bitmap Index Scan 的性能要比Index Scan 好。
3. 如果获取的结果集能够被索引覆盖，则Index Only Scan 因为不用去读数据，只扫描索引，性能一般最好。但是如果VM 文件未生成，可能性能就会比Index Scan 要差。