PostgreSQL 如何低成本评估索引碎片，现有估算 SQL 是否可靠？

2026-11-04数据小许

这段 SQL 不能可靠地计算索引碎片率，也不能保证算出的索引膨胀率接近实际情况。它假定每个表行对应 90 字节的索引空间，但索引定义和实际数据都没有为这个固定值提供依据。

如果要求完全不读取索引页面，可以利用系统目录和文件大小进行初筛、跟踪变化。不过，仅凭这些信息无法准确还原当前的页面密度和叶页碎片情况。

先区分索引碎片与索引膨胀

这两个概念需要分别判断：

索引碎片关注叶页的逻辑顺序与索引文件中页位置的关系，不能直接代表存储设备层面的文件碎片。
索引膨胀关注索引占用空间相对于合理存储需求是否过大，涉及页面空闲空间、可复用页面和索引条目等因素。

pgstattuple 扩展中的 pgstatindex() 提供 leaf_fragmentation、avg_leaf_density、empty_pages 和 deleted_pages 等 B-tree 指标。原 SQL 没有读取页面布局，算出的百分比不能代表这些指标。PostgreSQL pgstattuple 文档

原 SQL 为什么不可靠？

1. 90 在公式中表示字节数

核心表达式是：

NULLIF(reltuples, 0) * 90

reltuples 是行数估计，因此这个公式把每行的索引空间固定为 90 字节。

索引条目的大小取决于键的数据类型、实际长度、复合键、表达式、INCLUDE 列，以及条目头部和对齐开销。同一张表上的两个索引，合理大小也可能完全不同。

B-tree 默认 fillfactor 是 90，但它表示目标页面填充百分比，不能作为每行字节数直接乘以行数。页面预留空间也有用途，不能全部算作应当回收的膨胀。CREATE INDEX 文档

2. 这里取的是表的行数估计

子查询中的条件是：

WHERE oid = i.indrelid

i.indrelid 指向所属表，因此使用的是表的 reltuples。

部分索引只包含满足谓词的行，用全表行数估算会产生偏差。B-tree 去重还可能让多个重复键共享更紧凑的物理表示，进一步改变行数与索引空间之间的关系。即使将表行数改成索引的 reltuples，也无法单独解决宽度、去重和页面开销问题。pg_index 文档、B-tree 实现说明

3. reltuples 不是实时计数

pg_class.reltuples 和 relpages 都是估计值，由 VACUUM、ANALYZE 和部分 DDL 操作更新。reltuples = -1 表示行数未知，不能直接代入估算公式。pg_class 文档

原 SQL 还存在两个边界情况：

reltuples = 0 时，NULLIF 使后续计算得到 NULL，该索引会被过滤掉。这可能漏掉已经清空、但仍占用较大空间的索引。
reltuples = -1 时，该值没有被排除，仍会参与没有实际意义的计算。

更新统计信息只能改善行数估计，不能验证固定的 90 字节假设。

4. 按块取整不能补足存储模型

下面的形式只是把估算字节数向上取整到 block_size 的整数倍：

block_size * CEIL(estimated_bytes / block_size)

它没有计算页头、条目指针、内部页、元页或页面预留空间。如果基础估计有偏差，按块取整也不会让结果变得准确。

最后的 > 65 只是针对这个估算公式设置的筛选条件，不能解释成“索引碎片超过 65%”，也不能直接用作重建条件。

不扫描索引时，可以怎样初筛？

可以先列出 B-tree 索引的实际大小、所属表的估计行数、索引定义和存储参数。下面的查询不遍历索引页面，也不输出未经验证的碎片率：

SELECT
    n.nspname AS schemaname,
    t.relname AS tablename,
    c.relname AS indexname,
    pg_relation_size(c.oid) AS index_bytes,
    pg_size_pretty(pg_relation_size(c.oid)) AS index_size,
    t.reltuples AS estimated_table_rows,
    i.indpred IS NOT NULL AS is_partial,
    c.reloptions AS index_options,
    pg_get_indexdef(c.oid) AS index_definition
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
JOIN pg_index AS i ON i.indexrelid = c.oid
JOIN pg_class AS t ON t.oid = i.indrelid
JOIN pg_am AS am ON am.oid = c.relam
WHERE c.relkind = 'i'
  AND am.amname = 'btree'
  AND i.indisvalid
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND pg_relation_size(c.oid) > 1024 * 1024
ORDER BY index_bytes DESC;

pg_relation_size() 返回关系指定 fork 的大小，这里使用默认的主数据 fork。它提供空间占用信息，不提供页面使用率。数据库对象大小函数文档

使用结果时，建议关注：

同一个索引在一段时间内的大小变化，并结合数据量和索引定义的变化判断。
大量删除数据后，索引是否仍持续占用较多空间，以及后续写入是否会复用这些空间。
是否存在查询性能下降、缓存压力或空间不足等实际问题。

这些信息可以用来选择检查对象。直接比较不同索引的“大小除以全表行数”，通常没有统一的判断标准，部分索引尤其不适合这样比较。

需要确认时，仍要检查页面

如果允许检查少量候选索引，可以在低负载时逐个调用 pgstatindex()。前提是 pgstattuple 扩展已安装，且当前账号具有调用权限：

SELECT
    index_size,
    leaf_pages,
    empty_pages,
    deleted_pages,
    avg_leaf_density,
    leaf_fragmentation
FROM pgstatindex('public.your_index'::regclass);

将 public.your_index 替换为实际索引名。这一步会读取索引页面，因此不能满足“完全不扫描索引”的要求。

这些指标需要结合索引的 fillfactor 和写入模式解释。100 - avg_leaf_density 不能直接当作可回收空间百分比；leaf_fragmentation 较高也不等于查询一定变慢。并发修改期间，逐页累计得到的结果也不代表整个索引在同一时刻的快照。

pgstattuple_approx() 利用表的可见性映射减少扫描，不提供 B-tree 的 leaf_fragmentation，因此不能替代上述索引检查。pgstattuple 函数说明

如果完全不能扫描索引，就应将结果保留为候选名单和空间变化记录，不给出准确碎片率。是否需要 REINDEX，应结合页面检查、空间收益和实际查询表现决定，不能仅依据原 SQL 的 65% 阈值。