Shulou(Shulou.com)06/03 Report--

The index in pgsql cannot be renamed, and the creation of the duplicate name failed.

Command to create a secondary index: create index CONCURRENTLY idx_abc on tb1 (apene b)

Note: reindex re-index process is blocking, generally large tables are not recommended to use this command, you can rebuild an index, and then delete the old index.

Let's look at an example of page202 in the book "PostgreSQL practice":

Due to the MVCC mechanism of PG, when a large number of update operations are run, the index will swell. At this time, we can rebuild the index online without blocking query and update through create index concurrently. After creating the index, we can delete the original index to reduce the size of the index and improve the query speed. You can also use this method for primary keys, for example:

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)

In this way, the primary key index reconstruction is completed, and for large-scale database clusters, regular index reconstruction can be carried out through the pg_repack tool.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.