PostgreSQL硬核学习03--执行计划简单深入(扫描节点)

假期在家还是要学习滴~~
PostgreSQL学习01-常见SQL写法
PostgreSQL学习02-调优必备之查看sql的执行计划（简单查看）

1.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 }

• ANALYZE 选项为TRUE 会实际执行SQL，并获得相应的查询计划，默认为FALSE。如果优化一些修改数据的SQL
  需要真实的执行但是不能影响现有的数据，可以放在一个事务中，分析完成后可以直接回滚。 VERBOSE 选项为TRUE
  会显示查询计划的附加信息，默认为FALSE。附加信息包括查询计划中每个节点（后面具体解释节点的含义）输出的列（Output），表的SCHEMA
  信息，函数的SCHEMA 信息，表达式中列所属表的别名，被触发的触发器名称等。

• COSTS 选项为TRUE
  会显示每个计划节点的预估启动代价（找到第一个符合条件的结果的代价）和总代价，以及预估行数和每行宽度，默认为TRUE。

• BUFFERS 选项为TRUE 会显示关于缓存的使用信息，默认为FALSE。该参数只能与ANALYZE
  参数一起使用。缓冲区信息包括共享块（常规表或者索引块）、本地块（临时表或者索引块）和临时块（排序或者哈希等涉及到的短期存在的数据块）的命中块数，更新块数，挤出块数。

• TIMING 选项为TRUE 会显示每个计划节点的实际启动时间和总的执行时间，默认为TRUE。该参数只能与ANALYZE
  参数一起使用。因为对于一些系统来说，获取系统时间需要比较大的代价，如果只需要准确的返回行数，而不需要准确的时间，可以把该参数关闭。
  SUMMARY 选项为TRUE 会在查询计划后面输出总结信息，例如查询计划生成的时间和查询计划执行的时间。当ANALYZE
  选项打开时，它默认为TRUE。

• FORMAT 指定输出格式，默认为TEXT。各个格式输出的内容都是相同的，其中XML | JSON | YAML
  更有利于我们通过程序解析SQL 语句的查询计划，为了更有利于阅读，我们下文的例子都是使用TEXT 格式的输出结果。

2.节点类型

• 控制节点（Control Node)
• 扫描节点（ScanNode)
• 物化节点（Materialization Node)
• 连接节点（Join Node)
  扫描节点，简单来说就是为了扫描表的元组，每次获取一条元组（Bitmap Index Scan除外）作为上层节点的输入。当然严格的说，不光可以扫描表，还可以扫描函数的结果集、链表结构、子查询结果集等。
  支持：
  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

针对以上输出：
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选项为on时，会输出该项内容，具体的含义我们将在下文节点执行信息中详细介绍
Output: st_no, name 表明了SQL 的输出结果集的各个列，当EXPLAIN 命令中的选项VERBOSE 为on时才会显示
Filter: (class.st_no = 2) 表明了Seq Scan 节点之上的Filter 操作，即全表扫描时对每行记录进行过滤操作，过滤条件为class.st_no = 2
Rows Removed by Filter: 1199 表明了过滤操作过滤了多少行记录，属于Seq Scan 节点的VERBOSE 信息，只有EXPLAIN 命令中的VERBOSE 选项为on 时才会显示
Buffers: shared hit=11 表明了从共享缓存中命中了11 个BLOCK，属于Seq Scan 节点的BUFFERS 信息，只有EXPLAIN 命令中的BUFFERS 选项为on 时才会显示
Planning time: 0.066 ms 表明了生成查询计划的时间
Execution time: 0.160 ms 表明了实际的SQL 执行时间，其中不包括查询计划的生成时间。

3.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（或者说元组）少了，所以需要的代价也就小了，速度也就快了。

• 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 using no_index on public.class 表明使用public.class表的no_index 索引对表进行覆盖索引扫描

• Heap Fetches 表明需要扫描数据块的个数。

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

4.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)
  ==Bitmap Index Scan on no_index ==表明使用no_index 索引进行位图索引扫描
  Index Cond: (class.st_no = 2) 表明位图索引的条件为class.st_no = 2
  Bitmap Heap Scan on public.class 表明对public.class 表进行Bitmap Heap 扫描
  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
  Heap Blocks: exact=1 表明准确扫描到数据块的个数是1