{"Entry":{"collection":"sql","key":"reindex","name":"REINDEX","aliases":["reindex"],"metadata":{"aliases":["reindex"],"changed_in":["7.4","8.1","9.5","12","13","14","16"],"changes":[{"from":"6.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"added","synopsis":null,"to":"7.0"},{"from":"7.0","purpose_changed":true,"renamed":{"from_file":"sql-reindex.htm","to_file":"sql-reindex.html"},"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"7.1"},{"from":"7.1","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":["description","usage"],"removed":[]},"status":"changed","synopsis":null,"to":"7.2"},{"from":"7.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"7.3"},{"from":"7.3","purpose_changed":true,"renamed":null,"sections":{"added":["parameters","notes","examples"],"changed":["description","compatibility"],"removed":["usage"]},"status":"changed","synopsis":{"added":["REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ]"],"removed":["REINDEX { TABLE | DATABASE | INDEX } name [ FORCE ]"]},"to":"7.4"},{"from":"7.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.0"},{"from":"8.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes","examples"],"removed":[]},"status":"changed","synopsis":{"added":["REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]"],"removed":["REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ]"]},"to":"8.1"},{"from":"8.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"8.2"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.3"},{"from":"8.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.4"},{"from":"8.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.0"},{"from":"9.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.4"},{"from":"9.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":{"added":["REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name"],"removed":["REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]"]},"to":"9.5"},{"from":"9.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.6"},{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"10"},{"from":"10","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"11"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":["see_also"],"changed":["description","parameters","notes","examples"],"removed":[]},"status":"changed","synopsis":{"added":["REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name"],"removed":[]},"to":"12"},{"from":"12","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":{"added":["REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name","VERBOSE"],"removed":["REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name"]},"to":"13"},{"from":"13","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes","see_also"],"removed":[]},"status":"changed","synopsis":{"added":["CONCURRENTLY [ boolean ]","TABLESPACE new_tablespace","VERBOSE [ boolean ]"],"removed":[]},"to":"14"},{"from":"14","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"15"},{"from":"15","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes","see_also"],"removed":[]},"status":"changed","synopsis":{"added":["REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA } [ CONCURRENTLY ] name","REINDEX [ ( option [, ...] ) ] { DATABASE | SYSTEM } [ CONCURRENTLY ] [ name ]"],"removed":["REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name"]},"to":"16"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes","see_also"],"removed":[]},"status":"changed","synopsis":null,"to":"17"},{"from":"17","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"18"}],"content_hash":"3efd586567007a32564ad5380c25b747a28302c6549df6dace1141c266f844bb","editorial":{},"first_version":"7.0","group":"index","imported_at":"2026-09-30T17:43:36.818211+08:00","last_version":"20","name":"REINDEX","object":"","position":1000,"present_in":["7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"rebuild indexes","purpose_zh":"","related":["create-index","drop-index"],"slug":"reindex","source_rev":"a709ab85","synopsis":"REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA } [ CONCURRENTLY ] name\nREINDEX [ ( option [, ...] ) ] { DATABASE | SYSTEM } [ CONCURRENTLY ] [ name ]\n\nwhere option can be one of:\n\nCONCURRENTLY [ boolean ]\nTABLESPACE new_tablespace\nVERBOSE [ boolean ]","verb":"REINDEX"}},"Definition":{"Collection":"sql","Key":"reindex","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"reindex","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-REINDEX","file":"sql-reindex.html","lang":"en","name":"REINDEX","purpose":"rebuild indexes","purpose_zh":"","related":["create-index","drop-index"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e 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 to use \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e:\u003c/p\u003e\u003cdiv class=\"itemizedlist\"\u003e\u003cul class=\"itemizedlist\"\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eAn index has become corrupted, and no longer contains valid data. Although in theory this should never happen, in practice indexes can become corrupted due to software bugs or hardware failures. \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e provides a recovery method.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eAn index has become \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003ebloated\u003c/span\u003e”\u003c/span\u003e, that is it contains many empty or nearly-empty pages. This can occur with B-tree indexes in \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e under certain uncommon access patterns. \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e provides a way to reduce the space consumption of the index by writing a new version of the index without the dead pages. See \u003ca href=\"/docs/18/routine-reindex.html\" title=\"24.2. Routine Reindexing\"\u003eSection 24.2\u003c/a\u003e for more information.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eYou have altered a storage parameter (such as fillfactor) for an index, and wish to ensure that the change has taken full effect.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eIf an index build fails with the \u003ccode class=\"literal\"\u003eCONCURRENTLY\u003c/code\u003e option, this index is left as \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003einvalid\u003c/span\u003e”\u003c/span\u003e. Such indexes are useless but it can be convenient to use \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e to rebuild them. Note that only \u003ccode class=\"command\"\u003eREINDEX INDEX\u003c/code\u003e is able to perform a concurrent build on an invalid index.\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eINDEX\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eRecreate the specified index. This form of \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e cannot be executed inside a transaction block when used with a partitioned index.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTABLE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eRecreate all indexes of the specified table. If the table has a secondary \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eTOAST\u003c/span\u003e”\u003c/span\u003e table, that is reindexed as well. This form of \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e cannot be executed inside a transaction block when used with a partitioned table.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSCHEMA\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eRecreate all indexes of the specified schema. If a table of this schema has a secondary \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eTOAST\u003c/span\u003e”\u003c/span\u003e table, that is reindexed as well. Indexes on shared system catalogs are also processed. This form of \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e cannot be executed inside a transaction block.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDATABASE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eRecreate all indexes within the current database, except system catalogs. Indexes on system catalogs are not processed. This form of \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e cannot be executed inside a transaction block.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSYSTEM\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eRecreate all indexes on system catalogs within the current database. Indexes on shared system catalogs are included. Indexes on user tables are not processed. This form of \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e cannot be executed inside a transaction block.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of the specific index, table, or database to be reindexed. Index and table names can be schema-qualified. Presently, \u003ccode class=\"command\"\u003eREINDEX DATABASE\u003c/code\u003e and \u003ccode class=\"command\"\u003eREINDEX SYSTEM\u003c/code\u003e can only reindex the current database. Their parameter is optional, and it must match the current database's name.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eCONCURRENTLY\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eWhen this option is used, \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e will rebuild the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index rebuild locks out writes (but not reads) on the table until it's done. There are several caveats to be aware of when using this option — see \u003ca href=\"/docs/18/sql-reindex.html#SQL-REINDEX-CONCURRENTLY\" title=\"Rebuilding Indexes Concurrently\"\u003eRebuilding Indexes Concurrently\u003c/a\u003e below.\u003c/p\u003e\u003cp\u003eFor temporary tables, \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e is always non-concurrent, as no other session can access them, and non-concurrent reindex is cheaper.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTABLESPACE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies that indexes will be rebuilt on a new tablespace.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eVERBOSE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003ePrints a progress report as each index is reindexed at \u003ccode class=\"literal\"\u003eINFO\u003c/code\u003e level.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies whether the selected option should be turned on or off. You can write \u003ccode class=\"literal\"\u003eTRUE\u003c/code\u003e, \u003ccode class=\"literal\"\u003eON\u003c/code\u003e, or \u003ccode class=\"literal\"\u003e1\u003c/code\u003e to enable the option, and \u003ccode class=\"literal\"\u003eFALSE\u003c/code\u003e, \u003ccode class=\"literal\"\u003eOFF\u003c/code\u003e, or \u003ccode class=\"literal\"\u003e0\u003c/code\u003e to disable it. The \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e value can also be omitted, in which case \u003ccode class=\"literal\"\u003eTRUE\u003c/code\u003e is assumed.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_tablespace\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe tablespace where indexes will be rebuilt.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eIf you suspect corruption of an index on a user table, you can simply rebuild that index, or all indexes on the table, using \u003ccode class=\"command\"\u003eREINDEX INDEX\u003c/code\u003e or \u003ccode class=\"command\"\u003eREINDEX TABLE\u003c/code\u003e.\u003c/p\u003e\u003cp\u003eThings are more difficult if you need to recover from corruption of an index on a system table. In this case it's important for the system to not have used any of the suspect indexes itself. (Indeed, in this sort of scenario you might find that server processes are crashing immediately at start-up, due to reliance on the corrupted indexes.) To recover safely, the server must be started with the \u003ccode class=\"option\"\u003e-P\u003c/code\u003e option, which prevents it from using indexes for system catalog lookups.\u003c/p\u003e\u003cp\u003eOne way to do this is to shut down the server and start a single-user \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e server with the \u003ccode class=\"option\"\u003e-P\u003c/code\u003e option included on its command line. Then, \u003ccode class=\"command\"\u003eREINDEX DATABASE\u003c/code\u003e, \u003ccode class=\"command\"\u003eREINDEX SYSTEM\u003c/code\u003e, \u003ccode class=\"command\"\u003eREINDEX TABLE\u003c/code\u003e, or \u003ccode class=\"command\"\u003eREINDEX INDEX\u003c/code\u003e can be issued, depending on how much you want to reconstruct. If in doubt, use \u003ccode class=\"command\"\u003eREINDEX SYSTEM\u003c/code\u003e to select reconstruction of all system indexes in the database. Then quit the single-user server session and restart the regular server. See the \u003ca href=\"/docs/18/app-postgres.html\" title=\"postgres\"\u003e\u003cspan class=\"refentrytitle\"\u003e\u003cspan class=\"application\"\u003epostgres\u003c/span\u003e\u003c/span\u003e\u003c/a\u003e reference page for more information about how to interact with the single-user server interface.\u003c/p\u003e\u003cp\u003eAlternatively, a regular server session can be started with \u003ccode class=\"option\"\u003e-P\u003c/code\u003e included in its command line options. The method for doing this varies across clients, but in all \u003cspan class=\"application\"\u003elibpq\u003c/span\u003e-based clients, it is possible to set the \u003ccode class=\"envar\"\u003ePGOPTIONS\u003c/code\u003e environment variable to \u003ccode class=\"literal\"\u003e-P\u003c/code\u003e before starting the client. Note that while this method does not require locking out other clients, it might still be wise to prevent other users from connecting to the damaged database until repairs have been completed.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e is similar to a drop and recreate of the index in that the index contents are rebuilt from scratch. However, the locking considerations are rather different. \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e locks out writes but not reads of the index's parent table. It also takes an \u003ccode class=\"literal\"\u003eACCESS EXCLUSIVE\u003c/code\u003e lock on the specific index being processed, which will block reads that attempt to use that index. In particular, the query planner tries to take an \u003ccode class=\"literal\"\u003eACCESS SHARE\u003c/code\u003e lock on every index of the table, regardless of the query, and so \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e blocks virtually any queries except for some prepared queries whose plan has been cached and which don't use this very index. In contrast, \u003ccode class=\"command\"\u003eDROP INDEX\u003c/code\u003e momentarily takes an \u003ccode class=\"literal\"\u003eACCESS EXCLUSIVE\u003c/code\u003e lock on the parent table, blocking both writes and reads. The subsequent \u003ccode class=\"command\"\u003eCREATE INDEX\u003c/code\u003e locks out writes but not reads; since the index is not there, no read will attempt to use it, meaning that there will be no blocking but reads might be forced into expensive sequential scans.\u003c/p\u003e\u003cp\u003eWhile \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e is running, the \u003ca href=\"/docs/18/runtime-config-client.html#GUC-SEARCH-PATH\"\u003esearch_path\u003c/a\u003e is temporarily changed to \u003ccode class=\"literal\"\u003epg_catalog, pg_temp\u003c/code\u003e.\u003c/p\u003e\u003cp\u003eReindexing a single index or table requires having the \u003ccode class=\"literal\"\u003eMAINTAIN\u003c/code\u003e privilege on the table. Note that while \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e on a partitioned index or table requires having the \u003ccode class=\"literal\"\u003eMAINTAIN\u003c/code\u003e privilege on the partitioned table, such commands skip the privilege checks when processing the individual partitions. Reindexing a schema or database requires being the owner of that schema or database or having privileges of the \u003ca href=\"/docs/18/predefined-roles.html#PREDEFINED-ROLE-PG-MAINTAIN\"\u003epg_maintain\u003c/a\u003e role. Note specifically that it's thus possible for non-superusers to rebuild indexes of tables owned by other users. However, as a special exception, \u003ccode class=\"command\"\u003eREINDEX DATABASE\u003c/code\u003e, \u003ccode class=\"command\"\u003eREINDEX SCHEMA\u003c/code\u003e, and \u003ccode class=\"command\"\u003eREINDEX SYSTEM\u003c/code\u003e will skip indexes on shared catalogs unless the user has the \u003ccode class=\"literal\"\u003eMAINTAIN\u003c/code\u003e privilege on the catalog.\u003c/p\u003e\u003cp\u003eReindexing partitioned indexes or partitioned tables is supported with \u003ccode class=\"command\"\u003eREINDEX INDEX\u003c/code\u003e or \u003ccode class=\"command\"\u003eREINDEX TABLE\u003c/code\u003e, respectively. Each partition of the specified partitioned relation is reindexed in a separate transaction. Those commands cannot be used inside a transaction block when working on a partitioned table or index.\u003c/p\u003e\u003cp\u003eWhen using the \u003ccode class=\"literal\"\u003eTABLESPACE\u003c/code\u003e clause with \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e on a partitioned index or table, only the tablespace references of the leaf partitions are updated. As partitioned indexes are not updated, it is recommended to separately use \u003ccode class=\"command\"\u003eALTER TABLE ONLY\u003c/code\u003e on them so as any new partitions attached inherit the new tablespace. On failure, it may not have moved all the indexes to the new tablespace. Re-running the command will rebuild all the leaf partitions and move previously-unprocessed indexes to the new tablespace.\u003c/p\u003e\u003cp\u003eIf \u003ccode class=\"literal\"\u003eSCHEMA\u003c/code\u003e, \u003ccode class=\"literal\"\u003eDATABASE\u003c/code\u003e or \u003ccode class=\"literal\"\u003eSYSTEM\u003c/code\u003e is used with \u003ccode class=\"literal\"\u003eTABLESPACE\u003c/code\u003e, system relations are skipped and a single \u003ccode class=\"literal\"\u003eWARNING\u003c/code\u003e will be generated. Indexes on TOAST tables are rebuilt, but not moved to the new tablespace.\u003c/p\u003e\u003cdiv class=\"refsect2\"\u003e\u003ch3\u003eRebuilding Indexes Concurrently\u003c/h3\u003e\u003cp\u003eRebuilding an index can interfere with regular operation of a database. Normally \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e locks the table whose index is rebuilt against writes and performs the entire index build with a single scan of the table. Other transactions can still read the table, but if they try to insert, update, or delete rows in the table they will block until the index rebuild is finished. This could have a severe effect if the system is a live production database. Very large tables can take many hours to be indexed, and even for smaller tables, an index rebuild can lock out writers for periods that are unacceptably long for a production system.\u003c/p\u003e\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e supports rebuilding indexes with minimum locking of writes. This method is invoked by specifying the \u003ccode class=\"literal\"\u003eCONCURRENTLY\u003c/code\u003e option of \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e. When this option is used, \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e must perform two scans of the table for each index that needs to be rebuilt and wait for termination of all existing transactions that could potentially use the index. This method requires more total work than a standard index rebuild and takes significantly longer to complete as it needs to wait for unfinished transactions that might modify the index. However, since it allows normal operations to continue while the index is being rebuilt, this method is useful for rebuilding indexes in a production environment. Of course, the extra CPU, memory and I/O load imposed by the index rebuild may slow down other operations.\u003c/p\u003e\u003cp\u003eThe following steps occur in a concurrent reindex. Each step is run in a separate transaction. If there are multiple indexes to be rebuilt, then each step loops through all the indexes before moving to the next step.\u003c/p\u003e\u003cdiv class=\"orderedlist\"\u003e\u003col class=\"orderedlist\"\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eA new transient index definition is added to the catalog \u003ccode class=\"literal\"\u003epg_index\u003c/code\u003e. This definition will be used to replace the old index. A \u003ccode class=\"literal\"\u003eSHARE UPDATE EXCLUSIVE\u003c/code\u003e lock at session level is taken on the indexes being reindexed as well as their associated tables to prevent any schema modification while processing.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eA first pass to build the index is done for each new index. Once the index is built, its flag \u003ccode class=\"literal\"\u003epg_index.indisready\u003c/code\u003e is switched to \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003etrue\u003c/span\u003e”\u003c/span\u003e to make it ready for inserts, making it visible to other sessions once the transaction that performed the build is finished. This step is done in a separate transaction for each index.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eThen a second pass is performed to add tuples that were added while the first pass was running. This step is also done in a separate transaction for each index.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eAll the constraints that refer to the index are changed to refer to the new index definition, and the names of the indexes are changed. At this point, \u003ccode class=\"literal\"\u003epg_index.indisvalid\u003c/code\u003e is switched to \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003etrue\u003c/span\u003e”\u003c/span\u003e for the new index and to \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003efalse\u003c/span\u003e”\u003c/span\u003e for the old, and a cache invalidation is done causing all sessions that referenced the old index to be invalidated.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eThe old indexes have \u003ccode class=\"literal\"\u003epg_index.indisready\u003c/code\u003e switched to \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003efalse\u003c/span\u003e”\u003c/span\u003e to prevent any new tuple insertions, after waiting for running queries that might reference the old index to complete.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eThe old indexes are dropped. The \u003ccode class=\"literal\"\u003eSHARE UPDATE EXCLUSIVE\u003c/code\u003e session locks for the indexes and the table are released.\u003c/p\u003e\u003c/li\u003e\u003c/ol\u003e\u003c/div\u003e\u003cp\u003eIf a problem arises while rebuilding the indexes, such as a uniqueness violation in a unique index, the \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e command will fail but leave behind an \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003einvalid\u003c/span\u003e”\u003c/span\u003e new index in addition to the pre-existing one. This index will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead. The \u003cspan class=\"application\"\u003epsql\u003c/span\u003e \u003ccode class=\"command\"\u003e\\d\u003c/code\u003e command will report such an index as \u003ccode class=\"literal\"\u003eINVALID\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003epostgres=# \\d tab\n       Table \"public.tab\"\n Column |  Type   | Modifiers\n--------+---------+-----------\n col    | integer |\nIndexes:\n    \"idx\" btree (col)\n    \"idx_ccnew\" btree (col) INVALID\n\u003c/pre\u003e\u003cp\u003eIf the index marked \u003ccode class=\"literal\"\u003eINVALID\u003c/code\u003e is suffixed \u003ccode class=\"literal\"\u003e_ccnew\u003c/code\u003e, then it corresponds to the transient index created during the concurrent operation, and the recommended recovery method is to drop it using \u003ccode class=\"literal\"\u003eDROP INDEX\u003c/code\u003e, then attempt \u003ccode class=\"command\"\u003eREINDEX CONCURRENTLY\u003c/code\u003e again. If the invalid index is instead suffixed \u003ccode class=\"literal\"\u003e_ccold\u003c/code\u003e, it corresponds to the original index which could not be dropped; the recommended recovery method is to just drop said index, since the rebuild proper has been successful. A nonzero number may be appended to the suffix of the invalid index names to keep them unique, like \u003ccode class=\"literal\"\u003e_ccnew1\u003c/code\u003e, \u003ccode class=\"literal\"\u003e_ccold2\u003c/code\u003e, etc.\u003c/p\u003e\u003cp\u003eRegular index builds permit other regular index builds on the same table to occur simultaneously, but only one concurrent index build can occur on a table at a time. In both cases, no other types of schema modification on the table are allowed meanwhile. Another difference is that a regular \u003ccode class=\"command\"\u003eREINDEX TABLE\u003c/code\u003e or \u003ccode class=\"command\"\u003eREINDEX INDEX\u003c/code\u003e command can be performed within a transaction block, but \u003ccode class=\"command\"\u003eREINDEX CONCURRENTLY\u003c/code\u003e cannot.\u003c/p\u003e\u003cp\u003eLike any long-running transaction, \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e on a table can affect which tuples can be removed by concurrent \u003ccode class=\"command\"\u003eVACUUM\u003c/code\u003e on any other table.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eREINDEX SYSTEM\u003c/code\u003e does not support \u003ccode class=\"command\"\u003eCONCURRENTLY\u003c/code\u003e since system catalogs cannot be reindexed concurrently.\u003c/p\u003e\u003cp\u003eFurthermore, indexes for exclusion constraints cannot be reindexed concurrently. If such an index is named directly in this command, an error is raised. If a table or database with exclusion constraint indexes is reindexed concurrently, those indexes will be skipped. (It is possible to reindex such indexes without the \u003ccode class=\"command\"\u003eCONCURRENTLY\u003c/code\u003e option.)\u003c/p\u003e\u003cp\u003eEach backend running \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e will report its progress in the \u003ccode class=\"structname\"\u003epg_stat_progress_create_index\u003c/code\u003e view. See \u003ca href=\"/docs/18/progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING\" title=\"27.4.4. CREATE INDEX Progress Reporting\"\u003eSection 27.4.4\u003c/a\u003e for details.\u003c/p\u003e\u003c/div\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eRebuild a single index:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eREINDEX INDEX my_index;\n\u003c/pre\u003e\u003cp\u003eRebuild all the indexes on the table \u003ccode class=\"literal\"\u003emy_table\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eREINDEX TABLE my_table;\n\u003c/pre\u003e\u003cp\u003eRebuild all indexes in a particular database, without trusting the system indexes to be valid already:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e$ \u003cstrong\u003e\u003ccode\u003eexport PGOPTIONS=\"-P\"\u003c/code\u003e\u003c/strong\u003e\n$ \u003cstrong\u003e\u003ccode\u003epsql broken_db\u003c/code\u003e\u003c/strong\u003e\n...\nbroken_db=\u0026gt; REINDEX DATABASE broken_db;\nbroken_db=\u0026gt; \\q\n\u003c/pre\u003e\u003cp\u003eRebuild indexes for a table, without blocking read and write operations on involved relations while reindexing is in progress:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eREINDEX TABLE CONCURRENTLY my_broken_table;\n\u003c/pre\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003eThere is no \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e command in the SQL standard.\u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/create-index/?v=18\" title=\"CREATE INDEX\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE INDEX\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-index/?v=18\" title=\"DROP INDEX\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP INDEX\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/app-reindexdb.html\" title=\"reindexdb\"\u003e\u003cspan class=\"refentrytitle\"\u003e\u003cspan class=\"application\"\u003ereindexdb\u003c/span\u003e\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING\" title=\"27.4.4. CREATE INDEX Progress Reporting\"\u003eSection 27.4.4\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"REINDEX [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e [, ...] ) ] { INDEX | TABLE | SCHEMA } [ CONCURRENTLY ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\nREINDEX [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e [, ...] ) ] { DATABASE | SYSTEM } [ CONCURRENTLY ] [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e ]\n\n\u003cspan class=\"phrase\"\u003ewhere \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e can be one of:\u003c/span\u003e\n\n    CONCURRENTLY [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    TABLESPACE \u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_tablespace\u003c/code\u003e\u003c/em\u003e\n    VERBOSE [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]","synopsis_text":"REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA } [ CONCURRENTLY ] name\nREINDEX [ ( option [, ...] ) ] { DATABASE | SYSTEM } [ CONCURRENTLY ] [ name ]\n\nwhere option can be one of:\n\nCONCURRENTLY [ boolean ]\nTABLESPACE new_tablespace\nVERBOSE [ boolean ]"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"reindex","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"REINDEX","Summary":"重建索引","BodyHTML":"\u003cpre\u003eREINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA } [ CONCURRENTLY ] name\nREINDEX [ ( option [, ...] ) ] { DATABASE | SYSTEM } [ CONCURRENTLY ] [ name ]\n\n其中 option 可以是以下之一：\n\nCONCURRENTLY [ boolean ]\nTABLESPACE new_tablespace\nVERBOSE [ boolean ]\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eREINDEX\u003c/code\u003e使用索引所属表中存储的数据重建索引，并替换索引的旧副本。以下几种场景适合使用\u003ccode\u003eREINDEX\u003c/code\u003e：\u003c/p\u003e\u003cdiv\u003e\u003cul\u003e\u003cli\u003e\u003cp\u003e一个索引已经损坏，不再包含有效数据。尽管理论上这不该发生，但在实践中索引可能因软件缺陷或硬件故障而损坏。\u003ccode\u003eREINDEX\u003c/code\u003e提供了一种恢复方法。\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e一个索引已经\u003cspan\u003e“\u003cspan\u003e膨胀\u003c/span\u003e”\u003c/span\u003e，也就是说其中包含许多空页或几乎为空的页。在 \u003cspan\u003ePostgreSQL\u003c/span\u003e 中，B-树索引在某些不常见的访问模式下可能出现这种情况。\u003ccode\u003eREINDEX\u003c/code\u003e可通过写入一个不含死页的新版本索引来减少索引的空间消耗。详见\u003ca href=\"/docs/18/routine-reindex.html\" rel=\"nofollow\"\u003e第 24.2 节\u003c/a\u003e。\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e你修改了某个索引的存储参数（例如 fillfactor），并希望确保该更改已经完全生效。\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e如果使用\u003ccode\u003eCONCURRENTLY\u003c/code\u003e选项构建索引失败，该索引会被保留为\u003cspan\u003e“\u003cspan\u003e无效\u003c/span\u003e”\u003c/span\u003e。这类索引没有用处，但用\u003ccode\u003eREINDEX\u003c/code\u003e 重建它们可能很方便。注意，只有\u003ccode\u003eREINDEX INDEX\u003c/code\u003e才能在无效索引上执行并发构建。\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eINDEX\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e重新创建指定的索引。如果用于分区索引，则这种形式的 \u003ccode\u003eREINDEX\u003c/code\u003e不能在事务块内执行。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eTABLE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e重新创建指定表的所有索引。如果该表有一个辅助\u003cspan\u003e“\u003cspan\u003eTOAST\u003c/span\u003e”\u003c/span\u003e表，也会对其重新索引。如果用于分区表，则这种形式的 \u003ccode\u003eREINDEX\u003c/code\u003e不能在事务块内执行。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eSCHEMA\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e重新创建指定模式中的所有索引。如果该模式中的某个表有一个辅助\u003cspan\u003e“\u003cspan\u003eTOAST\u003c/span\u003e”\u003c/span\u003e表，也会对其重新索引。共享系统目录上的索引也会被处理。这种形式的\u003ccode\u003eREINDEX\u003c/code\u003e不能在事务块内执行。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eDATABASE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e重新创建当前数据库中除系统目录外的所有索引。系统目录上的索引不会被处理。这种形式的\u003ccode\u003eREINDEX\u003c/code\u003e不能在事务块内执行。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eSYSTEM\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e重新创建当前数据库内系统目录上的所有索引。共享系统目录上的索引也包含在内。用户表上的索引不会被处理。这种形式的 \u003ccode\u003eREINDEX\u003c/code\u003e不能在事务块内执行。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要重新索引的特定索引、表或数据库的名称。索引名和表名可以带模式限定。目前，\u003ccode\u003eREINDEX DATABASE\u003c/code\u003e和 \u003ccode\u003eREINDEX SYSTEM\u003c/code\u003e只能对当前数据库重新索引。它们的参数是可选的，但如果给出，就必须与当前数据库名匹配。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eCONCURRENTLY\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e使用此选项时，\u003cspan\u003ePostgreSQL\u003c/span\u003e会在不获取任何会阻止表上并发插入、更新或删除的锁的情况下重建索引；而标准索引重建会阻止表上的写入（但不阻止读取），直到完成。使用此选项时有若干注意事项，见下文\u003ca href=\"/docs/18/sql-reindex.html#SQL-REINDEX-CONCURRENTLY\" title=\"并发重建索引\" rel=\"nofollow\"\u003eRebuilding Indexes Concurrently\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e对于临时表，\u003ccode\u003eREINDEX\u003c/code\u003e始终以非并发方式执行，因为没有其他会话可以访问它们，而且非并发重新索引开销更小。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eTABLESPACE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定索引将在新的表空间中重建。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eVERBOSE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在每个索引被重建时，以 \u003ccode\u003eINFO\u003c/code\u003e 级别打印进度报告。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定所选选项是否开启。可以写\u003ccode\u003eTRUE\u003c/code\u003e、\u003ccode\u003eON\u003c/code\u003e或\u003ccode\u003e1\u003c/code\u003e来启用选项，写\u003ccode\u003eFALSE\u003c/code\u003e、\u003ccode\u003eOFF\u003c/code\u003e或\u003ccode\u003e0\u003c/code\u003e来禁用它。也可以省略\u003cem\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e值，此时假定为\u003ccode\u003eTRUE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003enew_tablespace\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e用于重建索引的表空间。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e如果怀疑某个用户表上的索引已经损坏，可以使用 \u003ccode\u003eREINDEX INDEX\u003c/code\u003e或\u003ccode\u003eREINDEX TABLE\u003c/code\u003e 直接重建该索引，或者重建该表上的所有索引。\u003c/p\u003e\u003cp\u003e如果需要从系统表上的索引损坏中恢复，情况就更复杂了。在这种情况下，重要的是系统本身没有使用任何可疑索引。（事实上，在这种场景下，你可能会发现服务器进程在启动时立即崩溃，因为它依赖损坏的索引。）要安全恢复，必须用\u003ccode\u003e-P\u003c/code\u003e选项启动服务器，该选项会阻止服务器在查找系统目录时使用索引。\u003c/p\u003e\u003cp\u003e一种做法是关闭服务器，并在命令行中包含\u003ccode\u003e-P\u003c/code\u003e选项来启动单用户 \u003cspan\u003ePostgreSQL\u003c/span\u003e 服务器。然后可以根据希望重建的范围，执行\u003ccode\u003eREINDEX DATABASE\u003c/code\u003e、\u003ccode\u003eREINDEX SYSTEM\u003c/code\u003e、\u003ccode\u003eREINDEX TABLE\u003c/code\u003e 或\u003ccode\u003eREINDEX INDEX\u003c/code\u003e。如果拿不准，就使用 \u003ccode\u003eREINDEX SYSTEM\u003c/code\u003e来重建该数据库中的所有系统索引。然后退出单用户服务器会话并重新启动常规服务器。关于如何与单用户服务器接口交互的更多信息，参见\u003ca href=\"/docs/18/app-postgres.html\" title=\"postgres\" rel=\"nofollow\"\u003e\u003cspan\u003e\u003cspan\u003epostgres\u003c/span\u003e\u003c/span\u003e\u003c/a\u003e参考页。\u003c/p\u003e\u003cp\u003e另一种方法是启动一个常规服务器会话，并在其命令行选项中包含 \u003ccode\u003e-P\u003c/code\u003e。具体做法因客户端而异，但对于所有基于 \u003cspan\u003elibpq\u003c/span\u003e的客户端，都可以在启动客户端之前将环境变量\u003ccode\u003ePGOPTIONS\u003c/code\u003e设置为\u003ccode\u003e-P\u003c/code\u003e。注意，尽管这种方法不需要阻止其他客户端，但在修复完成之前，阻止其他用户连接到受损数据库可能仍然更稳妥。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eREINDEX\u003c/code\u003e类似于删除并重新创建索引，因为索引内容都是从头重建的。不过，两者在锁方面的考量相当不同。\u003ccode\u003eREINDEX\u003c/code\u003e会阻止该索引所属表上的写入，但不阻止读取。它还会对正在处理的特定索引获取\u003ccode\u003eACCESS EXCLUSIVE\u003c/code\u003e锁，从而阻塞试图使用该索引的读取。尤其是，无论查询内容如何，查询规划器都会尝试对该表的每个索引获取\u003ccode\u003eACCESS SHARE\u003c/code\u003e锁，因此 \u003ccode\u003eREINDEX\u003c/code\u003e实际上会阻塞几乎所有查询，只有那些计划已缓存且不使用这个索引的某些预备查询除外。相比之下，\u003ccode\u003eDROP INDEX\u003c/code\u003e会短暂地对父表获取 \u003ccode\u003eACCESS EXCLUSIVE\u003c/code\u003e锁，同时阻塞写入和读取。随后的 \u003ccode\u003eCREATE INDEX\u003c/code\u003e会阻止写入但不阻止读取；由于索引不存在，读取不会尝试使用它，因此不会发生阻塞，但读取可能被迫使用代价高昂的顺序扫描。\u003c/p\u003e\u003cp\u003e在\u003ccode\u003eREINDEX\u003c/code\u003e运行期间，\u003ca href=\"/docs/18/runtime-config-client.html#GUC-SEARCH-PATH\" rel=\"nofollow\"\u003esearch_path\u003c/a\u003e会临时改为\u003ccode\u003epg_catalog, pg_temp\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e对单个索引或表重新索引，需要在相应表上具有 \u003ccode\u003eMAINTAIN\u003c/code\u003e 权限。注意，对分区索引或分区表执行\u003ccode\u003eREINDEX\u003c/code\u003e时，需要在该分区表上具有 \u003ccode\u003eMAINTAIN\u003c/code\u003e 权限，但在处理各个分区时会跳过权限检查。对模式或数据库重新索引，则需要是该模式或数据库的拥有者，或者具有\u003ca href=\"/docs/18/predefined-roles.html#PREDEFINED-ROLE-PG-MAINTAIN\" rel=\"nofollow\"\u003epg_maintain\u003c/a\u003e角色的权限。特别要注意，这意味着非超级用户也可能重建其他用户拥有的表的索引。不过，作为一个特殊例外，\u003ccode\u003eREINDEX DATABASE\u003c/code\u003e、\u003ccode\u003eREINDEX SCHEMA\u003c/code\u003e和\u003ccode\u003eREINDEX SYSTEM\u003c/code\u003e 会跳过共享系统目录上的索引，除非用户对该目录具有 \u003ccode\u003eMAINTAIN\u003c/code\u003e权限。\u003c/p\u003e\u003cp\u003e分别可使用\u003ccode\u003eREINDEX INDEX\u003c/code\u003e和 \u003ccode\u003eREINDEX TABLE\u003c/code\u003e对分区索引和分区表重新索引。指定分区关系的每个分区都会在单独的事务中重新索引。在处理分区表或分区索引时，这些命令不能在事务块内使用。\u003c/p\u003e\u003cp\u003e当对分区索引或分区表执行带\u003ccode\u003eTABLESPACE\u003c/code\u003e子句的 \u003ccode\u003eREINDEX\u003c/code\u003e时，只有叶分区的表空间引用会被更新。由于分区索引本身不会更新，建议另外对这些分区索引单独执行 \u003ccode\u003eALTER TABLE ONLY\u003c/code\u003e，以便后续附加的任何新分区都继承新表空间。如果命令失败，可能不会把所有索引都移动到新表空间。重新运行该命令将重建所有叶分区，并把先前未处理的索引移动到新表空间。\u003c/p\u003e\u003cp\u003e如果\u003ccode\u003eSCHEMA\u003c/code\u003e、\u003ccode\u003eDATABASE\u003c/code\u003e或 \u003ccode\u003eSYSTEM\u003c/code\u003e与\u003ccode\u003eTABLESPACE\u003c/code\u003e一起使用，则系统关系会被跳过，并生成一条\u003ccode\u003eWARNING\u003c/code\u003e。TOAST 表上的索引会重建，但不会移动到新的表空间。\u003c/p\u003e\u003cdiv\u003e\u003ch3\u003e并发重建索引\u003c/h3\u003e\u003cp\u003e重建索引可能会干扰数据库的正常运行。通常 \u003cspan\u003ePostgreSQL\u003c/span\u003e会锁住要重建其索引的表，阻止其写入，并通过一次扫描完成整个索引构建。其他事务仍可读取该表，但如果它们试图在表中插入、更新或删除行，就会阻塞直到索引重建完成。如果系统是在线生产数据库，这可能产生严重影响。对非常大的表重新索引可能需要很多小时，即便是较小的表，索引重建也可能在一段对生产系统而言不可接受的时间内阻止写入操作。\u003c/p\u003e\u003cp\u003e\u003cspan\u003ePostgreSQL\u003c/span\u003e支持在尽量少阻塞写入的情况下重建索引。这种方法是通过指定\u003ccode\u003eREINDEX\u003c/code\u003e的 \u003ccode\u003eCONCURRENTLY\u003c/code\u003e选项来调用的。使用该选项时，\u003cspan\u003ePostgreSQL\u003c/span\u003e必须为每个需要重建的索引对表执行两次扫描，并等待所有现有、可能使用该索引的事务结束。因此，这种方法比标准索引重建需要更多的总工作量，完成时间也明显更长，因为它必须等待那些可能修改索引的未完成事务。不过，由于它允许在索引重建期间继续进行正常操作，所以这种方法适合在生产环境中重建索引。当然，索引重建带来的额外 CPU、内存和 I/O 负载也可能拖慢其他操作。\u003c/p\u003e\u003cp\u003e并发重新索引会依次经历以下步骤。每个步骤都在单独的事务中运行。如果有多个要重建的索引，则每个步骤都会先遍历所有索引，再进入下一步。\u003c/p\u003e\u003cdiv\u003e\u003col\u003e\u003cli\u003e\u003cp\u003e先向系统目录\u003ccode\u003epg_index\u003c/code\u003e添加一个新的临时索引定义。该定义将用于替换旧索引。随后会在要重新索引的索引及其关联表上获取会话级\u003ccode\u003eSHARE UPDATE EXCLUSIVE\u003c/code\u003e锁，以防止处理期间发生任何模式修改。\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e然后为每个新索引执行第一次构建。索引构建完成后，会将其标志 \u003ccode\u003epg_index.indisready\u003c/code\u003e切换为\u003cspan\u003e“\u003cspan\u003etrue\u003c/span\u003e”\u003c/span\u003e，使其准备好接收插入；执行构建的事务结束后，它就会对其他会话可见。此步骤对每个索引都在单独的事务中完成。\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e接着执行第二次扫描，把第一次扫描期间新增的元组加入索引。此步骤同样对每个索引都在单独的事务中完成。\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e所有引用该索引的约束都会改为引用新的索引定义，同时索引名称也会被更改。此时，新索引的\u003ccode\u003epg_index.indisvalid\u003c/code\u003e会切换为\u003cspan\u003e“\u003cspan\u003etrue\u003c/span\u003e”\u003c/span\u003e，旧索引则切换为\u003cspan\u003e“\u003cspan\u003efalse\u003c/span\u003e”\u003c/span\u003e，并执行一次缓存失效，使所有引用旧索引的会话中的相关缓存项失效。\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e在等待可能引用旧索引的运行中查询完成之后，旧索引的 \u003ccode\u003epg_index.indisready\u003c/code\u003e会切换为\u003cspan\u003e“\u003cspan\u003efalse\u003c/span\u003e”\u003c/span\u003e，以防止任何新元组被插入其中。\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e删除旧索引。随后会释放为索引和表持有的 \u003ccode\u003eSHARE UPDATE EXCLUSIVE\u003c/code\u003e会话锁。\u003c/p\u003e\u003c/li\u003e\u003c/ol\u003e\u003c/div\u003e\u003cp\u003e如果在重建索引时出现问题，例如唯一索引上发生唯一性违反，\u003ccode\u003eREINDEX\u003c/code\u003e命令会失败，但除了原有索引外，还会留下一个\u003cspan\u003e“\u003cspan\u003e无效\u003c/span\u003e”\u003c/span\u003e的新索引。这个索引在查询时会被忽略，因为它可能不完整；不过它仍会带来更新开销。\u003cspan\u003epsql\u003c/span\u003e的 \u003ccode\u003e\\d\u003c/code\u003e命令会把这样的索引报告为 \u003ccode\u003eINVALID\u003c/code\u003e：\u003c/p\u003e\u003cpre\u003epostgres=# \\d tab\n       Table \u0026#34;public.tab\u0026#34;\n Column |  Type   | Modifiers\n--------+---------+-----------\n col    | integer |\nIndexes:\n    \u0026#34;idx\u0026#34; btree (col)\n    \u0026#34;idx_ccnew\u0026#34; btree (col) INVALID\n\u003c/pre\u003e\u003cp\u003e如果被标记为\u003ccode\u003eINVALID\u003c/code\u003e的索引带有 \u003ccode\u003e_ccnew\u003c/code\u003e后缀，那么它对应于并发操作期间创建的临时索引，推荐的恢复方法是使用\u003ccode\u003eDROP INDEX\u003c/code\u003e将其删除，然后再次尝试\u003ccode\u003eREINDEX CONCURRENTLY\u003c/code\u003e。如果无效索引带有 \u003ccode\u003e_ccold\u003c/code\u003e后缀，则它对应于未能删除的原始索引；由于重建本身实际上已经成功，推荐的恢复方法就是直接删除该索引。为了保持名称唯一，无效索引名的后缀后还可能附加非零数字，例如 \u003ccode\u003e_ccnew1\u003c/code\u003e、\u003ccode\u003e_ccold2\u003c/code\u003e等。\u003c/p\u003e\u003cp\u003e常规索引构建允许同一张表上的其他常规索引构建同时进行，但一张表在同一时间只能有一个并发索引构建。在这两种情况下，同时都不允许对该表进行其他类型的模式修改。另一个区别是，常规的 \u003ccode\u003eREINDEX TABLE\u003c/code\u003e或\u003ccode\u003eREINDEX INDEX\u003c/code\u003e 命令可以在事务块内执行，而\u003ccode\u003eREINDEX CONCURRENTLY\u003c/code\u003e不行。\u003c/p\u003e\u003cp\u003e与任何长时间运行的事务一样，对表执行\u003ccode\u003eREINDEX\u003c/code\u003e可能会影响并发\u003ccode\u003eVACUUM\u003c/code\u003e在其他表上能够移除哪些元组。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eREINDEX SYSTEM\u003c/code\u003e不支持 \u003ccode\u003eCONCURRENTLY\u003c/code\u003e，因为系统目录不能并发重新索引。\u003c/p\u003e\u003cp\u003e此外，用于排他约束的索引不能并发重新索引。如果在此命令中直接指定这样的索引，就会报错。如果并发地对包含排他约束索引的表或数据库重新索引，这些索引将被跳过。（不使用\u003ccode\u003eCONCURRENTLY\u003c/code\u003e选项时，可以重新索引这类索引。）\u003c/p\u003e\u003cp\u003e每个运行\u003ccode\u003eREINDEX\u003c/code\u003e的后端都会在 \u003ccode\u003epg_stat_progress_create_index\u003c/code\u003e视图中报告其进度。详见\u003ca href=\"/docs/18/progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING\" rel=\"nofollow\"\u003e第 27.4.4 节\u003c/a\u003e。\u003c/p\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e重建单个索引：\u003c/p\u003e\u003cpre\u003eREINDEX INDEX my_index;\n\u003c/pre\u003e\u003cp\u003e重建表\u003ccode\u003emy_table\u003c/code\u003e上的所有索引：\u003c/p\u003e\u003cpre\u003eREINDEX TABLE my_table;\n\u003c/pre\u003e\u003cp\u003e在不假定系统索引已经有效的情况下，重建某个数据库中的所有索引：\u003c/p\u003e\u003cpre\u003e$ \u003cstrong\u003e\u003ccode\u003eexport PGOPTIONS=\u0026#34;-P\u0026#34;\u003c/code\u003e\u003c/strong\u003e\n$ \u003cstrong\u003e\u003ccode\u003epsql broken_db\u003c/code\u003e\u003c/strong\u003e\n...\nbroken_db=\u0026gt; REINDEX DATABASE broken_db;\nbroken_db=\u0026gt; \\q\n\u003c/pre\u003e\u003cp\u003e重建某个表的索引，并在重建期间不阻塞对相关关系的读写操作：\u003c/p\u003e\u003cpre\u003eREINDEX TABLE CONCURRENTLY my_broken_table;\n\u003c/pre\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003e在 SQL 标准中没有\u003ccode\u003eREINDEX\u003c/code\u003e命令。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/create-index/?v=18\" title=\"CREATE INDEX\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE INDEX\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-index/?v=18\" title=\"DROP INDEX\" rel=\"nofollow\"\u003e\u003cspan\u003eDROP INDEX\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/app-reindexdb.html\" title=\"reindexdb\" rel=\"nofollow\"\u003e\u003cspan\u003e\u003cspan\u003ereindexdb\u003c/span\u003e\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING\" rel=\"nofollow\"\u003e第 27.4.4 节\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"e1dfa11988c89dfe30301eb32c2affd745dd38b9efa9e3f668434c978f26797b","Payload":{"purpose_zh":"重建索引","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e使用索引所属表中存储的数据重建索引，并替换索引的旧副本。以下几种场景适合使用\u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e：\u003c/p\u003e\u003cdiv class=\"itemizedlist\"\u003e\u003cul class=\"itemizedlist\"\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e一个索引已经损坏，不再包含有效数据。尽管理论上这不该发生，但在实践中索引可能因软件缺陷或硬件故障而损坏。\u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e提供了一种恢复方法。\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e一个索引已经\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e膨胀\u003c/span\u003e”\u003c/span\u003e，也就是说其中包含许多空页或几乎为空的页。在 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 中，B-树索引在某些不常见的访问模式下可能出现这种情况。\u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e可通过写入一个不含死页的新版本索引来减少索引的空间消耗。详见\u003ca href=\"/docs/18/routine-reindex.html\" title=\"24.2. 日常重建索引\"\u003e第 24.2 节\u003c/a\u003e。\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e你修改了某个索引的存储参数（例如 fillfactor），并希望确保该更改已经完全生效。\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e如果使用\u003ccode class=\"literal\"\u003eCONCURRENTLY\u003c/code\u003e选项构建索引失败，该索引会被保留为\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e无效\u003c/span\u003e”\u003c/span\u003e。这类索引没有用处，但用\u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e 重建它们可能很方便。注意，只有\u003ccode class=\"command\"\u003eREINDEX INDEX\u003c/code\u003e才能在无效索引上执行并发构建。\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eINDEX\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e重新创建指定的索引。如果用于分区索引，则这种形式的 \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e不能在事务块内执行。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTABLE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e重新创建指定表的所有索引。如果该表有一个辅助\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eTOAST\u003c/span\u003e”\u003c/span\u003e表，也会对其重新索引。如果用于分区表，则这种形式的 \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e不能在事务块内执行。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSCHEMA\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e重新创建指定模式中的所有索引。如果该模式中的某个表有一个辅助\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eTOAST\u003c/span\u003e”\u003c/span\u003e表，也会对其重新索引。共享系统目录上的索引也会被处理。这种形式的\u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e不能在事务块内执行。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDATABASE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e重新创建当前数据库中除系统目录外的所有索引。系统目录上的索引不会被处理。这种形式的\u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e不能在事务块内执行。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSYSTEM\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e重新创建当前数据库内系统目录上的所有索引。共享系统目录上的索引也包含在内。用户表上的索引不会被处理。这种形式的 \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e不能在事务块内执行。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要重新索引的特定索引、表或数据库的名称。索引名和表名可以带模式限定。目前，\u003ccode class=\"command\"\u003eREINDEX DATABASE\u003c/code\u003e和 \u003ccode class=\"command\"\u003eREINDEX SYSTEM\u003c/code\u003e只能对当前数据库重新索引。它们的参数是可选的，但如果给出，就必须与当前数据库名匹配。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eCONCURRENTLY\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e使用此选项时，\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e会在不获取任何会阻止表上并发插入、更新或删除的锁的情况下重建索引；而标准索引重建会阻止表上的写入（但不阻止读取），直到完成。使用此选项时有若干注意事项，见下文\u003ca href=\"/docs/18/sql-reindex.html#SQL-REINDEX-CONCURRENTLY\" title=\"并发重建索引\"\u003eRebuilding Indexes Concurrently\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e对于临时表，\u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e始终以非并发方式执行，因为没有其他会话可以访问它们，而且非并发重新索引开销更小。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTABLESPACE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定索引将在新的表空间中重建。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eVERBOSE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在每个索引被重建时，以 \u003ccode class=\"literal\"\u003eINFO\u003c/code\u003e 级别打印进度报告。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定所选选项是否开启。可以写\u003ccode class=\"literal\"\u003eTRUE\u003c/code\u003e、\u003ccode class=\"literal\"\u003eON\u003c/code\u003e或\u003ccode class=\"literal\"\u003e1\u003c/code\u003e来启用选项，写\u003ccode class=\"literal\"\u003eFALSE\u003c/code\u003e、\u003ccode class=\"literal\"\u003eOFF\u003c/code\u003e或\u003ccode class=\"literal\"\u003e0\u003c/code\u003e来禁用它。也可以省略\u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e值，此时假定为\u003ccode class=\"literal\"\u003eTRUE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_tablespace\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e用于重建索引的表空间。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e如果怀疑某个用户表上的索引已经损坏，可以使用 \u003ccode class=\"command\"\u003eREINDEX INDEX\u003c/code\u003e或\u003ccode class=\"command\"\u003eREINDEX TABLE\u003c/code\u003e 直接重建该索引，或者重建该表上的所有索引。\u003c/p\u003e\u003cp\u003e如果需要从系统表上的索引损坏中恢复，情况就更复杂了。在这种情况下，重要的是系统本身没有使用任何可疑索引。（事实上，在这种场景下，你可能会发现服务器进程在启动时立即崩溃，因为它依赖损坏的索引。）要安全恢复，必须用\u003ccode class=\"option\"\u003e-P\u003c/code\u003e选项启动服务器，该选项会阻止服务器在查找系统目录时使用索引。\u003c/p\u003e\u003cp\u003e一种做法是关闭服务器，并在命令行中包含\u003ccode class=\"option\"\u003e-P\u003c/code\u003e选项来启动单用户 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 服务器。然后可以根据希望重建的范围，执行\u003ccode class=\"command\"\u003eREINDEX DATABASE\u003c/code\u003e、\u003ccode class=\"command\"\u003eREINDEX SYSTEM\u003c/code\u003e、\u003ccode class=\"command\"\u003eREINDEX TABLE\u003c/code\u003e 或\u003ccode class=\"command\"\u003eREINDEX INDEX\u003c/code\u003e。如果拿不准，就使用 \u003ccode class=\"command\"\u003eREINDEX SYSTEM\u003c/code\u003e来重建该数据库中的所有系统索引。然后退出单用户服务器会话并重新启动常规服务器。关于如何与单用户服务器接口交互的更多信息，参见\u003ca href=\"/docs/18/app-postgres.html\" title=\"postgres\"\u003e\u003cspan class=\"refentrytitle\"\u003e\u003cspan class=\"application\"\u003epostgres\u003c/span\u003e\u003c/span\u003e\u003c/a\u003e参考页。\u003c/p\u003e\u003cp\u003e另一种方法是启动一个常规服务器会话，并在其命令行选项中包含 \u003ccode class=\"option\"\u003e-P\u003c/code\u003e。具体做法因客户端而异，但对于所有基于 \u003cspan class=\"application\"\u003elibpq\u003c/span\u003e的客户端，都可以在启动客户端之前将环境变量\u003ccode class=\"envar\"\u003ePGOPTIONS\u003c/code\u003e设置为\u003ccode class=\"literal\"\u003e-P\u003c/code\u003e。注意，尽管这种方法不需要阻止其他客户端，但在修复完成之前，阻止其他用户连接到受损数据库可能仍然更稳妥。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e类似于删除并重新创建索引，因为索引内容都是从头重建的。不过，两者在锁方面的考量相当不同。\u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e会阻止该索引所属表上的写入，但不阻止读取。它还会对正在处理的特定索引获取\u003ccode class=\"literal\"\u003eACCESS EXCLUSIVE\u003c/code\u003e锁，从而阻塞试图使用该索引的读取。尤其是，无论查询内容如何，查询规划器都会尝试对该表的每个索引获取\u003ccode class=\"literal\"\u003eACCESS SHARE\u003c/code\u003e锁，因此 \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e实际上会阻塞几乎所有查询，只有那些计划已缓存且不使用这个索引的某些预备查询除外。相比之下，\u003ccode class=\"command\"\u003eDROP INDEX\u003c/code\u003e会短暂地对父表获取 \u003ccode class=\"literal\"\u003eACCESS EXCLUSIVE\u003c/code\u003e锁，同时阻塞写入和读取。随后的 \u003ccode class=\"command\"\u003eCREATE INDEX\u003c/code\u003e会阻止写入但不阻止读取；由于索引不存在，读取不会尝试使用它，因此不会发生阻塞，但读取可能被迫使用代价高昂的顺序扫描。\u003c/p\u003e\u003cp\u003e在\u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e运行期间，\u003ca href=\"/docs/18/runtime-config-client.html#GUC-SEARCH-PATH\"\u003esearch_path\u003c/a\u003e会临时改为\u003ccode class=\"literal\"\u003epg_catalog, pg_temp\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e对单个索引或表重新索引，需要在相应表上具有 \u003ccode class=\"literal\"\u003eMAINTAIN\u003c/code\u003e 权限。注意，对分区索引或分区表执行\u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e时，需要在该分区表上具有 \u003ccode class=\"literal\"\u003eMAINTAIN\u003c/code\u003e 权限，但在处理各个分区时会跳过权限检查。对模式或数据库重新索引，则需要是该模式或数据库的拥有者，或者具有\u003ca href=\"/docs/18/predefined-roles.html#PREDEFINED-ROLE-PG-MAINTAIN\"\u003epg_maintain\u003c/a\u003e角色的权限。特别要注意，这意味着非超级用户也可能重建其他用户拥有的表的索引。不过，作为一个特殊例外，\u003ccode class=\"command\"\u003eREINDEX DATABASE\u003c/code\u003e、\u003ccode class=\"command\"\u003eREINDEX SCHEMA\u003c/code\u003e和\u003ccode class=\"command\"\u003eREINDEX SYSTEM\u003c/code\u003e 会跳过共享系统目录上的索引，除非用户对该目录具有 \u003ccode class=\"literal\"\u003eMAINTAIN\u003c/code\u003e权限。\u003c/p\u003e\u003cp\u003e分别可使用\u003ccode class=\"command\"\u003eREINDEX INDEX\u003c/code\u003e和 \u003ccode class=\"command\"\u003eREINDEX TABLE\u003c/code\u003e对分区索引和分区表重新索引。指定分区关系的每个分区都会在单独的事务中重新索引。在处理分区表或分区索引时，这些命令不能在事务块内使用。\u003c/p\u003e\u003cp\u003e当对分区索引或分区表执行带\u003ccode class=\"literal\"\u003eTABLESPACE\u003c/code\u003e子句的 \u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e时，只有叶分区的表空间引用会被更新。由于分区索引本身不会更新，建议另外对这些分区索引单独执行 \u003ccode class=\"command\"\u003eALTER TABLE ONLY\u003c/code\u003e，以便后续附加的任何新分区都继承新表空间。如果命令失败，可能不会把所有索引都移动到新表空间。重新运行该命令将重建所有叶分区，并把先前未处理的索引移动到新表空间。\u003c/p\u003e\u003cp\u003e如果\u003ccode class=\"literal\"\u003eSCHEMA\u003c/code\u003e、\u003ccode class=\"literal\"\u003eDATABASE\u003c/code\u003e或 \u003ccode class=\"literal\"\u003eSYSTEM\u003c/code\u003e与\u003ccode class=\"literal\"\u003eTABLESPACE\u003c/code\u003e一起使用，则系统关系会被跳过，并生成一条\u003ccode class=\"literal\"\u003eWARNING\u003c/code\u003e。TOAST 表上的索引会重建，但不会移动到新的表空间。\u003c/p\u003e\u003cdiv class=\"refsect2\"\u003e\u003ch3\u003e并发重建索引\u003c/h3\u003e\u003cp\u003e重建索引可能会干扰数据库的正常运行。通常 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e会锁住要重建其索引的表，阻止其写入，并通过一次扫描完成整个索引构建。其他事务仍可读取该表，但如果它们试图在表中插入、更新或删除行，就会阻塞直到索引重建完成。如果系统是在线生产数据库，这可能产生严重影响。对非常大的表重新索引可能需要很多小时，即便是较小的表，索引重建也可能在一段对生产系统而言不可接受的时间内阻止写入操作。\u003c/p\u003e\u003cp\u003e\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e支持在尽量少阻塞写入的情况下重建索引。这种方法是通过指定\u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e的 \u003ccode class=\"literal\"\u003eCONCURRENTLY\u003c/code\u003e选项来调用的。使用该选项时，\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e必须为每个需要重建的索引对表执行两次扫描，并等待所有现有、可能使用该索引的事务结束。因此，这种方法比标准索引重建需要更多的总工作量，完成时间也明显更长，因为它必须等待那些可能修改索引的未完成事务。不过，由于它允许在索引重建期间继续进行正常操作，所以这种方法适合在生产环境中重建索引。当然，索引重建带来的额外 CPU、内存和 I/O 负载也可能拖慢其他操作。\u003c/p\u003e\u003cp\u003e并发重新索引会依次经历以下步骤。每个步骤都在单独的事务中运行。如果有多个要重建的索引，则每个步骤都会先遍历所有索引，再进入下一步。\u003c/p\u003e\u003cdiv class=\"orderedlist\"\u003e\u003col class=\"orderedlist\"\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e先向系统目录\u003ccode class=\"literal\"\u003epg_index\u003c/code\u003e添加一个新的临时索引定义。该定义将用于替换旧索引。随后会在要重新索引的索引及其关联表上获取会话级\u003ccode class=\"literal\"\u003eSHARE UPDATE EXCLUSIVE\u003c/code\u003e锁，以防止处理期间发生任何模式修改。\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e然后为每个新索引执行第一次构建。索引构建完成后，会将其标志 \u003ccode class=\"literal\"\u003epg_index.indisready\u003c/code\u003e切换为\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003etrue\u003c/span\u003e”\u003c/span\u003e，使其准备好接收插入；执行构建的事务结束后，它就会对其他会话可见。此步骤对每个索引都在单独的事务中完成。\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e接着执行第二次扫描，把第一次扫描期间新增的元组加入索引。此步骤同样对每个索引都在单独的事务中完成。\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e所有引用该索引的约束都会改为引用新的索引定义，同时索引名称也会被更改。此时，新索引的\u003ccode class=\"literal\"\u003epg_index.indisvalid\u003c/code\u003e会切换为\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003etrue\u003c/span\u003e”\u003c/span\u003e，旧索引则切换为\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003efalse\u003c/span\u003e”\u003c/span\u003e，并执行一次缓存失效，使所有引用旧索引的会话中的相关缓存项失效。\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e在等待可能引用旧索引的运行中查询完成之后，旧索引的 \u003ccode class=\"literal\"\u003epg_index.indisready\u003c/code\u003e会切换为\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003efalse\u003c/span\u003e”\u003c/span\u003e，以防止任何新元组被插入其中。\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e删除旧索引。随后会释放为索引和表持有的 \u003ccode class=\"literal\"\u003eSHARE UPDATE EXCLUSIVE\u003c/code\u003e会话锁。\u003c/p\u003e\u003c/li\u003e\u003c/ol\u003e\u003c/div\u003e\u003cp\u003e如果在重建索引时出现问题，例如唯一索引上发生唯一性违反，\u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e命令会失败，但除了原有索引外，还会留下一个\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e无效\u003c/span\u003e”\u003c/span\u003e的新索引。这个索引在查询时会被忽略，因为它可能不完整；不过它仍会带来更新开销。\u003cspan class=\"application\"\u003epsql\u003c/span\u003e的 \u003ccode class=\"command\"\u003e\\d\u003c/code\u003e命令会把这样的索引报告为 \u003ccode class=\"literal\"\u003eINVALID\u003c/code\u003e：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003epostgres=# \\d tab\n       Table \"public.tab\"\n Column |  Type   | Modifiers\n--------+---------+-----------\n col    | integer |\nIndexes:\n    \"idx\" btree (col)\n    \"idx_ccnew\" btree (col) INVALID\n\u003c/pre\u003e\u003cp\u003e如果被标记为\u003ccode class=\"literal\"\u003eINVALID\u003c/code\u003e的索引带有 \u003ccode class=\"literal\"\u003e_ccnew\u003c/code\u003e后缀，那么它对应于并发操作期间创建的临时索引，推荐的恢复方法是使用\u003ccode class=\"literal\"\u003eDROP INDEX\u003c/code\u003e将其删除，然后再次尝试\u003ccode class=\"command\"\u003eREINDEX CONCURRENTLY\u003c/code\u003e。如果无效索引带有 \u003ccode class=\"literal\"\u003e_ccold\u003c/code\u003e后缀，则它对应于未能删除的原始索引；由于重建本身实际上已经成功，推荐的恢复方法就是直接删除该索引。为了保持名称唯一，无效索引名的后缀后还可能附加非零数字，例如 \u003ccode class=\"literal\"\u003e_ccnew1\u003c/code\u003e、\u003ccode class=\"literal\"\u003e_ccold2\u003c/code\u003e等。\u003c/p\u003e\u003cp\u003e常规索引构建允许同一张表上的其他常规索引构建同时进行，但一张表在同一时间只能有一个并发索引构建。在这两种情况下，同时都不允许对该表进行其他类型的模式修改。另一个区别是，常规的 \u003ccode class=\"command\"\u003eREINDEX TABLE\u003c/code\u003e或\u003ccode class=\"command\"\u003eREINDEX INDEX\u003c/code\u003e 命令可以在事务块内执行，而\u003ccode class=\"command\"\u003eREINDEX CONCURRENTLY\u003c/code\u003e不行。\u003c/p\u003e\u003cp\u003e与任何长时间运行的事务一样，对表执行\u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e可能会影响并发\u003ccode class=\"command\"\u003eVACUUM\u003c/code\u003e在其他表上能够移除哪些元组。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eREINDEX SYSTEM\u003c/code\u003e不支持 \u003ccode class=\"command\"\u003eCONCURRENTLY\u003c/code\u003e，因为系统目录不能并发重新索引。\u003c/p\u003e\u003cp\u003e此外，用于排他约束的索引不能并发重新索引。如果在此命令中直接指定这样的索引，就会报错。如果并发地对包含排他约束索引的表或数据库重新索引，这些索引将被跳过。（不使用\u003ccode class=\"command\"\u003eCONCURRENTLY\u003c/code\u003e选项时，可以重新索引这类索引。）\u003c/p\u003e\u003cp\u003e每个运行\u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e的后端都会在 \u003ccode class=\"structname\"\u003epg_stat_progress_create_index\u003c/code\u003e视图中报告其进度。详见\u003ca href=\"/docs/18/progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING\" title=\"27.4.4. CREATE INDEX 进度报告\"\u003e第 27.4.4 节\u003c/a\u003e。\u003c/p\u003e\u003c/div\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e重建单个索引：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eREINDEX INDEX my_index;\n\u003c/pre\u003e\u003cp\u003e重建表\u003ccode class=\"literal\"\u003emy_table\u003c/code\u003e上的所有索引：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eREINDEX TABLE my_table;\n\u003c/pre\u003e\u003cp\u003e在不假定系统索引已经有效的情况下，重建某个数据库中的所有索引：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e$ \u003cstrong\u003e\u003ccode\u003eexport PGOPTIONS=\"-P\"\u003c/code\u003e\u003c/strong\u003e\n$ \u003cstrong\u003e\u003ccode\u003epsql broken_db\u003c/code\u003e\u003c/strong\u003e\n...\nbroken_db=\u0026gt; REINDEX DATABASE broken_db;\nbroken_db=\u0026gt; \\q\n\u003c/pre\u003e\u003cp\u003e重建某个表的索引，并在重建期间不阻塞对相关关系的读写操作：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eREINDEX TABLE CONCURRENTLY my_broken_table;\n\u003c/pre\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003e在 SQL 标准中没有\u003ccode class=\"command\"\u003eREINDEX\u003c/code\u003e命令。\u003c/p\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/create-index/?v=18\" title=\"CREATE INDEX\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE INDEX\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-index/?v=18\" title=\"DROP INDEX\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP INDEX\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/app-reindexdb.html\" title=\"reindexdb\"\u003e\u003cspan class=\"refentrytitle\"\u003e\u003cspan class=\"application\"\u003ereindexdb\u003c/span\u003e\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/progress-reporting.html#CREATE-INDEX-PROGRESS-REPORTING\" title=\"27.4.4. CREATE INDEX 进度报告\"\u003e第 27.4.4 节\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"REINDEX [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e [, ...] ) ] { INDEX | TABLE | SCHEMA } [ CONCURRENTLY ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\nREINDEX [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e [, ...] ) ] { DATABASE | SYSTEM } [ CONCURRENTLY ] [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e ]\n\n\u003cspan class=\"phrase\"\u003e其中 \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e 可以是以下之一：\u003c/span\u003e\n\n    CONCURRENTLY [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    TABLESPACE \u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_tablespace\u003c/code\u003e\u003c/em\u003e\n    VERBOSE [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]","synopsis_text":"REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA } [ CONCURRENTLY ] name\nREINDEX [ ( option [, ...] ) ] { DATABASE | SYSTEM } [ CONCURRENTLY ] [ name ]\n\n其中 option 可以是以下之一：\n\nCONCURRENTLY [ boolean ]\nTABLESPACE new_tablespace\nVERBOSE [ boolean ]"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}