Aleksei Aleinikov：PostgreSQL 索引类型选择指南（1000 万行基准测试）
内容提要
文章基于1000万行数据实测PostgreSQL 18各类索引：B-tree为默认首选，等值查询约0.11毫秒；哈希索引无优势且体积大3倍；BRIN仅72 KiB，适合时间有序大表，但慢约3倍；部分索引只含0.85%的行，体积小160倍且速度相当；覆盖索引快1.4倍但大4.4倍，且UPDATE后失效；GIN适合数组、jsonb和全文检索，匹配稀少时快129倍；GiST/SP-GiST用于几何与范围查询。每增加一个索引都会拖慢写入，应据查询选型并清理未用索引。
延伸解读
索引选型先看查询形态，而非类型排名
文章反复强调从查询出发：等值、范围、排序优先 B-tree；只关心小比例行时用部分索引；大表按时间追加且查询常为“最近一天”时考虑 BRIN；数组、jsonb、全文检索用 GIN。哈希索引在本次测试中与 B-tree 速度相当但体积大 3 倍，说明类型选择应服从实际查询与数据分布，而不是套用理论优势。
空间与写入成本是索引的隐性账单
基准显示，部分索引只覆盖 0.85% 的行，体积约为全量索引的 1/160，速度却相当；覆盖索引快 1.4 倍但大 4.4 倍，且 UPDATE 后需 VACUUM 才能恢复索引-only 扫描优势。插入 100 万行时，索引从 0 增到 5 个，耗时从 1.9 秒升至 12.1 秒，其中 GIN 维护成本最高。选索引时要把磁盘、内存和写放大一起算进去。
索引没被用上，往往是查询写法的问题
文章给出两个实测案例：对 created_at 做 ::date 转换后，索引失效，顺序扫描耗时 329 毫秒，改为范围条件或表达式索引后降到 2.7 毫秒；当条件命中 70% 的行时，规划器选择顺序扫描是合理的。部分索引还要求查询条件能蕴含索引条件，参数化查询可能让规划器无法证明匹配。上线前用 EXPLAIN (ANALYZE, BUFFERS) 确认执行计划，比只看索引是否存在更可靠。
PostgreSQL 18 的新能力改变了部分取舍
PostgreSQL 18 引入多列 B-tree 的 skip scan，使未过滤前导列的查询也能利用现有索引，实测一小时范围查询从 233 毫秒降到 0.246 毫秒；并行 GIN 构建在 tags 索引上从 14.4 秒降到 3.8 秒；内置 uuidv7() 让主键按时间有序，插入 500 万行比随机 UUIDv4 快 1.3 倍，主键索引小约五分之一。评估索引策略时，应把版本能力纳入考虑。
Q&A
PostgreSQL 里建索引默认用哪种类型？它适合哪些查询？
默认是 B-tree。它支持等值、范围、ORDER BY 和 IS NULL，在 1000 万行测试中等值查询约 0.11 毫秒，索引 88.5 MiB，构建 1.4 秒。
哈希索引比 B-tree 快吗？实测结果如何？
不快。等值查询哈希 0.115 毫秒，B-tree 0.112 毫秒；哈希索引 267.6 MiB，是 B-tree 的 3 倍，构建 13 秒对 1.4 秒，且只支持等值，不支持范围和排序。
BRIN 索引适合什么场景？和 B-tree 比差多少？
适合按时间有序、追加写入的大表。一天范围查询 B-tree 3.0 毫秒、索引 214 MiB；BRIN 9.1 毫秒、仅 72 KiB，慢约 3 倍但小约 3000 倍。要求索引列与物理顺序相关（pg_stats.correlation 接近 1）。
部分索引能带来多大收益？使用时要注意什么？
只索引满足 WHERE 的行。测试中 failed 状态仅占 0.85%，部分索引 1.8 MiB，比全量索引 301 MiB 小约 160 倍，速度相当，写入成本极低。注意查询的 WHERE 必须蕴含索引的 WHERE，参数化条件可能让计划器无法匹配，需用 EXPLAIN 确认。
覆盖索引（INCLUDE）真的能加速查询吗？有什么代价？
能，但有限。测试中从 0.109 毫秒降到 0.078 毫秒（快 1.4 倍），索引却大 4.4 倍（386.7 MiB 对 88.5 MiB）。且索引-only 扫描依赖可见性映射，UPDATE 1% 行后优势消失，直到 VACUUM 重新运行。
GIN 索引用在什么数据类型上？效果取决于什么？
用于数组、jsonb、全文检索和 pg_trgm 的 LIKE '%x%'。效果取决于选择性：稀有标签快 129 倍（360 毫秒到 2.8 毫秒），匹配 2.5% 行时仅快 1.1 到 1.5 倍。jsonb 用 jsonb_path_ops 体积约一半且更快。
增加索引对写入性能影响有多大？
每个索引都会拖慢写入。插入 100 万行：仅主键 1.9 秒；加 1 个 B-tree 4.3 秒（2.3 倍）；3 个 5.8 秒（3.1 倍）；5 个（含两个 GIN）12.1 秒（6.4 倍）。GIN 维护成本最高。
PostgreSQL 18 的 skip scan 是什么？什么时候有用？
skip scan 让多列 B-tree 索引在查询未过滤前导列时也能使用。测试中索引 (status, created_at)，仅按 created_at 过滤：无索引 233 毫秒，有索引 0.246 毫秒，EXPLAIN 显示 Index Searches: 11。前导列不同值少时有效，不同值多时不能替代专用索引。
为什么查询没有用上索引？常见原因有哪些？
常见原因：索引列被函数或类型转换包裹（如 created_at::date），需改写为范围或建表达式索引；查询匹配大部分行时计划器选择顺序扫描（如 status='done' 占 70% 行，263 毫秒走顺序扫描）。用 EXPLAIN (ANALYZE, BUFFERS) 确认。
UUID 主键用 v4 还是 v7？对索引有什么影响？
推荐 UUIDv7。它带时间戳，新键按顺序追加到 B-tree 右端；随机 UUIDv4 会分散插入并分裂页面。插入 500 万行：UUIDv7 9.6 秒、主键 150 MiB；UUIDv4 12.9 秒、191 MiB。PostgreSQL 18 内置 uuidv7()。
