PostgreSQL / 系统课时

盘点索引空间与使用率

盘点索引空间与使用率

治理索引前先建立候选清单:哪些索引占空间最多,哪些长期几乎没有扫描,哪些虽然很大但正在承担关键查询。盘点的目标不是立刻删除索引,而是把空间成本、访问行为和业务重要性放到同一视图中。

学习目标

  • 能从系统目录和统计视图盘点索引空间占用。
  • 能识别低扫描索引,并正确理解 idx_scan 的含义与局限。
  • 能形成大索引、低使用率和疑似膨胀对象的治理候选清单。

先把索引大小和扫描次数放在一起

pg_stat_user_indexes 可以提供用户表索引的扫描统计,pg_relation_size() 可以取得索引当前物理大小。两者组合后,能快速发现“很大但很少被扫描”的对象,以及“很大且高频使用”的对象。

sql
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;

预期结果

text
 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 的小表却可能占据主要空间。可以把表大小与索引大小一起列出,判断空间比例。

sql
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;

预期结果

text
 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              | 12

index_pct_of_table 只是空间占比指标,不是膨胀率。一个索引本身设计得宽、列很多、数据量大,都可能自然占用较多空间,所以必须继续结合使用场景与增长趋势分析。

建立治理候选清单

盘点结果可以先按三个方向筛选:空间最大的索引、扫描次数长期偏低的索引、空间持续增长且与高更新删除表相关的索引。这样能把大量正常对象排除在后续治理范围之外。

需要特别警惕“低使用率 = 无用索引”的错误结论。统计信息可能刚重置,业务可能存在周期性访问,某个索引也可能只服务少量但关键的查询。确认删除前,应检查实际查询计划、业务访问窗口以及索引约束属性。

让盘点结果可以重复

一次手工查询只能回答当前状态,不能支持持续治理。建议固定执行这类盘点 SQL,把采样时间、索引 OID、索引大小和扫描次数保存下来,再按天或按小时比较增量。这样后续就能区分“长期占空间但稳定”“访问低但偶尔使用”和“持续增长且维护价值明显”的不同对象。

最终的治理候选应当是证据集合,而不是单个指标:空间成本说明影响有多大,访问统计说明它是否仍有价值,连续快照说明问题是否持续,业务与查询计划则决定能否安全处理。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

盘点索引空间与使用率

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通