PostgreSQL 索引设计指南

围绕真实查询设计索引，而不是为每一列都建立索引。

核心思路

使用执行计划观察扫描方式、估算行数和排序成本。联合索引的列顺序应匹配过滤与排序条件，部分索引适合高频、稳定的子集查询。

落地步骤

索引会增加写入与维护成本。上线后跟踪命中率、体积和未使用索引，并用接近生产规模的数据验证。

验证清单

实施后不要只确认“能运行”，还应检查错误率、延迟、资源占用和异常路径。把验证命令、预期结果和回滚方式写进变更记录，后续维护会轻松很多。

小结

好的工程实践不是堆叠工具，而是用清晰边界、可观察结果和可逆变更降低长期成本。先解决最重要的问题，再逐步自动化。

可执行实践

从 pg_stat_statements 找高总耗时 SQL，在接近生产的数据上执行 EXPLAIN (ANALYZE, BUFFERS)。联合索引顺序匹配过滤、排序和选择性，必要时采用部分或覆盖索引。

验证与退出条件

创建大索引评估锁和磁盘空间，生产可考虑 CONCURRENTLY。上线后比较执行时间、缓冲读取和写入成本，删除索引前确认完整业务周期内未使用。

实践场景

以“优化一个按租户和时间分页的慢查询”为例。实施前先保存当前版本、配置和关键指标基线，写明成功条件与停止条件；涉及生产数据或外部写入时，先在隔离环境验证。

分步实施

用真实参数执行 EXPLAIN ANALYZE，建立与过滤和排序顺序匹配的联合索引；控制包含列，避免索引覆盖所有字段。每完成一步都记录可观察结果，不把命令返回零等同于业务正确。

故障演练

比较冷热缓存、不同租户数据倾斜和深分页，确认写入开销与磁盘增长可接受。演练应使用可清理的测试数据，并验证告警、日志和恢复路径，而不只是确认系统最终又能访问。

观测与回滚

观察执行时间、扫描行数和索引命中；收益不足时并发删除新索引并恢复原计划。变更单中应写清负责人、观察窗口、阈值和回滚触发器，避免故障发生后再临时争论。

完成后把使用的版本、关键命令、观测窗口和结果写入变更记录。没有验证证据的“已完成”不能作为上线结论。

第二轮实践复核

可复用交付物： 查询指纹、计划前后对比和索引回退语句。复用时先固定软件版本、依赖锁文件、测试数据规模和运行环境；文章中的命令包含占位符，必须替换成自己的非生产目标后再执行。

最低验证： EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

执行记录要保存命令、退出码、关键输出、观察窗口和回滚结果。若验证会产生写入，先使用隔离环境或 dry-run；任何失败都不能通过扩大权限、关闭校验或跳过测试来“解决”。

完整实战：为多租户任务列表选索引

查询最近未完成任务：

SELECT id, priority, created_at
FROM jobs
WHERE tenant_id = $1
  AND finished_at IS NULL
ORDER BY priority DESC, created_at ASC, id ASC
LIMIT 100;

如果已完成任务占绝大多数，部分索引能显著减少体积：

1
2
3

CREATE INDEX CONCURRENTLY idx_jobs_open_queue
ON jobs (tenant_id, priority DESC, created_at, id)
WHERE finished_at IS NULL;

谓词必须与查询条件匹配。CONCURRENTLY 降低建索引对写入的阻塞，但耗时更长、资源消耗更高，失败还可能留下无效索引；仍需在维护计划中监控，并按锁定版本文档确认事务限制。

用真实执行计划验证

EXPLAIN (ANALYZE, BUFFERS, WAL, FORMAT TEXT)
SELECT id, priority, created_at
FROM jobs
WHERE tenant_id = 42 AND finished_at IS NULL
ORDER BY priority DESC, created_at, id
LIMIT 100;

检查实际/估算行数、shared hit/read、排序、heap fetch 和循环次数。索引扫描不总是优于顺序扫描：小表、低选择性查询或需要返回大量行时，顺序扫描可能更便宜。不要通过永久关闭某类扫描强迫计划器。

场景	额外关注
更新频繁列	索引维护、HOT update、膨胀
仅少数活跃行	部分索引与谓词稳定性
大范围时间数据	BRIN、分区与物理相关性
文本/数组搜索	GIN/GiST 的写入与 vacuum 成本
多条件组合	复合顺序、扩展统计、真实选择性

索引上线后跨一个完整业务周期观察查询延迟、idx scan、索引大小、WAL、写入延迟和 autovacuum。删除“未使用索引”前排除备用库、月度任务和统计重启影响；先标记候选并观察，再在可恢复窗口单独删除。

一手资料

资料与版本边界复核日期：2026-08-22。工具行为可能演进，实施前以锁定版本对应的官方文档为准。