Postgresql 中文操作指南

20.7. Query Planning #

20.7.1. Planner Method Configuration #

这些配置参数提供了一种粗略的方法，用于影响查询优化器选择的查询计划。如果优化器为特定查询选择的默认计划不是最优的，则一个 temporary 解决方案是使用其中一个配置参数强制优化器选择不同的计划。改善优化器选择的计划质量的更好方法包括调整规划程序成本常量（请参阅 Section 20.7.2 ）、手动运行 ANALYZE 、增加 default_statistics_target 配置参数的值以及使用 ALTER TABLE SET STATISTICS 增加用于特定列的统计信息的量。

enable_async_append (boolean) #
- 启用或禁用查询规划器使用异步感知追加计划类型的功能。默认值是 on 。
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) #
- 启用或禁用查询计划器对仅索引扫描计划类型的使用（请参阅 Section 11.9 ）。默认值为 on 。
enable_material (boolean) #
- 启用或禁用查询计划器对物化处理的使用。不可能完全禁止物化处理，但关闭此变量可防止计划器插入物化节点，除非在必需于正确性的情况下。默认值为 on 。
enable_memoize (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 。有关详细信息，请参见 Section 5.11.4 。
enable_partitionwise_join (boolean) #
- 启用或禁用查询计划器对分区连接的使用，它允许通过联接匹配分区来执行分区表之间的联接。分区连接目前仅适用于联接条件包括所有分区键时，这些分区键必须具有相同的数据类型，并且具有成对匹配的子分区。由于分区连接计划在计划过程中可能使用更多的 CPU 时间和内存，因此默认值为 off 。
enable_partitionwise_aggregate (boolean) #
- 启用或禁用查询计划器对分区分组或聚合的使用，它允许对分区表的每个分区单独执行分组或聚合。如果 GROUP BY 子句不包括分区键，则只可以按每个分区执行部分聚合，稍后必须完成最终化。由于分区分组或聚合并可以在计划过程中使用更多的 CPU 时间和内存，因此默认值为 off 。
enable_presorted_aggregate (boolean) #
- 控制查询计划器是否会生成一个计划，该计划将提供按查询的 ORDER BY / DISTINCT 聚合函数所需的顺序预排序的行。禁用时，查询计划器将生成一个计划，该计划始终要求执行器在执行包含 ORDER BY 或 DISTINCT 子句的每个聚合函数的聚合之前执行排序。启用时，计划器将尝试生成一个更有效的计划，该计划向聚合函数提供按聚合所需顺序预排序的输入。默认值是 on 。
enable_seqscan (boolean) #
- 启用或禁用查询计划器对顺序扫描计划类型的使用。不可能完全禁止顺序扫描，但关闭此变量可劝阻计划器在有其他可用的方法时使用一个顺序扫描。默认值为 on 。
enable_sort (boolean) #
- 启用或禁用查询计划器对显式排序步骤的使用。不可能完全禁止显式排序，但关闭此变量可劝阻计划器在有其他可用的方法时使用一个显式排序。默认值为 on 。
enable_tidscan (boolean) #
- 启用或禁用查询计划器对 TID 扫描计划类型的使用。默认值为 on 。

20.7.2. Planner Cost Constants #

本节所述的 cost_变量在任意比例尺上进行测量。只有其相对值才有意义，因此，所有向上或向下按相同因子进行的缩放，都不影响规划程序的选择。默认情况下，这些成本变量基于顺序页面获取的成本；即 _seq_page_cost_按惯例设置为 _1.0，而其他成本变量是根据该变量进行设置的。但如果您愿意，可以使用其他比例尺，例如特定计算机上的毫秒实际执行时间。

Note

不幸的是，没有明确方法来确定成本变量的理想值。将它们视为特定安装将接收到的所有查询组合的平均值是最好的做法。这意味着仅基于几个实验更改这些值非常冒险。

seq_page_cost (floating point) #
- 设置计划器对构成一组顺序提取的一部分的磁盘页面提取成本的估算。默认值为 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.
cpu_tuple_cost (floating point) #
- 设置计划器在查询过程中处理每一行的成本的估算。默认值为 0.01。
cpu_index_tuple_cost (floating point) #
- 设置计划器在索引扫描过程中处理每个索引条目的成本的估算。默认值为 0.005。
cpu_operator_cost (floating point) #
- 设置规划器对在查询期间执行的每个操作员或函数处理成本的估计。默认值为 0.0025。
parallel_setup_cost (floating point) #
- 设置规划器对启动并行工作人员进程成本的估计。默认值为 1000。
parallel_tuple_cost (floating point) #
- 设置规划器对将一个元组从一个并行工作人员进程传输到另一个进程的成本的估计。默认值为 0.1。
min_parallel_table_scan_size (integer) #
- 设置并行扫描需要扫描的表格数据的最少数量。对于并行顺序扫描，被扫描的表格数据数量始终等于表格大小，但当索引被使用时，被扫描的表格数据数量通常会少于表格大小。如果不带单位指定此值，则此值被视作以块为单位（即 BLCKSZ_字节，通常为 8k）。默认值为 8 兆字节 (_8MB).
min_parallel_index_scan_size (integer) #
- 设置为了被认为是一个并行扫描，必须扫描的最小索引数据量。注意，一个并行索引扫描一般不会触及整个索引；它指规划器认为扫描实际上会触及的页数，这一点很重要。此参数还用于决策某个特定索引是否可以参与一个并行 vacuum。参见 VACUUM 。如果未带单位指定此值，则视为块，即 BLCKSZ 字节，通常为 8kB。默认值为 512 千字节 ( 512kB )。
effective_cache_size (integer) #
- 设置规划器对单个查询可用的磁盘高速缓存的有效大小的假设。此项被列入使用索引成本的估计；较高值使索引扫描更有可能被使用，较低值使顺序扫描更有可能被使用。在设置此参数时，你应考虑 PostgreSQL 的共享缓冲区和内核磁盘高速缓存的用于 PostgreSQL 数据文件的部分，虽然一些数据可能存在于两个地方。此外，考虑到不同表上的预期并发查询数，因为它们将不得不共享可用空间。此参数对 PostgreSQL 分配的共享内存大小无影响，也不保留内核磁盘高速缓存；它仅用于估计目的。系统也不会假设数据在查询之间保留在磁盘高速缓存中。如果未带单位指定此值，则视为块，即 BLCKSZ 字节，通常为 8kB。默认值为 4 千兆字节 ( 4GB )。(如果 BLCKSZ 不为 8kB，则默认值与其成比例缩放。)
jit_above_cost (floating point) #
- 如果已启用（请参见 Chapter 32），则设置 JIT 编译激活之上的查询成本。执行 JIT 需要规划时间，但可以加速查询执行。将此设置为 -1_将禁用 JIT 编译。默认值为 _100000。
jit_inline_above_cost (floating point) #
- 设置查询成本，在此之上 JIT 编译尝试内嵌函数和运算符。内嵌会增加计划时间，但可以提高执行速度。将此设置为小于 jit_above_cost_是无意义的。将此设置为 _-1_将禁用内嵌。默认值为 _500000。
jit_optimize_above_cost (floating point) #
- 设置查询成本，在此之上 JIT 编译应用昂贵的优化。此类优化会增加计划时间，但可以提高执行速度。将此设置为小于 jit_above_cost_是无意义的，并且将其设置为大于 _jit_inline_above_cost_可能无益。将此设置为 _-1_会禁用昂贵的优化。默认值为 _500000。

Tip

虽然系统会让您将 _random_page_cost_设置为小于 _seq_page_cost_的值，但实际并不合理。不过，如果将它们设置为相同值是合理的，因为数据库完全缓存在 RAM 中，在这种情况下，顺序地接触页面不会受到任何处罚。此外，在大量缓存的数据库中，您应当相对 CPU 参数降低这两个值，因为获取 RAM 中页面的成本要远小于正常成本。

20.7.3. Genetic Query Optimizer #

遗传查询优化器 (GEQO) 是一种使用启发式搜索执行查询计划的算法。这会减少复杂查询（那些连接了许多关系）的计划时间，代价是产生的计划有时不如正常穷举搜索算法找到的计划。有关更多信息，请参见 Chapter 62。

geqo (boolean) #
- 启用或禁用遗传查询优化。默认情况下已启用。在生产环境中最好不要关闭它；geqo_threshold 变量对 GEQO 提供了更细粒度的控制。
geqo_threshold (integer) #
- 使用遗传查询优化来规划包含至少这么多个 FROM 项目的查询。（请注意，一个 FULL OUTER JOIN 构造只算作一个 FROM 项目。）默认值为 12。对于较简单的查询，通常最好使用常规的穷举搜索规划程序，但对于具有多表的查询，穷举搜索需要很长时间，通常比执行次优计划的惩罚时间更长。因此，查询大小的阈值是管理 GEQO 使用的便捷方式。
geqo_effort (integer) #
- 控制 GEQO 中的计划时间和查询计划质量之间的权衡。此变量必须是 1 至 10 区间内的整数。默认值为 5。较大的值会增加查询计划所花费的时间，但也会增加选择有效查询计划的可能性。
- geqo_effort 实际上没有直接做任何事；它仅用于计算影响 GEQO 行为的其他变量的默认值(如下所述)。如果你愿意，你可以手动设置其他参数。
geqo_pool_size (integer) #
- 控制 GEQO 使用的池大小，即遗传种群中的个体数量。它必须至少为 2，而有用值通常为 100 到 1000。如果它设置为零（默认设置），则会根据 geqo_effort 和查询中的表数量选择一个合适的值。
geqo_generations (integer) #
- 控制 GEQO 使用的世代数，即算法迭代次数。它必须至少为 1，而有用值与池大小范围相同。如果它设置为零（默认设置），则会根据 geqo_pool_size 选择一个合适的值。
geqo_selection_bias (floating point) #
- 控制 GEQO 使用的选择偏置。选择偏置是种群内的选择压力。值可以从 1.50 到 2.00，后者是默认值。
geqo_seed (floating point) #
- 控制 GEQO 用于在联接顺序搜索空间中选择随机路径的随机数发生器的初始值。该值可以从零（默认值）到一。改变值会改变所探索的联接路径集，并且可能导致找到更好或更差的最佳路径。

20.7.4. Other Planner Options #

default_statistics_target (integer) #
- 设置表的默认统计信息目标，无需通过 ALTER TABLE SET STATISTICS 设置的列特定的目标。较大的值会增加执行 ANALYZE 所需的时间，但可能会提高规划器估计的质量。默认值为 100。有关 PostgreSQL 查询规划器对统计信息使用的更多信息，请参阅 Section 14.2 。
constraint_exclusion (enum) #
- 控制查询规划器对表约束的使用以优化查询。 constraint_exclusion 的允许值是 on （检查所有表的约束）、 off （从不检查约束）和 partition （仅检查继承子表和 UNION ALL 子查询的约束）。 partition 是默认设置。它通常与传统继承树一起使用以提高性能。
- 当此参数针对某个特定表允许这样做时，规划器将把查询条件与该表的 CHECK 约束进行比较，并忽略扫描该表，因为该表的条件与该表的约束相矛盾。例如：

CREATE TABLE parent(key integer, ...);
CREATE TABLE child1000(check (key between 1000 and 1999)) INHERITS(parent);
CREATE TABLE child2000(check (key between 2000 and 2999)) INHERITS(parent);
...
SELECT * FROM parent WHERE key = 2400;

启用约束排除后，此 SELECT 将完全不会扫描 child1000，从而提高性能。
目前，仅在通常用于通过继承树实施表分区的情况下，才会默认启用约束排除。为所有表打开它会造成额外的计划开销，在简单查询中很明显，并且在大多数情况下不会对简单查询产生好处。如果没有使用传统继承分区的表，你可能更愿意将其完全关闭。（请注意，分区表的等效功能由单独的参数 enable_partition_pruning控制。）
有关使用约束排除来实现分区的更多信息，请参阅 Section 5.11.5。
- cursor_tuple_fraction (floating point) #
设置规划器对游标将检索的行中所占比例的估计。默认值为 0.1。此设置的较小值会使规划器倾向于对游标使用“快速启动”计划，这将快速检索前几行，同时可能花费很长时间来获取所有行。较大值会更加重视总预计时间。在 1.0 的最大设置下，游标的计划就像常规查询一样，仅考虑总预计时间，而不考虑可能多快交付第一行。
- from_collapse_limit (integer) #
如果生成的 _FROM_列表不超过此项数，则规划器会将子查询合并到上级查询中。较小的值会减少计划时间，但可能会产生较差的查询计划。默认值为八。有关更多信息，请参见 Section 14.3。
将此值设置为 geqo_threshold或更大值可能会触发使用 GEQO 规划器，从而导致非最佳计划。请参见 Section 20.7.3。
- jit (boolean) #
确定如果可用，PostgreSQL 是否可以使用 JIT 编译（请参见 Chapter 32）。默认值为 on。
- join_collapse_limit (integer) #
如果不会产生超过此数量的项目列表，规划程序将重写显式 JOIN 构造（除了 FULL JOIN_s) into lists of _FROM 项目）。较小的值会减少计划时间，但可能会产生较差的查询计划。
默认情况下，此变量与 from_collapse_limit_设置相同，这适用于大多数用途。将其设置为 1 时，将阻止重新排序显式的 _JOIN。因此，在查询中指定的显式连接顺序将成为连接关系的实际顺序。由于查询规划器并不总是选择最佳连接顺序，高级用户可以选择将此变量临时设置为 1，然后显式指定所需的连接顺序。有关更多信息，请参见 Section 14.3。
将此值设置为 geqo_threshold或更大值可能会触发使用 GEQO 规划器，从而导致非最佳计划。请参见 Section 20.7.3。
- plan_cache_mode (enum) #
已准备的语句（显式准备或隐式生成，例如由 PL/pgSQL）可以使用自定义计划或通用计划执行。对于每次执行，使用其特定的参数值重新完成自定义计划，而通用计划不依赖于参数值，并且可以在执行之间重新使用。因此，使用通用计划可以节省计划时间，但如果理想计划很大程度上依赖于参数值，则通用计划可能效率低下。在这些选项之间进行选择通常是自动完成的，但可以通过 plan_cache_mode 覆盖它。允许的值是 auto （默认值）、 force_custom_plan 和 force_generic_plan 。此设置在准备缓存的计划时考虑，而不是在准备计划时考虑。有关更多信息，请参阅 PREPARE 。
- recursive_worktable_factor (floating point) #
设置对于 recursive query ，将作为查询的初始非递归项的估计大小倍数，设置规划器对工作表平均大小的估计。这有助于规划器选择将工作表连接到查询的其他表的最佳方法。默认值是 10.0 。较小的值，例如 1.0 ，在递归从一个步骤到下一个步骤的“扇出”较低时可能会有所帮助，例如在最短路径查询中。图形分析查询可能受益于大于默认值的值。