四月 3, 2025
本章介绍如何优化 PostgreSQL 性能并提供示例。
优化器原理
PostgreSQL 优化器基于统计信息和代价模型，选择执行成本最低的查询计划，以实现高效数据检索。本节重点介绍优化器中代价估算的原理。
- 理解查询规划 – 了解 PostgreSQL 中的查询规划。
- 基本的关系级统计信息 – 了解系统表中基本的关系级别统计信息。
- 优化空值的估计数量 – 指导您如何优化空值的估计数量。
- 优化非重复值的估计数量 – 指导您如何优化非重复值的估计数量。
- 优化等值选择率估计 – 指导您如何优化等值的选择率估计。
- 优化范围匹配的选择率估计 – 指导您如何优化范围匹配的选择率估计。
- 优化表达式的选择率估计 – 指导您如何优化表达式的选择率估计。
- 优化多变量匹配的选择率估计 – 指导您如何优化多变量匹配的选择率估计。
- 使用表达式索引优化选择率估计 – 指导您如何使用表达式索引来优化查询的选择率估计。
查询计划
本节重点介绍各种查询计划的代价估算和内部原理，以及优化实践。
- 理解顺序扫描的成本估算 – 了解顺序扫描的成本估算。
- 理解并行查询的成本估算 – 了解并行查询的成本估算。
- 理解索引扫描的成本估算 – 了解普通索引扫描的成本估算。
- 理解内存排序的成本估算 – 了解内存排序的成本估算。
- 执行期间的分区剪枝 – 了解查询执行期间的分区剪枝。
- 顺序扫描 – 了解顺序扫描的内部原理。
- 索引扫描类型: 位图、索引和只用索引 – 了解 PostgreSQL 中三种不同的索引扫描类型：位图、索引和只用索引。
- 位图扫描 – 了解位图扫描的成本估算。
- 只用索引的扫描 – 了解只用索引的扫描（Index Only Scan），及其成本估算。
- 利用只用索引的扫描 – 指导您如何在 PostgreSQL 中利用只用索引的扫描。
- 并行查询优化 – 指导您如何对查询进行并行优化。
- 缓存查询计划 – 指导您如何在 PostgreSQL 中缓存查询计划。
优化 SQL 函数
本节介绍 SQL 函数优化相关的方法。
- 理解 PL/pgSQL 函数易失性 – 了解 PL/pgSQL 函数的易失性。
- 避免查询中冗余的函数调用 – 指导您如何避免查询中冗余的函数调用。
- 调整函数的估计执行成本 – 指导您如何调整函数的估计执行成本。
- 调整函数的估计返回行数 – 指导您如何调整函数的估计返回行数。
- 调整函数的并行安全性 – 指导您如何调整函数的并行安全性。
- 利用 SQL 函数的内联 – 指导您如何利用 SQL 函数的内联能力。
SQL 优化
本节介绍 SQL 查询优化相关的方法。
- EXPLAIN 语句 – 指导您如何使用
EXPLAIN语句返回查询的执行计划。 - 使用 EXPLAIN 解释参数化查询 – 指导您如何使用
EXPLAIN语句，显示参数化查询的执行计划。 - 测算查询返回的数据量及其网络传输成本 – 指导您如何测算查询返回的数据量和花费的时间。
- EXPLAIN 输出中的 ‘Rows Removed by Filter’ – 了解关于 EXPLAIN 输出中的 ‘Rows Removed by Filter’ 的内容。
- 优化索引扫描 – 指导您如何在 PostgreSQL 中优化索引扫描。
- 优化子查询 – 指导您如何优化 PostgreSQL 中的子查询。
- 重写查询中的 OR – 了解“好的”和“坏的”
OR，以及您可以采取哪些措施来避免后者。 - 优化 SELECT 查询中的连接 – 指导您如何通过改进连接，来优化 SELECT 类型的 SQL 查询。
- 优化 SELECT 查询中的筛选条件 – 指导您如何通过改进筛选条件，来优化 SELECT 类型的 SQL 查询。
- 使用索引优化 LIKE 筛选条件 – 指导您如何使用索引优化 LIKE 筛选条件。
- 使用估计值加速 COUNT(DISTINCT) – 指导您如何使用数据库估计值，来加速 COUNT(DISTINCT)。
- 使用聚合加速 COUNT(DISTINCT) – 指导您如何使用聚合，来加速 COUNT(DISTINCT)。
- 利用视图的内联 – 指导您如何利用视图的内联能力。
- 即时编译 (JIT) – 指导您使用即时编译 (JIT) 优化 SQL 查询。
配置参数调优
本节介绍 PostgreSQL 中配置参数的优化实践。
- 调整优化器配置参数 – 指导您如何在 PostgreSQL 中调整优化器配置参数。
- 优化 work_mem 设置 – 指导您如何在 PostgreSQL 中优化
work_mem设置。 - 优化 commit_delay 设置 – 指导您如何在 PostgreSQL 中优化
commit_delay设置。 - 优化 Linux 页面缓存 – 指导您如何优化 Linux 中的页面缓存。
- 优化 autovacuum 以清理死元组 – 指导您如何在 PostgreSQL 中优化 autovacuum，以高效清理死元组。
- 优化 autovacuum 以避免事务 ID 回卷的问题 – 指导您如何在 PostgreSQL 中调整 autovacuum，以避免事务 ID 回卷的问题。
- 提升 VACUUM 处理速度 – 指导您如何在 PostgreSQL 中提升 VACUUM 的处理速度。
- 优化检查点 – 指导您如何优化 PostgreSQL 中的检查点。
- 优化 GUC 配置参数 – 指导您如何在 PostgreSQL 中调优 GUC 配置参数。
- 参数调优对比查询优化 – 对比 PostgreSQL 中的参数调优与查询优化。
索引
本节介绍如何高效地使用索引来优化查询性能。
- 优化多列索引 – 指导您如何在 PostgreSQL 中优化多列索引。
- 复合索引对比多个索引 – 对比 PostgreSQL 中的复合索引和多个索引。
- 利用表达式索引 – 了解如何在 PostgreSQL 中利用表达式索引。
- 索引的负面影响和代价 – 了解 PostgreSQL 中索引的负面影响和成本。
其他优化技巧
- 记录慢查询日志 – 指导您如何在 PostgreSQL 中设置慢查询日志记录。
- 配置 pg_stat_statements 插件 – 指导您如何配置
pg_stat_statements插件。 - 使用 pg_stat_statements 分析查询 – 指导您如何使用
pg_stat_statements插件，查找需要优化的慢查询。 - 使用 Redis 缓存加速应用程序 – 指导您如何使用 Redis 将缓存能力集成到一个简单的 PostgreSQL 应用程序中。
- 优化表的填充因子设置 – 指导您如何在 PostgreSQL 中优化表的填充因子。
- 优化统计信息收集器 – 指导您如何在 PostgreSQL 中优化统计信息收集器。
- 记录锁等待和临时文件 – 指导您如何在 PostgreSQL 中记录锁等待和临时文件。
- 记录查询的内核资源使用情况 – 指导您如何在 PostgreSQL 中记录查询的内核资源使用情况。
- 拆分宽表 – 指导您如何在 PostgreSQL 中拆分宽表。
- 采用 LZ4 压缩 TOAST 表 – 了解 PostgreSQL 版本 14 中引入的 lz4 压缩 TOAST 的功能，及其用法。
- 测量网络对性能的影响 – 指导您如何测量网络对 PostgreSQL 性能的影响。
- 让数据页面容纳更多元组 – 了解如何让数据页面容纳更多元组。
- 优化批量数据加载性能 – 了解 PostgreSQL 中批量数据加载的一些性能优化技巧。
- 全页写 full_page_writes 的影响 – 了解 PostgreSQL 中全页写 full_page_writes 的影响。
- 为应用服务器优化网络延迟 – 指导您如何为应用服务器优化和 PostgreSQL 的通信网络延迟。
- 并行 VACUUM – 了解 PostgreSQL 中的并行 VACUUM 的工作原理。
了解更多
- PostgreSQL 索引
- PostgreSQL 文档：性能提示
- PostgreSQL 文档：并行查询
- PostgreSQL 文档：规划器如何使用统计信息