Die vorliegende Übersetzung wurde maschinell erstellt. Im Falle eines Konflikts oder eines Widerspruchs zwischen dieser übersetzten Fassung und der englischen Fassung (einschließlich infolge von Verzögerungen bei der Übersetzung) ist die englische Fassung maßgeblich.

Indizes neu erstellen

Der PostgreSQL-Befehl REINDEX erstellt einen Index neu, indem er die Daten verwendet, die in der Indextabelle gespeichert sind, und ersetzt die alte Kopie des Indexes. Wir empfehlen die Verwendung REINDEX in den folgenden Szenarien:

Die folgende Abfrage hilft Ihnen bei der Suche bloat_pct nach:

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;

Indexseiten, die vollständig leer sind, werden zur Wiederverwendung zurückgewonnen. Wir empfehlen jedoch eine regelmäßige Neuindizierung, wenn die Indexschlüssel auf einer Seite gelöscht wurden, aber noch Speicherplatz zugewiesen ist.

Die Neuerstellung des Index trägt zu einer besseren Abfrageleistung bei. Sie können einen Index auf drei Arten neu erstellen, wie in der folgenden Tabelle beschrieben.

Einen neuen Index erstellen

Wenn die CREATE INDEX Befehle DROP INDEX und zusammen verwendet werden, wird ein Index neu erstellt:

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

Der Nachteil dieses Ansatzes ist die ausschließliche Sperre der Tabelle, was sich negativ auf die Leistung während dieser Aktivität auswirkt. Der DROP INDEX Befehl erwirbt eine exklusive Sperre, die sowohl Lese- als auch Schreibvorgänge in der Tabelle blockiert. Der CREATE INDEX Befehl blockiert die Schreiboperationen in der Tabelle. Es ermöglicht Lesevorgänge, die jedoch bei der Indexerstellung teuer sind.

Einen Index neu aufbauen

Der REINDEX Befehl hilft Ihnen dabei, eine konsistente Datenbankleistung aufrechtzuerhalten. Wenn Sie eine große Anzahl von DML-Vorgängen an einer Tabelle ausführen, führen diese zu einer Aufblähung der Tabelle und des Indexes. Indizes werden verwendet, um das Nachschlagen in Tabellen zu beschleunigen und so die Abfrageleistung zu verbessern. Ein aufgeblähter Index wirkt sich auf Suchvorgänge und die Abfrageleistung aus. Aus diesem Grund empfehlen wir, Tabellen mit einem hohen Volumen an DML-Vorgängen neu zu indizieren, um eine konsistente Abfrageleistung zu gewährleisten.

REINDEXMit dem Befehl wird der Index von Grund auf neu erstellt, indem die Schreibvorgänge in der zugrunde liegenden Tabelle gesperrt werden. Lesevorgänge für die Tabelle sind jedoch zulässig. Es blockiert jedoch die Lesevorgänge für den Index. Abfragen, die den entsprechenden Index verwenden, werden blockiert, andere Abfragen jedoch nicht.

PostgreSQL Version 12 führte einen neuen optionalen Parameter einCONCURRENTLY, der den Index von Grund auf neu erstellt, aber die Schreib- oder Lesevorgänge für die Tabelle oder für Abfragen, die den Index verwenden, nicht sperrt. Wenn Sie diese Option verwenden, dauert es jedoch länger, bis der Vorgang abgeschlossen ist.

Beispiele

Einen Index erstellen und löschen

Erstellen Sie einen neuen Index mit der CONCURRENTLY Option:

create index CONCURRENTLY on table(columns) ;

Löschen Sie den alten Index mit der CONCURRENTLY Option:

drop index CONCURRENTLY <index name> ;

Einen Index neu aufbauen

So erstellen Sie einen einzelnen Index neu:

reindex index <index name> ;

Um alle Indizes in einer Tabelle neu aufzubauen:

reindex table <table name> ; 

Um alle Indizes in einem Schema neu aufzubauen:

reindex schema <schema name> ;

Gleichzeitiger Neuaufbau eines Indexes

So erstellen Sie einen einzelnen Index neu:

reindex index CONCURRENTLY <indexname> ;

Um alle Indizes in einer Tabelle neu aufzubauen:

reindex table CONCURRENTLY <tablename> ;

Um alle Indizes in einem Schema neu aufzubauen:

reindex schema CONCURRENTLY <schemaname> ;

Nur Indizes neu erstellen oder verschieben

So erstellen Sie einen einzelnen Index neu:

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

Um alle Indizes neu aufzubauen:

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