CentOS 上 PostgreSQL SQL 优化要点
一 执行计划与 SQL 编写
- 用执行计划定位瓶颈：优先使用 EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 观察是否出现 Seq Scan（全表扫描）、估算行数 Rows 与实际差距、以及 Actual Time；生产慎用 ANALYZE，避免对线上造成影响。结合 EXPLAIN 的图形化工具（如 pgAdmin）辅助判断。
- 避免索引失效的常见陷阱：不要在 WHERE 中对索引列做函数或类型转换（如将整数列与字符串比较），否则会导致 隐式类型转换 使索引失效；复合索引需遵循“最左前缀”原则，列顺序要与查询条件匹配。
- 优化分页与排序：深分页（如 OFFSET 100000）常引发大排序，尽量改为“键集分页”（记住上一页最后一条的主键/时间，下一页从该值之后取）；对大结果集的排序尽量利用索引或覆盖索引，减少 Sort 与回表。
- 子查询与连接：优先用 EXISTS 替代对大结果集的 IN；JOIN 前先对驱动表做高选择性过滤，减少参与 JOIN 的数据量。
二 索引策略与维护
- 索引类型选型：
- 等值/范围/排序：B-Tree
- 全文检索：GIN（配合 tsvector）
- 地理空间：GiST（PostGIS）
- JSONB/数组包含：GIN
- 时间序列大表：BRIN（按时间顺序存储时效果佳）
- 复合与覆盖索引：多列条件使用 复合索引；只查少数列时用 覆盖索引/INCLUDE 避免回表（减少 Heap Fetches）。
- 部分索引与表达式索引：对高选择性子集建立 Partial Index；对稳定表达式建立 表达式索引，减少运行时计算。
- 创建与维护：大表索引用 CREATE INDEX CONCURRENTLY 避免锁表；定期用 REINDEX 或 pg_repack 处理索引膨胀；监控索引使用率，清理无效索引，避免“过度索引”拖慢写入。
三 配置参数与执行引擎
- 内存与 I/O：
- shared_buffers：通常设为物理内存的 25%–40%
- work_mem：按并发与操作复杂度设置，避免过大导致换页或内存压力
- maintenance_work_mem：维护类操作（如 VACUUM/创建索引）可设较大
- wal_buffers：可设为 shared_buffers 的 1/32
- effective_io_concurrency：SSD 推荐 200 左右（视设备与负载微调）
- 并行查询：提高 max_parallel_workers_per_gather，并适度降低 parallel_tuple_cost / parallel_setup_cost，让优化器更容易选择并行执行。
- JIT 加速：在 PostgreSQL 16 中启用 JIT 可显著加速 OLAP/复杂表达式场景（需安装 LLVM 并在编译时启用 --with-llvm）：
- 示例：jit = on，jit_above_cost = 100000，jit_inline_above_cost = 500000，jit_optimize_above_cost = 1000000
- 参数调优工具：使用 pgTune 基于硬件与负载生成更合理的初始参数，再结合实际压测微调。
四 表设计与架构优化
- 规范化与反规范化：在一致性与性能间平衡；读多写少场景可适度 反规范化/预计算字段 减少 JOIN 与计算。
- 数据类型：优先选择最小够用的数据类型（如用 整数 替代定长字符串编码、用 日期时间类型 替代字符串），既省空间又提升比较/索引效率。
- 分区表：对超大表按时间/地域等维度分区，可显著减少扫描数据量、简化维护与过期数据清理。
- 连接池：使用 pgpool-II 等连接池减少连接开销、提升并发稳定性。
- 查询缓存：PostgreSQL 无内置结果缓存，可用 pgpool-II 提供的查询缓存能力或应用层缓存（如 Redis）。
五 维护监控与系统层优化
- 例行维护：定期执行 VACUUM（回收死元组）与 ANALYZE（更新统计信息），保持执行计划质量；批量导入后及时 ANALYZE；空间紧张或膨胀严重时再考虑 VACUUM FULL（维护窗口、谨慎使用）。
- 自动化与监控：用 pg_cron 定时维护；用 pgBadger 分析日志定位慢查询与异常；结合 pg_repack 在线重组表/索引降低膨胀与锁冲突。
- 存储与文件系统：将数据文件、WAL、临时表空间分离到不同磁盘降低 I/O 争用；优先 SSD；文件系统建议 ext4 noatime 减少元数据写放大。
- 内核与资源：适度降低 vm.swappiness 减少换页；提升 文件描述符限制 满足高并发连接需求。