先捕获慢查询再分析执行计划，通过日志定位耗时SQL，用EXPLAIN ANALYZE查全表扫描与性能卡点，更新统计信息并合理创建索引，优化SQL写法避免索引失效，最后基于实际需求调整配置参数。
PostgreSQL查询优化应从理解慢查询的根源开始。很多人一看到查询慢就急于加索引或调整配置，但真正有效的优化必须建立在准确识别瓶颈的基础上。第一步不是改配置也不是建索引，而是捕获并分析执行最慢、资源消耗最高的SQL语句。
1. 启用并分析慢查询日志（slow query log）
要优化查询，先得知道哪些查询需要优化。PostgreSQL通过日志记录执行时间较长的SQL语句，是发现问题的第一步。
关键操作：- 在 postgresql.conf 中启用慢查询日志：
log_min_duration_statement = 1000（记录执行超过1秒的SQL） - 设置 log_analyze = on 可记录执行计划信息（用于后续分析）
- 定期查看日志文件，找出频繁出现或耗时最长的SQL
2. 使用 EXPLAIN 和 EXPLAIN ANALYZE 理解执行计划
拿到可疑SQL后，使用 EXPLAIN 查看其执行计划，了解PostgreSQL是如何执行这条查询的。
重点观察：- 是否出现全表扫描（Seq Scan）而本应走索引？
- 表连接顺序是否合理？驱动表是否过大？
- 估算的行数（rows）与实际差异是否巨大？这可能意味着统计信息不准
使用 EXPLAIN ANALYZE 可获取真实执行耗时，帮助定位性能卡点，比如某个节点耗时特别高。
3. 检查表统计信息与索引有效性
执行计划不准往往源于统计信息陈旧或缺失。确保表的统计信息是最新的，是优化的基础。
建议操作：- 运行 ANALYZE 表名; 更新统计信息
- 检查是否存在缺失的索引，特别是WHERE、JOIN、ORDER BY中频繁使用的列
- 避免过度索引，索引会拖慢写操作
- 考虑使用部分索引（Partial Index）提升效率，例如只对活跃数据建索引
4. 优化SQL写法与数据库设计
有些性能问题源于SQL本身结构不合理。例如：
- 避免在WHERE中对字段做函数处理（如 WHERE to_char(date) = '2024-01'），这会导致索引失效
- 减少SELECT *，只取需要的字段
- 大表JOIN时注意连接条件是否走索引
- 考虑是否可以通过物化视图缓存复杂查询结果
5. 调整数据库配置（在明确需求后）
配置调优是最后一步。盲目调大会浪费内存甚至降低性能。
常见可调参数：- shared_buffers：通常设为物理内存的25%
- work_mem：提高排序和哈希操作效率，但不宜过高以防内存溢出
- effective_cache_size：影响执行计划选择，设为系统可用缓存的估计值
- max_parallel_workers_per_gather：对大查询启用并行扫描
基本上就这些。PostgreSQL查询优化的路线图是：先抓慢SQL → 分析执行计划 → 检查统计与索引 → 优化SQL结构 → 最后才是调整配置。每一步都依赖前一步的结论，跳步容易误判。关键是用数据说话，而不是凭感觉调优。
