PostgreSQL主键索引膨胀的重建方法

普通的索引膨胀处理比较简单，主键的索引膨胀也不复杂，只是在新旧索引交替时有一些小处理。本试验在primary key上通过CONCURRENTLY建立第二索引来解决索引膨胀问题，适用9.3、9.4，其他版本使用前请实际测试。

创建测试表

Table "swrd.mytbl"
Column | Type | Modifiers
--------+-----------------------------+----------------------------------------------------
id | integer | not null default nextval('mytbl_id_seq'::regclass)
val | timestamp without time zone | default now()
Indexes:
"mytbl_pkey" PRIMARY KEY, btree (id)


生成测试数据

生成测试数据步骤略，这里为了清楚看到测试的情况，生成10000000条。

swrd=# SELECT COUNT(*) FROM mytbl;
count
----------
10000000
(1 row)


创建第二索引

在id上创建第二索引，记得使用CONCURRENTLY参数

swrd=# CREATE UNIQUE INDEX CONCURRENTLY ON mytbl USING btree(id);
CREATE INDEX


可以看到id字段上同时有两个索引mytbl_pkey和mytbl_id_idx

swrd=# SELECT schemaname,relname,indexrelname,pg_relation_size(indexrelid) AS index_size,idx_scan,idx_tup_read,idx_tup_fetch FROM pg_stat_user_indexes WHERE indexrelname IN (SELECT indexname FROM pg_indexes WHERE schemaname = 'swrd' AND tablename = 'mytbl');
schemaname | relname | indexrelname | index_size | idx_scan | idx_tup_read | idx_tup_fetch
------------+---------+--------------+------------+----------+--------------+---------------
swrd | mytbl | mytbl_pkey | 224632832 | 0 | 0 | 0
swrd | mytbl | mytbl_id_idx | 224641024 | 0 | 0 | 0
(2 rows)


替换索引

开启事务删除主键索引同时将第二索引更新为主键的约束

swrd=# begin;
BEGIN
swrd=# ALTER TABLE mytbl DROP CONSTRAINT mytbl_pkey;
ALTER TABLE
swrd=# ALTER TABLE mytbl ADD CONSTRAINT mytbl_id_idx PRIMARY KEY USING INDEX mytbl_id_idx;
ALTER TABLE
swrd=# END;
COMMIT


检查测试表的索引，可见现在只有第二索引了

swrd=# SELECT schemaname,relname,indexrelname,pg_relation_size(indexrelid) AS index_size,idx_scan,idx_tup_read,idx_tup_fetch FROM pg_stat_user_indexes WHERE indexrelname IN (SELECT indexname FROM pg_indexes WHERE schemaname = 'swrd' AND tablename = 'mytbl');
schemaname | relname | indexrelname | index_size | idx_scan | idx_tup_read | idx_tup_fetch
------------+---------+--------------+------------+----------+--------------+---------------
swrd | mytbl | mytbl_id_idx | 224641024 | 0 | 0 | 0
(1 row)


检查表的定义

检查表定义，可以看到与最初建表时是一样的

swrd=# \d+ mytbl
Table "swrd.mytbl"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------------+----------------------------------------------------+---------+--------------+-------------
id | integer | not null default nextval('mytbl_id_seq'::regclass) | plain | |
val | timestamp without time zone | default now() | plain | |
Indexes:
"mytbl_id_idx" PRIMARY KEY, btree (id)


参考：PostgreSQL主键索引膨胀的重建方法