合 PG中的查询规划执行计划explain参数
Tags： PGPostgreSQL执行计划EXPLAINcostplan_cache_mode
PG中的执行计划：https://www.dbaup.com/pgzhongdezhixingjihuaexplain.html
查询规划
1. 规划器方法配制
这些配置参数提供了影响查询优化器选择查询规划的原始方法。如果优化器 为特定的查询选择的缺省规划并不是最优，那么我们就可以通过使用这些 配置参数强制优化器选择一个更好的规划来temporary解决这个 问题。不过，永久地关闭这些设置几乎从不是个好主意。更好的改善优化器 选择规划的方法包括调节Section 18.6.2、 更频繁运行ANALYZE、增大配置参数 default_statistics_target的值、使用 ALTER TABLE SET STATISTICS为某个字段增加收集的 统计信息。 这些配置参数影响查询优化器选择查询计划的暴力方法。如果优化器为一个特定查询选择的默认计划不是最优的，一种临时\解决方案是使用这些配置参数之一来强制优化器选择一个不同的计划。提高优化器选择的计划质量的更好的方式包括调整规划器的代价常数（见第 19.7.2 节）、手工运行ANALYZE、增加default_statistics_target配置参数的值以及使用ALTER TABLE SET STATISTICS增加为特定列收集的统计信息量。
enable_bitmapscan(boolean)允许或禁止查询规划器使用位图扫描计划类型。默认值是
on。enable_gathermerge(boolean)启用或者禁用查询规划器对收集归并计划类型的使用。默认值是
on。enable_hashagg(boolean)允许或禁用查询规划器使用哈希聚集计划类型。默认值是
on。enable_hashjoin(boolean)允许或禁止查询规划器使用哈希连接计划类型。默认值是
on。enable_incremental_sort(boolean)启用或禁用查询规划器对增量排序步骤的使用。默认为
on。enable_indexscan(boolean)允许或禁止查询规划器使用索引扫描计划类型。默认值是
on。enable_indexonlyscan(boolean)允许或禁止查询规划器使用只用索引扫描计划类型（见第 11.9 节）。默认值是
on。enable_material(boolean)允许或者禁止查询规划器使用物化。它不可能完全禁用物化，但是关闭这个变量将阻止规划器插入物化节点，除非为了保证正确性。默认值是
on。enable_mergejoin(boolean)允许或禁止查询规划器使用归并连接计划类型。默认值是
on。enable_nestloop(boolean)允许或禁止查询规划器使用嵌套循环连接计划。它不可能完全禁止嵌套循环连接，但是关闭这个变量将使得规划器尽可能优先使用其他方法。默认值是
on。enable_parallel_append(boolean)允许或禁止查询规划器使用并行追加计划类型。默认值是
on。enable_parallel_hash(boolean)允许或禁止查询规划器对并行哈希使用哈希连接计划类型。如果哈希连接计划也没有启用，这个参数没有效果。默认值是
on。enable_partition_pruning(boolean)允许或者禁止查询规划器从查询计划中消除一个分区表的分区。这也控制着规划器产生允许执行器在查询执行期间移除（忽略）分区的查询计划的能力。默认值是
on。详情请参考第 5.11.4 节。enable_partitionwise_join(boolean)允许或者禁止查询规划器使用面向分区的连接，这使得分区表之间的连接以连接匹配的分区的方式来执行。 面向分区的连接当前只适用于连接条件包括所有分区键的情况，连接条件必须是相同的数据类型并且子分区集合要1对1匹配。 由于面向分区的连接规划在规划期间会使用可观的CPU时间和内存，所以默认值为
off。enable_partitionwise_aggregate(boolean)允许或者禁止查询规划器使用面向分区的分组或聚集，这使得在分区表上的分组或聚集可以在每个分区上分别执行。如果
GROUP BY子句不包括分区键，只有部分聚集能够以基于每个分区的方式执行，并且finalization必须最后执行。由于面向分区的分组或聚集在规划期间会使用可观的CPU时间和内存，所以默认值为off。enable_seqscan(boolean)允许或禁止查询规划器使用顺序扫描计划类型。它不可能完全禁止顺序扫描，但是关闭这个变量将使得规划器尽可能优先使用其他方法。默认值是
on。enable_sort(boolean)允许或禁止查询规划器使用显式排序步骤。它不可能完全禁止显式排序，但是关闭这个变量将使得规划器尽可能优先使用其他方法。默认值是
on。enable_tidscan(boolean)允许或禁止查询规划器使用TID扫描计划类型。默认值是
on。
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 规划器，从而产生不确定的执行计划 |
2. 规划器代价常量
这一节中描述的代价变量可以按照任意尺度衡量。我们只关心它们的相对值，将它们以相同的因子缩放不会影响规划器的选择。默认情况下，这些代价变量是基于顺序页面获取的代价的，即seq_page_cost被设置为1.0并且其他代价变量都参考它来设置。不过你可以使用你喜欢的不同尺度，例如在一个特定机器上的真实执行时间。
- 顺序扫描一个数据块，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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | postgres=# select * from pg_settings where name in ('seq_page_cost','random_page_cost','cpu_tuple_cost','cpu_index_tuple_cost','cpu_operator_cost'); name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart ----------------------+---------+------+---------------------------------------+----------------------------------------------------------------------------------------------+------------+---------+---------+---------+---------+--------------+----------+----------+-----------+------------+------------+----------------- cpu_index_tuple_cost | 0.005 | | Query Tuning / Planner Cost Constants | Sets the planner's estimate of the cost of processing each index entry during an index scan. | | user | real | default | 0 | 1.79769e+308 | | 0.005 | 0.005 | | | f cpu_operator_cost | 0.0025 | | Query Tuning / Planner Cost Constants | Sets the planner's estimate of the cost of processing each operator or function call. | | user | real | default | 0 | 1.79769e+308 | | 0.0025 | 0.0025 | | | f cpu_tuple_cost | 0.01 | | Query Tuning / Planner Cost Constants | Sets the planner's estimate of the cost of processing each tuple (row). | | user | real | default | 0 | 1.79769e+308 | | 0.01 | 0.01 | | | f random_page_cost | 4 | | Query Tuning / Planner Cost Constants | Sets the planner's estimate of the cost of a nonsequentially fetched disk page. | | user | real | default | 0 | 1.79769e+308 | | 4 | 4 | | | f seq_page_cost | 1 | | Query Tuning / Planner Cost Constants | Sets the planner's estimate of the cost of a sequentially fetched disk page. | | user | real | default | 0 | 1.79769e+308 | | 1 | 1 | | | f (5 rows) postgres=# select * from pg_settings where category='Query Tuning / Planner Cost Constants'; name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart ------------------------------+---------+------+---------------------------------------+----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+---------+---------+--------------+----------+----------+-----------+------------+------------+----------------- cpu_index_tuple_cost | 0.005 | | Query Tuning / Planner Cost Constants | Sets the planner's estimate of the cost of processing each index entry during an index scan. | | user | real | default | 0 | 1.79769e+308 | | 0.005 | 0.005 | | | f cpu_operator_cost | 0.0025 | | Query Tuning / Planner Cost Constants | Sets the planner's estimate of the cost of processing each operator or function call. | | user | real | default | 0 | 1.79769e+308 | | 0.0025 | 0.0025 | | | f cpu_tuple_cost | 0.01 | | Query Tuning / Planner Cost Constants | Sets the planner's estimate of the cost of processing each tuple (row). | | user | real | default | 0 | 1.79769e+308 | | 0.01 | 0.01 | | | f effective_cache_size | 524288 | 8kB | Query Tuning / Planner Cost Constants | Sets the planner's assumption about the total size of the data caches. | That is, the total size of the caches (kernel cache and shared buffers) used for PostgreSQL data files. This is measured in disk pages, which are normally 8 kB each. | user | integer | default | 1 | 2147483647 | | 524288 | 524288 | | | f jit_above_cost | 100000 | | Query Tuning / Planner Cost Constants | Perform JIT compilation if query is more expensive. | -1 disables JIT compilation. | user | real | default | -1 | 1.79769e+308 | | 100000 | 100000 | | | f jit_inline_above_cost | 500000 | | Query Tuning / Planner Cost Constants | Perform JIT inlining if query is more expensive. | -1 disables inlining. | user | real | default | -1 | 1.79769e+308 | | 500000 | 500000 | | | f jit_optimize_above_cost | 500000 | | Query Tuning / Planner Cost Constants | Optimize JITed functions if query is more expensive. | -1 disables optimization. | user | real | default | -1 | 1.79769e+308 | | 500000 | 500000 | | | f min_parallel_index_scan_size | 64 | 8kB | Query Tuning / Planner Cost Constants | Sets the minimum amount of index data for a parallel scan. | If the planner estimates that it will read a number of index pages too small to reach this limit, a parallel scan will not be considered. | user | integer | default | 0 | 715827882 | | 64 | 64 | | | f min_parallel_table_scan_size | 1024 | 8kB | Query Tuning / Planner Cost Constants | Sets the minimum amount of table data for a parallel scan. | If the planner estimates that it will read a number of table pages too small to reach this limit, a parallel scan will not be considered. | user | integer | default | 0 | 715827882 | | 1024 | 1024 | | | f parallel_setup_cost | 1000 | | Query Tuning / Planner Cost Constants | Sets the planner's estimate of the cost of starting up worker processes for parallel query. | | user | real | default | 0 | 1.79769e+308 | | 1000 | 1000 | | | f parallel_tuple_cost | 0.1 | | Query Tuning / Planner Cost Constants | Sets the planner's estimate of the cost of passing each tuple (row) from worker to master backend. | | user | real | default | 0 | 1.79769e+308 | | 0.1 | 0.1 | | | f random_page_cost | 4 | | Query Tuning / Planner Cost Constants | Sets the planner's estimate of the cost of a nonsequentially fetched disk page. | | user | real | default | 0 | 1.79769e+308 | | 4 | 4 | | | f seq_page_cost | 1 | | Query Tuning / Planner Cost Constants | Sets the planner's estimate of the cost of a sequentially fetched disk page. | | user | real | default | 0 | 1.79769e+308 | | 1 | 1 | | | f (13 rows) |
注意
不幸的是，没有一种良定义的方法来决定代价变量的理想值。它们最好被作为一个特定安装将接收到的查询的平均值来对待。这意味着基于少量的实验来改变它们是有风险的。
seq_page_cost(floating point)设置规划器计算一次顺序磁盘页面抓取的开销。默认值是1.0。 通过设置同名的表空间参数，这个值可以重写为一个特定的表空间。 参阅ALTER TABLESPACE。 设置规划器对一系列顺序磁盘页面获取中的一次的代价估计。默认值是 1.0。通过把表和索引放在一个特殊的表空间（要设置该表空间的同名参数）中可以覆盖这个值（见ALTER TABLESPACE）。
random_page_cost(floating point)设置规划器对一次非顺序获取磁盘页面的代价估计。默认值是 4.0。通过把表和索引放在一个特殊的表空间（要设置该表空间的同名参数）中可以覆盖这个值（见ALTER TABLESPACE）。减少这个值（相对于
seq_page_cost）将导致系统更倾向于索引扫描；提高它将让索引扫描看起来相对更昂贵。你可以一起提高或降低两个值来改变磁盘 I/O 代价相对于 CPU 代价的重要性，后者由下列参数描述。对磁盘存储的随机访问通常比顺序访问要贵不止四倍。但是，由于对磁盘的大部分随机访问（例如被索引的读取）都被假定在高速缓冲中进行，所以使用了一个较低的默认值（4.0）。默认值可以被想成把随机访问建模为比顺序访问慢 40 倍，而期望 90% 的随机读取会被缓存。如果你相信 90% 的缓冲率对你的负载是一个不正确的假设，你可以增加 random_page_cost 来更好的反映随机存储读取的真正代价。 相应地，如果你的数据可以完全放在高速缓存中（例如当数据库小于服务器总内存时），降低 random_page_cost 可能是合适的。 为具有很低的随机读取代价的存储（例如固态驱动器）采用较低的 random_page_cost 值可能更好，例如1.1。提示虽然允许你将random_page_cost设置的比 seq_page_cost小，但是物理上的实际情况并不受此影响。 然而当所有数据库都位于内存中时，两者设置为相等是非常合理的，因为 在此情况下，乱序抓取并不比顺序抓取开销更大。同样，在缓冲率很高的 数据库上，你应当相对于 CPU 开销同时降低这两个值，因为获取内存中 的页比通常情况下的开销小许多。 尽管系统可以是你把random_page_cost设置得小于seq_page_cost，但是实际上没有意义。不过，如果数据库被整个缓存在 RAM 中，将它们设置为相等是有意义的，因为在那种情况中不按顺序访问页面是没有惩罚值的。同样，在一个高度缓存化的数据库中，你应该相对于 CPU 参数降低这两个值，因为获取一个已经在 RAM 中的页面的代价要远小于通常情况下的代价。cpu_tuple_cost(floating point)设置规划器对一次查询中处理每一行的代价估计。默认值是 0.01。
cpu_index_tuple_cost(floating point)设置规划器对一次索引扫描中处理每一个索引项的代价估计。默认值是 0.005。
cpu_operator_cost(floating point)设置规划器对于一次查询中处理每个操作符或函数的代价估计。默认值是 0.0025。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
