PG中的执行计划EXPLAIN
👉 本文共约4977个字，系统预计阅读时间或需19分钟。
PG中的查询规划参数参考：https://www.xmmup.com/pgzhongdechaxunguihuacanshu.html
EXPLAIN
常用语句
- 预生成执行计划
EXPLAIN sql- 真实执行计划
EXPLAIN ANALYZE sql- 输出详细内容
EXPLAIN (ANALYZEon, TIMINGon, VERBOSEon, BUFFERSon, COSTSon) sql语法
EXPLAIN [ ( option [, ...] ) ] statement EXPLAIN [ ANALYZE ] [ VERBOSE ] statemen选项
- analyze：执行语句并显示真正的运行时间和其它统计信息，会真正执行SQL语句；
- verbose：显示额外的信息,尤其是计划树中每个节点的字段列表,schema识别表和函数名称。总是打印统计数据中显示的每个触发器的名字；
- costs：包括每个计划节点的启动成本预估和总成本的消耗，也包括行数和行宽度的预估；
- buffers：使用信息，特别包括共享块命中、读、脏和写的次数，本地块命中、读、脏和写，临时块读和写的次数；
- timing：在输出中包含实际启动时间和每个节点花费的时间，重复读系统块在某些系统上会显著的减缓查询的速度，只在ANALYZE也启用的时候使用；
- format：声明输出格式，可以为TEXT、XML、JSON 或 YAML，默认 text；
EXPLAIN 输出
cost：第一个数字表示启动的成本，也就是返回第一行需要多少 cost 值；第二个数字表示返回所有的数据的成本。默认 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：表示每行平均宽度为多少字节
buffers
- shared hit：表示在共享内存中直接读到 xxx 个块，
- read：表示从磁盘读了 xxx 块
- written：写磁盘工 xxx 块
执行计划含义
Seq Scan：全表扫描
Index Scan：索引扫描
Bitmap Heap Scan：位图扫描
Filter：条件过滤
Nestloop Join：嵌套循环连接，是在两个表做连接时，内表呗外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大，要把返回子集较小的表作为外表，而且在内表的连接字段上要有索引，否则会很慢。执行过程：
- 确定一个驱动表（outer table），另一个表为 inner table
- 驱动表中的每一行与 inner 表中的相应记录 JOIN 类似一个嵌套的循环
Hash Join：使用两个表中较小的表，并利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。适用于较小的表可以完全放入内存中的情况。如果表很大，不能完全放入内存，优化器会将它分割成若干不同的分区，把不能放入内存的部分写入磁盘的临时段。
Merge Join：如果源数据上有索引，或者结果已经被排过序，在执行排序合并连接时就不需要排序了，Merge Join 的性能会优于散列连接。
参数
ENABLE_* 参数
| 参数名称 | 类型 | 说明 |
|---|---|---|
| enable_seqscan | boolean | 是否选择全表顺序扫描。实际上并不能完全禁止全表扫描，但是把这个变量关闭会让优化器在存在其他方法时有限选择其他方法 |
| enable_indexscan | boolean | 是否选择索引扫描 |
| enable_bitmapscan | boolean | 是否选择位图扫描 |
| enable_tidscan | boolean | 是否选择位图扫描 |
| enable_nestloop | boolean | 多表连接时，是否选择嵌套循环连接。如果设置为“off”，执行计划只有走嵌套循环连接一条路时，优化器也只能选择走这一条路，但是如果有其他连接方法可走，优化器会优先选择其他方法。 |
| enable_hashjoin | boolean | 多表连接时，是否选择 hash 连接 |
| enable_mergejoin | boolean | 多表连接时，是否选择 merge 连接 |
| enable_hashagg | boolean | 是否使用 hash 聚合 |
| enable_sort | boolean | 是否使用明确的排序，如果设置为“off”，执行计划只有排序一条路时，优化器也只能选择这条路，但是如果有其他连接方法可走，优化器会优先选择其他方法。 |
COST 基准值参数
| 参数名称 | 类型 | 说明 |
|---|---|---|
| seq_page_cost | float | 执行计划中依次顺序访问一个数据块页面的开销。默认值是 1.0 |
| random_page_cost | float | 执行计划中计算随机访问一个数据块页面的开销。默认值是 4.0 |
| cpu_tuple_cost | float | 执行计划中计算处理一条数据行的开销。默认值为 0.01 |
| cpu_index_tuple_cost | float | 执行计划中计算处理一条索引行的开销。默认为 0.005 |
| cpu_operator_cost | float | 执行计划中执行一个操作符或函数的开销。默认为 0.0025 |
| effective_cache_size | int | 执行计划中在一次索引扫描中可用的磁盘缓冲区的有效大小。在计算一个索引的预计开销值时会对这个参数加以考虑。更高的数值会导致更可能使用索引扫描，更低的数值会导致更有可能选择顺序全表扫描。默认是 16384 个数据块大小，即 128 MB。 |
基因查询优化的参数
基因查询优化（GEQO）是一个使用探索式搜索来执行查询规划的算法，它可以降低负载查询的规划时间。GEQO 的检索是随机的，因此它生成的执行计划可能会有不确定性。
| 参数名称 | 类型 | 说明 |
|---|---|---|
| geqo | integer | 允许或禁止基因查询变化，在生产系统中最好把此参数打开，默认是打开的。geqo_threshold 参数提供了一种是否使用基因查询优化方法的更惊喜的控制方法 |
| geqo_threshold | integer | 只有当涉及的 FROM 关系数量至少有这么多个时，才是用基因查询优化。对于数量小于此值的查询，规划器做判断要花很多时间。默认是 12。一个 FULL OUTER JOIN 只算一个 FROM 项。 |
| geqo_effort | integer | 控制 GEQO 里规划时间和查询规划有效性之间的平衡。这个变量必须是一个从 1 到 10 的整数。默认值是 5。大的数值增加花在进行查询规划上面的时间，但是也很可能提高选中更有效的查询规划的几率。 |
| geqo_pool_size | integer | 控制 GEQO 使用的池大小。池大小是基因全体中的个体数量，它必须至少是 2，有用的数值通常在 100 到 1000 之间。如果把它设置为 0（默认值），那么就会基于 geqo_effort 和查询中表的数量选取一个合适的值 |
| geqo_generations | integer | 控制 GEQO 使用的子代数目。子代的意思是算法的迭代次数。它必须至少是 1，有用值的范围和池大小相同。如果设置为 0（默认值），那么将基于 geqo_effort 选取合适的值。 |
| geqo_selection_bias | float | 控制 GEQO 使用的选择性偏好。选择性偏好是指在一个种群中的选择性压力。数值可以是 1.5 到 2.0 之间，默认值是 2.0 |
| geqo_seed | float | 控制 GEQO 使用的随机数产生器的初始值，用以选择随机路径。这个值可以从 0（默认值）到 1.修改这个值会改变连接路径搜索的设置，同时会找到最优或最差路径 |
其他执行计划配置项
| 参数名称 | 类型 | 说明 |
|---|---|---|
| default_statistics_target | enum | 此参数设置表字段的默认直方图统计目标值，如果表字段的直方图统计目标值没有用 ALTER TABLE SET STATISTICS 明确设置过，则使用此参数指定的值。此值越大，ANALYZE 需要花费越多的时间，同时统计出的直方图信息越详细，这样生成的执行计划也越准确。默认值是 100，最大值是 10000 |
| constraint_exclusion | float | 指定执行计划中是否使用约束排除。可以取三个值：partition、on、off。默认值为 partition。约束排除就是指优化器分析 where 中的过滤条件与表上的 check 约束。当优化器使用约束排除时，需要花更多的时间去对比约束条件和 where 中的过滤条件，在多数情况下，对无继承的表打开约束排除意义不大，所以 PostgreSQL 把此值默认设置为 partition。当对一张表做查询时，如果这张表有很多继承的子表，通常也需要扫描这些子表，设置为“partition”，优化器就会对这些子表做约束排除分析 |
| cursor_tuple_fraction | float | 游标在选择执行计算时有两种策略：第一种是选择总体执行代价最低的，第二种是返回第一条记录时代价最低的。有时总体执行代价最低，但返回第一条记录到代价不是最低，这是返回给用户的第一条记录的时间比较长，这会让用户觉得等待较长的时间，系统才有相应，导致用户体验不太好。为了让用户体验比较好，可以选择返回第一条记录最快的执行计划，这时用户可以比较快地看到第一条记录。设置游标，在选择总体代价最低的执行计划和返回第一条记录代价最低的执行计划两者之间，比较倾向性的大小。默认值是 0.1。最大值是 1.0，此时游标会选择总体代价最低的执行计划，而不考虑多久才会输出第一个行 |
| from_collapse_limit | integer | 默认值是 8。如果查询重写生成的 FROM 后的项目数不超过这个限制数目，优化器将把子查询融合到上层查询。小的数值可缩短规划的时间，但是可能会生成差一些的执行计划。将这个值设置得与配置项 geqo_threshold 的数值相同或更大，可能触发使用 GEQO 规划器，从而产生不确定的执行计划 |
| join_collapse_limit | integer | 如果查询重写生成的 FROM 后的项目数不超过这个限制数目，优化器把显式使用 JOIN 子句（不包括 FULL JOIN）的连接也重写到 FROM 后的列表中。小的数值可缩短规划的时间，但是可能会生成差一些的查询计划值。默认值与 from_collapse_limit 一样。将这个值设置得与配置项 geqo_threshold 的数值相同或更大，可能触发使用 GEQO 规划器，从而产生不确定的执行计划 |
注意
在加上 ANALYZE 选项后，会真正执行实际的 SQL，如果 SQL 语句是一个插入、删除、更新或 CREATE TABLE AS 语句，这些语句会修改数据库。为了不影响实际的数据，可以吧 EXPLAIN ANALYZE 放到一个事务中，执行完后回滚事务，如下：
1 2 3 | BEGIN; EXPLAIN ANALYZE ...; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
pg12的explain语句可以带有SETTINGS ON选项
pg12的explain语句可以带有SETTINGS ON选项,该选项用于输出与执行计划相关的并且并非默认值的参数
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) |
