PostgreSQL 索引优化指南

索引不是万能的

索引通过牺牲写入性能和存储空间来换取查询速度。滥用索引反而会拖慢写入密集型应用。在添加索引前，先用 EXPLAIN ANALYZE 确认它是否真的被用到。

常用索引类型

-- B-tree 索引（默认，适合等值和范围查询）
CREATE INDEX idx_posts_created_at ON posts (created_at DESC);

-- 复合索引（顺序很重要！高选择性字段放前面）
CREATE INDEX idx_posts_user_published
  ON posts (author_id, published, created_at DESC);

-- 部分索引（只索引满足条件的行，体积更小）
CREATE INDEX idx_posts_published
  ON posts (created_at DESC)
  WHERE published = true;

-- GIN 索引（适合 JSONB、数组、全文搜索）
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

分析执行计划

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM posts
WHERE author_id = 1 AND published = true
ORDER BY created_at DESC
LIMIT 20;

关键词解读：

• Seq Scan — 全表扫描，通常意味着缺少索引
• Index Scan — 用到了索引
• Bitmap Heap Scan — 用索引找到行 ID，再批量读取数据页
• cost — (启动成本..总成本)

慢查询监控

-- 开启慢查询日志（在 postgresql.conf）
log_min_duration_statement = 1000  -- 记录超过 1s 的查询

-- 使用 pg_stat_statements 扩展统计
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

总结

索引优化的核心是：先测量，再分析，再优化。不要凭感觉添加索引，让 EXPLAIN ANALYZE 告诉你真正的瓶颈在哪里。