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: