部分インデックス

11.7. 部分インデックス

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

部分インデックスを利用する主な目的は、頻出値に対してインデックスを作成しないようにすることです。 (テーブル全体の行のうち、数パーセント以上を占める)頻出値を検索する問い合わせでは、いかなる場合でもインデックスを使用しないため、それらに対してインデックスを作成してもまったく意味がありません。 このため、インデックスのサイズを小さくでき、その結果、インデックスを使用する問い合わせが速くなります。 また、インデックスを更新する必要のないケースも生じるため、テーブルを更新する作業の多くも速くなります。 例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';

また、以下のような問い合わせの場合、このインデックスは使用できません。

SELECT * FROM access_log WHERE client_ip = inet '192.168.100.23';

このような部分インデックスを使用するには、あらかじめ頻出値が何であるかを知っている必要があることに、注意してください。 値の分布が(アプリケーションの性質上)固有で、かつ静的(時間が経っても変化しない)である場合、このような部分インデックスの作成は難しいことではありません。 しかし、頻出値が、単にロードされたデータに偶然一致したものである場合、時間経過に伴ってインデックス定義を変更する保守作業が非常に大変になります。

部分インデックスを使用するもう1つの方法は、一般的な問い合わせに必要のない値をインデックスから取り除くことです (例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 < 1""x < 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;

これは、成功するテストがほとんどなく、失敗するテストが多い場合に特に有効な方法です。

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

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

部分インデックスの詳細については、 The case for partial indexes Partial indexing in POSTGRES: research project、およびGeneralized Partial Indexes (キャッシュされたバージョン) を参照してください。