CREATE INDEX

Name

CREATE INDEX  -- 新しいインデックスの定義

Synopsis

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 ]
  

入力

UNIQUE

インデックスが(すでにデータがある状態で)作成されるときとデータが追加されるときに、テーブル内の値が重複していないかをチェックします。重複エントリを生じる結果となるデータの挿入または更新はエラーとなります。

index_name

作成されるインデックスの名前です。 この名前には、スキーマ名を含めることはできません。インデックスは、常にその親テーブルと同じスキーマに作成されます。

table

インデックスが作成されるテーブルの名前です (スキーマ修飾名の場合もあります)。

acc_method

インデックスに使われるアクセスメソッドの名前です。デフォルトのアクセスメソッドは BTREE です。PostgreSQL は 4 つのインデックス用アクセスメソッドを提供します。

BTREE

Lehman-Yao 高並行性 B-trees の実装です。

RTREE

Guttman の二次分割アルゴリズムを使った標準 R-trees の実装です。

HASH

Litwin の線形ハッシュの実装です。

GIST

汎化インデックス検索ツリー

column

テーブルの列の名前です。

ops_name

関連する演算子クラスです。詳細は下記を参照して下さい。

func_name

インデックス付けされる値を返す関数です。

predicate

部分インデックス用制約式を定義します。

出力

CREATE INDEX

インデックスの作成に成功すると返されるメッセージです。

ERROR: Cannot create index:'index_name' already exists.

インデックスの作成が不可能な場合このエラーが返されます。

説明

CREATE INDEXindex_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つの演算子クラスをそのデータ型に定義し、インデックスを作るときに正しいクラスを選択します。特殊な目的を持つ演算子クラスもいくつかあります。

以下の問い合わせはすべての定義された演算子クラスを示します。

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;
    

使用方法

以下の文はテーブル films にあるフィールド title に B-tree インデックスを作ります。

CREATE UNIQUE INDEX title_idx
    ON films (title);
  

互換性

SQL92

CREATE INDEX は PostgreSQL の言語拡張です。

SQL92 には CREATE INDEX コマンドはありません。