CREATE [ UNIQUE ] INDEX index_name ON table
[ USING acc_name ] ( column [ ops_name ] [, ...] )
CREATE [ UNIQUE ] INDEX index_name ON table
[ USING acc_name ] ( func_name( column [, ... ]) [ ops_name ] )
テーブルに(既にデータがあり)インデックスが作成されたときと、 データが追加される毎に値が重複していないかをチェックします。 複製エントリーを生ずる結果となるデータの挿入または更新はエラーと なります。
作成されるインデックスの名前です。
インデックスが作成されるテーブルの名前です。
インデックスに使われるアクセスメソッドの名前です。 デフォルトのアクセスメソッドは BTREE です。Postgres は 三つのアクセスメソッドをインデックスに提供します。
Lehman-Yao 高並行性 btree の実装です。
Guttman の二次分割アルゴリズムを使った標準 rtree を 実装しています。
Litwin の線形ハッシュの実装です。
テーブルの列の名前です。
関連する演算子クラスです。詳細は下記を見て下さい。
インデックス付けされる値を返す関数です。
CREATE INDEX はインデックス index_name を指定された table 上に作ります。
Tip: インデックスは主にデータベースの性能を向上させるために使われます。 しかし不適当に使用すると性能の低下につながります。
上記で示した最初に構文では、インデックスのキーフィールドは列名 として指定されました。複数のフィールドは、もしインデックスアクセス メソッドが複列インデックスをサポートする場合は指定できます。
上記で示される第二の構文では、ある一つのテーブルの一つまたは複数の列に適用 されるユーザ定義関数 func_name の結果に従ってインデックスが指定されます。 これらの関数によるインデックスは、データベースに適用する際、 何らかの変換 が必要とされる演算子に基づいてデータに高速にアクセスするときに使用する ことが出来ます。
Postgres はインデックス用に btree、rtree、hash アクセスメソッドを 用意しています。btree アクセスメソッドは Lehman-Yao 高並行性 btree の実装です。rtree アクセスメソッドは Guttman の二次分割アルゴリズム を使って標準 rtree を実装します。hash アクセスメソッドは Litwin の 線形ハッシュの実装です。使われたアルゴリズムを挙げたのは、単に これらのアクセスメソッドはすべて完全に動的であり定期的に 最適化される必要(例えば静的 hash アクセスメソッドではあります) がないということを示すためです。
インデックスを削除するためには DROP INDEX を使います。
Postgres 問い合わせオプティマイザは、 インデックスされた属性が下記のどれかを含む比較に関連するときは いつも btree の使用を考慮します。 <, <=, =, >=, >
Postgres 問い合わせオプティマイザは、 インデックスされた属性が下記のどれかを含む比較に関連するときは いつも rtree の使用を考慮します。 <<, &<, &>, >>, @, ~=, &&
Postgres 問い合わせオプティマイザは、 インデックスされた属性が = を含む比較に関連するときは いつも hash インデックスの使用を考慮します。
現在では btree アクセスメソッドのみが複列インデックスを サポートしています。16 個まではデフォルトで指定することが できます(この制限は Postgres 構築の際に変えられます)。
演算子クラス はインデックスのそれぞれの列 に指定することができます。演算子クラスはオペレータがその列のインデックス によって使われるオペレータを識別します。例えば、4 バイト整数上の btree インデックスは int4_ops クラスを使います。 この演算子クラスは 4 バイト整数の比較関数を含みます。実践では、 通常、フィールドのデータ型のデフォルト演算しクラスは十分です。 演算子クラスを持つ大きな意味は、いくつかのデータ型には一つ以上の 意味がある順番があるかもしれないということです。例えば、絶対値 または実数部のどちらかを使って複素数のデータ型をソートしたいかも しれません。そうするためには二つの演算子クラスをそのデータ型に 宣言し、インデックスを作るときに正しいクラスを選択します。 特殊な目的を持つ演算子クラスもいくつかあります。
演算子クラス box_ops と bigbox_ops はどちらも rtree インデックスを box データ型 でサポートしています。それらの違いは、bigbox_ops は、積算、加算、そして減算によって生じる浮動小数点の例外を避ける ため四角形の座標を縮小することです。もしその四角形があるフィールド が 20,000 ユニットかそれ以上の場合、bigbox_ops を使うべきです。
以下の問い合わせは全ての定義された演算子クラスを示します。
SELECT am.amname AS acc_name,
opc.opcname AS ops_name,
opr.oprname AS ops_comp
FROM pg_am am, pg_amop amop,
pg_opclass opc, pg_operator opr
WHERE amop.amopid = am.oid AND
amop.amopclaid = opc.oid AND
amop.amopopr = opr.oid
ORDER BY acc_name, ops_name, ops_comp