他のバージョンの文書11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

CREATE INDEX

CREATE INDEX — 新しいインデックスを定義する

概要

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

説明

CREATE INDEXは、指定したリレーションの指定した列(複数可)に対するインデックスを作ります。 リレーションとしてテーブルまたはマテリアライズドビューを取ることができます。 インデックスは主にデータベースの性能を向上するために使われます (しかし、インデックスの不適切な使用は性能の低下につながる可能性があります)。

インデックスのキーフィールドは、列名、または括弧に囲まれた式として指定されます。 インデックスメソッドが複数列に対するインデックスをサポートする場合は、複数のフィールドを指定できます。

インデックスのフィールドとして、テーブル行の1つ以上の列の値から計算する式を指定できます。 この機能は、元のデータに何らかの変換を加えた値を基とするデータへの高速なアクセスを行う手段として使用することができます。 例えば、upper(col)という計算に基づくインデックスがあれば、WHERE upper(col) = 'JIM'という句ではインデックスを使用することができます。

PostgreSQLはB-tree、ハッシュ、GiST、SP-GiST、GIN、BRINのインデックスメソッドを用意しています。 ユーザが独自にインデックスメソッドを定義することもできますが、これはかなり複雑です。

WHERE句が存在する場合、部分インデックスが作成されます。 部分インデックスは、テーブルの一部、通常は、テーブルの中でよりインデックスが有用な部分のみのエントリを持つインデックスです。 例えば、請求済みの注文と未請求の注文を情報として持つテーブルがあり、テーブル全体における未請求の注文の割合が小さく、かつ、頻繁に使用される場合、未請求の注文のみにインデックスを作成することで性能を向上できます。 部分インデックスのその他の利用方法として、UNIQUE付きのWHEREを使用して、テーブルの部分集合に一意性を強制する例が考えられます。 詳細は11.8を参照してください。

WHERE句内の式では、元となるテーブルの列のみを参照できます。 しかし、インデックスを付加する列だけではなく、全ての列を使用することができます。 また、現在、副問い合わせと集約式については、WHEREで使用することができません。 同一の制限は、式で表されたインデックスのフィールドにも適用されます。

インデックスの定義で使用される全ての関数と演算子は、不変(immutable)でなければなりません。 つまり、結果は入力引数にのみに依存し、(他のテーブルの内容や現時刻などの)外部からの影響を受けてはなりません。 この制限によって、インデックスの動作が十分定義されていることが保証されます。 インデックス式やWHERE句にユーザ定義の関数を使用する場合、関数を作成する際、IMMUTABLE(不変)オプションを付けることを忘れないでください。

パラメータ

UNIQUE

インデックスを(既にデータがある状態で)作成する時、およびテーブルにデータを追加する時に、テーブル内の値が重複していないかを検査します。 重複エントリを生じるデータの挿入または更新はエラーとなります。

一意性インデックスがパーティションテーブルに適用されるときには、追加的な制限が適用されます。CREATE TABLEを参照してください。

CONCURRENTLY

このオプションを使用すると、PostgreSQLは、対象テーブルに対する同時挿入、更新、削除を防止するようなロックを獲得せずにインデックスを作成します。 通常のインデックス作成処理では、完了するまで対象テーブルへの書き込みはできません(読み取りは可能です)。 このオプションを使用する際に注意しなければならない点が複数あります。 インデックスの同時作成を参照してください。

IF NOT EXISTS

同じ名前のリレーションが既に存在している場合にエラーとしません。 この場合、注意が発行されます。 既存のインデックスが、作成されようとしていたものと類似のものである保証は全くないことに注意してください。 IF NOT EXISTSを指定する場合はインデックス名が必須です。

INCLUDE

オプションのINCLUDE句は非キー列としてインデックスに含める列のリストを指定します。 非キー列をインデックススキャンの検索条件に使うことはできません。また、インデックスで何であれ一意性制約や排他制約を強制する目的に対しても無視されます。 しかしながら、インデックスオンリースキャンは、インデックスエントリから値を直接得ることができるので、インデックスのテーブルを見に行く必要なく、非キー列の内容を返すことができます。 このように非キー列の追加は、そうでないとできないインデックスオンリースキャンを利用可能にします。

インデックスに非キー列を加えることには、特に幅広の列については、保守的であるのが賢明です。 インデックス列がインデックス型で許される最大サイズを超えた場合、データ挿入は失敗してしまいます。 いかなる場合でも、非キー列はインデックスのテーブルからデータを複製して、インデックスのサイズを膨張させます。よって、潜在的に検索を遅くします。

INCLUDE句にある列リストは適合した演算子クラスを必要としません。ここには与えられたアクセスメソッドに対して定義された演算子クラスを持たないデータ型の列を含めることができます。

インデックスオンリースキャンで使うことができないため、INCLUDEする列に式は対応していません。

今のところ本機能はB-treeインデックスアクセスメソッドにのみ対応しています。 B-treeインデックスではINCLUDE句にリストされた列の値は、ヒープタプルに対応するリーフタプルに含まれますが、ツリーを辿るのに使われる上位レベルのインデックスエントリには含まれません。

name

作成するインデックスの名前です。 この名前には、スキーマ名を含めることはできません。 インデックスは、常にその親テーブルと同じスキーマに作成されます。 この名前を省略すると、PostgreSQLはその親テーブルの名前とインデックス付けされる列名に基づいた適切な名前を選びます。

ONLY

テーブルがパーティションテーブルであっても、パーティションにインデックス作成を再帰的に実行しないことを示します。 デフォルトでは再帰実行します。

table_name

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

method

使用するインデックスメソッドの名前です。 btreehashgistspgistginbrinから選択します。 デフォルトのメソッドはbtreeです。

column_name

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

expression

テーブル上の1つ以上の列を使用した式です。 通常この式は、構文で示した通り括弧で囲む必要があります。 しかし、式が関数呼び出し形式になっている場合は括弧を省略することができます。

collation

インデックスで使用する照合順序の名前です。 デフォルトではインデックスはインデックス付け対象の列で宣言された照合順序またはインデックス付け対象の式の結果の照合順序を使用します。 デフォルト以外の照合順序を使用する式を含む問い合わせで、デフォルト以外の照合順序を持つインデックスが有用になるかもしれません。

opclass

演算子クラスの名前です。詳細は下記を参照してください。

ASC

正方向のソート順を指定します(これがデフォルトです)。

DESC

逆方向のソート順を指定します。

NULLS FIRST

NULLを非NULLより前にソートすることを指定します。 これはDESCが指定された場合のデフォルトです。

NULLS LAST

NULLを非NULLより後にソートすることを指定します。 これはDESCが指定されない場合のデフォルトです。

storage_parameter

インデックスメソッド固有の格納パラメータの名前です。 インデックス格納パラメータを参照してください。

tablespace_name

インデックスを生成するテーブル空間です。 指定されなかった場合、default_tablespace、もし一時テーブル上のインデックスであれば、temp_tablespacesが考慮されます。

predicate

部分インデックス用の制約式です。

インデックス格納パラメータ

WITH句を使うと、インデックスの格納パラメータを指定できます。 インデックスメソッドはそれぞれ固有の設定可能な格納パラメータを持ちます。 B-tree、ハッシュ、GiSTおよびSP-GiSTといったインデックスはすべて次のパラメータを受け付けます。

fillfactor

インデックス用のフィルファクタは割合(パーセント)で、インデックスメソッドがインデックスページをまとめ上げる時にどの程度ページを使用するかを決定するものです。 B-treeでは、リーフページは初期インデックス構築時と右側(新しい最大キー値を追加する方向)にインデックスを拡張する時にこの割合分までページを使用します。 その後ページすべてが完全に使用されると分割され、インデックスの効果が徐々に劣化します。 B-treeのデフォルトのフィルファクタは90ですが、10から100までの任意の整数値を設定することができます。 テーブルが静的な場合、100が最善でインデックスの物理サイズを最小化できます。 更新が非常に多い場合は、ページ分割の頻度を少なくするために、より小さなフィルファクタを設定する方が良いです。 この他のインデックスメソッドでは、フィルファクタを異なる意味で使用しますが、おおよそは同じです。 メソッドによってフィルファクタのデフォルト値は異なります。

B-treeインデックスは以下パラメータを追加的に受け付けます。

vacuum_cleanup_index_scale_factor

vacuum_cleanup_index_scale_factorのインデックス毎の値です。

GiSTインデックスではさらに以下のパラメータを受け付けます。

buffering

64.4.1で説明するバッファリング構築技術をインデックスを構築する時に使用するかどうかを決定します。 OFFで無効に、ONで有効になります。 またAUTOと指定すると、最初は無効ですが、 インデックスサイズがeffective_cache_sizeに達した後はその場で有効になります。 デフォルトはAUTOです。

GINインデックスでは以下の異なるパラメータを受け付けます。

fastupdate

この設定は66.4.1で説明する高速更新技法を使用するかどうかを制御します。 これは論理値パラメータであり、ONは高速更新を有効に、OFFは無効にします。 (19.1の説明のように、ONOFFと異なる記載も可能です。) デフォルトはONです。

注記

ALTER INDEXを使用してfastupdateを無効にすることにより、以後の挿入は待機中のインデックス項目リストに入らないようになります。 しかし、このコマンド自体はこれまでの項目を吐き出しません。 確実に待機中のリストを空にするためには、続いてテーブルをVACUUMするか、gin_clean_pending_list関数を呼び出すのが良いでしょう。

gin_pending_list_limit

gin_pending_list_limitのカスタムパラメータです。 値はキロバイト単位で指定します。

BRINインデックスは別のパラメータを受け入れます。

pages_per_range

BRINインデックスの各エントリについて1つのブロックレンジを構成するテーブルブロックの数を定義します(詳しくは67.1参照)。 デフォルトは128です。

autosummarize

次のページへの挿入が検知された時に、いつでも直前のページに対してサマリー処理を起動するかどうかを定義します。

インデックスの同時作成

インデックスの作成が、通常のデータベース操作に影響を与えることがあります。 通常PostgreSQLは、対象テーブルに対する書き込みをロックしてから、対象テーブル全体のインデックス作成を一度のスキャンで行います。 他のトランザクションはテーブルを読み取ることはできますが、対象テーブル内の行を挿入、更新、削除しようとすると、インデックス作成が完了するまでブロックされます。 実行中の運用状態のデータベースシステムの場合、これは重大な影響を与える可能性があります。 非常に大規模なテーブルに対するインデックス作成は何時間もかかることがあり得ます。 また小規模なテーブルであっても、インデックス作成により、運用状態のシステムとしては受け入れられないほど長い時間、書き込みロックがかかる可能性があります。

PostgreSQLは書き込みをロックしないインデックス作成もサポートしています。 CREATE INDEXCONCURRENTLYオプションをつけることでこの方式が行われます。 このオプションを使うと、PostgreSQLはテーブルを2回スキャンしなければなりません。 さらに、潜在的にそのインデックスを更新または使用する可能性がある、実行中のすべてのトランザクションが終わるまで待機しなければなりません。 したがって、この方式は通常の方式よりも総作業時間がかかり、また、完了するまでの時間が非常に長くなります。 しかし、インデックス作成中に通常の操作を行い続けることができますので、この方式は運用環境での新規インデックス作成に有用です。 もちろん、インデックス作成によりCPUや入出力に余分に負荷がかかりますので、他の操作が低速になる可能性があります。

同時実行インデックス構築では実際には、1つのトランザクションでシステムカタログに登録され、さらに2つのトランザクションで2つのテーブルスキャンが起こります。 各テーブルスキャンの前に、インデックス構築はテーブルを修正した実行中のトランザクションが終了するのを待たなければなりません。 2回目のスキャンの後、インデックス構築は2回目のスキャンより前のスナップショット(第13章参照)を持つすべてのトランザクションが終了するのを待たなければなりません。 その後でようやく、インデックスは利用可能であると印が付けられ、CREATE INDEXコマンドが終了します。 しかし、それでもインデックスは問い合わせに対して即座に利用可能であるとは限りません。 最悪の場合、インデックス構築開始前のトランザクションが存在する間は利用できません。

たとえばデッドロックや一意性インデックスにおける一意性違反など、テーブルスキャン中に問題が発生すると、CREATE INDEXは失敗しますが、無効なインデックスが残ってしまいます。 こうしたインデックスは完全ではない可能性がありますので、問い合わせの際には無視されます。 しかし、更新時にオーバーヘッドがかかります。 psql\dコマンドでは、こうしたインデックスをINVALIDとして報告します。

postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 col    | integer |           |          | 
Indexes:
    "idx" btree (col) INVALID

こうした場合の推奨復旧方法は、インデックスを削除し、再度CREATE INDEX CONCURRENTLYを実行することです。 (他にもREINDEXを使用したインデックスの再作成という方法もあります。 しかし、REINDEXは同時作成をサポートしていませんので、この方法は魅力がありません。)

この他に一意性インデックスを同時作成する場合の注意事項があります。 2回目のテーブルスキャンが始まる時点で、他のトランザクションに対する一意性制約が既に有効になっているという点です。 これは、インデックスが使用できるようになる前やインデックス作成が最終的に失敗したとしても、制約違反が他のトランザクションで報告されてしまうことを意味します。 また、2回目のスキャン中に失敗した後も、無効なインデックスによる一意性制約は強制され続けます。

式インデックスや部分インデックスの同時作成もサポートされています。 式の評価中にエラーが発生した場合も、上で説明した一意性制約違反と同様な状況が発生します。

同一テーブルに対する通常のインデックス作成処理は複数並行して行うことができます。 しかし、あるテーブルに対するインデックスの同時作成は一度に1つしか行うことができません。 また、どちらの場合でもインデックス作成中のテーブルのスキーマ変更はできません。 この他に、通常のCREATE INDEXコマンドはトランザクションブロック内で実行させることができますが、CREATE INDEX CONCURRENTLYは実行させることができないという相違点があります。

注釈

インデックスが、どのような時に使用され、どのような時に使用されないか、また、どのような場合に有用かといった情報については第11章を参照してください。

現在は、B-tree、GiST、GIN、BRINインデックスメソッドのみが、複数列に対するインデックスをサポートしています。 指定できる列は、デフォルトでは32個までです(この制限はPostgreSQLのコンパイル時に変更できます)。 現在、B-treeのみが一意性インデックスをサポートしています。

演算子クラスは、インデックスのそれぞれの列に指定することができます。 演算子クラスは、その列のインデックスが使う演算子を識別します。 例えば、4バイト整数に対するB-treeインデックスには、int4_opsクラスを使います。 この演算子クラスには、4バイト整数の比較関数が含まれています。 実際の使用では、通常、列のデータ型のデフォルト演算子クラスで十分です。 演算子クラスを保持する主な理由は、データ型の中には有意な順序を2つ以上持つものがあるかもしれないからです。 例えば、複素数のソートで絶対値または実数部のどちらかを使いたい場合がありえます。 これを実現するには、データ型として2つの演算子クラスを定義し、インデックスを作る時に適切なクラスを選択します。 演算子クラスについての詳細は、11.938.15を参照してください。

CREATE INDEXがパーティションテーブルに実行されたときのデフォルトの振る舞いは、全パーティションが一致するインデックスを持つようにする全パーティションへの再帰的な実行です。 各パーティションは最初に同等のインデックスが既に存在するかの判断のために検査され、存在するなら作成するインデックスに対するパーティションインデックスとしてアタッチされます。新たに作成するインデックスが既存インデックスの親インデックスとなります。 一致するインデックスが存在しない場合、新たなインデックスが作られて、自動的にアタッチされます。各パーティションの新たなインデックス名は、コマンドでインデックスが指定されなかった場合と同様に決定されます。 ONLYオプションが指定された場合、再帰処理は行われず、そのインデックスは無効と印付けされます。 (ALTER INDEX ... ATTACH PARTITIONは、ひとたび全パーティションが一致するインデックスを得たなら、インデックスを有効に印付けします) しかしながら、ONLYが指定されたとしても、将来にCREATE TABLE ... PARTITION OFを使って作成されるあらゆるパーティションは自動的に一致するインデックスを持つことに注意してください。

順序付きスキャンをサポートするインデックスメソッド(現時点ではB-Treeのみ)では、ASCDESCNULLS FIRSTNULLS LAST句(省略可能)をオプションで指定し、インデックスのソート順を変更することができます。 順序付きインデックスは正方向にも逆方向にもスキャンすることができますので、単一列に対するDESCインデックスは通常は有用ではありません。 このソート順序はすでに通常のインデックスを使用して実現できます。 これらのオプションの価値は、SELECT ... ORDER BY x ASC, y DESCなどの順序指定が混在する問い合わせによって要求されるソート順に一致する、複数列に対するインデックスを作成できる点です。 NULLSオプションは、インデックスに基づいた問い合わせにおいてソート処理を省略するためにNULLのソート順を低くする動作をサポートする必要がある場合に有用です。 デフォルトの動作はNULLのソート順を高くするです。

ほとんどのインデックスメソッドにおいて、インデックス作成速度はmaintenance_work_memの設定に依存します。 より大きな値を設定すると、インデックス作成に必要となる時間が短縮されます。 ただし、実際に使用できるメモリ量を超えるほど大きくすると、マシンがスワップ状態になり、遅くなります。

PostgreSQLはテーブルの行をより高速に処理するために複数CPUを効かせてインデックスを作成できます。 この機能はパラレルインデックス作成と呼ばれています。 パラレルでのインデックス作成をサポートしているインデックスメソッド(今のところB-Treeのみ)に対して、maintenance_work_memでは、いくつのワーカプロセスが実行されているかに拘らず、各インデックス作成操作で使うことができる全体のメモリの最大量を指定します。 一般にコストモデルは、もしあるなら、どれだけの数のワーカプロセスを要求すべきかを自動的に決定します。

パラレルインデックス作成ではmaintenance_work_memを増やすことで、同様の逐次インデックス作成ではほとんど恩恵がみられない場合でも恩恵があるかもしれません。 パラレルワーカはmaintenance_work_mem全体の内、少なくとも32MBの割り当て分を持たなければならないため、maintenance_work_memは要求されるワーカプロセス数に影響を及ぼすかもしれないことに注意してください。 また、リーダープロセスに対しても32MBの割り当てを残さなければなりません。 max_parallel_maintenance_workersを増やすことで、より多くのワーカが使用できるようになるかもしれません。これは、インデックス作成が既にI/Oバウンドであるのでない限り、インデックス作成の所要時間を減らすでしょう。 もちろん、休止している十分なCPU容量もある前提です。

ALTER TABLEを通してparallel_workersの値を設定することで、テーブルに対してCREATE INDEXでどれだけのワーカプロセス数が要求されるかを、直接に調整できます。 これはコストモデルを完全に無視して、maintenance_work_memがパラレルワーカの要求数に影響を与えることを回避します。 ALTER TABLEを通してparallel_workersを0に設定することは、そのテーブルに対するパラレルインデックス作成を全ての場合に無効化します。

ヒント

インデックス作成のチューニング一部としてparallel_workersを設定した後、これをリセットしたいかもしれません。 parallel_workers全てのパラレルテーブルスキャンに影響を与えるので、これは不注意な問い合わせプランの変更を回避します。

CONCURRENTLYオプションを伴うCREATE INDEXは特に制限なくパラレル作成をサポートしますが、実際には最初のテーブルスキャンだけがパラレルに実行されます。

インデックスを削除するには、DROP INDEXを使用してください。

以前のPostgreSQLにはR-treeインデックスメソッドがありました。 GiSTメソッドに比べて大きな利点がありませんでしたので、このメソッドは削除されました。 古いデータベースからGiSTへの変換を簡単にするため、USING rtreeが指定された場合、CREATE INDEXUSING gistと解釈します。

テーブルfilmsの列titleに一意性のB-treeインデックスを作成します。

CREATE UNIQUE INDEX title_idx ON films (title);

テーブルfilmsの列titleに、列directorと列ratingを含めて、一意性のB-treeインデックスを作成します。

CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating);

大文字小文字を区別しない検索が効率的になるように、式lower(title)に対してインデックスを作成します。

CREATE INDEX ON films ((lower(title)));

(この例では、インデックス名を省略することを選びました。 このためシステムがfilms_lower_idxなどという名前を選ぶことになります。)

デフォルト以外の照合順序でインデックスを作成します。

CREATE INDEX title_idx_german ON films (title COLLATE "de_DE");

デフォルトと異なるNULLのソート順を指定したインデックスを作成します。

CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);

デフォルトと異なるフィルファクタを持つインデックスを作成します。

CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);

高速更新を無効にしてGINインデックスを作成します。

CREATE INDEX gin_idx ON documents_table USING GIN (locations) WITH (fastupdate = off);

テーブルfilms上の列codeに対するインデックスを作成します。 また、このインデックスをテーブル空間indexspace内に生成します。

CREATE INDEX code_idx ON films (code) TABLESPACE indexspace;

変換関数の結果に対するbox操作を効率的に使用できるようにpoint属性にGiSTインデックスを作成します。

CREATE INDEX pointloc
    ON points USING gist (box(location,location));
SELECT * FROM points
    WHERE box(location,location) && '(0,0),(1,1)'::box;

対象テーブルへの書き込みをロックせずにインデックスを作成します。

CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

互換性

CREATE INDEXPostgreSQLの拡張です。 標準SQLにはインデックスについての規定はありません。

関連項目

ALTER INDEX, DROP INDEX