本文共 2205 字，大约阅读时间需要 7 分钟。
PostgreSQL 查询优化指南：5 个关键参数和方法
PostgreSQL 作为一个功能强大的开源数据库，拥有强大的查询优化器功能。通过合理配置查询优化器，可以显著提升查询性能。本文将深入探讨 5 个关键参数及其配置方法，帮助你更好地理解和优化数据库查询。
一、规划方法配置
查询优化器通过不同的策略调整查询执行计划，以下是几种常见的优化器配置参数：
1.1 启用位图扫描
- 作用：控制优化器是否使用位图扫描类型。
- 默认值：
enable_bitmapscan = on - 说明：默认情况下启用，适用于大数据量的查询。
1.2 启用散列连接
- 作用：控制优化器是否使用散列连接类型。
- 默认值：
enable_hashagg = on - 说明：默认启用，适用于高效的主键匹配操作。
1.3 启用合并连接
- 作用：控制优化器是否使用合并连接类型。
- 默认值：
enable_mergejoin = on - 说明：默认启用，适用于需要关联多个表的复杂查询。
1.4 启用索引扫描
- 作用：控制优化器是否使用索引扫描类型。
- 默认值：
enable_indexscan = on - 说明：默认启用，适用于高效的索引查询。
1.5 启用仅索引扫描
- 作用：控制优化器是否仅使用索引扫描。
- 默认值：
enable_indexonlyscan = on - 说明：默认启用，优先使用索引，提升查询速度。
1.6 启用材料化
- 作用：控制优化器是否使用材料化技术。
- 默认值：
enable_material = on - 说明：默认启用，适用于复杂查询。
1.7 启用序列扫描
- 作用：控制优化器是否使用序列扫描类型。
- 默认值：
enable_seqscan = on - 说明：默认启用，适用于简单查询。
1.8 启用排序
- 作用：控制优化器是否使用明确排序步骤。
- 默认值：
enable_sort = on - 说明：默认启用，适用于需要排序的查询。
二、优化器成本常量
优化器通过成本模型评估不同查询计划的执行成本。以下是常用的成本常量配置参数：
2.1 顺序页面成本
- 类型：浮点数
- 默认值：
seq_page_cost = 1.0 - 说明：表示顺序读取数据块的成本。
2.2 随机页面成本
- 类型：浮点数
- 默认值：
random_page_cost = 4.0 - 说明：表示随机读取数据块的成本。
2.3 CPU 行处理成本
- 类型：浮点数
- 默认值：
cpu_tuple_cost = 0.01 - 说明：表示处理每行数据的 CPU 成本。
2.4 CPU 索引项处理成本
- 类型：浮点数
- 默认值：
cpu_index_tuple_cost = 0.005 - 说明：表示扫描索引时处理每个索引项的 CPU 成本。
2.5 CPU 运算符成本
- 类型：浮点数
- 默认值：
cpu_operator_cost = 0.0025 - 说明：表示处理运算符或函数的 CPU 成本。
2.6 有效缓存大小
- 类型：整数
- 默认值：
effective_cache_size = 4GB - 说明：设置查询可以使用的缓存区大小。
三、遗传查询优化器
遗传查询优化器（Genetic Query Optimizer，GQO）通过遗传算法自动优化查询计划。以下是 GQO 的关键参数：
3.1 启用遗传优化器
- 类型：布尔
- 默认值：
geqo = on - 说明：默认启用，适用于复杂查询。
3.2 遗传优化器门槛
- 类型：整数
- 默认值：
geqo_threshold = 12 - 说明：如果从句中引用的表超过该数，则启用遗传优化器。
3.3 遗传优化器努力程度
- 类型：整数
- 默认值：
geqo_effort = 5 - 说明：控制优化器在性能和时间之间的平衡。
3.4 池大小
- 类型：整数
- 默认值：
geqo_pool_size = 0 - 说明：控制遗传群体的个体数。
3.5 代数大小
- 类型：整数
- 默认值：
geqo_generations = 0 - 说明：控制遗传算法的迭代次数。
3.6 代选择偏差
- 类型：浮点数
- 默认值：
geqo_selection_bias = 2.0 - 说明：控制遗传优化器的代选择偏差。
3.7 种子值
- 类型：浮点数
- 默认值：
geqo_seed = 0.0 - 说明：控制遗传优化器的随机性。
四、其他规划选项
4.1 默认统计目标
- 类型：整数
- 默认值：
default_statistics_target = 100 - 说明：设置收集优化器统计数据的目标值。
4.2 约束排除
- 类型：字符
- 默认值：
constraint_exclusion = partition - 说明：控制优化器是否使用表上的约束条件。
4.3 曲柄分割比例
- 类型：浮点数
- 默认值：
cursor_tuple_fraction = 0.1 - 说明：控制访问计划中使用曲柄的比例。
4.4 从句折叠限制
- 类型：整数
- 默认值：
from_collapse_limit = 8 - 说明：控制从句的折叠策略。
4.5 连接折叠限制
- 类型：整数
- 默认值：
join_collapse_limit = 8 - 说明：控制连接句的折叠策略。
4.6 强制并行模式
- 类型：布尔
- 默认值：
force_parallel_mode = off - 说明：控制查询是否强制并行执行。
通过合理配置以上参数，可以显著提升 PostgreSQL 查询性能。建议在实际应用中根据具体需求调整参数值，避免盲目更改默认值。
转载地址：http://qpxfk.baihongyu.com/