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主键索引膨胀的重建方法

PostgreSQL主键索引膨胀的重建方法的相关教程结束。