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

?