CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace ]
[ WHERE predicate ]CREATE INDEXは、指定したテーブル上にnameインデックスを作ります。 インデックスは主にデータベースの性能を向上するために使われます （しかし、インデックスの不適切な使用は性能の低下につながる可能性があります）。
インデックスのキーフィールドは、列名、または括弧に囲まれた式として指定されます。 インデックスメソッドが複数列に対するインデックスをサポートする場合は、複数のフィールドを指定できます。
インデックスのフィールドとして、テーブル行の1つ以上の列の値を計算する式を指定できます。 この機能は、元のデータに何らかの変換を加えた値を基とするデータへの高速なアクセスを行う手段として使用することができます。 例えば、upper(col)という計算に基づくインデックスがあれば、WHERE upper(col) = 'JIM'句ではインデックスを使用することができます。
PostgreSQLはB-tree、hash、GiST、GINのインデックスメソッドを用意しています。 ユーザが独自にインデックスメソッドを定義することもできますが、これはかなり複雑です。
WHERE句が存在する場合、部分インデックスが作成されます。 部分インデックスは、テーブルの一部、通常は、テーブルの中でよりインデックスが有用な部分のみのエントリを持つインデックスです。 例えば、支払済みの注文と未支払の注文を情報として持つテーブルがあり、テーブル全体における未支払の注文の割合が小さく、かつ、頻繁に使用される場合、未支払の注文のみにインデックスを作成することで性能を向上できます。 部分インデックスのその他の利用方法として、UNIQUE付きのWHEREを使用して、テーブルの一部に一意性を強制する例が考えられます。 詳細は項11.8を参照してください。
WHERE句内の式では、元となるテーブルの列のみを参照できます。 しかし、インデックスを付加する列だけではなく、全ての列を使用することができます。 また、現在、副問い合わせと集約式については、WHEREで使用することができません。 同一の制限は、式で表されたインデックスのフィールドにも適用されます。
インデックスの定義で使用される全ての関数と演算子は、"不変"（immutable）でなければなりません。 つまり、結果は入力引数にのみに依存し、（他のテーブルの内容や現時刻などの）外部からの影響を受けてはなりません。 この制限によって、インデックスの動作が十分定義されていることが保証されます。 インデックス式やWHERE句にユーザ定義の関数を使用する場合、関数を作成する際、IMMUTABLE（不変）オプションを付けることを忘れないでください。
インデックスを（既にデータがある状態で）作成する時、テーブルにデータを追加する時に、テーブル内の値が重複していないかを検査します。 重複エントリを生じるデータの挿入または更新はエラーとなります。
このオプションを使用すると、PostgreSQLは、対象テーブルに対する同時挿入、更新、削除を防止するようなロックを獲得せずにインデックスを作成します。 通常のインデックス作成処理では、完了するまで対象テーブルへの書き込みはロックされます（読み取りはロックされません）。 このオプションを使用する際に注意しなければならない点が複数あります。 インデックスの同時作成を参照してください。
作成するインデックスの名前です。 この名前には、スキーマ名を含めることはできません。 インデックスは、常にその親テーブルと同じスキーマに作成されます。
インデックスを作成するテーブルの名前です（スキーマ修飾名の場合もあります）。
使用するインデックスメソッドの名前です。 btree、hash、gist、ginから選択します。 デフォルトのメソッドはbtreeです。
テーブルの列の名前です。
テーブル上の1つ以上の列を使用した式です。 通常この式は、構文で示した通り括弧で囲む必要があります。 しかし、式が関数呼び出し形式になっている場合は括弧を省略することができます。
演算子クラスの名前です。詳細は下記を参照してください。
（デフォルトの）正方向のソート順を指定します。
逆方向のソート順を指定します。
NULLを非NULLより前にソートすることを指定します。 これはDESCが指定された場合のデフォルトです。
NULLを非NULLより後にソートすることを指定します。 これはDESCが指定されない場合のデフォルトです。
インデックス固有の格納パラメータの名前です。 詳細は後述します。
インデックスを生成するテーブル空間です。 指定されなかった場合、default_tablespace、もし一時テーブル上のインデックスであれば、temp_tablespacesが考慮されます。
部分インデックス用の制約式です。
WITH句では、インデックスに対し格納パラメータを指定することができます。 インデックスメソッドはそれぞれ固有の設定可能な格納パラメータを持つことができます。 組み込み済みのインデックスはすべて以下の1つのパラメータを取ります。
インデックス用のfillfactorは割合（パーセント）で、インデックスメソッドがインデックスページをまとめ上げる時にどの程度ページを使用するかを決定するものです。 B-treeでは、リーフページは初期インデックス構築時と右側（キー値が大きい方向）にインデックスを拡張する時にこの割合分ページを使用します。 その後ページすべてが完全に使用されると、インデックスの効果が緩やかに分割されるように分割されます。 B-treeのデフォルトのfillfactorは90ですが、10から100までの任意の値を設定することができます。 テーブルの変化がほとんどない場合、100が最善でインデックスの物理サイズが最小化できます。 更新が非常に多い場合は、より小さなfillfactorを設定することで、ページ分割の頻度を少なくすることができます。 この他のインデックスメソッドでは、fillfactorをおおよそは同じですが、異なる意味で使用します。 メソッドによってfillfactorのデフォルト値は異なります。
インデックスの作成が、通常のデータベース操作に影響を与えることがあります。 通常PostgreSQLは、対象テーブルに対する書き込みをロックしてから、対象テーブルを一度スキャンして全体をインデックス付けします。 他のトランザクションはテーブルを読み取ることはできますが、対象テーブル内の行を挿入、更新、削除しようとすると、インデックス作成が完了するまでブロックされます。 活発な運用状態のデータベースシステムの場合、これは重大な影響を与えます。 非常に大規模なテーブルに対するインデックス作成は何時間もかかることがあり得ます。 また小規模なテーブルであっても、インデックス作成により、運用状態のシステムでは受け入れられない期間、書き込みロックがかかる可能性があります。
PostgreSQLは書き込みをロックしないインデックス作成もサポートしています。 CREATE INDEXにCONCURRENTLYオプションをつけることでこの方式が行われます。 このオプションを使うと、PostgreSQLはテーブルを2回スキャンしなければなりません。 さらに、潜在的にそのインデックスを使用する可能性がある、実行中のすべてのトランザクションが終わるまで待機しなければなりません。 したがって、この方式は通常の方式よりも総作業時間がかかり、また、完了するまでの時間が非常に長くなります。 しかし、インデックス作成中に通常の操作を行い続けることができますので、この方式は運用環境での新規インデックス作成に有用です。 もちろん、インデックス作成によりCPUや入出力に負荷がかかりますので、他の操作が低速になる可能性があります。
同時実行インデックス構築では実際、インデックスは第一トランザクションでシステムカタログに登録され、第二および第三トランザクションで2つのテーブルのスキャンが起こります。 たとえば一意性インデックスにおける一意性違反など、テーブルスキャン中に問題が発生すると、CREATE INDEXは失敗しますが、"無効な"インデックスが残ってしまいます。 こうしたインデックスは完全ではない可能性がりますので、問い合わせの際には無視されます。 しかし、更新時にオーバヘッドがかかります。 psqlの\dコマンドでは、こうしたインデックスにINVALID記号を付与します。
postgres=# \d tab
Table "public.tab"
Column | Type | Modifiers
--------+---------+-----------
col | integer |
Indexes:
"idx" btree (col) INVALIDこうした場合の推奨復旧方法は、インデックスを削除し、再度CREATE INDEX CONCURRENTLYを実行することです。 （他にもREINDEXを使用したインデックスの再作成という方法もあります。 しかし、REINDEXは同時作成をサポートしていませんので、この方法は魅力がありません。）
この他に一意性インデックスを同時作成する場合の注意事項があります。 2回目のテーブルスキャンが始まる時点で、他のトランザクションに対する一意性制約が既に有効になっているという点です。 これは、インデックスが使用できるようになる前やインデックス作成が最終的に失敗したとしても、制約違反が他のトランザクションで報告されてしまうことを意味します。 また、2回目のスキャン中に失敗した後も、"無効な"インデックスによる一意性制約は強制され続けます。
式インデックスや部分インデックスの同時作成もサポートされています。 式の評価中にエラーが発生した場合も、上で説明した一意性制約違反と同様な状況が発生します。
同一テーブルに対する通常のインデックス作成処理は複数並行して行うことができます。 しかし、あるテーブルに対するインデックスの同時作成は一度に1つしか行うことができません。 また、どの場合でもインデックス作成中はテーブルスキーマの変更はできません。 この他に、通常のCREATE INDEXコマンドはトランザクションブロック内で実行させることができますが、CREATE INDEX CONCURRENTLYは実行させることができないという相違点があります。
インデックスが、どのような時に使用され、どのような時に使用されないか、また、どのような場合に有用かといった情報については第11章を参照してください。
現在は、B-treeとGiSTインデックスメソッドのみが、複数列に対するインデックスをサポートしています。 指定できる列は、デフォルトでは32個までです（この制限はPostgreSQL構築の際に変更できます）。 現在、B-treeのみが一意性インデックスをサポートしています。
演算子クラスは、インデックスのそれぞれの列に指定することができます。 演算子クラスは、その列のインデックスが使う演算子を識別します。 例えば、4バイト整数に対するB-treeインデックスには、int4_opsクラスを使います。 この演算子クラスには、4バイト整数の比較関数が含まれています。 実際の使用では、通常、列のデータ型のデフォルト演算子クラスで十分です。 演算子クラスを保持する主な理由は、データ型の中には有意な順序を2つ以上持つものがあるかもしれないからです。 例えば、絶対値または実数部のどちらかを使って複素数のデータ型をソートしたい場合がありえます。 これを実現するには、データ型として2つの演算子クラスを定義し、インデックスを作る時に適切なクラスを選択します。 演算子クラスについての詳細は、項11.9と項34.14を参照してください。
順序付きスキャンをサポートするインデックスメソッド（現時点ではB-Treeのみ）では、省略可能なASC、DESC、NULLS FIRST、NULLS LAST句を指定し、通常のインデックスの方向を反転させることができます。 順序付きインデックスは正方向にも逆方向にもスキャンすることができますので、単一列に対するDESCインデックスは通常は有用ではありません。 このソート順序はすでに通常のインデックスを使用して実現できます。 これらのオプションの価値は、SELECT ... ORDER BY x ASC, y DESCなどの順序指定が混在する問い合わせによって要求されるソート順に一致する、複数列に対するインデックスを作成できる点です。 NULLSオプションは、ソート処理を中断するインデックスに基づいた問い合わせにおける"NULLのソート順を低くする"動作をサポートする必要がある場合に有用です。 デフォルトの動作は"NULLのソート順を高くする"です。
インデックスを削除するには、DROP INDEXを使用してください。
以前のPostgreSQLにはR-treeインデックスメソッドがありました。 GiSTメソッドに比べて大きな利点がありませんでしたので、このメソッドは削除されました。 古いデータベースからGiSTへの変換を簡単にするため、USING rtreeが指定された場合、CREATE INDEXはUSING gistと解釈します。
filmsテーブルのtitle列にB-treeインデックスを作成します。
CREATE UNIQUE INDEX title_idx ON films (title);
大文字小文字を区別しない検索が効率的になるように、lower(title)式に対してインデックスを作成します。
CREATE INDEX lower_title_idx ON films ((lower(title)));
デフォルト以外のNULLのソート順を指定したインデックスを作成します。
CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);
デフォルト以外のfillファクタを持つインデックスを作成します。
CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);
filmsテーブル上にcode列に対するインデックスを作成します。 また、このインデックスをindexspaceテーブル空間内に生成します。
CREATE INDEX code_idx ON films(code) TABLESPACE indexspace;
対象テーブルへの書き込みをロックせずにインデックスを作成します。
CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);