索引膨胀
对于索引，随着业务不断的增删改，会造成膨胀，尤其Btree索引，也会涉及索引分裂、合并等，导致索引访问效率降低、维护成本增加。另外，索引页的复用与HEAP PAGE不一样，因为索引的内容是有序结构，只有符合顺序的ITEM才能插入对应的PAGE中，不像HEAP TUPLE，只要有空间就可以插入。index page无论在任何位置，都不能从磁盘删除，因此索引变大后，不能回收空间，除非vacuum full。所以索引膨胀后，通常需要重建索引来回收索引空间。
此外，对于B树索引，新构建的索引比多次更新的索引访问速度稍快，因为逻辑上相邻的页面通常在新构建索引中也是物理上相邻的。为了提高访问速度，定期重新B_Tree 索引可能是值得的。
对于重建索引，REINDEX在任何情况下都可以安全方便地使用。默认情况下，该命令需要ACCESS EXCLUSIVE锁。可以使用CONCURRENTLY选项创建索引，该选项只需要SHARE UPDATE EXCLUSIV锁，不阻塞读写。
索引膨胀的原因：
1.大量删除发生后，导致索引页面稀疏，降低了索引使用效率。
2.长时间运行的事务，阻止了vacuum对表的清理工作，因而导致页面稀疏状态一直保持。
3.索引字段乱序写入，导致索引频繁分裂，使得索引页并不是百分百填满，所以膨胀。
查询获取每个表的行数、索引以及有关这些索引的一些信息：
SELECT pg_class.relname, pg_size_pretty(pg_class.reltuples::bigint) AS rows_in_bytes, pg_class.reltuples AS num_rows, COUNT(*) AS total_indexes, COUNT(*) FILTER ( WHERE indisunique) AS unique_indexes, COUNT(*) FILTER ( WHERE indnatts = 1 ) AS single_column_indexes, COUNT(*) FILTER ( WHERE indnatts IS DISTINCT FROM 1 ) AS multi_column_indexesFROM pg_namespace LEFT JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace LEFT JOIN pg_index ON pg_class.oid = pg_index.indrelidWHERE pg_namespace.nspname = 'public' AND pg_class.relkind = 'r'GROUP BY pg_class.relname, pg_class.reltuplesORDER BY pg_class.reltuples DESC;复制
SELECT t.schemaname, t.tablename, c.reltuples::bigint AS num_rows, pg_size_pretty(pg_relation_size(c.oid)) AS table_size, psai.indexrelname AS index_name, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END AS "unique", psai.idx_scan AS number_of_scans, psai.idx_tup_read AS tuples_read, psai.idx_tup_fetch AS tuples_fetchedFROM pg_tables t LEFT JOIN pg_class c ON t.tablename = c.relname LEFT JOIN pg_index i ON c.oid = i.indrelid LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelidWHERE t.schemaname NOT IN ('pg_catalog', 'information_schema')ORDER BY 1, 2;复制
查找重复索引，查找具有相同列集、相同操作类、表达式和谓词的多个索引，但需要人为判断需要删除的重复项：
SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) as size, (array_agg(idx))[1] as idx1, (array_agg(idx))[2] as idx2, (array_agg(idx))[3] as idx3, (array_agg(idx))[4] as idx4FROM ( SELECT indexrelid::regclass as idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||coalesce(indexprs::text,'')||E'\n' || coalesce(indpred::text,'')) as key FROM pg_index) subGROUP BY key HAVING count(*)>1ORDER BY sum(pg_relation_size(idx)) DESC;复制
查找未使用的索引：
select indexrelid::regclass as index, relid::regclass as table from pg_stat_user_indexes JOIN pg_index USING (indexrelid) where idx_scan = 0 and indisunique is false;复制
有些场景，重建索引后，索引就变小了。通常这种情况是索引字段乱序写入，导致索引频繁分裂，使得索引页并不是百分百填满，密度低，索引页面浪费。
索引碎片模拟
乱序写入: test=# create table t_split(id int); CREATE TABLE test=# create index idx_split on t_split (id); CREATE INDEX test=# insert into t_split select random()*1000000 from generate_series(1,1000000); INSERT 0 1000000 test=# \di+ idx_split List of relations Schema | Name | Type | Owner | Table | Size | Description--------+-----------+-------+--------+---------+-------+------------- public | idx_split | index | system | t_split | 30 MB | (1 row) 顺序写入: test=# truncate t_split ; TRUNCATE TABLE test=# \di+ idx_split List of relations Schema | Name | Type | Owner | Table | Size | Description--------+-----------+-------+--------+---------+------------+------------- public | idx_split | index | system | t_split | 8192 bytes | (1 row) test=# insert into t_split select generate_series(1,1000000); INSERT 0 1000000 test=# \di+ idx_split List of relations Schema | Name | Type | Owner | Table | Size | Description--------+-----------+-------+--------+---------+-------+------------- public | idx_split | index | system | t_split | 22 MB | (1 row) 先写入数据，后建索引: test=# drop index idx_split ; DROP INDEX test=# create index idx_split on t_split (id); CREATE INDEX test=# \di+ idx_split List of relations Schema | Name | Type | Owner | Table | Size | Description--------+-----------+-------+--------+---------+-------+------------- public | idx_split | index | system | t_split | 22 MB |(1 row) 业务运行久了，不断的增删改，也会导致索引碎片: test=# create table test(id int);CREATE TABLEtest=# insert into test values(generate_series(1,1000000));INSERT 0 1000000test=# create index idx_fragmented on test(id);CREATE INDEXCREATE EXTENSION kbstattuple;刚刚创建的索引没有碎片：test=# \xExpanded display is on.test=# SELECT * FROM pgstatindex('idx_fragmented');-[ RECORD 1 ]------+---------version | 4tree_level | 2index_size | 22609920root_block_no | 289internal_pages | 11leaf_pages | 2748empty_pages | 0deleted_pages | 0avg_leaf_density | 89.93leaf_fragmentation | 0 leaf_fragmentation的碎片率是33.33%:test=# insert into test values(generate_series(1,1000000));INSERT 0 1000000test=# SELECT * FROM pgstatindex('idx_fragmented');-[ RECORD 1 ]------+---------version | 4tree_level | 2index_size | 67846144root_block_no | 289internal_pages | 39leaf_pages | 8242empty_pages | 0deleted_pages | 0avg_leaf_density | 60.06leaf_fragmentation | 33.33 reindex之后，即可回收空间，减少碎片。 test=# reindex index idx_fragmented;REINDEXtest=# \di+ idx_fragmentedList of relations-[ RECORD 1 ]---------------Schema | publicName | idx_fragmentedType | indexOwner | systemTable | testSize | 43 MBDescription | test=# SELECT * FROM pgstatindex('idx_fragmented');-[ RECORD 1 ]------+---------version | 4tree_level | 2index_size | 45236224root_block_no | 208internal_pages | 26leaf_pages | 5495empty_pages | 0deleted_pages | 0avg_leaf_density | 90.01leaf_fragmentation | 0复制
总结
通过以上方法监控索引膨胀，以及索引碎片情况，及时对索引reindex 进行碎片优化，建议不要在一个表上建太多索引，准确评估经常update的列和经常select的列，以便创建合适的索引。