PG創建or修改索引
- 2019 年 10 月 4 日
- 筆記
pgsql中的索引不能重名，重名的創建失敗。
創建二級索引的命令：create index CONCURRENTLY idx_abc on tb1(a,b);
注意：reindex 重建索引的過程是阻塞的，一般大表不建議使用這個命令，可以重建一個索引，然後刪除老的索引。
下面看一個《PostgreSQL實戰》書上page202的例子：
由於PG的MVCC機制，當運行大量的更新操作後，會有索引膨脹的現象。這時候 可以通過 create index concurrently 不阻塞查詢和更新的情況下，在線重新重建索引，創建好索引之後，再刪除原先的索引，減少索引的尺寸，提高查詢速度。對於主鍵也可以使用這種方式進行，例如：
db1=# d testdata Table "public.testdata" Column | Type | Collation | Nullable | Default ----------+--------------+-----------+----------+--------- id | integer | | not null | course | integer | | | grade | numeric(4,2) | | | testtime | date | | | Indexes: "testdata_pkey" PRIMARY KEY, btree (id) db1=# create unique index concurrently on testdata using btree(id); db1=# 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 ='public' and tablename='testdata'); schemaname | relname | indexrelname | index_size | idx_scan | idx_tup_read | idx_tup_fetch ------------+----------+-----------------+------------+----------+--------------+--------------- public | testdata | testdata_pkey | 16384 | 2 | 5999998 | 5999998 public | testdata | testdata_id_idx | 16384 | 0 | 0 | 0 (2 rows) db1=# begin; BEGIN db1=# alter table testdata drop constraint testdata_pkey; ALTER TABLE db1=# alter table testdata add constraint testdata_id_idx primary key using index testdata_id_idx ; ALTER TABLE db1=# end; COMMIT db1=# d testdata Table "public.testdata" Column | Type | Collation | Nullable | Default ----------+--------------+-----------+----------+--------- id | integer | | not null | course | integer | | | grade | numeric(4,2) | | | testtime | date | | | Indexes: "testdata_id_idx" PRIMARY KEY, btree (id) db1=# 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 ='public' and tablename='testdata'); schemaname | relname | indexrelname | index_size | idx_scan | idx_tup_read | idx_tup_fetch ------------+----------+-----------------+------------+----------+--------------+--------------- public | testdata | testdata_id_idx | 16384 | 0 | 0 | 0 (1 row)
這樣就完成了主鍵索引的重建，對於大規模的資料庫集群，可以通過 pg_repack 工具進行定時的索引重建。
