説明

<command>CREATE INDEX</command> constructs an index on the specified column(s) of the specified relation, which can be a table or a materialized view. Indexes are primarily used to enhance database performance (though inappropriate use can result in slower performance). CREATE INDEXは、指定したリレーションの指定した列(複数可)に対するインデックスを作ります。 リレーションとしてテーブルまたはマテリアライズドビューを取ることができます。 インデックスは主にデータベースの性能を向上するために使われます （しかし、インデックスの不適切な使用は性能の低下につながる可能性があります）。

The key field(s) for the index are specified as column names, or alternatively as expressions written in parentheses. Multiple fields can be specified if the index method supports multicolumn indexes. インデックスのキーフィールドは、列名、または括弧に囲まれた式として指定されます。 インデックスメソッドが複数列に対するインデックスをサポートする場合は、複数のフィールドを指定できます。

An index field can be an expression computed from the values of one or more columns of the table row. This feature can be used to obtain fast access to data based on some transformation of the basic data. For example, an index computed on <literal>upper(col)</literal> would allow the clause <literal>WHERE upper(col) = 'JIM'</literal> to use an index. インデックスのフィールドとして、テーブル行の1つ以上の列の値から計算する式を指定できます。 この機能は、元のデータに何らかの変換を加えた値を基とするデータへの高速なアクセスを行う手段として使用することができます。 例えば、upper(col)という計算に基づくインデックスがあれば、WHERE upper(col) = 'JIM'という句ではインデックスを使用することができます。

<productname>PostgreSQL</productname> provides the index methods B-tree, hash, GiST, SP-GiST, GIN, and BRIN. Users can also define their own index methods, but that is fairly complicated. PostgreSQLはB-tree、ハッシュ、GiST、SP-GiST、GIN、BRINのインデックスメソッドを用意しています。 ユーザが独自にインデックスメソッドを定義することもできますが、これはかなり複雑です。

When the <literal>WHERE</literal> clause is present, a <firstterm>partial index</firstterm> is created. A partial index is an index that contains entries for only a portion of a table, usually a portion that is more useful for indexing than the rest of the table. For example, if you have a table that contains both billed and unbilled orders where the unbilled orders take up a small fraction of the total table and yet that is an often used section, you can improve performance by creating an index on just that portion. Another possible application is to use <literal>WHERE</literal> with <literal>UNIQUE</literal> to enforce uniqueness over a subset of a table. See <xref linkend="indexes-partial"/> for more discussion. WHERE句が存在する場合、部分インデックスが作成されます。 部分インデックスは、テーブルの一部、通常は、テーブルの中でよりインデックスが有用な部分のみのエントリを持つインデックスです。 例えば、請求済みの注文と未請求の注文を情報として持つテーブルがあり、テーブル全体における未請求の注文の割合が小さく、かつ、頻繁に使用される場合、未請求の注文のみにインデックスを作成することで性能を向上できます。 部分インデックスのその他の利用方法として、UNIQUE付きのWHEREを使用して、テーブルの部分集合に一意性を強制する例が考えられます。 詳細は11.8を参照してください。

The expression used in the <literal>WHERE</literal> clause can refer only to columns of the underlying table, but it can use all columns, not just the ones being indexed. Presently, subqueries and aggregate expressions are also forbidden in <literal>WHERE</literal>. The same restrictions apply to index fields that are expressions. WHERE句内の式では、元となるテーブルの列のみを参照できます。 しかし、インデックスを付加する列だけではなく、全ての列を使用することができます。 また、現在、副問い合わせと集約式については、WHEREで使用することができません。 同一の制限は、式で表されたインデックスのフィールドにも適用されます。

All functions and operators used in an index definition must be <quote>immutable</quote>, that is, their results must depend only on their arguments and never on any outside influence (such as the contents of another table or the current time). This restriction ensures that the behavior of the index is well-defined. To use a user-defined function in an index expression or <literal>WHERE</literal> clause, remember to mark the function immutable when you create it. インデックスの定義で使用される全ての関数と演算子は、「不変」（immutable）でなければなりません。 つまり、結果は入力引数にのみに依存し、（他のテーブルの内容や現時刻などの）外部からの影響を受けてはなりません。 この制限によって、インデックスの動作が十分定義されていることが保証されます。 インデックス式やWHERE句にユーザ定義の関数を使用する場合、関数を作成する際、IMMUTABLE（不変）オプションを付けることを忘れないでください。

パラメータ

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

注釈

See <xref linkend="indexes"/> for information about when indexes can be used, when they are not used, and in which particular situations they can be useful. インデックスが、どのような時に使用され、どのような時に使用されないか、また、どのような場合に有用かといった情報については第11章を参照してください。

Currently, only the B-tree, GiST, GIN, and BRIN index methods support multiple-key-column indexes. Whether there can be multiple key columns is independent of whether <literal>INCLUDE</literal> columns can be added to the index. Indexes can have up to 32 columns, including <literal>INCLUDE</literal> columns. (This limit can be altered when building <productname>PostgreSQL</productname>.) Only B-tree currently supports unique indexes. 現在は、B-tree、GiST、GIN、BRINインデックスメソッドのみが、複数キー列に対するインデックスをサポートしています。 複数キー列があるかどうかはインデックスにINCLUDE列を追加できるかどうかとは独立です。 インデックスはINCLUDE列を含めて32列まで持てます。 （この制限はPostgreSQLのコンパイル時に変更できます。） 現在、B-treeのみが一意性インデックスをサポートしています。

An <firstterm>operator class</firstterm> with optional parameters can be specified for each column of an index. The operator class identifies the operators to be used by the index for that column. For example, a B-tree index on four-byte integers would use the <literal>int4_ops</literal> class; this operator class includes comparison functions for four-byte integers. In practice the default operator class for the column's data type is usually sufficient. The main point of having operator classes is that for some data types, there could be more than one meaningful ordering. For example, we might want to sort a complex-number data type either by absolute value or by real part. We could do this by defining two operator classes for the data type and then selecting the proper class when creating an index. More information about operator classes is in <xref linkend="indexes-opclass"/> and in <xref linkend="xindex"/>. オプションのパラメータのついた演算子クラスは、インデックスのそれぞれの列に指定することができます。 演算子クラスは、その列のインデックスが使う演算子を識別します。 例えば、4バイト整数に対するB-treeインデックスには、int4_opsクラスを使います。 この演算子クラスには、4バイト整数の比較関数が含まれています。 実際の使用では、通常、列のデータ型のデフォルト演算子クラスで十分です。 演算子クラスを保持する主な理由は、データ型の中には有意な順序を2つ以上持つものがあるかもしれないからです。 例えば、複素数のソートで絶対値または実数部のどちらかを使いたい場合がありえます。 これを実現するには、データ型として2つの演算子クラスを定義し、インデックスを作る時に適切なクラスを選択します。 演算子クラスについての詳細は、11.10と38.16を参照してください。

When <literal>CREATE INDEX</literal> is invoked on a partitioned table, the default behavior is to recurse to all partitions to ensure they all have matching indexes. Each partition is first checked to determine whether an equivalent index already exists, and if so, that index will become attached as a partition index to the index being created, which will become its parent index. If no matching index exists, a new index will be created and automatically attached; the name of the new index in each partition will be determined as if no index name had been specified in the command. If the <literal>ONLY</literal> option is specified, no recursion is done, and the index is marked invalid. (<command>ALTER INDEX ... ATTACH PARTITION</command> marks the index valid, once all partitions acquire matching indexes.) Note, however, that any partition that is created in the future using <command>CREATE TABLE ... PARTITION OF</command> will automatically have a matching index, regardless of whether <literal>ONLY</literal> is specified. CREATE INDEXがパーティションテーブルに実行されたときのデフォルトの振る舞いは、全パーティションが一致するインデックスを持つようにする全パーティションへの再帰的な実行です。 各パーティションは最初に同等のインデックスが既に存在するかの判断のために検査され、存在するなら作成するインデックスに対するパーティションインデックスとしてアタッチされます。新たに作成するインデックスが既存インデックスの親インデックスとなります。 一致するインデックスが存在しない場合、新たなインデックスが作られて、自動的にアタッチされます。各パーティションの新たなインデックス名は、コマンドでインデックスが指定されなかった場合と同様に決定されます。 ONLYオプションが指定された場合、再帰処理は行われず、そのインデックスは無効と印付けされます。 （ALTER INDEX ... ATTACH PARTITIONは、ひとたび全パーティションが一致するインデックスを得たなら、インデックスを有効に印付けします） しかしながら、ONLYが指定されたとしても、将来にCREATE TABLE ... PARTITION OFを使って作成されるあらゆるパーティションは自動的に一致するインデックスを持つことに注意してください。

For index methods that support ordered scans (currently, only B-tree), the optional clauses <literal>ASC</literal>, <literal>DESC</literal>, <literal>NULLS FIRST</literal>, and/or <literal>NULLS LAST</literal> can be specified to modify the sort ordering of the index. Since an ordered index can be scanned either forward or backward, it is not normally useful to create a single-column <literal>DESC</literal> index &mdash; that sort ordering is already available with a regular index. The value of these options is that multicolumn indexes can be created that match the sort ordering requested by a mixed-ordering query, such as <literal>SELECT ... ORDER BY x ASC, y DESC</literal>. The <literal>NULLS</literal> options are useful if you need to support <quote>nulls sort low</quote> behavior, rather than the default <quote>nulls sort high</quote>, in queries that depend on indexes to avoid sorting steps. 順序付きスキャンをサポートするインデックスメソッド（現時点ではB-Treeのみ）では、ASC、DESC、NULLS FIRST、NULLS LAST句(省略可能)をオプションで指定し、インデックスのソート順を変更することができます。 順序付きインデックスは正方向にも逆方向にもスキャンすることができますので、単一列に対するDESCインデックスは通常は有用ではありません。 このソート順序はすでに通常のインデックスを使用して実現できます。 これらのオプションの価値は、SELECT ... ORDER BY x ASC, y DESCなどの順序指定が混在する問い合わせによって要求されるソート順に一致する、複数列に対するインデックスを作成できる点です。 NULLSオプションは、インデックスに基づいた問い合わせにおいてソート処理を省略するために「NULLのソート順を低くする」動作をサポートする必要がある場合に有用です。 デフォルトの動作は「NULLのソート順を高くする」です。

The system regularly collects statistics on all of a table's columns. Newly-created non-expression indexes can immediately use these statistics to determine an index's usefulness. For new expression indexes, it is necessary to run <link linkend="sql-analyze"><command>ANALYZE</command></link> or wait for the <link linkend="autovacuum">autovacuum daemon</link> to analyze the table to generate statistics for these indexes. システムは定期的にテーブルの列すべての統計情報を集めています。 新しく作られた、式でないインデックスは、インデックスの有用性を決定するのにその統計情報をすぐに使うことができます。 新しい式インデックスに対しては、そのインデックスのための統計情報を生成するために、ANALYZEを実行するか、autovacuumデーモンがそのテーブルを解析するのを待つことが必要です。

For most index methods, the speed of creating an index is dependent on the setting of <xref linkend="guc-maintenance-work-mem"/>. Larger values will reduce the time needed for index creation, so long as you don't make it larger than the amount of memory really available, which would drive the machine into swapping. ほとんどのインデックスメソッドにおいて、インデックス作成速度はmaintenance_work_memの設定に依存します。 より大きな値を設定すると、インデックス作成に必要となる時間が短縮されます。 ただし、実際に使用できるメモリ量を超えるほど大きくすると、マシンがスワップ状態になり、遅くなります。

<productname>PostgreSQL</productname> can build indexes while leveraging multiple CPUs in order to process the table rows faster. This feature is known as <firstterm>parallel index build</firstterm>. For index methods that support building indexes in parallel (currently, only B-tree), <varname>maintenance_work_mem</varname> specifies the maximum amount of memory that can be used by each index build operation as a whole, regardless of how many worker processes were started. Generally, a cost model automatically determines how many worker processes should be requested, if any. PostgreSQLはテーブルの行をより高速に処理するために複数CPUを効かせてインデックスを作成できます。 この機能はパラレルインデックス作成と呼ばれています。 パラレルでのインデックス作成をサポートしているインデックスメソッド（今のところB-Treeのみ）に対して、maintenance_work_memでは、いくつのワーカープロセスが実行されているかに拘らず、各インデックス作成操作で使うことができる全体のメモリの最大量を指定します。 一般にコストモデルは、もしあるなら、どれだけの数のワーカープロセスを要求すべきかを自動的に決定します。

Parallel index builds may benefit from increasing <varname>maintenance_work_mem</varname> where an equivalent serial index build will see little or no benefit. Note that <varname>maintenance_work_mem</varname> may influence the number of worker processes requested, since parallel workers must have at least a <literal>32MB</literal> share of the total <varname>maintenance_work_mem</varname> budget. There must also be a remaining <literal>32MB</literal> share for the leader process. Increasing <xref linkend="guc-max-parallel-maintenance-workers"/> may allow more workers to be used, which will reduce the time needed for index creation, so long as the index build is not already I/O bound. Of course, there should also be sufficient CPU capacity that would otherwise lie idle. パラレルインデックス作成ではmaintenance_work_memを増やすことで、同様の逐次インデックス作成ではほとんど恩恵がみられない場合でも恩恵があるかもしれません。 パラレルワーカーはmaintenance_work_mem全体の内、少なくとも32MBの割り当て分を持たなければならないため、maintenance_work_memは要求されるワーカープロセス数に影響を及ぼすかもしれないことに注意してください。 また、リーダープロセスに対しても32MBの割り当てを残さなければなりません。 max_parallel_maintenance_workersを増やすことで、より多くのワーカーが使用できるようになるかもしれません。これは、インデックス作成が既にI/Oバウンドであるのでない限り、インデックス作成の所要時間を減らすでしょう。 もちろん、休止している十分なCPU容量もある前提です。

Setting a value for <literal>parallel_workers</literal> via <link linkend="sql-altertable"><command>ALTER TABLE</command></link> directly controls how many parallel worker processes will be requested by a <command>CREATE INDEX</command> against the table. This bypasses the cost model completely, and prevents <varname>maintenance_work_mem</varname> from affecting how many parallel workers are requested. Setting <literal>parallel_workers</literal> to 0 via <command>ALTER TABLE</command> will disable parallel index builds on the table in all cases. ALTER TABLEを通してparallel_workersの値を設定することで、テーブルに対してCREATE INDEXでどれだけのワーカープロセス数が要求されるかを、直接に調整できます。 これはコストモデルを完全に無視して、maintenance_work_memがパラレルワーカーの要求数に影響を与えることを回避します。 ALTER TABLEを通してparallel_workersを0に設定することは、そのテーブルに対するパラレルインデックス作成を全ての場合に無効化します。

While <command>CREATE INDEX</command> with the <literal>CONCURRENTLY</literal> option supports parallel builds without special restrictions, only the first table scan is actually performed in parallel. CONCURRENTLYオプションを伴うCREATE INDEXは特に制限なくパラレル作成をサポートしますが、実際には最初のテーブルスキャンだけがパラレルに実行されます。

Use <link linkend="sql-dropindex"><command>DROP INDEX</command></link> to remove an index. インデックスを削除するには、DROP INDEXを使用してください。

Like any long-running transaction, <command>CREATE INDEX</command> on a table can affect which tuples can be removed by concurrent <command>VACUUM</command> on any other table. 他の時間のかかるトランザクションと同じく、あるテーブルに対するCREATE INDEXは、その他のテーブルに対する同時実行中のVACUUMによりどのタプルが削除できるかに影響します。

Prior releases of <productname>PostgreSQL</productname> also had an R-tree index method. This method has been removed because it had no significant advantages over the GiST method. If <literal>USING rtree</literal> is specified, <command>CREATE INDEX</command> will interpret it as <literal>USING gist</literal>, to simplify conversion of old databases to GiST. 以前のPostgreSQLにはR-treeインデックスメソッドがありました。 GiSTメソッドに比べて大きな利点がありませんでしたので、このメソッドは削除されました。 古いデータベースからGiSTへの変換を簡単にするため、USING rtreeが指定された場合、CREATE INDEXはUSING gistと解釈します。

Each backend running <command>CREATE INDEX</command> will report its progress in the <structname>pg_stat_progress_create_index</structname> view. See <xref linkend="create-index-progress-reporting"/> for details. CREATE INDEXを実行している各バックエンドはその進捗をpg_stat_progress_create_indexビューで報告します。 詳細は28.4.4を参照してください。

例

To create a unique B-tree index on the column <literal>title</literal> in the table <literal>films</literal>: テーブルfilmsの列titleに一意性のB-treeインデックスを作成します。

CREATE UNIQUE INDEX title_idx ON films (title);

To create a unique B-tree index on the column <literal>title</literal> with included columns <literal>director</literal> and <literal>rating</literal> in the table <literal>films</literal>: テーブルfilmsの列titleに、列directorと列ratingを含めて、一意性のB-treeインデックスを作成します。

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

To create a B-Tree index with deduplication disabled: 重複排除を無効にしたB-Treeを作成します。

CREATE INDEX title_idx ON films (title) WITH (deduplicate_items = off);

To create an index on the expression <literal>lower(title)</literal>, allowing efficient case-insensitive searches: 大文字小文字を区別しない検索が効率的になるように、式lower(title)に対してインデックスを作成します。

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

(In this example we have chosen to omit the index name, so the system will choose a name, typically <literal>films_lower_idx</literal>.) (この例では、インデックス名を省略することを選びました。 このためシステムがfilms_lower_idxなどという名前を選ぶことになります。)

To create an index with non-default collation: デフォルト以外の照合順序でインデックスを作成します。

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

To create an index with non-default sort ordering of nulls: デフォルトと異なるNULLのソート順を指定したインデックスを作成します。

CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);

To create an index with non-default fill factor: デフォルトと異なるフィルファクタを持つインデックスを作成します。

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

To create a <acronym>GIN</acronym> index with fast updates disabled: 高速更新を無効にしてGINインデックスを作成します。

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

To create an index on the column <literal>code</literal> in the table <literal>films</literal> and have the index reside in the tablespace <literal>indexspace</literal>: テーブルfilms上の列codeに対するインデックスを作成します。 また、このインデックスをテーブル空間indexspace内に生成します。

CREATE INDEX code_idx ON films (code) TABLESPACE indexspace;

To create a GiST index on a point attribute so that we can efficiently use box operators on the result of the conversion function: 変換関数の結果に対する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;

To create an index without locking out writes to the table: 対象テーブルへの書き込みをロックせずにインデックスを作成します。

CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

互換性

<command>CREATE INDEX</command> is a <productname>PostgreSQL</productname> language extension. There are no provisions for indexes in the SQL standard. CREATE INDEXはPostgreSQLの拡張です。 標準SQLにはインデックスについての規定はありません。

関連項目