EXPLAIN输出

EXPLAIN输出结构

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

PolarDB中SQL执行的一些特点：

• 按照查询计划树从底往上执行。
• 基于火山模型执行，即可以简单理解为每个节点执行返回一行记录给父节点（Bitmap Index Scan 除外）。

通过以上特征可以了解到EXPLAIN输出的是一个用户可视化的查询计划树，可以查看到您执行了哪些节点（操作），并且每个节点（操作）的代价预估。

真实执行信息

当EXPLAIN命令中ANALYZE选项为TRUE时，会在代价估计信息之后输出真实执行信息，包括：

• actual time：执行时间，格式为xxx..xxx，在..之前的是该节点实际的启动时间，即找到符合该节点条件的第一个结果实际需要的时间，在..之后的是该节点实际的执行时间。
• rows：指的是该节点实际的返回行数。
• loops：指的是该节点实际的重启次数。如果一个计划节点在运行过程中，它的相关参数值（如绑定变量）发生了变化，就需要重新运行这个计划节点。

代价估计信息一般是和真实执行信息比较相近的，即预估代价和实际时间成正比且返回结果集的行数相近。但由于统计信息的时效性，有可能找到的预估代价最小的性能却很差，这就需要开发者调整参数或主动执行vacuum analyze命令对表的统计信息进行及时更新，保证PostgreSQL的执行优化器能够找到相对较优的查询计划树。

输出节点

• 节点类型
  在EXPLAIN命令的输出结果中可能包含多种类型的执行节点，可以大体分为几大类：

  • 控制节点（Control Node）
  • 扫描节点（ScanNode）
  • 物化节点（Materialization Node）
  • 连接节点（Join Node）
• 扫描节点

  扫描节点就是为了扫描表的元组，每次获取一条元组（Bitmap Index Scan除外）作为上层节点的输入。扫描节点不光可以扫描表，还可以扫描函数的结果集、链表结构、子查询结果集等。

  目前在PolarDB中支持：

  • 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、Index Scan、IndexOnly Scan、BitmapIndex Scan、BitmapHeap Scan。

• Seq 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

  其中：

  参数	说明
  Seq Scan on public.class	表明了这个节点的类型和作用对象，即在class表上进行了全表扫描。
  (cost=0.00..26.00 rows=1 width=35)	表明了这个节点的代价估计。
  (actual time=0.136..0.141 rows=1 loops=1)	表明了这个节点的真实执行信息，当EXPLAIN命令中的ANALYZE选项为TRUE时，会输出该项内容。
  Output: st_no, name	表明了SQL的输出结果集的各个列，当EXPLAIN命令中的选项VERBOSE为TRUE时才会显示。
  Filter: (class.st_no = 2)	表明了Seq Scan节点之上的Filter操作，即全表扫描时对每行记录进行过滤操作，过滤条件为class.st_no = 2。
  Rows Removed by Filter: 1199	表明了过滤操作过滤了多少行记录，属于Seq Scan节点的VERBOSE信息，只有EXPLAIN命令中的VERBOSE选项为TRUE时才会显示。
  Buffers: shared hit=11	表明了从共享缓存中命中了11个BLOCK，属于Seq Scan节点的BUFFERS信息，只有EXPLAIN命令中的BUFFERS选项为TRUE时才会显示。
  Planning time: 0.066 ms	表明了生成查询计划的时间。
  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)

  其中：

  参数	说明
  Index Scan using no_index on public.class	表明是使用的public.class表的no_index索引对表进行索引扫描的。
  Index Cond: (class.st_no = 2)	表明索引扫描的条件是class.st_no = 2。

  通过示例可以看出，使用了索引之后，对相同表的相同条件的扫描速度变快了。这是因为从全表扫描变为索引扫描，通过Buffers: shared hit=3可以得出结果，需要扫描的元组（BLOCK）变少，所需要的代价就会变小，扫描速度就会变快。

Index Only 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)

虽然Index Only Scan可以从索引直接输出结果。但是因为PostgreSQL MVCC机制的实现，需要对扫描的元组进行可见性判断，即检查visibility MAP文件。当新建表之后，如果没有进行过vacuum和autovacuum操作，这时还没有VM文件，而索引并没有保存记录的版本信息，索引Index Only Scan还是需要扫描数据块（Heap Fetches代表需要扫描的数据块个数）来获取版本信息，这个时候可能会比Index Scan慢。

Bitmap Index Scan与Bitmap Heap 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)

其中：

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

以上结论都是基于理论分析得到的结果，其实PostgreSQL的EXPLAIN命令中输出的cost，rows，width等代价估计信息中已经展示了这些扫描节点或者其他节点的预估代价，通过对预估代价的比较，可以选择出最小代价的查询计划树。