他のバージョンの文書 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

7.8. 部分インデックス

部分インデックスとは、テーブルのサブセットに構築されるインデックスです。サブセットは「部分インデックスの述語」と言われる条件式で定義されます。その述語の条件を満たす行に対してのみインデックスが作成されます。

部分インデックスを利用する主な目的は、頻出値に対してインデックスの作成を行なわないことです。(テーブル全体の数パーセントを占める)頻出値を検索する問い合わせでは元々インデックスを使用しないため、それらに対してインデックスを作成することはあまり意味がありません。部分インデックスは、普通のインデックスよりもサイズが小さくなりますので、インデックスを使用する問い合わせも高速になります。また、テーブル更新時に必ずしもすべてのインデックスを更新しないため、更新作業も早くなります。 Example 7-1では、この概念に基づく可能アプリケーションを表しています。

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

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

頻出値をインデックスから排除するもう1つの方法として、 Example 7-2にもあるように、一般的な問い合わせに必要ないものをインデックスから取り除く方法が考えられます。この方法の利点は上記のものと同じですが、インデックスの利用が有効な場合でも「必要のないもの」はインデックスを使用しない、というケースもありえます。このように、部分インデックスを作成する場合には細心の注意を払い、検証を行なう必要があります。

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-2からも分かるように、インデックスが作成された列名と述語で使用されている列名は一致している必要はありません。 PostgreSQLでは、任意述語の部分インデックスをサポートしていますので、インデックスを作成するテーブルの列が関連されてさえいれば作成されます。しかし、部分インデックスを作成する際の述語は、インデックスの使用が有益な問い合わせの条件と一致する必要があります。つまり、部分インデックスは、システムによって問い合わせの WHERE 条件を数学的にインデックスの述語に当てはめることができると判断された問い合わせでのみ使用可能です。 PostgreSQLには、異った書式で書かれた述語が同等のものであるかどうかを数学的に判明できるような、洗練された定理証明機能はありません。(その理由としては、そのような定理証明器の実装が困難であるだけではなく、実際の利用にはあまりにも実行速度が遅いということがあげられます。)データベースシステムは「x < 1」は「x < 2」を意味するというような単純な不等式は認識しますが、それ以外は、述語条件と問い合わせの条件式は一致していなければなりません。一致していない場合は、インデックスが使用可能と認識されません。

3つめの部分インデックスの使用例では、問い合わせでインデックスを全く使用しません。つまり、Example 7-3にあるように、テーブルのサブセットとして一意インデックスを作成するということです。これは、インデックスの述語を満たすすべての行が、満たさない行を制約すること無く、一意な値であることを強制します。

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;

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

最後に、部分インデックスはデータベースシステムの問い合わせ実行プランの選択を上書きする場合にも使えます。インデックスを使用すべきでない場合でも、特殊なデータ分布の場合では、インデックスを使用してしまう場合があり得ます。このような場合、インデックスを使用すべきではない問い合わせに対してそのインデックスを使用できなくするように設定することができます。通常、PostgreSQLは合理的なインデックスの使用を行ないます(例えば、よくある値の検索の場合にはインデックスを使用しないなどです。前述の例ではインデックスの大きさを小さくするだけのもので、インデックスの使用を抑制するためのものではありません)。非常に間違った計画を選択するのであれば、バグ報告をお願いします。

部分インデックスを作成するということは、問い合わせ実行プランナと同等の知識を持っていて、特に、いつインデックスが有益であるかを知っているということを意味します。このような知識を得るためには、経験とPostgreSQLではどのようにインデックスが実行されているかの知識が必要となります。多くの場合、部分インデックスが通常のインデックスよりも有益なケースは希です。

部分インデックスに関するより詳細はThe case for partial indexesPartial indexing in POSTGRES: research projectGeneralized Partial Indexesなどを参照して下さい。