首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >PG创建or修改索引

PG创建or修改索引

作者头像
保持热爱奔赴山海
发布2019-09-17 14:56:49
2.2K0
发布2019-09-17 14:56:49
举报
文章被收录于专栏:饮水机管理员饮水机管理员

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 工具进行定时的索引重建。

本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
原始发表:2019-09-01 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档