PostgreSQL / 系统课时
盘点索引空间与使用率
盘点索引空间与使用率
治理索引前先建立候选清单:哪些索引占空间最多,哪些长期几乎没有扫描,哪些虽然很大但正在承担关键查询。盘点的目标不是立刻删除索引,而是把空间成本、访问行为和业务重要性放到同一视图中。
学习目标
- 能从系统目录和统计视图盘点索引空间占用。
- 能识别低扫描索引,并正确理解
idx_scan的含义与局限。 - 能形成大索引、低使用率和疑似膨胀对象的治理候选清单。
先把索引大小和扫描次数放在一起
pg_stat_user_indexes 可以提供用户表索引的扫描统计,pg_relation_size() 可以取得索引当前物理大小。两者组合后,能快速发现“很大但很少被扫描”的对象,以及“很大且高频使用”的对象。
SELECT
s.schemaname,
s.relname AS table_name,
s.indexrelname AS index_name,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
pg_relation_size(s.indexrelid) AS index_bytes,
s.idx_scan,
s.idx_tup_read,
s.idx_tup_fetch
FROM pg_stat_user_indexes s
ORDER BY index_bytes DESC
LIMIT 30;预期结果
schemaname | table_name | index_name | index_size | index_bytes | idx_scan | idx_tup_read | idx_tup_fetch
------------+------------+------------------+------------+-------------+----------+--------------+--------------
app | orders | orders_customer | 512 MB | 536870912 | 850000 | ... | ...
app | audit_log | audit_log_type | 420 MB | 440401920 | 12 | ... | ...这里的 idx_scan 是累计扫描次数,不是数据库定义的“索引使用率百分比”。它会随统计信息生命周期变化,因此比较不同索引时可以用于初筛,但不要据此直接判定“无用”。
再看索引占整张表多大
单独看索引大小容易误判。一个 500 MB 的索引对 20 GB 的业务表可能很普通,对 700 MB 的小表却可能占据主要空间。可以把表大小与索引大小一起列出,判断空间比例。
SELECT
s.schemaname,
s.relname AS table_name,
s.indexrelname AS index_name,
pg_size_pretty(pg_relation_size(s.relid)) AS table_size,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
round(
100.0 * pg_relation_size(s.indexrelid)
/ NULLIF(pg_relation_size(s.relid), 0),
2
) AS index_pct_of_table,
s.idx_scan
FROM pg_stat_user_indexes s
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 30;预期结果
schemaname | table_name | index_name | table_size | index_size | index_pct_of_table | idx_scan
------------+------------+----------------+------------+------------+--------------------+----------
app | orders | orders_customer | 2 GB | 512 MB | 25.00 | 850000
app | audit_log | audit_log_type | 600 MB | 420 MB | 70.00 | 12index_pct_of_table 只是空间占比指标,不是膨胀率。一个索引本身设计得宽、列很多、数据量大,都可能自然占用较多空间,所以必须继续结合使用场景与增长趋势分析。
建立治理候选清单
盘点结果可以先按三个方向筛选:空间最大的索引、扫描次数长期偏低的索引、空间持续增长且与高更新删除表相关的索引。这样能把大量正常对象排除在后续治理范围之外。
需要特别警惕“低使用率 = 无用索引”的错误结论。统计信息可能刚重置,业务可能存在周期性访问,某个索引也可能只服务少量但关键的查询。确认删除前,应检查实际查询计划、业务访问窗口以及索引约束属性。
让盘点结果可以重复
一次手工查询只能回答当前状态,不能支持持续治理。建议固定执行这类盘点 SQL,把采样时间、索引 OID、索引大小和扫描次数保存下来,再按天或按小时比较增量。这样后续就能区分“长期占空间但稳定”“访问低但偶尔使用”和“持续增长且维护价值明显”的不同对象。
最终的治理候选应当是证据集合,而不是单个指标:空间成本说明影响有多大,访问统计说明它是否仍有价值,连续快照说明问题是否持续,业务与查询计划则决定能否安全处理。