CREATE [ UNIQUE ] INDEX index_name ON table
[ USING acc_method ] ( column [ ops_name ] [, ...] )
[ WHERE predicate ]
CREATE [ UNIQUE ] INDEX index_name ON table
[ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
[ WHERE predicate ]
インデックスが（すでにデータがある状態で）作成されるときとデータが追加されるときに、テーブル内の値が重複していないかをチェックします。重複エントリを生じる結果となるデータの挿入または更新はエラーとなります。
作成されるインデックスの名前です。 この名前には、スキーマ名を含めることはできません。インデックスは、常にその親テーブルと同じスキーマに作成されます。
インデックスが作成されるテーブルの名前です (スキーマ修飾名の場合もあります)。
インデックスに使われるアクセスメソッドの名前です。デフォルトのアクセスメソッドは BTREE です。PostgreSQL は 4 つのインデックス用アクセスメソッドを提供します。
Lehman-Yao 高並行性 B-trees の実装です。
Guttman の二次分割アルゴリズムを使った標準 R-trees の実装です。
Litwin の線形ハッシュの実装です。
汎化インデックス検索ツリー
テーブルの列の名前です。
関連する演算子クラスです。詳細は下記を参照して下さい。
インデックス付けされる値を返す関数です。
部分インデックス用制約式を定義します。
CREATE INDEXは index_name インデックスを指定された table上に作ります。
Tip: インデックスは主にデータベースの性能を向上させるために使われます。しかし不適切な使用は性能の低下につながります。
上記の最初に示した構文では、インデックスのキーフィールドは列名として指定されました。もしインデックスアクセスメソッドが複数列に対するインデックスをサポートする場合は、複数のフィールドを指定できます。
上記の第2の構文では、ある1つのテーブルの1つまたは複数の列に適用されるユーザ定義関数 func_name の結果に従ってインデックスが指定されます。これらの 関数インデックス は、通常元となるデータに適用する際に何らかの変換が必要とされる、演算子に基づくデータに高速にアクセスするときに使用することができます。 たとえば、upper(col) に関数インデックスを作成すると、WHERE upper(col) = 'JIM' 句でインデックスを使用できます。
PostgreSQL はインデックス用に B-tree、R-tree、hash、GiST のアクセスメソッドを用意しています。B-tree アクセスメソッドは Lehman-Yao 高並行性 B-tree の実装です。R-tree アクセスメソッドは Guttman の二次分割アルゴリズムを使用する標準 R-tree を実装します。hash アクセスメソッドは Litwin の線形ハッシュの実装です。使われたアルゴリズムを挙げたのは、単にこれらのアクセスメソッドはすべて完全に動的であり定期的に最適化される必要（たとえば静的hashアクセスメソッドではあります）がないということを示すためです。
WHERE 句が存在する場合、部分インデックスが作成されます。部分インデックスは、テーブルの一部、通常は、テーブルの中でより何らかの興味のある部分のみのエントリを持つインデックスです。例えば、支払済み、未支払の注文を内容としたテーブルがあり、テーブル全体の中で未支払の注文の割合が小さく、かつ、頻繁に使用される場合、この部分のみにインデックスを作成することで性能を向上させることができます。この他の実現方法としては、UNIQUE 付きの WHERE を使用して、テーブルの一部に一意性を強制することです。
WHERE 句で使用される式はテーブル内にある列のみを参照することができます(しかし、インデックスされた列だけではなく、全ての列を使用することができます)。 現在、副問い合わせと集約式についても、WHERE で使用することができません。
インデックスの定義で使用される全ての関数と演算子は、不変 でなければなりません。 つまり、その結果は入力引数にのみに依存し、(他のテーブルの内容や現時刻などの) 外部からの影響を受けてはなりません。この制限は、インデックスの動作は十分に定義されたものであることを確実にします。 ユーザ定義の関数をインデックスに使用するため、関数を作成する際、その関数に IMMUTABLE (不変) オプションを付けることを忘れないで下さい。
インデックスを削除するには DROP INDEXを使用して下さい。
PostgreSQLの問い合わせオプティマイザは、インデックスされた属性が下記のどれかを含む比較に関連するときは常にB-treeの使用を考慮します。 <, <=, =, >=, >
PostgreSQLの問い合わせオプティマイザは、インデックスされた属性が下記のどれかを含む比較に関連するときはいつもR-treeの使用を考慮します。 <<, &<, &>, >>, @, ~=, &&
PostgreSQLの問い合わせオプティマイザは、インデックスされた属性が=を使用する比較に関連するときはいつもhashインデックスの使用を考慮します。
テストの結果、PostgreSQL での hash インデックスの処理速度は B-tree インデックスと同等あるいは遅いもので、また、必要なインデックスサイズおよび構築時間についても B-tree インデックスよりかなり劣ることが分かりました。 また、hash インデックスは、高同時実行状況下でのパフォーマンスも良くありません。 これらの理由により、hash インデックスの使用は推奨されていません。
現在では、B-tree と gist アクセスメソッドのみが、複数列に対するインデックスをサポートしています。32個まではデフォルトで指定することができます（この制限はPostgreSQL構築の際に変更できます）。現在では B-tree のみが一意性インデックスをサポートしています。
演算子クラスはインデックスのそれぞれの列に指定することができます。演算子クラスはその列のインデックスによって使われる演算子を識別します。たとえば、4バイト整数上のB-treeインデックスはint4_opsクラスを使います。 この演算子クラスは4バイト整数の比較関数を含みます。実践では、通常、フィールドのデータ型のデフォルト演算子クラスは十分です。演算子クラスを持つ大きな意味は、いくつかのデータ型には1つ以上の意味がある順番があるかもしれないということです。たとえば、絶対値または実数部のどちらかを使って複素数のデータ型をソートしたいかもしれません。そうするためには2つの演算子クラスをそのデータ型に定義し、インデックスを作るときに正しいクラスを選択します。特殊な目的を持つ演算子クラスもいくつかあります。
演算子クラス box_ops と bigbox_ops は、どちらも box データ型のR-treeインデックスをサポートしています。 この違いは、bigbox_ops は、非常に大きな浮動小数点座標に対する積算、加算、減算によって生じる浮動小数点の例外を避けるために四角形の座標を縮小することです。(注意:これは少し前までは真実でした。 しかし、現在ではこの 2 つの演算子クラスはどちらも浮動小数点を使用し、実質的には同じものです。)
以下の問い合わせはすべての定義された演算子クラスを示します。
SELECT am.amname AS acc_method,
opc.opcname AS ops_name
FROM pg_am am, pg_opclass opc
WHERE opc.opcamid = am.oid
ORDER BY acc_method, ops_name;