PostgreSQL 查看索引碎片的方法

PostgreSQL 查看索引碎片的方法

在 PostgreSQL 中，索引碎片(Index Fragmentation)是指索引由于频繁的插入、更新和删除操作导致物理存储不连续，从而影响查询性能的情况。以下是几种查看索引碎片的方法：

一 使用 pgstattuple 扩展

1.1 安装扩展

CREATE EXTENSION IF NOT EXISTS pgstattuple;

1.2 查看单个索引的碎片情况

SELECT * FROM pgstattuple('索引名');

输出示例：

white=# SELECT * FROM pgstattuple('yewu1.idx_t1_2');table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------335872 |           0 |         0 |             0 |                0 |              0 |                  0 |     319444 |        95.11
(1 row)table_len          -- 索引总大小(字节)
tuple_count        -- 索引项数量
tuple_len          -- 有效索引项总大小
tuple_percent      -- 有效数据百分比
dead_tuple_count   -- 死元组数量
dead_tuple_len     -- 死元组总大小
dead_tuple_percent -- 死元组百分比
free_space         -- 空闲空间
free_percent       -- 空闲空间百分比

二 使用 pgstatindex 函数

SELECT * FROM pgstatindex('索引名');

输出示例：

white=# SELECT * FROM pgstatindex('yewu1.idx_t1_2');version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation 
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------4 |          1 |     335872 |             3 |              1 |          1 |           0 |            38 |             0.05 |                  0
(1 row)

三 实用查询脚本

3.1 索引碎片概览

SELECTn.nspname AS schema_name,t.relname AS table_name,i.relname AS index_name,pg_size_pretty(pg_relation_size(i.oid)) AS index_size,a.idx_scan AS index_scans,(s.free_space::float / s.table_len * 100)::numeric(5,2) AS fragmentation_percent,s.dead_tuple_percent AS dead_tuple_percent
FROMpg_class t
JOINpg_index ix ON t.oid = ix.indrelid
JOINpg_class i ON i.oid = ix.indexrelid
LEFT JOINpg_namespace n ON n.oid = t.relnamespace
LEFT JOINpg_stat_all_indexes a ON a.indexrelid = i.oid
JOIN LATERALpgstattuple(i.oid) s ON true
WHEREt.relkind = 'r' ANDi.relkind = 'i' ANDn.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BYfragmentation_percent DESC;

输出示例：

 schema_name |    table_name    |      index_name       | index_size | index_scans | fragmentation_percent | dead_tuple_percent 
-------------+------------------+-----------------------+------------+-------------+-----------------------+--------------------yewu1       | t1               | idx_t1_2              | 328 kB     |           2 |                 95.11 |                  0yewu1       | t1               | idx_t1                | 240 kB     |           6 |                 93.32 |                  0public      | pgbench_branches | pgbench_branches_pkey | 16 kB      |           0 |                 48.51 |                  0public      | pgbench_tellers  | pgbench_tellers_pkey  | 16 kB      |           0 |                 37.52 |                  0public      | pgbench_accounts | pgbench_accounts_pkey | 21 MB      |           0 |                  9.84 |                  0
(5 rows)

3.2 需要重建的索引识别

SELECTn.nspname AS schema_name,t.relname AS table_name,i.relname AS index_name,pg_size_pretty(pg_relation_size(i.oid)) AS index_size,(s.free_space::float / s.table_len * 100)::numeric(5,2) AS fragmentation_percent,s.dead_tuple_percent AS dead_tuple_percent
FROMpg_class t
JOINpg_index ix ON t.oid = ix.indrelid
JOINpg_class i ON i.oid = ix.indexrelid
LEFT JOINpg_namespace n ON n.oid = t.relnamespace
JOIN LATERALpgstattuple(i.oid) s ON true
WHEREt.relkind = 'r' ANDi.relkind = 'i' ANDn.nspname NOT IN ('pg_catalog', 'information_schema') AND((s.free_space::float / s.table_len * 100) > 20 OR s.dead_tuple_percent > 20)
ORDER BYpg_relation_size(i.oid) DESC;

输出示例：

 schema_name |    table_name    |      index_name       | index_size | fragmentation_percent | dead_tuple_percent 
-------------+------------------+-----------------------+------------+-----------------------+--------------------yewu1       | t1               | idx_t1_2              | 328 kB     |                 95.11 |                  0yewu1       | t1               | idx_t1                | 240 kB     |                 93.32 |                  0public      | pgbench_branches | pgbench_branches_pkey | 16 kB      |                 48.51 |                  0public      | pgbench_tellers  | pgbench_tellers_pkey  | 16 kB      |                 37.52 |                  0
(4 rows)

四 碎片处理建议

1. 轻度碎片(10-20%)：

   • 定期执行 VACUUM 或 ANALYZE

2. 中度碎片(20-40%)：

   REINDEX INDEX 索引名;
   

3. 严重碎片(>40%)：

   -- 不阻塞业务的并发重建
   REINDEX INDEX CONCURRENTLY 索引名;-- 或者使用pg_repack
   pg_repack -d 数据库名 --only-indexes -t 表名
   

4. 系统级重建：

   -- 重建整个数据库索引
   REINDEX DATABASE 数据库名;-- 重建系统目录索引
   REINDEX SYSTEM 数据库名;
   

五 注意事项

1. REINDEX 会锁表，生产环境建议使用 CONCURRENTLY 选项
2. 大表索引重建需要足够的磁盘空间（约等于原索引大小）
3. 监控 pg_stat_progress_create_index 查看重建进度
4. 重建后执行 ANALYZE 更新统计信息

通过以上方法，可以全面了解 PostgreSQL 索引的碎片情况，并采取适当措施优化数据库性能。

谨记：心存敬畏，行有所止。