REINDEX()			 SQL Commands			     REINDEX()



NAME
       REINDEX - rebuild indexes


SYNOPSIS
       REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]


DESCRIPTION
       REINDEX	rebuilds  an index using the data stored in the index's table,
       replacing the old copy of the index. There are two main reasons to  use
       REINDEX:

       o An  index  has	 become	 corrupted, and no longer contains valid data.
	 Although in theory this should never happen, in practice indexes  may
	 become	 corrupted  due to software bugs or hardware failures. REINDEX
	 provides a recovery method.

       o The index in question contains a lot of dead index pages that are not
	 being	reclaimed.  This  can  occur with B-tree indexes in PostgreSQL
	 under certain access patterns. REINDEX provides a way to  reduce  the
	 space	consumption of the index by writing a new version of the index
	 without the dead pages. See the documentation for more information.

PARAMETERS
       INDEX  Recreate the specified index.

       TABLE  Recreate all indexes of the specified table. If the table has  a
	      secondary ``TOAST'' table, that is reindexed as well.

       DATABASE
	      Recreate	all  indexes  within the current database.  Indexes on
	      shared system catalogs are skipped except	 in  stand-alone  mode
	      (see below).

       SYSTEM Recreate all indexes on system catalogs within the current data-
	      base.  Indexes on user tables are not processed.	Also,  indexes
	      on shared system catalogs are skipped except in stand-alone mode
	      (see below).

       name   The name of the specific index, table, or database to  be	 rein-
	      dexed.   Index   and   table   names  may	 be  schema-qualified.
	      Presently, REINDEX DATABASE and REINDEX SYSTEM can only  reindex
	      the  current database, so their parameter must match the current
	      database's name.

       FORCE  This is an obsolete option; it is ignored if specified.

NOTES
       If you suspect corruption of an index on a user table, you  can	simply
       rebuild that index, or all indexes on the table, using REINDEX INDEX or
       REINDEX TABLE.

       Things 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 may 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 -P option, which prevents
       it from using indexes for system catalog lookups.

       One way to do this is to shut down the postmaster and  start  a	stand-
       alone  PostgreSQL  server  with	the  -P option included on its command
       line.  Then, REINDEX DATABASE, REINDEX SYSTEM, REINDEX TABLE, or	 REIN-
       DEX INDEX can be issued, depending on how much you want to reconstruct.
       If in doubt, use REINDEX SYSTEM to select reconstruction of all	system
       indexes	in  the	 database. Then quit the standalone server session and
       restart the regular server.  See the  postgres(1)  reference  page  for
       more  information  about	 how  to  interact with the stand-alone server
       interface.

       Alternatively, a regular server session can be started with -P included
       in  its	command line options.  The method for doing this varies across
       clients, but in all libpq-based clients, it  is	possible  to  set  the
       PGOPTIONS  environment  variable to -P before starting the client. Note
       that while this method does not require locking out other  clients,  it
       may still be wise to prevent other users from connecting to the damaged
       database until repairs have been completed.

       If corruption is suspected in the indexes of any of the	shared	system
       catalogs	 (which are pg_authid, pg_auth_members, pg_database, pg_pltem-
       plate, pg_shdepend, and pg_tablespace), then a standalone  server  must
       be  used	 to  repair  it.  REINDEX  will not process shared catalogs in
       multiuser mode.

       For all indexes except the shared system catalogs,  REINDEX  is	crash-
       safe  and  transaction-safe.  REINDEX  is  not  crash-safe  for	shared
       indexes, which is why this case is disallowed during normal  operation.
       If  a  failure  occurs while reindexing one of these catalogs in stand-
       alone mode, it will not be possible to restart the regular server until
       the  problem  is rectified. (The typical symptom of a partially rebuilt
       shared index is ``index is not a btree'' errors.)

       REINDEX is similar to a drop and recreate of  the  index	 in  that  the
       index contents are rebuilt from scratch. However, the locking consider-
       ations are rather different. REINDEX locks out writes but not reads  of
       the  index's  parent table. It also takes an exclusive lock on the spe-
       cific index being processed, which will block reads that attempt to use
       that index. In contrast, DROP INDEX momentarily takes exclusive lock on
       the parent table, blocking both writes and reads. The subsequent CREATE
       INDEX  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  may be forced into expensive sequential scans. Another important
       point is that the drop/create approach  invalidates  any	 cached	 query
       plans that use the index, while REINDEX does not.

       Reindexing  a  single  index  or table requires being the owner of that
       index or table. Reindexing a database requires being the owner  of  the
       database	 (note	that the owner can therefore rebuild indexes of tables
       owned by other users). Of course, superusers can	 always	 reindex  any-
       thing.

       Prior  to  PostgreSQL  8.1,  REINDEX  DATABASE  processed  only	system
       indexes, not all indexes as one would expect from the  name.  This  has
       been  changed to reduce the surprise factor. The old behavior is avail-
       able as REINDEX SYSTEM.

       Prior to PostgreSQL 7.4, REINDEX TABLE did  not	automatically  process
       TOAST  tables,  and  so those had to be reindexed by separate commands.
       This is still possible, but redundant.

EXAMPLES
       Recreate the indexes on the table my_table:

       REINDEX TABLE my_table;


       Rebuild a single index:

       REINDEX INDEX my_index;


       Rebuild all indexes in a particular database, without trusting the sys-
       tem indexes to be valid already:

       $ export PGOPTIONS="-P"
       $ psql broken_db
       broken_db=> REINDEX DATABASE broken_db;
       broken_db=> \q


COMPATIBILITY
       There is no REINDEX command in the SQL standard.



SQL - Language Statements	  2010-12-14			     REINDEX()