翻訳は機械翻訳により提供されています。提供された翻訳内容と英語版の間で齟齬、不一致または矛盾がある場合、英語版が優先します。 # インデックスの再構築 PostgreSQL の [REINDEX](https://www.postgresql.org/docs/current/sql-reindex.html) コマンドで、インデックスのテーブルに保存されているデータを使用し、インデックスの古いコピーを置き換えることでてインデックスを再構築します。次のようなシナリオでは、`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; ``` 完全に空のインデックスページは、繰り返し再利用されます。ただし、ページのインデックスキーが削除されてもスペースが割り当てられている場合は、定期的にインデックスを再作成することをお勧めします。 インデックスを再作成すると、クエリのパフォーマンスが向上します。次の表に示すように、3 つの方法でインデックスを再作成できます。 | | | | | --- |--- |--- | | **メソッド** | **説明** | **制限** | | `CONCURRENTLY` オプションと合わせて使用する `CREATE INDEX` および `DROP INDEX` | 新しいインデックスを構築し、古いインデックスを削除します。オプティマイザは、古いインデックスの代わりに新しく作成されたインデックスを使用してプランを生成します。オフピーク時間中は、古いインデックスを削除できます。 | `CONCURRENTLY` オプションを使用している場合は、受信するすべての変更を追跡する必要があるためインデックスの作成に時間がかかります。変更が停止すると、処理が完了としてマークされます。 | | `CONCURRENTLY` オプションを使用する `REINDEX` | 再構築処理中に書き込み操作をロックします。PostgreSQL 12 ****以降のバージョンでは `CONCURRENTLY` オプションが用意されているため、これらのロックを回避できます。 | `CONCURRENTLY` を使用すると、インデックスの再構築に時間がかかります。 | | `pg_repack` 拡張機能 | テーブルから肥大化を削除し、インデックスを再構築します。 | この拡張機能は、EC2 インスタンスまたはデータベースに接続されているローカルコンピュータから実行する必要があります。 | ## 新しいインデックスを作成する `DROP INDEX` コマンドと `CREATE INDEX` コマンドを併用すると、インデックスが再構築されます。 ``` DROP INDEX CREATE INDEX ON TABLE ([,]) ``` この方法の欠点は、テーブルに対する排他的ロックであり、このアクティビティ中のパフォーマンスに影響することです。`DROP INDEX` コマンドによって排他的ロックを取得し、テーブルに対する読み取り操作と書き込み操作の両方をブロックします。`CREATE INDEX` コマンドを使用して、テーブルに対する書き込み操作をブロックします。これにより読み取り操作が可能になりますが、インデックスの作成時にコストがかかります。 ## インデックスの再構築 `REINDEX` コマンドは、一貫したデータベースパフォーマンスを維持するのに役立ちます。テーブルに対して多数の DML 操作を実行すると、テーブルとインデックスの両方が肥大化します。インデックスを使用することでテーブルの検索が速くなり、クエリのパフォーマンスが向上します。インデックスの肥大化は、検索やクエリのパフォーマンスに影響します。したがって、クエリの一貫したパフォーマンスを維持するために、大量の DML 操作が含まれるテーブルに対してインデックスの再構築を実行することをお勧めします。 `REINDEX` コマンドによって、基になるテーブルの書き込み操作をロックし、インデックスを最初から再構築しますが、テーブルでの読み取り操作は可能です。ただし、インデックスでは読み取り操作がブロックされます。対応するインデックスを使用するクエリはブロックされますが、他のクエリはブロックされません。 PostgreSQL バージョン 12 では、新しいオプションパラメータの `CONCURRENTLY` が導入されました。これによりインデックスがゼロから再構築されますが、テーブルでの読み取り操作や書き込み操作、またはインデックスを使用するクエリはロックされません。ただし、このオプションを使用すると、処理が完了するまでに時間がかかります。 ## 例 **インデックスの作成と削除** `CONCURRENTLY` オプションを使用して新しいインデックスを作成します。 ``` create index CONCURRENTLY on table(columns) ; ``` `CONCURRENTLY` オプションを使用して古いインデックスを削除します。 ``` drop index CONCURRENTLY ; ``` **インデックスの再構築** 単一のインデックスを再構築するには: ``` reindex index ; ``` テーブル内のすべてのインデックスを再構築するには: ``` reindex table ; ``` スキーマ内のすべてのインデックスを再構築するには: ``` reindex schema ; ``` **インデックスを同時に再構築する** 単一のインデックスを再構築するには: ``` reindex index CONCURRENTLY ; ``` テーブル内のすべてのインデックスを再構築するには: ``` reindex table CONCURRENTLY ; ``` スキーマ内のすべてのインデックスを再構築するには: ``` reindex schema CONCURRENTLY ; ``` **インデックスのみを再構築または再配置する** 単一のインデックスを再構築するには: ``` pg_repack -h -d -i -k ``` すべてのインデックスを再構築するには: ``` pg_repack -h -d -x -t -k ```