索引设计与维护

索引加速读、拖慢写；DBA 要在 查询模式 与 写入/膨胀 间权衡。与 /roadmaps/postgresql-dba 索引节点对齐。

学习目标

• 选择 B-tree、GIN、部分索引适用场景
• 用 pg_stat_user_indexes 发现未使用索引
• 理解索引 bloat 与 REINDEX 时机

核心概念

B-tree（默认）

CREATE UNIQUE INDEX idx_cf_track_slug ON app.cf_track (slug);
CREATE INDEX idx_cf_lesson_track ON app.cf_lesson (track_id);

-- 部分索引：只索引活跃行
CREATE INDEX idx_track_active ON app.cf_track (sort_order)
WHERE is_member = true;

复合索引遵循 最左前缀；高选择性列放前（与 MySQL 相同原则）。

GIN 与表达式索引

CREATE INDEX idx_track_meta_gin ON app.cf_track USING gin (metadata);
CREATE INDEX idx_track_lower_slug ON app.cf_track (lower(slug));

JSONB 用 GIN；函数索引保证 lower(slug) 查询可用索引。

索引审计

SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'app'
ORDER BY idx_scan ASC;

SELECT pg_size_pretty(pg_relation_size('app.idx_cf_track_slug'));

长期 idx_scan = 0 的索引考虑删除；大表 REINDEX CONCURRENTLY idx_name;（PG 12+）避免长时间排他锁。

与 MySQL/Spring Boot 对照

项	PostgreSQL	MySQL 8 (InnoDB)
默认索引	B-tree	B+Tree 聚簇/二级
JSON 索引	GIN / jsonb_path_ops	函数索引（8.0.13+）
在线重建	REINDEX CONCURRENTLY	ALTER TABLE ... ALGORITHM=INPLACE
统计	pg_stat_user_indexes	sys.schema_unused_indexes（8.0）

MyBatis 动态 SQL 若对列套 LOWER()，须建表达式索引；否则 Seq Scan。Spring Data 分页 ORDER BY id 依赖合适索引避免 filesort 类问题。

练习

• 对 slug 等值与 metadata @> 各建索引，EXPLAIN 对比
• 找出 idx_scan 最低的 3 个索引并说明是否可删