合 PG中的执行计划EXPLAIN介绍

EXPLAIN命令

• 常用语句

• • 预生成执行计划

  EXPLAIN sql

  • 真实执行计划

  EXPLAIN ANALYZE sql

  • 输出详细内容

  EXPLAIN (ANALYZE on, TIMING on, VERBOSE on, BUFFERS on, COSTS on) sql

• 语法

  EXPLAIN [ ( option [, ...] ) ] statement EXPLAIN [ ANALYZE ] [ VERBOSE ] statemen

• 选项

• • analyze：执行语句并显示真正的运行时间和其它统计信息，会真正执行SQL语句；
  • verbose：显示额外的信息,尤其是计划树中每个节点的字段列表,schema识别表和函数名称。总是打印统计数据中显示的每个触发器的名字；
  • costs：包括每个计划节点的启动成本预估和总成本的消耗，也包括行数和行宽度的预估；
  • buffers：使用信息，特别包括共享块命中、读、脏和写的次数，本地块命中、读、脏和写，临时块读和写的次数；
  • timing：在输出中包含实际启动时间和每个节点花费的时间，重复读系统块在某些系统上会显著的减缓查询的速度，只在ANALYZE也启用的时候使用；
  • format：声明输出格式，可以为TEXT、XML、JSON 或 YAML，默认 text；

EXPLAIN 输出

• cost：这是查询执行的总代价。代价是通过估计查询执行所需的资源和时间来计算的，代价越低表示查询执行越高效。以点点“..”分为2个部分数字，第一个数字表示启动的成本，也就是返回第一行需要多少 cost 值；第二个数字表示返回所有的数据的成本。

  0.00: 这是查询执行的启始代价，也就是开始执行查询时的代价估计。

  1555.49: 这是查询执行的总代价，表示执行整个查询所需的估计代价。

  默认 cost 值如下:

  • 顺序扫描一个数据块，cost值定为1，参数为seq_page_cost
  • 随机扫描一个数据块，cost值定为4，参数为random_page_cost
  • 处理一个数据行的CPU，cost为0.01，参数为cpu_tuple_cost
  • 处理一个索引行的CPU，cost为0.005，参数为cpu_index_tuple_cost
  • 每个操作符的 CPU 代价为 0.0025，参数为cpu_operator_cost

• rows：表示会返回多少行

• width：表示每行平均宽度为多少字节，在这个例子中，每一行的宽度是 15 字节。

• buffers

• • shared hit：表示在共享内存中直接读到 xxx 个块，
  • read：表示从磁盘读了 xxx 块
  • written：写磁盘工 xxx 块

执行计划含义

• Seq Scan：全表扫描，当数据表中没有索引，或者满足条件的数据集较大，索引扫描的成本高于全表扫描，这时规划器会选择使用全表扫描。

• Index Scan：索引扫描，查询列有索引，则直接扫描索引，不再进行全表扫描，耗费时间小于顺序扫描。

  本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

  多了筛选条件后，会打开每条记录，进行筛选记录，花费时间变多了。但是，将筛选条件放到扫描里面是有好处的，尤其是在多表join时，构造自然选择的块有很大的区别

• 全索引扫描（index only scan）：当查询的条件都在索引中，也会走该扫描方式，不会读取表文件，例如覆盖索引和include索引

• 位图索引扫描（Bitmap Index Scan）：也是一种走索引的方式，方法是扫描索引，把满足条件的行或者块在内存中建一个位图，扫描完索引后，再跟进位图中记录的指针到表的数据文件读取相应的数据。在or、and、in子句和有多个条件都可以同时走不同的索引时，都可能走Bitmap Index Scan 。

• Bitmap Heap Scan：位图堆扫描

• Filter：条件过滤

• Nestloop Join：嵌套循环连接，是在两个表做连接时，内表呗外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大，要把返回子集较小的表作为外表，而且在内表的连接字段上要有索引，否则会很慢。执行过程：

  1、确定一个驱动表（outer table），另一个表为 inner table

  2、驱动表中的每一行与 inner 表中的相应记录 JOIN 类似一个嵌套的循环

• Hash Join：使用两个表中较小的表，并利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。适用于较小的表可以完全放入内存中的情况。如果表很大，不能完全放入内存，优化器会将它分割成若干不同的分区，把不能放入内存的部分写入磁盘的临时段。

• Merge Join：如果源数据上有索引，或者结果已经被排过序，在执行排序合并连接时就不需要排序了，Merge Join 的性能会优于散列连接。

注意

在加上 ANALYZE 选项后，会真正执行实际的 SQL，如果 SQL 语句是一个插入、删除、更新或 CREATE TABLE AS 语句，这些语句会修改数据库。为了不影响实际的数据，可以吧 EXPLAIN ANALYZE 放到一个事务中，执行完后回滚事务，如下：

BEGIN; EXPLAIN ANALYZE ...;

1 2 3

BEGIN; EXPLAIN ANALYZE ...;

pg12的explain语句可以带有SETTINGS ON选项

pg12的explain语句可以带有SETTINGS ON选项,该选项用于输出与执行计划相关的并且并非默认值的参数

postgres=# SET random_page_cost = 1.0 ; SET postgres=# EXPLAIN (SETTINGS ON) SELECT * FROM data1 WHERE c1=100 ; QUERY PLAN ------------------------------------------------------- Seq Scan on data1 (cost=0.00..20.00 rows=4 width=74) Filter: (c1 = 100) Settings: random_page_cost = '1' ---->>此参数 (3 rows)

1 2 3 4 5 6 7 8 9

postgres=# SET random_page_cost = 1.0 ; SET postgres=# EXPLAIN (SETTINGS ON) SELECT * FROM data1 WHERE c1=100 ;                       QUERY PLAN                       ------------------------------------------------------- Seq Scan on data1  (cost=0.00..20.00 rows=4 width=74)    Filter: (c1 = 100) Settings: random_page_cost = '1' ---->>此参数 (3 rows)

参考

PG中的索引扫描参考：https://www.dbaup.com/pgzhongdesuoyinsaomiaoleixing.html

PG中的recheck参考：https://www.dbaup.com/pgsuoyinyouhuazhirecheck.html