PostgreSQL / 系统课时
识别重复与低价值索引
识别重复与低价值索引
JSONB 表很容易同时积累整列 GIN、路径表达式索引和普通 B-tree 索引。索引越多并不代表查询越快,每次写入都要维护这些结构。本课通过定义、尺寸、使用统计和约束依赖建立索引清单,识别完全重复以及收益不足的治理候选项。
学习目标
- 盘点 JSONB 相关索引的定义、尺寸、访问次数和约束依赖
- 区分完全重复索引与仅部分覆盖的相似索引
- 在删除索引前验证查询收益、统计窗口和业务约束
建立索引清单
先不要根据索引名称判断重复。两个名称不同的索引可能定义完全相同,而看起来相似的 GIN 与表达式索引又可能支持完全不同的谓词。应直接读取 PostgreSQL 系统目录和使用统计。
下面查询 event_store 上所有用户索引,同时显示访问次数、尺寸、完整定义,以及索引是否被约束引用。
SELECT
c.relname AS index_name,
am.amname AS access_method,
pg_size_pretty(pg_relation_size(c.oid)) AS index_size,
COALESCE(s.idx_scan, 0) AS idx_scan,
con.conname AS constraint_name,
pg_get_indexdef(c.oid) AS index_definition
FROM pg_index AS i
JOIN pg_class AS c ON c.oid = i.indexrelid
JOIN pg_class AS t ON t.oid = i.indrelid
JOIN pg_am AS am ON am.oid = c.relam
LEFT JOIN pg_stat_user_indexes AS s ON s.indexrelid = c.oid
LEFT JOIN pg_constraint AS con ON con.conindid = c.oid
WHERE t.oid = 'event_store'::regclass
ORDER BY pg_relation_size(c.oid) DESC;预期结果
index_name | access_method | index_size | idx_scan | constraint_name | index_definition
-----------------------+---------------+------------+----------+-----------------+------------------------------
<INDEX_NAME> | gin | <VALUE> | <VALUE> | | CREATE INDEX ... USING gin ...
<INDEX_NAME> | btree | <VALUE> | <VALUE> | <VALUE> | CREATE INDEX ... USING btree ...idx_scan 是自统计信息上次重置以来的累计值,因此 <VALUE> 为环境动态数据。一次看到 idx_scan = 0 不能直接证明索引无用,应先确认统计窗口覆盖了完整业务周期。
先找完全重复,再看重叠覆盖
完全重复索引通常具有相同的表、访问方法、键或表达式、操作符类以及部分索引谓词。例如两条都对 payload 使用同一种 GIN 操作符类且没有不同过滤条件,其中一条往往只是重复维护成本。
“相似”则不等于“重复”。整列 jsonb_ops GIN 可以支持多类 JSONB 操作符,而 jsonb_path_ops 的能力范围不同;对 payload->>'tenant_id' 建立的 B-tree 表达式索引,主要服务标量等值或排序谓词,也不能简单视为整列 GIN 的副本。
因此治理时应比较实际查询谓词。如果线上 SQL 使用 payload @> ...,就检查哪条索引真正进入执行计划;如果 SQL 使用 (payload->>'tenant_id') = ...,则重点验证对应表达式索引,而不是仅比较索引列名。
判断低价值索引
低价值索引通常同时具备几个信号:尺寸较大、观察周期内访问很少、写入频繁,并且其查询已经被其他索引稳定覆盖。但这些信号必须结合使用,不能把 idx_scan 当作自动删除开关。
还要排除两类特殊对象。第一类是支撑 PRIMARY KEY、UNIQUE 或 EXCLUDE 等约束的索引,删除索引可能破坏约束语义;第二类是低频但关键的索引,例如月度结算、故障处置或管理任务只偶尔执行,却可能有严格延迟要求。
安全地完成治理
治理顺序应从风险最低的对象开始:先确认完全重复,再处理明显被覆盖且收益不足的索引。对每个候选项保存原始 CREATE INDEX 定义,并用代表性 SQL 的 EXPLAIN (ANALYZE, BUFFERS) 验证查询是否依赖它。
生产环境需要删除普通索引时,通常优先评估 DROP INDEX CONCURRENTLY,以降低对并发访问的阻塞风险;该命令有自身限制,例如不能放在显式事务块中,也不能直接用于仍由约束依赖的索引。
真正有效的索引治理不是追求“索引数量最少”,而是让每一份磁盘和写入维护成本都有明确查询收益。对 JSONB 尤其要避免同时保留多个边界不清的宽泛索引:先从查询谓词确认职责,再决定保留、收窄还是淘汰。