Linux 上提升 PostgreSQL 查询速度的系统化做法
一 诊断与定位
- 使用 EXPLAIN ANALYZE 查看执行计划，关注是否出现 Seq Scan（全表扫描）、Sort/Hash 是否发生磁盘溢出（如 external merge/disk）、以及实际行数与估计行数是否偏差过大。
- 检查索引是否被使用：在 pg_stat_user_indexes 中查看 idx_scan，识别“建了未用”的索引。
- 排查系统瓶颈：用 iostat -x 1 观察磁盘 %util、await、svctm；当 %util 接近 100% 通常意味着 I/O 饱和。
- 确认统计信息新鲜度：执行 ANALYZE 更新统计信息，避免优化器做出错误成本估计。
- 控制连接风暴：避免 max_connections 过大，结合连接池（如 pgBouncer/pgpool-II）复用连接，降低创建/销毁开销。
二 索引与 SQL 写法优化
- 选对索引类型：
- B-tree：等值与范围查询的通用选择。
- Hash：仅等值查询。
- GIN：全文检索、JSONB、数组等多值类型。
- GiST：地理空间（PostGIS）、文本相似度等。
- BRIN：大表、按时间/顺序存储的数据，空间占用小、适合粗粒度过滤。
- 设计复合索引：将高选择性列放在前面，遵循最左前缀原则；多列条件尽量复用同一复合索引。
- 使用部分索引：对子集数据建索引，如 CREATE INDEX CONCURRENTLY idx_active ON orders(order_id) WHERE status=‘active’; 更小更快。
- 覆盖索引与仅索引扫描：用 INCLUDE 把查询所需列加入索引，减少回表，促成 Index-Only Scan。
- 表达式/函数索引：如 lower(col)；注意写入开销。
- 上线索引建议用 CREATE INDEX CONCURRENTLY，避免阻塞写入。
- SQL 侧优化：避免 **SELECT ***，只取需要的列；减少不必要的子查询、JOIN 与排序；为 WHERE/JOIN/ORDER BY/GROUP BY 中常用列建立合适索引。
三 配置参数与内存调优
- 使用 pgTune 依据硬件与工作负载生成基础配置，再结合实际压测微调。
- 核心参数建议（需结合实例与负载压测验证）：
- shared_buffers：常设为内存的约 25%（专用库场景）；过大反而增加操作系统换页压力。
- work_mem：每个排序/哈希操作可用内存；并发高时要控制总内存占用，避免 OOM。
- maintenance_work_mem：VACUUM/创建索引等维护操作内存，可设较大（如数百 MB 到数 GB）。
- effective_cache_size：提示优化器可用缓存大小，通常设为内存的 50%–75%，影响索引/顺序扫描成本估计。
- wal_buffers：一般 16–32 MB 即可，写密集可适当增大。
- max_connections：优先用连接池，避免过高导致上下文切换与内存膨胀。
- 启用 JIT（若已安装 LLVM）：在合适查询上可缩短执行时间。
四 存储 文件系统与 Linux 内核
- 存储与布局：优先 SSD；将 数据文件、WAL 日志、临时表空间 分离到不同物理磁盘，降低 I/O 争用；大表可按时间做 分区表，查询只扫描相关分区。
- 文件系统与调度：
- 建议 ext4 noatime 减少元数据写。
- I/O 调度器：SSD 可用 noop；通用/虚拟化可选 deadline。
- 预读：如 blockdev --setra 16384 /dev/sda。
- 透明大页（THP）：数据库负载下建议关闭，减少分配延迟与抖动。
- NUMA：在 NUMA 架构上绑定内存/CPU 或使用 numactl 优化，减少跨 NUMA 访问。
- 内存与网络：适度降低 vm.swappiness；必要时调优 tcp_rmem/wmem、somaxconn、tcp_max_syn_backlog 等网络参数。
五 维护与监控
- 例行维护：
- 定期 VACUUM（或启用 autovacuum）清理死元组，防止表膨胀；
- 定期 ANALYZE 更新统计信息，保障执行计划质量；
- 索引膨胀或碎片严重时用 REINDEX（或 CONCURRENTLY）重建。
- 监控与迭代：
- 用 pg_stat_user_indexes、pg_stat_activity 观察索引使用与慢 SQL；
- 结合 iostat 等系统工具持续验证 I/O 与 CPU 瓶颈；
- 优化是迭代过程：定位瓶颈 → 实施改动 → 复测验证 → 继续优化。