PostgreSQL – REINDEX
In postgreSQL, the REINDEX statement is used to recover corrupt indexes.
An index can get corrupted due to software bugs or hardware failures.
Syntax: REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name;
in the above syntax the VERBOSE keyword is optional argument which is used to show the progress report while reindexing a table. To rebuild a single index, you need to set the index name after REINDEX INDEX clause as shown below:
Syntax: REINDEX INDEX index_name;
To rebuild all the indexes of a table, we can make use of the TABLE keyword as shown below:
Syntax: REINDEX TABLE table_name;
For rebuilding all indices in a schema, one can make use of the SCHEMA keyword as shown below:
Syntax: REINDEX SCHEMA schema_name;
To recover all indices in a specific database, you need to set the database name after the REINDEX DATABASE clause as shown below:
Syntax: REINDEX DATABASE database_name;
The following statement can be used to recover all indices on system catalogs from a specific database:
Syntax: REINDEX SYSTEM database_name;
Example:
In this example we will build a REINDEX for the customer table in the sample database:
REINDEX TABLE customer;
Output: