PolarDB:CREATE INDEX

説明

CREATE INDEXは、指定されたリレーションの指定された列 (テーブルまたはマテリアライズドビュー) にインデックスを作成します。 インデックスは主にデータベースのパフォーマンスを向上させるために使用されます (ただし、不適切な使用ではパフォーマンスが低下する可能性があります) 。

インデックスのキーフィールドは、列名として指定するか、括弧内に記述された式として指定します。 インデックスメソッドがマルチカラムインデックスをサポートしている場合、複数のフィールドを指定できます。

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

PolarDBは、B-tree、hash、GiST、SP-GiST、GIN、およびBRINのインデックスメソッドを提供します。 ユーザーは独自のインデックスメソッドを定義することもできますが、それはかなり複雑です。

WHERE句が存在する場合、部分インデックスが作成されます。 部分インデックスは、テーブルの一部のみのエントリを含むインデックスです。通常は、テーブルの残りの部分よりもインデックス作成に役立つ部分です。 たとえば、請求された注文と請求されていない注文の両方を含むテーブルがあり、請求されていない注文がテーブル全体のごく一部を占めるにもかかわらず、よく使用されるセクションである場合、その部分だけにインデックスを作成することでパフォーマンスを向上させることができます。 別の可能なアプリケーションは、WHEREをUNIQUEとともに使用して、テーブルのサブセットに一意性を適用することです。

WHERE句で使用される式は、基になるテーブルの列のみを参照できますが、インデックスが作成されている列だけでなく、すべての列を使用できます。 現在、サブクエリと集計式もWHEREで禁止されています。 式であるインデックスフィールドにも同じ制限が適用されます。

インデックス定義で使用されるすべての関数と演算子は「不変」である必要があります。つまり、結果は引数にのみ依存し、外部の影響 (別のテーブルの内容や現在の時刻など) には依存しない必要があります。 この制限により、インデックスの動作が明確に定義されます。 インデックス式またはWHERE句でユーザー定義関数を使用するには、関数を作成するときに必ず不変とマークしてください。

概要

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [存在しない場合] name ] ON [ ONLY ] table_name [ USINGメソッド]
        ( { column_name | (式) } [ COLLATE照合順序] [ opclass [ ( opclass_parameter = value [, ...] ) ] ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...])
        [含まれる (column_name [, ...] ) ]
        [WITH ( storage_parameter [= value] [, ... ] ) ]
        [TABLESPACE tablespace_name]
        [どこ述語] 

パラメーター

UNIQUE: インデックスの作成時 (データが既に存在する場合) およびデータが追加されるたびに、システムがテーブル内の重複値をチェックします。 エントリが重複するデータを挿入または更新しようとすると、エラーが発生します。

一意のインデックスがパーティションテーブルに適用される場合は、追加の制限が適用されます。「CREATE TABLE」をご参照ください。

CONCURRENTLY: このオプションを使用すると、PostgreSQLは、テーブルへの同時挿入、更新、または削除を防止するロックを実行せずにインデックスをビルドします。 このオプションを使用する場合、注意すべき点がいくつかあります。以下にする「インデックスの同時構築」をご参照ください。

一時テーブルの場合、他のセッションではアクセスできないため、CREATE INDEXは常に非同時であり、非同時インデックスの作成は安価です。

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

INCLUDE: オプションのINCLUDE句は、非キー列としてインデックスに含まれる列のリストを指定します。 キー以外の列は、インデックススキャン検索資格では使用できず、インデックスによって強制される一意性または除外制約の目的では無視されます。 ただし、インデックスのみのスキャンでは、非キー列の内容はインデックスエントリから直接利用できるため、インデックスのテーブルにアクセスする必要なく、非キー列の内容を返すことができます。 したがって、非キー列の追加により、インデックスのみのスキャンを、そうでなければそれらを使用することができなかったクエリに使用することができる。

非キー列、特にワイド列をインデックスに追加することについては、保守的であることが賢明です。 インデックスタプルがインデックスタイプに許可されている最大サイズを超えると、データ挿入は失敗します。 いずれの場合も、非キー列はインデックスのテーブルからのデータを複製し、インデックスのサイズを大きくするため、検索が遅くなる可能性があります。 さらに、Bツリー重複排除は、非キー列を有するインデックスでは決して使用されない。

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

式はインデックスのみのスキャンでは使用できないため、含まれる列としてはサポートされません。

現在、Bツリー、GiST、およびSP-GiSTインデックスアクセス方法は、この機能をサポートしています。 これらのインデックスでは, INCLUDE句に記載されている列の値は, ヒープタプルに対応するリーフタプルに含まれますが, ツリーナビゲーションで使用する上位のインデックスエントリには含まれません。

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

ONLY: テーブルがパーティション分割されている場合、パーティションのインデックスの作成を再帰しないことを示します。 デフォルトは再帰です。

table_name: インデックスを作成するテーブルの名前 (スキーマ修飾) 。

method: 使用するインデックスメソッドの名前。 選択肢は、btree、hash、gist、spgist、gin、brin、またはbloomなどのユーザーがインストールしたアクセスメソッドです。 デフォルトのメソッドはbtreeです。

colum_name: テーブルの列の名前。

expression: テーブルの1つ以上の列に基づく式。 式は通常、構文に示すように、周囲に括弧を付けて記述する必要があります。 ただし、式が関数呼び出しの形式の場合は、括弧を省略できます。

collation: インデックスに使用する照合順序の名前。 既定では、インデックスは、インデックスを作成する列に対して宣言された照合順序、またはインデックスを作成する式の結果照合順序を使用します。 デフォルトでない照合順序を持つインデックスは、デフォルトでない照合順序を使用する式を含むクエリに役立ちます。

opclass: 演算子クラスの名前。 詳細については以下を参照されたい。

opclass_parameter: 演算子クラスパラメーターの名前。 詳細については以下を参照されたい。

ASC: 昇順の並べ替え順序 (デフォルト) を指定します。

DESC: ソート順の降順を指定します。

NULLS FIRST: nullを非nullの前にソートすることを指定します。 これは、DESCが指定されている場合のデフォルトです。

NULLS LAST: ヌルが非ヌルの後にソートされることを指定する。 これは、DESCが指定されていない場合のデフォルトです。

storage_parameter: インデックスメソッド固有のストレージパラメーターの名前。

tablespace_name: インデックスを作成するテーブルスペース。 指定しない場合、default_tablespaceが参照されるか、temp_tablespacesが一時テーブルのインデックスをします。

述語: 部分インデックスの制約式。

インデックスストレージのパラメータ

オプションのWITH句は、インデックスになストレージパラメーターを指定します。 各インデックスメソッドには、許可されたストレージパラメーターの独自のセットがあります。 Bツリー、ハッシュ、GiST、およびSP-GiSTインデックスメソッドはすべて、このパラメータを受け入れます。

fillfactor (integer) : インデックスのfillfactorは、インデックスメソッドがインデックスページをパックしようとする量を決定するパーセンテージです。 Bツリーの場合、最初のインデックスの作成中、およびインデックスを右側に拡張する (新しい最大のキー値を追加する) ときに、リーフページはこの割合で埋められます。 その後、ページが完全にいっぱいになると、ページは分割され、オンディスクインデックス構造の断片化につながります。 Bツリーは90のデフォルトのfillfactorを使用しますが、10から100までの任意の整数値を選択できます。 テーブルが静的である場合、指数の物理的サイズを最小化することができるので、100の充填率が最良である。 ただし、更新負荷が大きいテーブルでは、ページ分割の必要性を最小限に抑えるために、フィルファクタが小さい方が有利です。 他のインデックスメソッドは、異なるが大まかに類似した方法でfillfactorを使用します。デフォルトのfillfactorはメソッド間で異なります。

B-treeインデックスはさらにこのパラメータを受け入れます:

deduplicate_items (boolean) : Bツリー重複排除手法の使用を制御します。 最適化を有効または無効にするには、ONまたはOFFに設定します。 デフォルトはONです。

GiSTインデックスはさらにこのパラメータを受け入れます:

buffering (enum) : バッファリングされたビルド手法でインデックスをビルドするかどうかを指定します。 OFFではバッファリングは無効になり、ONではバッファリングは有効になり、AUTOでは最初は無効になりますが、インデックスサイズがeffective_cache_sizeに達するとオンザフライでオンになります。 デフォルトはAUTOです。

GINインデックスは異なるパラメータを受け入れます:

fastupdate (boolean) : この設定は高速更新手法の使用を制御します。これはブール型パラメータです。ONは高速更新を有効にし、OFFは無効にします。 デフォルトはONです。

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

BRINindexesは異なるパラメータを受け入れます:

pages_per_range (integer) : BRINindexのエントリごとに1つのブロック範囲を構成するテーブルブロックの数を定義します。 デフォルトは128です。

autosomasal (boolean) : 次のページで挿入が検出されるたびに、前のページ範囲で要約実行をキューに入れるかどうかを定義します。

同時にインデックスを構築する

インデックスの作成は、データベースの通常の操作を妨げる可能性があります。 通常、PolarDBは、インデックス作成するテーブルを書き込みに対してロックし、テーブルの1回のスキャンでインデックス作成全体を実行します。 他のトランザクションはテーブルを読み取ることができますが、テーブル内の行を挿入、更新、または削除しようとすると、インデックスのビルドが完了するまでブロックされます。 これは、システムがライブプロダクションデータベースである場合に重大な影響を及ぼす可能性がある。 非常に大きなテーブルは、インデックスを作成するのに多くの時間を要する可能性があり、より小さなテーブルの場合でも、インデックスビルドは、プロダクションシステムにとって許容できないほど長い期間にわたってライターをロックアウトする可能性があります。

PolarDBは、書き込みをロックアウトせずにインデックスを作成できます。 このメソッドは、CREATE INDEXのCONCURRENTLYオプションを指定することによって呼び出されます。 このオプションを使用すると、PolarDBはテーブルの2回のスキャンを実行する必要があります。さらに、インデックスを変更または使用する可能性のあるすべての既存のトランザクションが終了するまで待機する必要があります。 したがって、この方法は、標準インデックス構築よりも多くの総作業を必要とし、完了するまでにかなり時間がかかる。 ただし、インデックスの作成中に通常の操作を続行できるため、本番環境で新しいインデックスを追加する場合に便利です。 もちろん、インデックス作成によって課される余分なCPUおよびI/O負荷は、他の動作を遅くする可能性がある。

同時インデックスビルドでは、インデックスは実際には1つのトランザクションで「無効」インデックスとしてシステムカタログに入力され、2つのテーブルスキャンがさらに2つのトランザクションで発生します。 各テーブルスキャンの前に、インデックスビルドは、テーブルを変更した既存のトランザクションが終了するのを待つ必要があります。 2回目のスキャンの後、インデックスビルドは、関連するインデックスが部分的であるか、単純な列参照ではない列がある場合、他のテーブルでの同時インデックスビルドの任意のフェーズで使用されるトランザクションを含め、2回目のスキャンに先行するスナップショットを持つトランザクションが終了するのを待つ必要があります。 最後に、インデックスは「有効」とマークされ、使用準備ができており、CREATE indexコマンドは終了します。 しかしながら、その場合であっても、インデックスは、クエリに対して即座に使用可能ではない場合がある。最悪の場合、インデックス構築の開始前のトランザクションが存在する限り、インデックスは使用できない。

テーブルのスキャン中にデッドロックや一意のインデックスの一意性違反などの問題が発生した場合、CREATE indexコマンドは失敗しますが、「無効な」インデックスは残ります。 このインデックスは不完全である可能性があるため、クエリの目的では無視されますが、それでも更新のオーバーヘッドが消費されます。 Thepsql \dコマンドは、次のようなインデックスをINVALIDとして報告します。

postgres=# \dタブ
           テーブル "public.tab"
     列 | タイプ | 照合 | Nullable | デフォルト
    --------+---------+-----------+----------+---------
     col | 整数 | | |
    Indexes:
        "idx" btree (col) 無効 

このような場合に推奨される回復方法は、インデックスを削除し、CREATE index CONCURRENTLYをもう一度実行することです。 (別の可能性は、REINDEX index CONCURRENTLYでインデックスを再構築することである) 。

一意のインデックスを同時に作成する場合のもう1つの注意点は、2回目のテーブルスキャンの開始時に一意性制約が他のトランザクションに対してすでに適用されていることです。 これは、インデックスが使用可能になる前に、またはインデックスの作成が最終的に失敗した場合でも、他のクエリで制約違反が報告される可能性があることを意味します。 また、第2のスキャンで失敗が発生した場合、「無効」インデックスは、その後、その一意性制約を強制し続ける。

式インデックスと部分インデックスの同時ビルドがサポートされています。 これらの式の評価でエラーが発生すると、一意の制約違反について前述したのと同様の動作が発生する可能性があります。

通常のインデックスビルドでは、同じテーブル上で他の通常のインデックスビルドを同時に実行できますが、同時に実行できるインデックスのビルドは1つだけです。 いずれの場合も、インデックスの作成中にテーブルのスキーマ変更は許可されません。 もう1つの違いは、通常のCREATE INDEXコマンドはトランザクションブロック内で実行できますが、CREATE INDEX CONCURRENTLYは実行できないことです。

パーティションテーブルのインデックスの同時ビルドは現在サポートされていません。 ただし、パーティションテーブルへの書き込みがロックアウトされる時間を短縮するために、各パーティションのインデックスを個別に同時に作成し、最後にパーティションインデックスを非同時に作成することもできます。 この場合、区分されたインデックスを構築することは、メタデータのみの操作である。

注

現在、B-tree、GiST、GIN、およびBRINインデックスメソッドのみが複数のキー列インデックスをサポートしています。 複数のキー列があるかどうかは、INCLUDE列をインデックスに追加できるかどうかとは無関係です。 インデックスには、INCLUDE列を含む最大32列を含めることができます。 (この制限は、PolarDBを構築するときに変更できます。) 現在、Bツリーのみが一意のインデックスをサポートしています。

オプションのパラメーターを持つ演算子クラスは、インデックスの各列に指定できます。 演算子クラスは、その列のインデックスによって使用される演算子を識別します。 たとえば、4バイト整数のBツリーインデックスはint4_opsクラスを使用します。この演算子クラスには、4バイト整数の比較関数が含まれています。 実際には、通常、列のデータ型の既定の演算子クラスで十分です。 演算子クラスを持つことの主なポイントは、いくつかのデータタイプに対して、2つ以上の意味のある順序付けがあり得ることである。 たとえば、複素数データ型を絶対値または実数部でソートする場合があります。 これを行うには、データ型の2つの演算子クラスを定義し、インデックスを作成するときに適切なクラスを選択します。

CREATE INDEXがパーティションテーブルで呼び出された場合、既定の動作はすべてのパーティションに再帰して、すべてのパーティションにインデックスが一致するようにします。 各パーティションは、まず、同等のインデックスが既に存在するかどうかを判定するためにチェックされ、存在する場合、そのインデックスは、作成されるインデックスにパーティションインデックスとして添付され、そのインデックスは、その親インデックスとなる。 一致するインデックスが存在しない場合、新しいインデックスが作成され、自動的にアタッチされます。各パーティションの新しいインデックスの名前は、コマンドでインデックス名が指定されていないかのように決定されます。 ONLYオプションを指定した場合、再帰は実行されず、インデックスは無効とマークされます。 (ALTER INDEX... ATTACH PARTITIONは、すべてのパーティションが一致するインデックスを取得すると、インデックスを有効にマークします。ただし、CREATE TABLEを使用して将来作成されるパーティションはすべて... PARTITION OFは、ONLYが指定されているかどうかに関係なく、自動的に一致するインデックスを持ちます。

順序付きスキャンをサポートするインデックスメソッド (現在はBツリーのみ) の場合、オプションの句ASC、DESC、NULLS FIRST、および /またはNULLS LASTを指定して、インデックスのソート順序を変更できます。 順序付けられたインデックスは順方向または逆方向のいずれかでスキャンできるため、通常、単一列のDESCインデックスを作成することは有用ではありません。 これらのオプションの値は、SELECTなどの混合順序付けクエリによって要求されたソート順序に一致する複数列のインデックスを作成できることです。 注文BY x ASC、y DESC。 NULLSオプションは、ソート手順を回避するためにインデックスに依存するクエリで、デフォルトの「null sort high」ではなく、「null sort low」動作をサポートする必要がある場合に便利です。

ほとんどのインデックスメソッドでは、インデックスの作成速度はmaintenance_work_memの設定に依存します。 値を大きくすると、実際に使用可能なメモリの量よりも大きくしない限り、インデックスの作成に必要な時間が短縮されます。

PolarDBは、テーブルの行をより高速に処理するために、複数のCPUを活用しながらインデックスを構築できます。 この機能は、並列インデックス構築と呼ばれます。 インデックスの並列構築をサポートするインデックスメソッド (現在はBツリーのみ) の場合、maintenance_work_memは、開始されたワーカープロセスの数に関係なく、各インデックス構築操作全体で使用できるメモリの最大量を指定します。 一般に、コストモデルは、要求されるワーカープロセスの数を自動的に決定する。

並列インデックス構築は、同等のシリアルインデックス構築がほとんどまたはまったく利益を得られないmaintenance_work_memを増やすことから利益を得ることができる。 並列ワーカーにはmaintenance_work_mem予算の合計の少なくとも32MBのシェアが必要なため、maintenance_work_memは要求されるワーカープロセスの数に影響を与える可能性があります。 リーダープロセスの残りの32MBの共有も必要です。 max_parallel_maintenance_workersのを増やすと、より多くのワーカーを使用できるようになります。これにより、インデックスのビルドがI/Oバインドされていない限り、インデックスの作成に必要な時間が短縮されます。 もちろん、そうでなければアイドル状態になるであろう十分なCPU容量もあるべきである。

ALTER TABLEを介してparallel_workerの値を設定すると、テーブルに対してCREATE INDEXによって要求される並列ワーカープロセスの数が直接制御されます。 これにより、コストモデルが完全にバイパスされ、maintenance_work_memが要求される並列ワーカーの数に影響を与えるのを防ぎます。 ALTER TABLEを介してparallel_workerを0に設定すると、すべての場合でテーブルの並列インデックスビルドが無効になります。

インデックスビルドのチューニングの一環として、parallel_workersを設定した後にリセットすることができます。 これにより、parallel_workersはすべての並列テーブルスキャンに影響を与えるため、クエリプランに対する不注意な変更が回避されます。

CONCURRENTLYオプション付きのCREATE INDEXは特別な制限なしで並列ビルドをサポートしますが、実際には最初のテーブルスキャンのみが並列に実行されます。

インデックスを削除するには、DROP INDEXを使用します。

PolarDB Lの以前のリリースには、R-tree indexメソッドもありました。 この方法は、GiST法よりも大きな利点がなかったため、削除されました。 USING rtreeが指定されている場合、CREATE INDEXは、古いデータベースのgistへの変換を簡素化するために、USING GiSTとして解釈します。

例

テーブルフィルムの列タイトルに一意のBツリーインデックスを作成するには:

CREATE UNIQUE INDEX title_idx ON films (title);

テーブルフィルムに含まれる列のディレクターとレーティングを使用して、列のタイトルに一意のBツリーインデックスを作成するには:

CREATE UNIQUE INDEX title_idx ON films (タイトル) INCLUDE (監督、評価);

重複排除を無効にしてB-Treeインデックスを作成するには:

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

式lower(title) にインデックスを作成し、効率的な大文字小文字を区別しない検索を可能にするには:

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

デフォルト以外の照合順序でインデックスを作成するには:

CREATE INDEX title_idx_german ON films (タイトルCOLLATE "de_DE");

NULLのデフォルト以外のソート順序でインデックスを作成するには:

CREATE INDEX title_idx_nulls_low ONフィルム (タイトル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);

テーブルフィルムの列コードにインデックスを作成し、インデックスをテーブルスペースindexspaceに配置するには:

CREATE INDEX code_idx ON films (code) TABLESPACE indexspace;

変換関数の結果に対してボックス演算子を効率的に使用できるように、point属性にGiSTインデックスを作成するには:

CREATE INDEX pointloc
        要点を使用してポイント (ボックス (場所、場所));
    SELECT * ポイントから
        WHEREボックス (場所、場所) && '(0,0) 、(1,1)':: ボックス; 

テーブルへの書き込みをロックアウトせずにインデックスを作成するには:

CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);