REINDEX
Function
REINDEX rebuilds an index using the data stored in the index's table, replacing the old copy of the index.
There are several scenarios in which REINDEX can be used:
- An index has become corrupted, and no longer contains valid data.
- An index has become "bloated", that is, it contains many empty or nearly-empty pages.
- You have altered a storage parameter (such as fillfactor) for an index, and wish to ensure that the change has taken full effect.
An index build with the CONCURRENTLY option failed, leaving an "invalid" index.
Precautions
Index reconstruction of the REINDEX DATABASE or SYSTEM type cannot be performed in transaction blocks.
Syntax
- Rebuild a general index.
1
REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ];
- Rebuild an index partition.
1 2
REINDEX { | TABLE} name PARTITION partition_name [ FORCE ];
Parameter Description
- INDEX
Recreates the specified index.
- TABLE
Recreates all indexes of the specified table. If the table has a secondary TOAST table, that is reindexed as well.
- DATABASE
Recreates all indexes within the current database.
- SYSTEM
Recreates all indexes on system catalogs within the current database. Indexes on user tables are not processed.
- name
Name of the specific index, table, or database to be reindexed. Index and table names can be schema-qualified.
REINDEX DATABASE and SYSTEM can create indexes for only the current database. Therefore, name must be the same as the current database name.
- FORCE
This is an obsolete option. It is ignored if specified.
- partition_name
Specifies the name of the partition or index partition to be reindexed.
Value range:
- If it is REINDEX INDEX, specify the name of an index partition.
- If it is REINDEX TABLE, specify the name of a partition.
Index reconstruction of the REINDEX DATABASE or SYSTEM type cannot be performed in transaction blocks.
Examples
Rebuild a single index:
1
|
REINDEX INDEX tpcds.tpcds_customer_index1; |
Rebuild all indexes on the tpcds.customer_t1 table:
1
|
REINDEX TABLE tpcds.customer_t1; |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.