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](https://www.postgresql.org/docs/current/sql-reindex.html) 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: + Wenn ein Index beschädigt wird und keine gültigen Daten mehr enthält. Dies kann auf Software- oder Hardwarefehler zurückzuführen sein. + Wenn Abfragen, die zuvor den Index verwendet haben, ihn nicht mehr verwenden. + Wenn der Index mit einer großen Anzahl leerer oder fast leerer Seiten aufgebläht wird. Sie sollten den Vorgang ausführen`REINDEX`, wenn der Prozentsatz der aufgeblähten Menge (`bloat_pct`) größer als 20 ist. 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. | | | | | --- |--- |--- | | **Methode** | **Beschreibung** | **Einschränkungen** | | `CREATE INDEX`und `DROP INDEX` mit der Option `CONCURRENTLY` | Erzeugt einen neuen Index und entfernt den alten Index. Der Optimierer generiert Pläne, indem er den neu erstellten Index anstelle des alten Index verwendet. Zu Zeiten mit geringer Auslastung können Sie den alten Index löschen. | Die Indexerstellung nimmt mehr Zeit in Anspruch, wenn Sie `CONCURRENTLY` diese Option verwenden, da sie alle eingehenden Änderungen verfolgen muss. Wenn Änderungen eingefroren sind, wird der Vorgang als abgeschlossen markiert. | | `REINDEX`mit der `CONCURRENTLY` Option | Sperrt Schreibvorgänge während des Neuaufbauvorgangs. PostgreSQL Version 12**** und spätere Versionen bieten die `CONCURRENTLY` Option, mit der diese Sperren vermieden werden. | Die Verwendung `CONCURRENTLY` erfordert eine längere Zeit, um den Index neu zu erstellen. | | `pg_repack`Erweiterung | Löscht den Bloat aus einer Tabelle und baut den Index neu auf. | Sie müssen diese Erweiterung von einer EC2 Instanz oder Ihrem lokalen Computer aus ausführen, der mit der Datenbank verbunden ist. | ## Einen neuen Index erstellen Wenn die `CREATE INDEX` Befehle `DROP INDEX` und zusammen verwendet werden, wird ein Index neu erstellt: ``` DROP INDEX CREATE INDEX ON TABLE ([,]) ``` 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. `REINDEX`Mit 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 ein`CONCURRENTLY`, 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 ; ``` **Einen Index neu aufbauen** So erstellen Sie einen einzelnen Index neu: ``` reindex index ; ``` Um alle Indizes in einer Tabelle neu aufzubauen: ``` reindex table ; ``` Um alle Indizes in einem Schema neu aufzubauen: ``` reindex schema ; ``` **Gleichzeitiger Neuaufbau eines Indexes** So erstellen Sie einen einzelnen Index neu: ``` reindex index CONCURRENTLY ; ``` Um alle Indizes in einer Tabelle neu aufzubauen: ``` reindex table CONCURRENTLY ; ``` Um alle Indizes in einem Schema neu aufzubauen: ``` reindex schema CONCURRENTLY ; ``` **Nur Indizes neu erstellen oder verschieben** So erstellen Sie einen einzelnen Index neu: ``` pg_repack -h -d -i -k ``` Um alle Indizes neu aufzubauen: ``` pg_repack -h -d -x -t -k ```