7.8. 部分インデックス

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

このような部分インデックスは、あらかじめ頻出値が何であるかを知っている必要があります。値の分布が(アプリケーション上の性質より)固有で、かつ固定な(時間が経っても変化しない)場合、このような部分インデックスの作成は難しくありません。しかし、頻出値がたまたまロードされたデータの単なる結果として決定される場合、メンテナンスがとても大変になります。

Example 7-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は支払済みかもしれませんし、未支払かもしれないからです。

Example 7-3. 一意な部分インデックスの作成

テストの結果が格納されているテーブルがあるとします。与えられた件名(subject)とターゲット(target)の組み合せに対して、"成功"となる入力値は1つだけで、失敗になるものは複数あることを保証したいとします。下記はそのコマンドです。

CREATE TABLE tests (subject text,
                    target text,
                    success bool,
                    ...);
CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
    WHERE success;

上記のような、成功の場合が少なく、失敗の場合が多いようなケースに特に有効です。