REINDEX

Rebuild the index.

Summary

REINDEX {INDEX | TABLE | DATABASE | SYSTEM} name

describe

REINDEX rebuilds the index using the data stored in the index table, replacing the old copy of the index. REINDEX can be used in several cases:

The index becomes swollen, that is, it contains many empty or almost empty pages. This can happen in some rare access modes. B-tree indexes in YMatrix databases. REINDEX provides a way to reduce index space consumption by writing new versions of indexes without dead pages.
You have changed the FILLFACTOR storage parameters of the index and want to ensure that the changes made are fully effective.

Parameters

INDEX

Recreate the specified index.

TABLE

Recreate all indexes of the specified table. If the table has a secondary TOAST table, the table will also be reindexed.

DATABASE

Recreate all indexes in the current database. Indexes on shared system directories will also be processed. This form of REINDEX cannot be executed within a transaction block.

SYSTEM

Recreate all indexes on the system directory within the current database. Includes indexes on shared system directories. The index on the user table is not processed. This form of REINDEX cannot be executed within a transaction block.

name

The name of the specific index, table, or database to be re-indexed. The index and table names can be schema-qualified. Currently, REINDEX DATABASE and REINDEX SYSTEM can only re-index the current database, so their parameters must match the name of the current database.

Note

REINDEX is similar to deleting indexes and recreating indexes because the index content is rebuilt from scratch. However, locking precautions are very different. REINDEX locks writes to the index's parent table but does not lock reads. It also takes an exclusive lock on the specific index being processed, which will block the read of that index. Instead, DROP INDEX immediately adds exclusive locks to the parent table, thus preventing write and read. The subsequent CREATE INDEX locks the write but does not lock the read; since the index does not exist, no read attempts are made, which means there will be no blockage, but the read operation may force a costly sequential scan.

Reindexing a single index or table requires that it be the owner of that index or table. Reindexing a database requires becoming the owner of the database (note that the owner can therefore rebuild the index of tables owned by other users).

REINDEX does not update index reltuples and relpages statistics. To update these statistics, run ANALYZE on the table after re-index.

If you suspect that the shared global system directory indexes are corrupted, you can only index them in YMatrix utility mode. A typical symptom of shared index corruption is the "index is not btree" error, otherwise the server will crash immediately due to relying on a corrupt index at startup. In this case, please contact YMatrix Customer Support: [email protected].

Example

Reconstruct a single index:

REINDEX INDEX my_index;

Rebuild all indexes on table my_table:

REINDEX TABLE my_table;

compatibility

There is no REINDEX command in the SQL standard.