説明

CREATE INDEXは、指定したリレーションの指定した列(複数可)に対するインデックスを作ります。 リレーションとしてテーブルまたはマテリアライズドビューを取ることができます。 インデックスは主にデータベースの性能を向上するために使われます （しかし、インデックスの不適切な使用は性能の低下につながる可能性があります）。

インデックスのキーフィールドは、列名、または括弧に囲まれた式として指定されます。 インデックスメソッドが複数列に対するインデックスをサポートする場合は、複数のフィールドを指定できます。

インデックスのフィールドとして、テーブル行の1つ以上の列の値から計算する式を指定できます。 この機能は、元のデータに何らかの変換を加えた値を基とするデータへの高速なアクセスを行う手段として使用することができます。 例えば、upper(col)という計算に基づくインデックスがあれば、WHERE upper(col) = 'JIM'という句ではインデックスを使用することができます。

PostgreSQLはB-tree、ハッシュ、GiST、SP-GiST、GIN、BRINのインデックスメソッドを用意しています。 ユーザが独自にインデックスメソッドを定義することもできますが、これはかなり複雑です。

WHERE句が存在する場合、部分インデックスが作成されます。 部分インデックスは、テーブルの一部、通常は、テーブルの中でよりインデックスが有用な部分のみのエントリを持つインデックスです。 例えば、請求済みの注文と未請求の注文を情報として持つテーブルがあり、テーブル全体における未請求の注文の割合が小さく、かつ、頻繁に使用される場合、未請求の注文のみにインデックスを作成することで性能を向上できます。 部分インデックスのその他の利用方法として、UNIQUE付きのWHEREを使用して、テーブルの部分集合に一意性を強制する例が考えられます。 詳細は11.8を参照してください。

WHERE句内の式では、元となるテーブルの列のみを参照できます。 しかし、インデックスを付加する列だけではなく、全ての列を使用することができます。 また、現在、副問い合わせと集約式については、WHEREで使用することができません。 同一の制限は、式で表されたインデックスのフィールドにも適用されます。

インデックスの定義で使用される全ての関数と演算子は、「不変」（immutable）でなければなりません。 つまり、結果は入力引数にのみに依存し、（他のテーブルの内容や現時刻などの）外部からの影響を受けてはなりません。 この制限によって、インデックスの動作が十分定義されていることが保証されます。 インデックス式やWHERE句にユーザ定義の関数を使用する場合、関数を作成する際、IMMUTABLE（不変）オプションを付けることを忘れないでください。

パラメータ

postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 col    | integer |           |          |
Indexes:
    "idx" btree (col) INVALID

注釈

インデックスが、どのような時に使用され、どのような時に使用されないか、また、どのような場合に有用かといった情報については第11章を参照してください。

現在は、B-tree、GiST、GIN、BRINインデックスメソッドのみが、複数キー列に対するインデックスをサポートしています。 複数キー列があるかどうかはインデックスにINCLUDE列を追加できるかどうかとは独立です。 インデックスはINCLUDE列を含めて32列まで持てます。 （この制限はPostgreSQLのコンパイル時に変更できます。） 現在、B-treeのみが一意性インデックスをサポートしています。

オプションのパラメータのついた演算子クラスは、インデックスのそれぞれの列に指定することができます。 演算子クラスは、その列のインデックスが使う演算子を識別します。 例えば、4バイト整数に対するB-treeインデックスには、int4_opsクラスを使います。 この演算子クラスには、4バイト整数の比較関数が含まれています。 実際の使用では、通常、列のデータ型のデフォルト演算子クラスで十分です。 演算子クラスを保持する主な理由は、データ型の中には有意な順序を2つ以上持つものがあるかもしれないからです。 例えば、複素数のソートで絶対値または実数部のどちらかを使いたい場合がありえます。 これを実現するには、データ型として2つの演算子クラスを定義し、インデックスを作る時に適切なクラスを選択します。 演算子クラスについての詳細は、11.10と38.16を参照してください。

CREATE INDEXがパーティションテーブルに実行されたときのデフォルトの振る舞いは、全パーティションが一致するインデックスを持つようにする全パーティションへの再帰的な実行です。 各パーティションは最初に同等のインデックスが既に存在するかの判断のために検査され、存在するなら作成するインデックスに対するパーティションインデックスとしてアタッチされます。新たに作成するインデックスが既存インデックスの親インデックスとなります。 一致するインデックスが存在しない場合、新たなインデックスが作られて、自動的にアタッチされます。各パーティションの新たなインデックス名は、コマンドでインデックスが指定されなかった場合と同様に決定されます。 ONLYオプションが指定された場合、再帰処理は行われず、そのインデックスは無効と印付けされます。 （ALTER INDEX ... ATTACH PARTITIONは、ひとたび全パーティションが一致するインデックスを得たなら、インデックスを有効に印付けします） しかしながら、ONLYが指定されたとしても、将来にCREATE TABLE ... PARTITION OFを使って作成されるあらゆるパーティションは自動的に一致するインデックスを持つことに注意してください。

順序付きスキャンをサポートするインデックスメソッド（現時点ではB-Treeのみ）では、ASC、DESC、NULLS FIRST、NULLS LAST句(省略可能)をオプションで指定し、インデックスのソート順を変更することができます。 順序付きインデックスは正方向にも逆方向にもスキャンすることができますので、単一列に対するDESCインデックスは通常は有用ではありません。 このソート順序はすでに通常のインデックスを使用して実現できます。 これらのオプションの価値は、SELECT ... ORDER BY x ASC, y DESCなどの順序指定が混在する問い合わせによって要求されるソート順に一致する、複数列に対するインデックスを作成できる点です。 NULLSオプションは、インデックスに基づいた問い合わせにおいてソート処理を省略するために「NULLのソート順を低くする」動作をサポートする必要がある場合に有用です。 デフォルトの動作は「NULLのソート順を高くする」です。

システムは定期的にテーブルの列すべての統計情報を集めています。 新しく作られた、式でないインデックスは、インデックスの有用性を決定するのにその統計情報をすぐに使うことができます。 新しい式インデックスに対しては、そのインデックスのための統計情報を生成するために、ANALYZEを実行するか、autovacuumデーモンがそのテーブルを解析するのを待つことが必要です。

ほとんどのインデックスメソッドにおいて、インデックス作成速度はmaintenance_work_memの設定に依存します。 より大きな値を設定すると、インデックス作成に必要となる時間が短縮されます。 ただし、実際に使用できるメモリ量を超えるほど大きくすると、マシンがスワップ状態になり、遅くなります。

PostgreSQLはテーブルの行をより高速に処理するために複数CPUを効かせてインデックスを作成できます。 この機能はパラレルインデックス作成と呼ばれています。 パラレルでのインデックス作成をサポートしているインデックスメソッド（今のところB-Treeのみ）に対して、maintenance_work_memでは、いくつのワーカープロセスが実行されているかに拘らず、各インデックス作成操作で使うことができる全体のメモリの最大量を指定します。 一般にコストモデルは、もしあるなら、どれだけの数のワーカープロセスを要求すべきかを自動的に決定します。

パラレルインデックス作成ではmaintenance_work_memを増やすことで、同様の逐次インデックス作成ではほとんど恩恵がみられない場合でも恩恵があるかもしれません。 パラレルワーカーはmaintenance_work_mem全体の内、少なくとも32MBの割り当て分を持たなければならないため、maintenance_work_memは要求されるワーカープロセス数に影響を及ぼすかもしれないことに注意してください。 また、リーダープロセスに対しても32MBの割り当てを残さなければなりません。 max_parallel_maintenance_workersを増やすことで、より多くのワーカーが使用できるようになるかもしれません。これは、インデックス作成が既にI/Oバウンドであるのでない限り、インデックス作成の所要時間を減らすでしょう。 もちろん、休止している十分なCPU容量もある前提です。

ALTER TABLEを通してparallel_workersの値を設定することで、テーブルに対してCREATE INDEXでどれだけのワーカープロセス数が要求されるかを、直接に調整できます。 これはコストモデルを完全に無視して、maintenance_work_memがパラレルワーカーの要求数に影響を与えることを回避します。 ALTER TABLEを通してparallel_workersを0に設定することは、そのテーブルに対するパラレルインデックス作成を全ての場合に無効化します。

CONCURRENTLYオプションを伴うCREATE INDEXは特に制限なくパラレル作成をサポートしますが、実際には最初のテーブルスキャンだけがパラレルに実行されます。

インデックスを削除するには、DROP INDEXを使用してください。

他の時間のかかるトランザクションと同じく、あるテーブルに対するCREATE INDEXは、その他のテーブルに対する同時実行中のVACUUMによりどのタプルが削除できるかに影響します。

以前のPostgreSQLにはR-treeインデックスメソッドがありました。 GiSTメソッドに比べて大きな利点がありませんでしたので、このメソッドは削除されました。 古いデータベースからGiSTへの変換を簡単にするため、USING rtreeが指定された場合、CREATE INDEXはUSING gistと解釈します。

CREATE INDEXを実行している各バックエンドはその進捗をpg_stat_progress_create_indexビューで報告します。 詳細は28.4.2を参照してください。

例

テーブルfilmsの列titleに一意性のB-treeインデックスを作成します。

CREATE UNIQUE INDEX title_idx ON films (title);

テーブルfilmsの列titleに、列directorと列ratingを含めて、一意性のB-treeインデックスを作成します。

CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating);

重複排除を無効にしたB-Treeを作成します。

CREATE INDEX title_idx ON films (title) WITH (deduplicate_items = off);

大文字小文字を区別しない検索が効率的になるように、式lower(title)に対してインデックスを作成します。

CREATE INDEX ON films ((lower(title)));

(この例では、インデックス名を省略することを選びました。 このためシステムがfilms_lower_idxなどという名前を選ぶことになります。)

デフォルト以外の照合順序でインデックスを作成します。

CREATE INDEX title_idx_german ON films (title COLLATE "de_DE");

デフォルトと異なるNULLのソート順を指定したインデックスを作成します。

CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);

デフォルトと異なるフィルファクタを持つインデックスを作成します。

CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);

高速更新を無効にしてGINインデックスを作成します。

CREATE INDEX gin_idx ON documents_table USING GIN (locations) WITH (fastupdate = off);

テーブルfilms上の列codeに対するインデックスを作成します。 また、このインデックスをテーブル空間indexspace内に生成します。

CREATE INDEX code_idx ON films (code) TABLESPACE indexspace;

変換関数の結果に対するbox操作を効率的に使用できるようにpoint属性にGiSTインデックスを作成します。

CREATE INDEX pointloc
    ON points USING gist (box(location,location));
SELECT * FROM points
    WHERE box(location,location) && '(0,0),(1,1)'::box;

対象テーブルへの書き込みをロックせずにインデックスを作成します。

CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

互換性

CREATE INDEXはPostgreSQLの拡張です。 標準SQLにはインデックスについての規定はありません。

関連項目