Ubuntu 上用好 PostgreSQL 索引的实用技巧
一 环境准备与快速诊断
- 在 Ubuntu 上管理索引与在任何 Linux 发行版一致，常用工具为 psql、pgAdmin 与服务器配置。验证索引是否生效首选 EXPLAIN ANALYZE，观察是否出现 Index Scan / Index Only Scan，并留意实际耗时与行数估计是否准确。执行计划若显示 Seq Scan 且返回大量行，通常索引未被使用。必要时运行 ANALYZE table 更新统计信息，避免计划器误判。
- 监控索引使用情况：查询 pg_stat_user_indexes.idx_scan 可了解命中；对长期 idx_scan=0 且非唯一约束的索引，评估是否可以删除，降低写入与维护成本。
- 在 pgAdmin 中可图形化创建与管理索引（索引类型、列、排序规则、并发创建等），便于团队协作与变更审计。
二 索引类型与适用场景
| 索引类型 |
典型场景 |
关键要点 |
| B-Tree |
等值（=）、范围（<、>、BETWEEN）、排序/分组 |
默认类型；支持 LIKE ‘prefix%’；多列遵循左前缀匹配 |
| Hash |
仅等值（=） |
不支持范围/排序；通用性不如 B-Tree，生产谨慎 |
| GiST |
几何/地理、范围重叠、全文检索、KNN |
适合“重叠/邻近”类查询，支持多种距离/空间操作符 |
| GIN |
数组、JSONB、全文检索 |
适合“包含/存在”类查询，构建与维护成本较高 |
| SP-GiST |
IP 段、树形路径、非平衡数据 |
对稀疏/不均匀分布数据更高效 |
| BRIN |
超大表 + 物理有序（时间序列、自增 ID） |
索引体积极小，按块范围跳过不匹配数据，顺序性要求高 |
- 组合策略：查询可组合多个索引（位图扫描 Bitmap Index Scan），但 ORDER BY 可能触发额外排序；规划器会权衡成本选择单索引或多索引路径。
三 设计与创建的最佳实践
- 复合索引列顺序：将高选择性、常用于过滤的等值列放前，范围/排序列放后，例如 (user_id, created_at DESC) 能高效支持 “WHERE user_id=? AND created_at BETWEEN …”。多列索引遵循左前缀原则，避免“跳列”使用导致失效。
- 覆盖索引与仅索引扫描：通过 INCLUDE 将查询所需非键列并入索引，促成 Index Only Scan，减少回表；宽列放入 INCLUDE 会增加索引体积，需权衡。
- 表达式/函数索引：对 LOWER(email)、to_tsvector(‘english’, content)、profile->>‘city’ 等表达式建索引，查询条件必须与之完全一致才能命中。
- 部分索引：只为热点子集建索引，显著节省空间与维护，例如 CREATE INDEX CONCURRENTLY ON users(email) WHERE active = true; 仅加速活跃用户查询。
- 并发与在线变更：大表建索引使用 CREATE INDEX CONCURRENTLY，避免阻塞写入；失败会留下 INVALID 索引，需要清理重建。
- 何时不建或删除索引：小表、写多读少、过滤性很差（如性别）、极少使用的列；定期清理 idx_scan=0 的冗余索引，减少写放大与计划复杂度。
四 维护与监控要点
- 统计信息与碎片：定期 ANALYZE 更新统计，避免错误计划；出现索引膨胀/性能退化时 REINDEX 重建；配合 VACUUM 回收空间、降低碎片。
- 使用率与成本：通过 pg_stat_user_indexes 观察扫描次数与维护开销，识别“建了但不用”的索引；结合慢查询与 EXPLAIN ANALYZE 验证优化成效。
- 写入权衡：索引会提升读性能但增加 INSERT/UPDATE/DELETE 成本；在 高写入 场景应控制索引数量与宽度，优先选择高选择性列与覆盖列，减少回表与维护压力。
五 常见坑与快速排查清单
- 在索引列上做函数或计算（如 substring(col,1,3)=‘abc’、对列使用表达式）常导致索引失效；改写为 col LIKE ‘abc%’ 或等价表达式索引。
- 多列索引未按左前缀使用（如索引是 (a,b) 但查询只用 b=），将无法高效利用索引；调整顺序或新增单列/组合索引。
- LIKE ‘%bar’ 这类后缀模糊匹配通常不走 B-Tree 索引；考虑 GiST/全文检索 或改写检索方式。
- 统计信息过时导致错误计划；执行 ANALYZE 并复核 EXPLAIN ANALYZE 的实际行数与成本。
- 过度索引与写入放大；删除长期 idx_scan=0 的索引，保留高价值索引并控制列宽与数量。