Routine rebuild index
Background Information
After the database has been deleted many times, the index keys on the index page will be deleted, causing the index to bloat. Rebuilding indexes routinely can effectively improve query efficiency.
The index type supported by the database is B-tree index, and routine index rebuilding can effectively improve query efficiency.
If a large amount of data is deleted, the index keys on the index pages will be deleted, resulting in a decrease in the number of index pages and causing index inflation. Rebuilding indexes reclaims wasted space.
The logically adjacent pages in the newly created index are usually adjacent in the physical structure, so a newly created index is faster to access than an index that has been updated multiple times.
rebuild index
There are two ways to rebuild the index:
First run the DROP INDEX statement to delete the index, and then run the CREATE INDEX statement to create the index.
During the process of deleting the index, a short-term exclusive lock will be added on the parent table to prevent related read and write operations. During index creation, write operations are locked but read operations are not locked. At this time, read operations can only use sequential scans.
Rebuild indexes using the REINDEX statement.
Using the REINDEX TABLE statement to rebuild an index will add an exclusive lock during the rebuilding process to prevent related read and write operations.
Using the REINDEX INTERNAL TABLE statement to rebuild the index of the desc table (including the cudesc table of the column-stored table) will add an exclusive lock during the rebuilding process to prevent related read and write operations.
Steps
Assume that there is a normal index "areaS_idx" on the "area_id" field on the imported table "areaS". There are two ways to rebuild the index:
First delete the index (DROP INDEX), and then create the index (CREATE INDEX).
Delete the index.
openGauss=# DROP INDEX areaS_idx;
When the result shows the following information, it means the deletion is successful.
DROP INDEX
Create indexes.
openGauss=# CREATE INDEX areaS_idx ON areaS (area_id);
When the result displays the following information, it means the creation is successful.
CREATE INDEX
Use REINDEX to rebuild the index.
Rebuild indexes using the REINDEX TABLE statement.
openGauss=# REINDEX TABLE areaS;
When the result displays the following information, it means the reconstruction is successful.
REINDEX
Use REINDEX INTERNAL TABLE to rebuild the indexes of the desc table (including the cudesc table of the column-stored table).
openGauss=# REINDEX INTERNAL TABLE areaS;
When the result displays the following information, it means the reconstruction is successful.
REINDEX
Note: Before rebuilding the index, users can temporarily increase the values of maintenance_work_mem and psort_work_mem to speed up index rebuilding.
Top comments (0)