インデックスは、テーブルの2つ以上の列に定義することができます。 例えば、以下のようなテーブルがあるとします。
CREATE TABLE test2 ( major int, minor int, name varchar );
(例えば、/dev
ディレクトリの内容をデータベースに保持していて)頻繁に下記のような問い合わせを発行するとします。
SELECT name FROM test2 WHERE major =constant
AND minor =constant
;
このような場合、major
およびminor
という2つの列に1つのインデックスを定義する方が適切かもしれません。
CREATE INDEX test2_mm_idx ON test2 (major, minor);
現在、B-tree、GiST、GINおよびBRINインデックス型でのみ、複数列インデックスをサポートしています。
最高32列まで指定可能です。
(この上限は、PostgreSQLを構築する際に変更可能です。
pg_config_manual.h
ファイルを参照してください。)
複数列に対するB-treeインデックスをインデックス対象列の任意の部分集合を含む問い合わせ条件で使用することができます。
しかし、先頭側の(左側)列に制約がある場合に、このインデックスはもっとも効率的になります。
正確な規則は、先頭側の列への等価制約、および、等価制約を持たない先頭列への不等号制約がスキャン対象のインデックス範囲を制限するために使用されます。
これらの列の右側の列に対する制約は、このインデックス内から検査されます。
ですので、テーブルアクセスを適切に抑えますが、スキャンされるインデックスの範囲を減らしません。
例えば、(a, b, c)
に対するインデックスがあり、WHERE a = 5 AND b >= 42 AND c < 77
という問い合わせ条件があったとすると、
a
= 5かつb
= 42を持つ項目を先頭に、a
= 5となる最後の項目までのインデックスをスキャンしなければなりません。
c
>= 77を持つインデックス項目は飛ばされますが、スキャンを行わなければなりません。
このインデックスは原理上、 a
に対する制約を持たず、b
あるいはc
に制約に持つ問い合わせでも使用することができます。
しかし、インデックス全体がスキャンされますので、ほとんどの場合、プランナはインデックスの使用よりもシーケンシャルテーブルスキャンを選択します。
複数列GiSTインデックスは、インデックス対象列の任意の部分集合を含む問い合わせ条件で使用することができます。 他の列に対する条件は、インデックスで返される項目を制限します。 しかし、先頭列に対する条件が、インデックスのスキャン量を決定するもっとも重要なものです。 先頭列の個別値がわずかな場合、他の列が多くの個別値を持っていたとしても、相対的にGiSTインデックスは非効率的になります。
複数列GINインデックスは、インデックス対象列の任意の部分集合を含む問い合わせ条件で使用することができます。 B-TreeやGiSTと異なり、インデックス検索の効果はどのインデックス列が問い合わせ条件で使用されているかに関係なく同じです。
複数列BRINインデックスは、インデックス対象列の任意の部分集合を含む問い合わせ条件で使用することができます。
GINと同様に、またB-treeやGiSTとは異なり、インデックス検索の効果はどのインデックス列が問い合わせ条件で使用されているかに関係なく同じです。
一つのテーブルに対して複数列BRINインデックスを一つ持つ代わりに複数のBRINインデックスを持つ唯一の理由は、異なるpages_per_range
ストレージパラメータを持つためです。
当然ながら、インデックス種類に対して適切な演算子を各列に使用しなければなりません。 他の演算子を含む句は考慮されません。
複数列インデックスは慎重に使用する必要があります。 多くの場合、単一列のインデックスで十分であり、また、その方がディスク領域と時間を節約できます。 テーブルの使用方法が極端に様式化されていない限り、4つ以上の列を使用しているインデックスは、不適切である可能性が高いでしょう。 異なるインデックス構成の利点に関するこの他の説明について11.5. 複数のインデックスの組み合わせおよび11.11. インデックスオンリースキャンも参照してください。