11.7. 部分インデックス

例 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';

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

例 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-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;

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