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 の結果に従ってインデックスが指定されます。これらの 関数によるインデックス は、通常元となるデータに適用する際に何らかの変換が必要とされる、演算子に基づくデータに高速にアクセスするときに使用することができます。
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 で使用することができません。
インデックスの定義で使用される全ての関数と演算子は、キャッシュ可能でなければなりません。つまり、その結果は入力引数にのみに依存し、(他のテーブルの内容や現時刻などの)外部からの影響を受けてはなりません。この制限は、インデックスの動作は十分に定義されたものであることを確実にします。ユーザ定義の関数をインデックスに使用するために、関数を作成する時にその関数にキャッシュ可能と印を付けることを忘れないで下さい。
インデックスを削除するには DROP INDEXを使用して下さい。
PostgreSQLの問い合わせオプティマイザは、インデックスされた属性が下記のどれかを含む比較に関連するときは常にB-treeの使用を考慮します。 <, <=, =, >=, >
PostgreSQLの問い合わせオプティマイザは、インデックスされた属性が下記のどれかを含む比較に関連するときはいつもR-treeの使用を考慮します。 <<, &<, &>, >>, @, ~=, &&
PostgreSQLの問い合わせオプティマイザは、インデックスされた属性が=を使用する比較に関連するときはいつもhashインデックスの使用を考慮します。
現在ではB-treeとgistアクセスメソッドのみが複数列に対するインデックスをサポートしています。16個まではデフォルトで指定することができます（この制限は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,
opr.oprname AS ops_comp
FROM pg_am am, pg_opclass opc, pg_amop amop, pg_operator opr
WHERE opc.opcamid = am.oid AND
amop.amopclaid = opc.oid AND
amop.amopopr = opr.oid
ORDER BY acc_method, ops_name, ops_comp;