专栏首页二狗的DBA之路PG创建or修改索引

PG创建or修改索引

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MongoDB 基线检查项

    二狗不要跑
  • PG中只读账号的授权操作

    但是,只读账号稍微费事点,如果我们处理不好的话,每次新加表都要再执行一次对只读账号的重新授权操作。好在PG为我们考虑好了这个场景,也是有方法解决的。

    二狗不要跑
  • MySQL binlog event 详解

    我也是只菜鸡,blog写的不对或者不严谨的地方还请大伙指出来,我及时改正,免得误人子弟。

    二狗不要跑
  • 中国优步大数据负责人江天:优步交通大数据探索与应用

    <数据猿导读> 中国优步大数据负责人江天在中国信息通信大数据大会上发表了以“优步交通大数据探索与应用”为主题的演讲。他讲到,优步的大数据应用有两个方向:一是利用...

    数据猿
  • R语言系列第四期(番外篇):样本容量和把握度计算

    在做任何课题之前,我们都需要计算推荐的样本容量,不然不论是研究生开题还是答辩,总有人要问你你的样本容量是多大这样的问题,所有我们这里简单给大家说说如何计算简单检...

    百味科研芝士
  • 《数据结构》 队列(Queue)操作代码集合

    队列基本操作代码集合,来自《数据结构-用C语言描述》(第二版) 高教社 队列是受限制的链表或顺序表(只能从队首取结点,先进先出FIFO),相关操作可以...

    Steve Wang
  • python df遍历的N种方式

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 ...

    多凡
  • 哈佛大学心理系教授:获得一种与岁月对抗的力量

    在岁月面前,我们真的无能为力吗?将一个人的心理时钟倒拨20年,有可能吗?在这里,心理学将为我们发现这种“可能”。

    用户1756920
  • MySQL中IS NULL、IS NOT NULL、!=不能用索引?胡扯!

    这种说法愈演愈烈,甚至被很多同学奉为真理。咱啥话也不说,举个例子。假如我们有个表s1,结构如下:

    JAVA葵花宝典
  • MySQL中IS NULL、IS NOT NULL、!=不能用索引?胡扯!

    这种说法愈演愈烈,甚至被很多同学奉为真理。咱啥话也不说,举个例子。假如我们有个表s1,结构如下:

    帅地

扫码关注云+社区

领取腾讯云代金券