PG中的查询规划执行计划explain参数

0    759    5

Tags:

👉 本文共约13552个字,系统预计阅读时间或需51分钟。

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_seqscanboolean是否选择全表顺序扫描。实际上并不能完全禁止全表扫描,但是把这个变量关闭会让优化器在存在其他方法时有限选择其他方法
enable_indexscanboolean是否选择索引扫描
enable_bitmapscanboolean是否选择位图扫描
enable_tidscanboolean是否选择位图扫描
enable_nestloopboolean多表连接时,是否选择嵌套循环连接。如果设置为“off”,执行计划只有走嵌套循环连接一条路时,优化器也只能选择走这一条路,但是如果有其他连接方法可走,优化器会优先选择其他方法。
enable_hashjoinboolean多表连接时,是否选择 hash 连接
enable_mergejoinboolean多表连接时,是否选择 merge 连接
enable_hashaggboolean是否使用 hash 聚合
enable_sortboolean是否使用明确的排序,如果设置为“off”,执行计划只有排序一条路时,优化器也只能选择这条路,但是如果有其他连接方法可走,优化器会优先选择其他方法。

COST 基准值参数

优化器参数配置名称         类型         说明
seq_page_costfloat执行计划中依次顺序访问一个数据块页面的开销。默认值是 1.0
random_page_costfloat执行计划中计算随机访问一个数据块页面的开销。默认值是 4.0,降低该值有助于选择索引扫描。
cpu_tuple_costfloat执行计划中计算处理一条数据行的开销。默认值为 0.01
cpu_index_tuple_costfloat执行计划中计算处理一条索引行的开销。默认为 0.005
cpu_operator_costfloat执行计划中执行一个操作符或函数的开销。默认为 0.0025
effective_cache_sizeint执行计划中在一次索引扫描中可用的磁盘缓冲区的有效大小。在计算一个索引的预计开销值时会对这个参数加以考虑。更高的数值会导致更可能使用索引扫描,更低的数值会导致更有可能选择顺序全表扫描。默认是 16384 个数据块大小,即 128 MB。

基因查询优化的参数

基因查询优化(GEQO)是一个使用探索式搜索来执行查询规划的算法,它可以降低负载查询的规划时间。GEQO 的检索是随机的,因此它生成的执行计划可能会有不确定性。

优化器参数配置名称 类型    说明
geqointeger允许或禁止基因查询变化,在生产系统中最好把此参数打开,默认是打开的。geqo_threshold 参数提供了一种是否使用基因查询优化方法的更惊喜的控制方法
geqo_thresholdinteger只有当涉及的 FROM 关系数量至少有这么多个时,才是用基因查询优化。对于数量小于此值的查询,规划器做判断要花很多时间。默认是 12。一个 FULL OUTER JOIN 只算一个 FROM 项。
geqo_effortinteger控制 GEQO 里规划时间和查询规划有效性之间的平衡。这个变量必须是一个从 1 到 10 的整数。默认值是 5。大的数值增加花在进行查询规划上面的时间,但是也很可能提高选中更有效的查询规划的几率。
geqo_pool_sizeinteger控制 GEQO 使用的池大小。池大小是基因全体中的个体数量,它必须至少是 2,有用的数值通常在 100 到 1000 之间。如果把它设置为 0(默认值),那么就会基于 geqo_effort 和查询中表的数量选取一个合适的值
geqo_generationsinteger控制 GEQO 使用的子代数目。子代的意思是算法的迭代次数。它必须至少是 1,有用值的范围和池大小相同。如果设置为 0(默认值),那么将基于 geqo_effort 选取合适的值。
geqo_selection_biasfloat控制 GEQO 使用的选择性偏好。选择性偏好是指在一个种群中的选择性压力。数值可以是 1.5 到 2.0 之间,默认值是 2.0
geqo_seedfloat控制 GEQO 使用的随机数产生器的初始值,用以选择随机路径。这个值可以从 0(默认值)到 1.修改这个值会改变连接路径搜索的设置,同时会找到最优或最差路径

其他执行计划配置项

优化器参数配置名称类型说明
default_statistics_targetenum此参数设置表字段的默认直方图统计目标值,如果表字段的直方图统计目标值没有用 ALTER TABLE SET STATISTICS 明确设置过,则使用此参数指定的值。此值越大,ANALYZE 需要花费越多的时间,同时统计出的直方图信息越详细,这样生成的执行计划也越准确。默认值是 100,最大值是 10000
constraint_exclusionfloat指定执行计划中是否使用约束排除。可以取三个值:partition、on、off。默认值为 partition。约束排除就是指优化器分析 where 中的过滤条件与表上的 check 约束。当优化器使用约束排除时,需要花更多的时间去对比约束条件和 where 中的过滤条件,在多数情况下,对无继承的表打开约束排除意义不大,所以 PostgreSQL 把此值默认设置为 partition。当对一张表做查询时,如果这张表有很多继承的子表,通常也需要扫描这些子表,设置为“partition”,优化器就会对这些子表做约束排除分析
cursor_tuple_fractionfloat游标在选择执行计算时有两种策略:第一种是选择总体执行代价最低的,第二种是返回第一条记录时代价最低的。有时总体执行代价最低,但返回第一条记录到代价不是最低,这是返回给用户的第一条记录的时间比较长,这会让用户觉得等待较长的时间,系统才有相应,导致用户体验不太好。为了让用户体验比较好,可以选择返回第一条记录最快的执行计划,这时用户可以比较快地看到第一条记录。设置游标,在选择总体代价最低的执行计划和返回第一条记录代价最低的执行计划两者之间,比较倾向性的大小。默认值是 0.1。最大值是 1.0,此时游标会选择总体代价最低的执行计划,而不考虑多久才会输出第一个行
from_collapse_limitinteger默认值是 8。如果查询重写生成的 FROM 后的项目数不超过这个限制数目,优化器将把子查询融合到上层查询。小的数值可缩短规划的时间,但是可能会生成差一些的执行计划。将这个值设置得与配置项 geqo_threshold 的数值相同或更大,可能触发使用 GEQO 规划器,从而产生不确定的执行计划
join_collapse_limitinteger如果查询重写生成的 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

注意

不幸的是,没有一种良定义的方法来决定代价变量的理想值。它们最好被作为一个特定安装将接收到的查询的平均值来对待。这意味着基于少量的实验来改变它们是有风险的。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复