PostgreSQL 索引膨胀

pgstatindex

select * from pgstatindex('tmp_pkey');

输出

postgres=# select * from pgstatindex('tmp_pkey');
-[ RECORD 1 ]------+--------
version            | 4
tree_level         | 2
index_size         | 5931008
root_block_no      | 116
internal_pages     | 8
leaf_pages         | 715
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 89.9
leaf_fragmentation | 0

Time: 24.873 ms

字段含义

• empty_pages：不包含任何用户数据或索引条目的页数量。空页面可能是由于索引重组或删除操作产生的。
• deleted_pages：这些页不再包含有效数据，但可能尚未被回收或清理。高数量的被删除的页可能表明需要进行索引重建 (REINDEX) 以清理这些废弃空间。
• avg_leaf_density：每个叶子页面上实际使用的空间与页面总空间的比例。值越高，表示索引页中存储的数据越紧凑，存储效率越高。低密度可能表明有较多的未使用空间，可能导致更多的磁盘 I/O，从而影响查询性能。
• leaf_fragmentation：碎片化的页通常会包含很多未使用的空间，会增加磁盘查找时间，因为相同数量的数据需要访问更多的页。值越高，表明叶子页面的碎片化程度越严重

示例

数据准备

create table tmp(id text primary key, content text);
insert into tmp select gen_random_uuid() id, 'text'::varchar(100) content from generate_series(1,100000);

查看索引膨胀

postgres=# select * from pgstatindex('tmp_pkey');
 version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation 
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
       4 |          2 |    7815168 |           161 |             11 |        942 |           0 |             0 |             68.4 |              50.21
(1 row)

Time: 11.172 ms

重建索引

重建索引后leaf_fragmentation叶子碎片率降到0。

reindex index CONCURRENTLY tmp_pkey;

输出

postgres=# reindex index CONCURRENTLY tmp_pkey;
REINDEX
Time: 1906.183 ms (00:01.906)
postgres=# select * from pgstatindex('tmp_pkey');
 version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation 
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
       4 |          2 |    5931008 |           116 |              8 |        715 |           0 |             0 |             89.9 |                  0
(1 row)

Time: 6.568 ms

索引占用的磁盘页数

pg_relpages用于快速估算表或索引的磁盘页数

select pg_relpages('index_name');

查看表占用的磁盘页数（没有执行vacuum full前）

postgres=# select pg_relpages('tmp_pkey');
 pg_relpages 
-------------
         974
(1 row)

Time: 3.400 ms

查看表占用的磁盘页数（执行vacuum full后）

postgres=# select pg_relpages('tmp_pkey');
 pg_relpages 
-------------
         724
(1 row)

Time: 3.977 ms

补充

对于HASH索引可以使用pgstathashindex函数。

对于GIN索引可以使用pgstatginindex函数。

表膨胀文章

PostgreSQL 表膨胀

官方文档

https://www.postgresql.org/docs/17/pgstattuple.html