在 CentOS 上提升 PostgreSQL 查询效率的实用方案
一 系统层优化
- 使用SSD/NVMe替代 HDD，优先保障随机 I/O 能力；为数据库分配充足内存与多核 CPU，并尽量减少同机其他负载。
- 调整内核与文件系统：降低vm.swappiness（如 1–10），减少换页；对数据库盘使用noatime挂载；选择deadline/noop等更适合数据库的 I/O 调度器；按需提升文件描述符限制（ulimit -n）。
- 启用大页内存（Huge Pages）以减少 TLB 缺失，提高内存访问效率。
- 保持操作系统与数据库版本及时更新，获取性能修复与新特性。
二 数据库参数调优
- 内存与缓存
- shared_buffers：通常设为物理内存的25%–40%。
- work_mem：按“每个排序/哈希操作”计，结合并发度与查询复杂度谨慎上调，避免总内存被撑爆。
- maintenance_work_mem：用于 VACUUM、CREATE INDEX 等维护操作，批量或大表操作可适当增大。
- effective_cache_size：提示优化器可用的系统缓存规模，常用为内存的50%–75%（不直接分配内存）。
- wal_buffers：WAL 缓存，常设为 shared_buffers 的 1/32 起步。
- effective_io_concurrency：SSD 可设较高（如200）以提升 I/O 并发。
- 并行与执行
- 提升并行度：max_parallel_workers_per_gather、max_worker_processes 结合 CPU 核数配置；适度降低 parallel_tuple_cost / parallel_setup_cost 以更易触发并行。
- 诊断与自动化
- 启用 pg_stat_statements 定位高耗时 SQL；使用 pgTune 基于硬件与负载生成参数建议；按需开启 JIT（如 PostgreSQL 16）以加速 OLAP/复杂表达式查询。
三 索引与查询优化
- 索引策略
- 常用索引类型：B-tree（等值/范围）、Hash（仅等值）、GIN（全文、数组、JSONB）、GiST（地理空间、文本近似）、BRIN（大表顺序数据、范围粗筛）、Partial（条件子集）。
- 组合索引遵循“高选择性列在前”，覆盖常用 WHERE/JOIN/ORDER BY/GROUP BY；必要时使用覆盖索引（INCLUDE）减少回表；对大表按时间/地域等做分区表以减少扫描。
- 上线索引建议使用 CREATE INDEX CONCURRENTLY 避免写阻塞；定期 REINDEX/VACUUM 控制膨胀。
- SQL 写法
- 避免 **SELECT ***，只取所需列；减少不必要的子查询与排序；在 WHERE 中避免对索引列使用函数（会失效索引）；对大结果集分页合理（keyset 优于 OFFSET）。
- 执行计划与监控
- 使用 EXPLAIN (ANALYZE) 识别全表扫描、磁盘排序/聚合等瓶颈；结合 pg_stat_user_indexes 检查索引使用率，删除未使用/重复索引。
四 维护与监控
- 例行维护
- 定期执行 VACUUM（回收死元组）与 ANALYZE（更新统计信息），对大表可分区+VACUUM 策略；在批量导入/删除后及时维护。
- 日志与报表
- 使用 pgBadger 分析 PostgreSQL 日志，快速定位慢查询与异常模式。
- 连接与扩展
- 通过 PgBouncer 等连接池降低连接开销；在 shared_preload_libraries 中启用 pg_stat_statements、pg_prewarm，前者用于 SQL 性能剖析，后者可将热点表/索引预加载到 shared_buffers，缩短冷启动后的首次查询延迟。
五 快速实施清单
- 基线采集：开启 pg_stat_statements，用 pgBadger 跑一周日志，列出 Top N 慢 SQL；对代表性语句执行 EXPLAIN (ANALYZE) 留存基线。
- 索引改造：为高频过滤/关联/排序字段建立合适索引（B-tree/GIN/GiST/BRIN/Partial），必要时用 CONCURRENTLY 上线；为大表实施分区并按分区键裁剪扫描。
- 参数模板（示例，需结合实际内存与负载调优）：
- shared_buffers = 物理内存的25%–40%；effective_cache_size = 内存的50%–75%；
- work_mem 按并发与操作数估算；maintenance_work_mem 适当放大；
- wal_buffers ≈ shared_buffers/32；effective_io_concurrency（SSD）可设200；
- 并行参数与 JIT 依据 CPU/查询特征开启与调门槛。
- 系统层加固：使用 SSD、设置 noatime、调低 vm.swappiness、提升 ulimit -n、启用 Huge Pages。
- 回归验证：在预发环境复现慢 SQL，对比 EXPLAIN (ANALYZE) 与监控指标；必要时用 pg_prewarm 预热热点数据，观察首轮与稳态性能差异。