正在重建索引

PostgreSQL REINDEX 命令使用存储在索引表中的数据并替换索引的旧副本来重建索引。我们建议您在以下场景REINDEX中使用：

当索引损坏且不再包含有效数据时。这可能是由于软件或硬件故障而发生的。
当先前使用该索引的查询停止使用该索引时。
当索引因大量空页或几乎空页而变得膨胀时。你应该在膨胀百分比 (bloat_pct) 大于 20 REINDEX 时运行。

以下查询可帮助您查找bloat_pct：


SELECT current_database(), nspname AS schemaname, tblname, idxname, bs*(relpages)::bigint AS real_size,
  bs*(relpages-est_pages)::bigint AS extra_size,
  100 * (relpages-est_pages)::float / relpages AS extra_pct,
  fillfactor,
  CASE WHEN relpages > est_pages_ff
    THEN bs*(relpages-est_pages_ff)
    ELSE 0
  END AS bloat_size,
  100 * (relpages-est_pages_ff)::float / relpages AS bloat_pct,
  is_na
  -- , 100-(pst).avg_leaf_density AS pst_avg_bloat, est_pages, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples, relpages -- (DEBUG INFO)
FROM (
  SELECT coalesce(1 +
         ceil(reltuples/floor((bs-pageopqdata-pagehdr)/(4+nulldatahdrwidth)::float)), 0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth)
      ) AS est_pages,
      coalesce(1 +
         ceil(reltuples/floor((bs-pageopqdata-pagehdr)*fillfactor/(100*(4+nulldatahdrwidth)::float))), 0
      ) AS est_pages_ff,
      bs, nspname, tblname, idxname, relpages, fillfactor, is_na
      -- , pgstatindex(idxoid) AS pst, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples -- (DEBUG INFO)
  FROM (
      SELECT maxalign, bs, nspname, tblname, idxname, reltuples, relpages, idxoid, fillfactor,
            ( index_tuple_hdr_bm +
                maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN
                  WHEN index_tuple_hdr_bm%maxalign = 0 THEN maxalign
                  ELSE index_tuple_hdr_bm%maxalign
                END
              + nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN
                  WHEN nulldatawidth = 0 THEN 0
                  WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign
                  ELSE nulldatawidth::integer%maxalign
                END
            )::numeric AS nulldatahdrwidth, pagehdr, pageopqdata, is_na
            -- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO)
      FROM (
          SELECT n.nspname, i.tblname, i.idxname, i.reltuples, i.relpages,
              i.idxoid, i.fillfactor, current_setting('block_size')::numeric AS bs,
              CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?)
                WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8
                ELSE 4
              END AS maxalign,
              /* per page header, fixed size: 20 for 7.X, 24 for others */
              24 AS pagehdr,
              /* per page btree opaque data */
              16 AS pageopqdata,
              /* per tuple header: add IndexAttributeBitMapData if some cols are null-able */
              CASE WHEN max(coalesce(s.null_frac,0)) = 0
                  THEN 8 -- IndexTupleData size
                  ELSE 8 + (( 32 + 8 - 1 ) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8)
              END AS index_tuple_hdr_bm,
              /* data len: we remove null values save space using it fractionnal part from stats */
              sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS nulldatawidth,
              max( CASE WHEN i.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na
          FROM (
              SELECT ct.relname AS tblname, ct.relnamespace, ic.idxname, ic.attpos, ic.indkey, ic.indkey[ic.attpos], ic.reltuples, ic.relpages, ic.tbloid, ic.idxoid, ic.fillfactor,
                  coalesce(a1.attnum, a2.attnum) AS attnum, coalesce(a1.attname, a2.attname) AS attname, coalesce(a1.atttypid, a2.atttypid) AS atttypid,
                  CASE WHEN a1.attnum IS NULL
                  THEN ic.idxname
                  ELSE ct.relname
                  END AS attrelname
              FROM (
                  SELECT idxname, reltuples, relpages, tbloid, idxoid, fillfactor, indkey,
                      pg_catalog.generate_series(1,indnatts) AS attpos
                  FROM (
                      SELECT ci.relname AS idxname, ci.reltuples, ci.relpages, i.indrelid AS tbloid,
                          i.indexrelid AS idxoid,
                          coalesce(substring(
                              array_to_string(ci.reloptions, ' ')
                              from 'fillfactor=([0-9]+)')::smallint, 90) AS fillfactor,
                          i.indnatts,
                          pg_catalog.string_to_array(pg_catalog.textin(
                              pg_catalog.int2vectorout(i.indkey)),' ')::int[] AS indkey
                      FROM pg_catalog.pg_index i
                      JOIN pg_catalog.pg_class ci ON ci.oid = i.indexrelid
                      WHERE ci.relam=(SELECT oid FROM pg_am WHERE amname = 'btree')
                      AND ci.relpages > 0
                  ) AS idx_data
              ) AS ic
              JOIN pg_catalog.pg_class ct ON ct.oid = ic.tbloid
              LEFT JOIN pg_catalog.pg_attribute a1 ON
                  ic.indkey[ic.attpos] <> 0
                  AND a1.attrelid = ic.tbloid
                  AND a1.attnum = ic.indkey[ic.attpos]
              LEFT JOIN pg_catalog.pg_attribute a2 ON
                  ic.indkey[ic.attpos] = 0
                  AND a2.attrelid = ic.idxoid
                  AND a2.attnum = ic.attpos
            ) i
            JOIN pg_catalog.pg_namespace n ON n.oid = i.relnamespace
            JOIN pg_catalog.pg_stats s ON s.schemaname = n.nspname
                                      AND s.tablename = i.attrelname
                                      AND s.attname = i.attname
            GROUP BY 1,2,3,4,5,6,7,8,9,10,11
      ) AS rows_data_stats
  ) AS rows_hdr_pdg_stats
) AS relation_stats
ORDER BY nspname, tblname, idxname;

完全为空的索引页会被回收以供重复使用。但是，如果页面上的索引键已被删除但仍有空间分配，我们建议定期重新编制索引。

重新创建索引有助于提供更好的查询性能。您可以通过三种方式重新创建索引，如下表所述。

方法	描述	限制
`CREATE INDEX`并`DROP INDEX`有`CONCURRENTLY`选项	生成新索引并删除旧索引。优化器使用新创建的索引而不是旧的索引来生成计划。在低峰时段，您可以删除旧索引。	使用该`CONCURRENTLY`选项时，创建索引需要更多时间，因为它必须跟踪所有传入的更改。冻结更改后，该过程将被标记为已完成。
`REINDEX`有`CONCURRENTLY`选项	在重建过程中锁定写入操作。PostgreSQL 版本 12 及更高版本提供了避免`CONCURRENTLY`这些锁定的选项。	使用`CONCURRENTLY`需要更长的时间来重建索引。
`pg_repack`延期	清理表中的膨胀并重建索引。	您必须从 EC2 实例或连接到数据库的本地计算机上运行此扩展程序。

创建新索引

DROP INDEX和CREATE INDEX命令一起使用时，会重建索引：


DROP INDEX <index_name>
CREATE INDEX <index_name> ON TABLE <table_name> (<column1>[,<column2>])

这种方法的缺点是它对桌子的排他性锁定，这会影响此活动期间的性能。该DROP INDEX命令获取一个排他锁，该锁会阻止对表的读取和写入操作。该CREATE INDEX命令会阻止对表的写入操作。它允许读取操作，但是在创建索引期间，读取操作的成本很高。

重建索引

该REINDEX命令可帮助您保持一致的数据库性能。当您对表执行大量 DML 操作时，这些操作会导致表和索引膨胀。索引用于加快对表的查找速度，以提高查询性能。索引膨胀会影响查找和查询性能。因此，我们建议您对具有大量 DML 操作的表执行重新索引，以保持查询性能的一致性。

该REINDEX命令通过锁定底层表上的写入操作从头开始重建索引，但它允许对表进行读取操作。但是，它确实会阻止对索引的读取操作。使用相应索引的查询会被阻止，但其他查询不会。

PostgreSQL 版本 12 引入了一个新的可选参数CONCURRENTLY，该参数从头开始重建索引，但不会锁定对表或使用该索引的查询的写入或读取操作。但是，使用此选项时，完成该过程需要更长时间。

示例

创建和删除索引

使用以下CONCURRENTLY选项创建新索引：


create index CONCURRENTLY on table(columns) ;

使用以下CONCURRENTLY选项删除旧索引：


drop index CONCURRENTLY <index name> ;

重建索引

要重建单个索引，请执行以下操作：


reindex index <index name> ;

要重建表中的所有索引，请执行以下操作：


reindex table <table name> ;

要重建架构中的所有索引，请执行以下操作：


reindex schema <schema name> ;

同时重建索引

要重建单个索引，请执行以下操作：


reindex index CONCURRENTLY <indexname> ;

要重建表中的所有索引，请执行以下操作：


reindex table CONCURRENTLY <tablename> ;

要重建架构中的所有索引，请执行以下操作：


reindex schema CONCURRENTLY <schemaname> ;

仅重建或重新定位索引

要重建单个索引，请执行以下操作：


pg_repack -h <hostname> -d <dbname> -i <indexname> -k

要重建所有索引，请执行以下操作：


pg_repack -h <hostname> -d <dbname> -x <indexname> -t <tablename> -k

Javascript 在您的浏览器中被禁用或不可用。

要使用 Amazon Web Services 文档，必须启用 Javascript。请参阅浏览器的帮助页面以了解相关说明。

文档惯例

用 pg_repack 去除膨胀

示例：使用自动吸尘和满真空来回收空间