11.3. 複数列インデックス

インデックスは、テーブルの1つ以上の列に定義することができます。 例えば、以下のようなテーブルがあるとします。

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インデックス種類でのみ、複数列インデックスをサポートしています。 最高32列まで指定可能です (この上限は、PostgreSQLを構築する際に変更可能です。 pg_config.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、あるいは、bまたはcを制約に持つ問い合わせで使用することができます。 しかし、インデックス全体がスキャンされますので、ほとんどの場合、プランナはインデックスの使用よりもシーケンシャルテーブルスキャンを選択します。

複数列GiSTインデックスは、インデックス対象列の任意の部分集合を含む問い合わせ条件で使用することができます。 他の列に対する条件は、インデックスで返される項目を制限します。 しかし、先頭列に対する条件が、インデックスのスキャン量を決定するもっとも重要なものです。 先頭列による制限が小さい場合、他の列による制限が大きい場合であっても、相対的にGiSTインデックスは非効率的になります。

当然ながら、インデックス種類に対して適切な演算子を各列に使用しなければなりません。 他の演算子を含む句は考慮されません。

複数列インデックスは、慎重に使用する必要があります。 多くの場合、単一列のインデックスで十分であり、また、その方がディスク領域と時間を節約できます。 テーブルの使用方法が極端に様式化されていない限り、3つ以上の列を使用しているインデックスは、ほぼ間違いなく不適切であると言えます。 異なるインデックスの設定の利点に関するこの他の説明について項11.4も参照してください。