Postgresql-SQL-优化：参数优化

1 DB、表、字段、索引

1.1 多表join

多表join是 join字段数据类型、字符集保持一致，否则会发生隐式转换，增加成本消耗

建议：

获取多表join SQL，分析表的字符集是否一致，分析表的join 字段数据类型是否一致

2 参数调优

2.1 内存参数调优

 

一、全局共享内存参数

参数名	默认值	作用	推荐值	注意事项
shared_buffers	128MB	数据库共享缓存池	25% 总内存（最大 8-32GB）	OLAP可提升至40%，需留内存给OS和其他进程
wal_buffers	-1 (自动)	WAL日志缓冲区	16-32MB	高并发写入场景可设为64MB
max_prepared_transactions	0	两阶段提交事务数	>= max_connections	启用需设置非零值

---

二、会话级私有内存参数

参数名	默认值	作用	推荐值	计算公式
work_mem	4MB	排序/哈希操作内存	(总内存 - shared_buffers) / (max_connections * 3)	OLTP: 8-64MB, OLAP: 64-512MB
maintenance_work_mem	64MB	VACUUM/索引构建内存	1-2GB	大表维护时可临时调高
temp_buffers	8MB	临时表缓存	16-128MB	频繁使用临时表时调整

---

三、连接与并发控制

参数名	默认值	作用	推荐值	关联影响
max_connections	100	最大连接数	根据业务需求	每连接消耗约10MB内存
effective_cache_size	4GB	优化器预估的OS缓存	总内存的50-75%	不影响实际内存分配

---

四、专项优化参数

1. 大表/分析场景

sql

复制

-- 提升并行查询性能
SET max_parallel_workers_per_gather = 4;  -- 并行工作进程数
SET min_parallel_table_scan_size = 1GB;   -- 触发并行的表大小阈值

2. 高并发写入场景

sql

复制

-- 优化WAL和提交效率
SET commit_delay = 10;          -- 组提交延迟（微秒）
SET commit_siblings = 5;        -- 触发组提交的并发事务数

---

五、内存分配公式验证

总内存需求估算：

 

复制

总内存 ≥ shared_buffers + (max_connections × (work_mem + temp_buffers)) + wal_buffers

示例：

• 服务器内存：64GB

• 推荐配置：

  ini

  复制

  shared_buffers = 16GB
  work_mem = 16MB
  max_connections = 200
  temp_buffers = 32MB

  内存占用：16GB + 200*(16MB+32MB) + 64MB ≈ 16GB + 9.6GB + 0.064GB = 25.664GB

---

六、调优操作步骤

1. 查看当前配置：

   sql

   复制

   SELECT name, setting, unit FROM pg_settings 
   WHERE name IN ('shared_buffers','work_mem','wal_buffers','max_connections');

2. 动态调整（无需重启）：

   sql

   复制

   ALTER SYSTEM SET work_mem = '32MB';  -- 全局生效
   SET LOCAL work_mem = '64MB';         -- 当前会话生效

3. 永久生效配置：

   bash

   复制

   # 修改 postgresql.conf
   shared_buffers = 16GB
   work_mem = 32MB

4. 监控工具：

   sql

   复制

   -- 检查缓存命中率
   SELECT sum(heap_blks_hit)/(sum(heap_blks_hit)+sum(heap_blks_read)) AS hit_ratio 
   FROM pg_statio_user_tables;

---

七、不同场景推荐配置

场景	关键参数调整	典型值
OLTP	高并发小事务	work_mem=32MB, max_connections=500, shared_buffers=8GB
OLAP	大查询分析	work_mem=256MB, max_connections=50, shared_buffers=24GB
混合负载	平衡配置	work_mem=64MB, shared_buffers=16GB, maintenance_work_mem=2GB

---

通过合理配置这些参数，可使 PostgreSQL 的内存使用效率提升 30-50%。调整后需使用 EXPLAIN ANALYZE 验证执行计划变化，并通过 pg_stat_statements 监控实际性能提升效果。

2.2 设置优化器对 随机磁盘I/O操作 的成本估算参数调优

2.2.1 random_page_cost参数含义

• 作用：设置优化器对 随机磁盘I/O操作 的成本估算值。

• 默认值：4.0（表示随机访问的成本是顺序访问的4倍）。

• 关联参数：

  • seq_page_cost：顺序访问一个磁盘页的成本（默认 1.0）。

  • cpu_tuple_cost：处理一行数据的CPU成本。

  • cpu_index_tuple_cost：索引扫描时处理一行索引的CPU成本。

---

工作原理

1. 执行计划选择：

   • 优化器通过比较不同执行计划的 总成本（由 I/O 和 CPU 成本组成）选择最优方案。

   • 若索引扫描涉及大量随机 I/O，其成本计算公式为：

      

     复制

     总成本 = random_page_cost × 预计随机访问的页数 + CPU 处理成本

   • 若全表扫描是顺序 I/O，其成本为：

      

     复制

     总成本 = seq_page_cost × 预计顺序访问的页数 + CPU 处理成本

2. 典型场景：

   • 当索引扫描需要多次随机访问磁盘页时，random_page_cost 的值直接影响优化器是否选择索引。

   • 例如：若 random_page_cost 过高，优化器可能更倾向于全表扫描；若设置过低，可能过度选择索引扫描。

---

配置建议

1. 传统机械硬盘（HDD）：

   • 保持默认值 4.0，因为随机 I/O 性能远差于顺序 I/O。

2. 固态硬盘（SSD/NVMe）：

   • 降低至 1.1 或 2.0，因 SSD 的随机访问性能接近顺序访问。

3. 全内存缓存（如数据完全在内存中）：

   • 可设置为 0.1 或更低（需谨慎），因为内存访问无磁盘寻址开销。

---

调整示例

sql

复制

-- 设置随机页访问成本为 1.5（适用于 SSD）
ALTER SYSTEM SET random_page_cost = 1.5;
SELECT pg_reload_conf();

-- 仅对当前会话生效
SET random_page_cost = 1.5;

---

注意事项

1. 与缓存无关：优化器无法预知数据是否在缓存中，因此成本估算固定基于磁盘访问。

2. 存储类型适配：

   • 若使用 RAID 或网络存储（如 SAN），需根据实际 I/O 性能调整。

3. 监控与验证：

   • 使用 EXPLAIN ANALYZE 对比调整前后的执行计划和实际耗时。

   • 观察查询性能变化，避免过度优化。

---

示例场景

场景：某查询在 SSD 上频繁选择全表扫描，但期望使用索引。
操作：将 random_page_cost 从 4.0 降至 1.5，使索引扫描成本更低。
验证：

sql

复制

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100;
-- 观察是否从 Seq Scan 变为 Index Scan

---

通过合理配置 random_page_cost，可以显著提升 PostgreSQL 对现代存储设备的执行计划选择准确性，从而优化查询性能。建议结合实际硬件环境和工作负载进行测试调整。