【PostgreSQL】部分インデックス(Partial Index)でWHERE条件付きの軽量インデックスを作る

部分インデックスとは

CREATE INDEXにWHERE句を付けると、テーブルの一部の行だけを対象にしたインデックスを作成できる。これを部分インデックスと呼ぶ。全行を対象にした通常のインデックスと比べてサイズが小さくなり、更新時のオーバーヘッドも減らせる。

100万件の注文のうち、pending（未処理）状態の注文が1000件しかないテーブルを例にする。

SELECT status, count(*) FROM orders GROUP BY status;

  status   | count  
-----------+--------
 completed | 999000
 pending   |   1000

status列全体に通常のインデックスを作成すると、ほとんどがcompletedの行にもかかわらず全行分のサイズになる。

CREATE INDEX idx_orders_status_full ON orders (status);
SELECT pg_size_pretty(pg_relation_size('idx_orders_status_full'));

 pg_size_pretty 
----------------
 6904 kB

WHERE条件を付けてインデックスを作成する

pendingの注文だけを検索する用途であれば、WHERE句で対象行を絞った部分インデックスで十分である。通常のインデックスは削除し、部分インデックスに置き換える。

DROP INDEX idx_orders_status_full;
CREATE INDEX idx_orders_status_pending ON orders (status) WHERE status = 'pending';
SELECT pg_size_pretty(pg_relation_size('idx_orders_status_pending'));

 pg_size_pretty 
----------------
 16 kB

対象行が1000件（全体の0.1%）に絞られたことで、インデックスサイズは6904 kBから16 kBまで縮小した。

部分インデックスが使われる条件

部分インデックスは、クエリの検索条件がインデックスのWHERE句を包含する場合にのみ使われる。

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

 Index Scan using idx_orders_status_pending on orders  (cost=0.15..45.28 rows=1000 width=17) (actual time=0.014..2.052 rows=1000 loops=1)

status = 'pending'という条件はインデックスのWHERE句と一致するため、Index Scanで部分インデックスが使われている。一方、WHERE句の条件に含まれない値で検索すると、プランナーはこの部分インデックスを候補にすら入れない。

EXPLAIN SELECT * FROM orders WHERE status = 'cancelled';

 Gather  (cost=1000.00..12578.43 rows=1 width=17)
   Workers Planned: 2
   ->  Parallel Seq Scan on orders  (cost=0.00..11578.33 rows=1 width=17)
         Filter: (status = 'cancelled'::text)

cancelledはordersテーブルに1件も存在しないが、それでもidx_orders_status_pendingは使われない。プランナーはクエリの条件がインデックスのWHERE句の範囲に含まれるかどうかを静的に判断するため、status = 'pending'以外の条件では常に対象外になる。

部分UNIQUE制約への応用

部分インデックスはUNIQUEと組み合わせて、テーブルの一部の行だけを対象にした一意制約としても使える。論理削除するテーブルで、削除されていない行の中でだけメールアドレスを一意にしたい場合を考える。

CREATE TABLE users (
  id serial PRIMARY KEY,
  email text NOT NULL,
  deleted_at timestamptz
);
CREATE UNIQUE INDEX idx_users_email_active ON users (email) WHERE deleted_at IS NULL;

deleted_atがNULLの行だけを対象にしているため、削除済みユーザーと同じメールアドレスで新規登録できる。

INSERT INTO users (email) VALUES ('taro@example.com');
INSERT INTO users (email, deleted_at) VALUES ('taro@example.com', now());

INSERT 0 1
INSERT 0 1

一方、deleted_atがNULLの行同士でメールアドレスが重複すると、制約違反になる。

INSERT INTO users (email) VALUES ('taro@example.com');

ERROR:  duplicate key value violates unique constraint "idx_users_email_active"
DETAIL:  Key (email)=(taro@example.com) already exists.

テーブル全体にUNIQUE制約を付けると論理削除済みの行も対象になってしまうが、部分インデックスであれば有効な行だけに制約を絞り込める。