Back to Blog

PostgreSQL 慢查询优化实战

2 min read

PostgreSQL 慢查询优化实战

PostgreSQL 慢查询的核心问题通常是索引未命中、统计信息过时或执行计划偏离预期。解决这类问题不需要重启服务，只需利用 EXPLAIN (ANALYZE, BUFFERS) 拿到真实执行计划，就能定位瓶颈并针对性修复。

第一步：定位慢查询

不要凭感觉猜哪条 SQL 慢。直接查系统视图，找出耗时最长的查询：

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

如果不确定是否安装了 pg_stat_statements，先确认扩展已启用：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

第二步：分析执行计划

拿到可疑 SQL 后，用 EXPLAIN (ANALYZE, BUFFERS) 替换 SELECT 执行。关键看几项：

  • Seq Scan：全表扫描，通常意味着缺索引
  • Filter / Rows Removed：过滤掉了大量数据，说明谓词选择率低
  • Heap Blocks：实际读取的数据页数量，过高说明 I/O 压力大

示例输出解读：

Seq Scan on orders  (cost=0.00..15800.00 rows=1 width=100)
  Filter: (created_at > '2023-01-01')
  Rows Removed by Filter: 999000

这里扫描了 100 万行，只留下 1 行，典型的全表扫描浪费。

第三步：创建合适索引

针对上面的例子，加一个局部索引即可：

CREATE INDEX idx_orders_created_at
ON orders (created_at)
WHERE created_at >= '2023-01-01';

注意：索引列的顺序很重要。如果是复合查询，把等值条件列放前面，范围条件列放后面。

常见错误：盲目加索引

很多开发者一看到慢查询就加索引，结果导致写入性能下降、空间膨胀。正确的做法是：

  1. 先确认查询是否真的走了索引
  2. 检查统计信息是否最新
  3. 再考虑是否需要调整索引策略

统计信息过期问题

PostgreSQL 依赖统计信息决定执行计划。如果刚插入大量数据，统计信息可能滞后。手动更新：

ANALYZE orders;

或者调大相关表的统计目标，让分析更精细：

ALTER TABLE orders ALTER COLUMN created_at SET STATISTICS 1000;
ANALYZE orders;

参数调优参考

如果确认索引和统计信息都没问题，再考虑数据库参数。重点看这几个：

  • effective_cache_size：建议设为内存的 50%-75%
  • random_page_cost：SSD 上可降为 1.1
  • work_mem：查询临时排序/哈希空间，不宜过大

修改参数后记得重启或 reload：

SELECT pg_reload_conf();

后续深入

了解 PostgreSQL 的执行器内部机制，能帮你预判优化效果。推荐阅读官方文档中关于「查询规划器」的章节，理解它如何权衡不同访问路径。