★PostgreSQLカンファレンス2024 12月6日開催/チケット販売中★
他のバージョンの文書 16 | 15 | 14 | 13 | 12 | 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

11.8. 部分インデックス #

部分インデックスとは、テーブルの部分集合に構築されるインデックスです。 部分集合は、(部分インデックスの述語と呼ばれる)条件式で定義されます。 部分インデックスには、その述語を満たすテーブル行のみに対するエントリが含まれます。 部分インデックスは特別な機能です。 しかし、これらが有用となる状況が複数あります。

部分インデックスを利用する主な目的は、頻出値に対してインデックスを作成しないようにすることです。 (テーブル全体の行のうち、数パーセント以上を占める)頻出値を検索する問い合わせでは、いかなる場合でもインデックスを使用しないため、インデックスにそれらの行を持ち続けることは全く意味がありません。 これによりインデックスのサイズが小さくなりますので、インデックスを使用する問い合わせが速くなります。 また、インデックスを更新する必要のないケースも生じるため、テーブルを更新する作業の多くも速くなります。 例 11.1にこの概念に基づいた用例を示します。

例11.1 頻出値を除外するための部分インデックスの作成

ウェブサーバのアクセスログをデータベースに格納しているとします。 多くのアクセスは、社内のIPアドレスの範囲内から発信されています。 しかし、範囲外のアドレス(例えば、社員がダイアルアップ接続している場所)からの発信もあります。 主に範囲外からのアクセスをIPアドレスで検索する場合、社内のサブネットに該当するIPアドレスの範囲にインデックスを作成する必要はないでしょう。

以下のようなテーブルがあると想定します。

CREATE TABLE access_log (
    url varchar,
    client_ip inet,
    ...
);

この例に適する部分インデックスを作成するには、以下のようなコマンドを使用します。

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
WHERE NOT (client_ip > inet '192.168.100.0' AND
           client_ip < inet '192.168.100.255');

このインデックスを使用できる問い合わせの典型的な例は、以下のようなものです。

SELECT *
FROM access_log
WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';

この問い合わせのIPアドレスは部分インデックスでカバーされています。 以下の問い合わせは、インデックスから除外されているIPアドレスを使用しているので、部分インデックスを使用できません。

SELECT *
FROM access_log
WHERE url = '/index.html' AND client_ip = inet '192.168.100.23';

このような部分インデックスを使用するには、あらかじめ頻出値が何であるかを知っている必要があることに注意してください。 値の分布が変わらない場合に、このような部分インデックスが最善です。 データの分布が新しくなった場合はインデックスの再作成によって調整できますが、これはメンテナンス作業を増やしてしまいます。


部分インデックスを使用する有効な他の方法としては、一般的な問い合わせに必要のない値をインデックスから取り除くことです。 例 11.2を参照してください。 この方法の利点は上で示したものと同じです。 ただ、この方法を使用すると、インデックススキャンが適している場合でも、必要のない値へのインデックスを介したアクセスが防止されてしまいます。 以上のことから明白なように、このようなケースで部分インデックスを作成する際は、細心の注意を払い、十分な検証を行う必要があります。

例11.2 必要のない値を除外するための部分インデックスの作成

請求済み注文書および未請求注文書からなる、1つのテーブルがあるとします。 そして、未請求注文書の方がテーブル全体に対する割合が小さく、かつその部分へのアクセス数が最も多かったとします。 このような場合、未請求の行のみにインデックスを作成することにより、性能を向上させることができます。 インデックスの作成には、以下のようなコマンドを使用します。

CREATE INDEX orders_unbilled_index ON orders (order_nr)
    WHERE billed is not true;

このインデックスを使用する問い合わせの例としては、次のものが考えられます。

SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;

しかし、このインデックスは、order_nrをまったく使用しない問い合わせでも使用することができます。 以下は、その例です。

SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;

この問い合わせでは、システムがインデックス全体を検索する必要があるため、amount列に部分インデックスを作成した場合ほど効率は良くありません。 しかし、未請求注文書データが比較的少ない場合は、この部分インデックスを未請求注文書を検出するためだけに使用した方が効率が良い可能性があります。

以下の問い合わせでは、このインデックスを使用できないことに注意してください。

SELECT * FROM orders WHERE order_nr = 3501;

注文番号3501は請求済みかもしれませんし、未請求かもしれないからです。


例 11.2でもわかるように、インデックスが付けられた列名と、述語で使用されている列名は、一致している必要はありません。 PostgreSQLでは、インデックス付けされるテーブルの列だけが含まれているのなら、任意の述語で部分インデックスを使用できます。 しかし、この述語は、インデックスを使用させたい問い合わせの条件と一致する必要があることに留意してください。 正確に言うと、部分インデックスを問い合わせで使用できるのは、インデックスの述語が問い合わせのWHERE条件に数学的に当てはまるとシステムが判断できる場合のみです。 PostgreSQLには、異なった形式で記述された述語が数学的に同等のものであると判断できるような、洗練された定理証明機能はありません。 (そのような汎用的な定理証明機能の作成は、非常に困難であるだけではなく、おそらく実際の利用にはあまりにも実行速度が遅過ぎるでしょう。) システムでは、例えばx < 1x < 2を意味するというような、単純な比較演算子の意味は認識可能です。 しかし、それ以外の場合は、述語条件は問い合わせのWHERE条件と完全に一致している必要があります。 一致していない場合は、インデックスは使用可能と認識されません。 一致するかどうかは、実行時ではなく、問い合わせ計画作成時に判定されます。 したがって、パラメータ付きの問い合わせでは部分インデックスは動作しません。 たとえば、x < ?と指定されたパラメータを持つ、プリペアド問い合わせでは、どのようなパラメータ値であってもx < 2を表しません。

部分インデックスの考えられる3つ目の用法では、問い合わせでインデックスをまったく使用しません。 この考え方は、テーブルの部分集合に一意インデックスを作成するというものです。 例 11.3を参照してください。 これにより、インデックスの述語を満たさない行を制約することなく、その述語を満たす行での一意性を強制します。

例11.3 一意な部分インデックスの作成

テストの結果が格納されているテーブルがあるとします。 与えられた件名(subject)および対象(target)の組み合わせに対して、成功のエントリが確実に1つしかないようにします。 失敗のエントリは、複数あっても構いません。 以下に、これを実行する一例を示します。

CREATE TABLE tests (
    subject text,
    target text,
    success boolean,
    ...
);

CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
    WHERE success;

これは、成功するテストが少なく、失敗するテストが多い場合に特に有効な手法です。 また、IS NULL制限を使用して一意の部分インデックスを作成することにより、ひとつの列にNULL値をひとつのみ許可できます。


最後に、部分インデックスは、システムの問い合わせ計画の選択を変更するためにも使用できます。 特殊なデータ分布を持つデータ集合では、システムが実際には使用すべきでないインデックスを使用してしまうことがあります。 このような場合、特定の問い合わせでは使用することができないインデックスを設定することができます。 通常、PostgreSQLはインデックスの使用について適切な選択を行います(例えば、頻出値の検索にはインデックスを使用しませんので、前述の例はインデックスのサイズを実際に小さくするだけのもので、インデックスの使用を制限するためには必要はありません)。 まったく不適切な計画を選択するようであれば、バグとして報告してください。

部分インデックスを作成するには、少なくとも問い合わせプランナと同等の知識を持っていること、特に、インデックスが有益となる状況を理解している必要があることに留意してください。 このような知識を得るためには、PostgreSQLでインデックスがどのように機能するかを理解し、経験を積むことが必要です。 ほとんどの場合、通常のインデックスと比べて、部分インデックスを使用する利点は微細です。 例 11.4のように、かなり逆効果な場合があります。

例11.4 パーティショニングの代わりに部分インデックスを使用しない

例えば、重複しない部分インデックスの大きなセットを作りたいと思うかもしれません。

CREATE INDEX mytable_cat_1 ON mytable (data) WHERE category = 1;
CREATE INDEX mytable_cat_2 ON mytable (data) WHERE category = 2;
CREATE INDEX mytable_cat_3 ON mytable (data) WHERE category = 3;
...
CREATE INDEX mytable_cat_N ON mytable (data) WHERE category = N;

これは良くないアイディアです!ほとんどの場合、以下のように宣言された、部分的でない単一のインデックスを使用する方が良いでしょう。

CREATE INDEX mytable_cat_data ON mytable (category, data);

11.3で説明されている理由から、最初にcategory列を指定します。) この大きなインデックスでの検索は、小さなインデックスでの検索よりも2,3ツリーレベルを下に移動する必要がありますが、部分インデックスの適切な1つを選択するためにプランナがおこなう作業よりも、ほぼ確実にコストが削減できます。 この問題の核心は、システムが部分インデックス間の関係を理解していないことと、現在の問い合わせに適用出来るかどうかそれぞれ苦労してテストすることです。

テーブルが非常に大きくて、単一のインデックスが本当に悪いアイデアである場合は、代わりにパーティショニングを使用する必要があります(5.11を参照してください)。 このメカニズムにより、テーブルとインデックスが重複していないことが、システムで認識されるため、パフォーマンスが大幅に向上します。


部分インデックスの詳細については、[ston89b][olson93]、および[seshadri95]を参照してください。