PG创建or修改索引

2023-12-14 16:07:04

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');

------------+----------+-----------------+------------+----------+--------------+---------------

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 ;