部分インデックス

ここまでは、列に対してインデックスを作成する ことだけを取り上げてきました。どの行にインデックスを 作成するかも、部分インデックス(PostgreSQL) あるいはフィルター選択されたインデックス(SQL Server)という機能で実現できます。

部分インデックスは、以下の例にあるステータスコードのような定数を使った、比較的よく使われるwhere句で便利です。

SELECT message
  FROM messages
 WHERE processed = 'N'
   AND receiver  = ?

このようなクエリは、キューのシステムで非常によく使われます。クエリは、特定の受信者に向けた未処理のメッセージを全て取得します。 既に処理されたメッセージはほとんど必要ないと言っていいでしょう。もし必要だとしても、プライマリキーのような別の基準でアクセスされることに なるはずです。

このようなクエリを最適化するには、2カラムに対するインデックスを使います。このクエリだけを考えるなら、範囲条件は指定していないので、 インデックスの列の順番を気にする必要はありません。

CREATE INDEX messages_todo
          ON messages (receiver, processed)

このインデックスは目的を果たすのには十分なのですが、滅多に検索されない行、つまり処理済みのメッセージの行を大量に含んでいます。 巨大なディスク容量を使っていたとしても、対数的スケーラビリティによって、インデックスはクエリの実行を非常に高速にすることができます。

部分インデックスを使うと、インデックスが未処理のメッセージのみを含むよう制限することができます。この定義の文法は意外にも簡単で、 where句を使うだけです。

CREATE INDEX messages_todo
          ON messages (receiver)
       WHERE processed = 'N'

このインデックスは、where句の条件を満たす行だけ からなります。今回の例では、インデックス上はPROCESSEDは 常に'N'になるので、クエリのwhere句から PROCESSED列の指定を外してしまうことすらできてしまいます。 これは2つの次元でインデックスのサイズを減らせたことを示しています。より少ない行しか含まないという垂直方向と、列を減らしたという水平方向 です。

これで、インデックスは非常に小さくなります。キューのシステムの場合、テーブルサイズが大きくなっていったとしても、インデックスのサイズは あまり変わらないということになります。インデックスは未処理のメッセージしか含まないからです。

部分インデックスのwhere句は、任意の複雑さで 指定できます。関数に対しては、インデックスの定義と同様に、確定的な関数しか使えないという、基本的な制限があります。SQL Serverでは、より制限が厳しく、インデックス 述語内では関数もOR演算子も使用できません.

データベースは、クエリにwhere句が あれば常に部分インデックスを利用できます。

SELECT message
  FROM messages
 WHERE processed = 'N'