Menu

PostgreSQL CREATE INDEX:インデックスの種類と作り方

CREATE INDEX はデフォルトで B-tree インデックスを作り、PostgreSQL がテーブル全体を読まずに行を探せるようにします。ほかの種類 (GIN、GiST、BRIN、hash) は JSONB、配列、全文検索、範囲型、巨大なテーブルに使います。

このページのコードはエディタで実行できます。編集してすぐに結果を確認できます。

CREATE INDEX name ON table (column); はインデックスを作り、PostgreSQL がテーブル全体を読まずに一致する行を探せるようにします。デフォルトの種類は B-tree で、=、<、>、BETWEEN、IN、IS NULL、ORDER BY に効きます。ほかの種類は USING で選びます: gin、gist、spgist、brin、hash。

実行計画の1行目は Index Scan using users_email_idx on users です。PostgreSQL はインデックスで値を引き、一致する行だけを読みます。CREATE INDEX の行を消してもう一度実行すると、10,000 行すべてを読む Seq Scan on users になります。

数十行程度のテーブルでは、インデックスがあってもプランナーはシーケンシャルスキャンを選ぶことがよくあります。数ページを読むほうが安いからです。実行計画の読み方は EXPLAIN ANALYZE で解説しています。

インデックスの種類を選ぶ

種類使いどころ
B-tree (デフォルト)等価、範囲、並べ替え、LIKE 'prefix%' (C 照合順序か text_pattern_ops を使う場合)
GIN多くの要素を持つ値: jsonb (@>, ?)、配列 (@>, &&)、全文検索の tsvector
GiST図形、範囲 (&&、重なり)、全文検索、最近傍の ORDER BY ... <->
SP-GiST重ならない領域に分けられるデータ: 点、IP アドレス、テキストの接頭辞
BRIN追記だけの created_at のように、値が物理的な順序に沿った巨大なテーブル
Hash等価のみ。B-tree が同じことをそれ以上にこなすので、選ばれることはまれ

jsonb 列に GIN インデックスを作ると、包含のクエリでインデックスが使われます。

実行計画には GIN インデックスに対する Bitmap Index Scan があり、それが一致する行を読む Bitmap Heap Scan に渡されます。演算子については JSONB のページ で解説しています。

複数の列にインデックスを作る

複合 B-tree インデックスは1つ目の列で並べ、1つ目の列の各値の中で2つ目の列で並べます。先頭の列、または先頭の列とそれに続く列で絞り込むクエリに効きます。2つ目の列だけで絞り込んでもあまり効果がないので、いつも = で絞り込む列を先頭に置きます。

CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);

-- served well by the index above
SELECT * FROM orders WHERE customer_id = 7 AND created_at > '2026-01-01';
SELECT * FROM orders WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 10;

INCLUDE (PostgreSQL 11 以降) は、並べ替えには使わない追加の列をインデックスに保存します: CREATE INDEX ON orders (customer_id) INCLUDE (total);。こうすると、それらの列だけを読むクエリはインデックスだけで答えられます。これが Index Only Scan です。

ユニークインデックスと部分インデックス: 一部の行にルールを課す

CREATE UNIQUE INDEX は重複する値を拒否します。部分インデックスは WHERE を加え、条件に合う行だけをインデックスに入れます。サイズが小さくなり、一意性のルールを一部の行だけに適用することもできます。

削除されていない行が1つだけなので、両方の行が受け付けられます。同じメールアドレスで削除されていない3つ目の行を入れると、duplicate key value violates unique constraint "accounts_email_live" で失敗します。クエリが部分インデックスを使うのは、その WHERE がインデックスの条件 (ここでは WHERE NOT deleted) を満たすことが明らかなときだけです。

式にインデックスを作る

インデックスには式の結果を保存できます。使うにはクエリで同じ式を繰り返す必要があり、email のインデックスは WHERE lower(email) = ... には効きません。

WHERE が lower(email) をそのまま繰り返しているので、実行計画は users_email_lower_idx を使います。式は不変 (immutable) でなければならないので、now() や設定に依存する関数は拒否されます。

稼働中のデータベースでインデックスを一覧、作成、削除する

pg_indexes は、すべてのインデックスを、それを作り直す文と一緒に一覧にします。

主キーと UNIQUE 制約は、それぞれ自分のインデックス users_pkey と users_email_key を作りました。psql では \d users で、テーブルの列の下に同じ一覧が表示されます。

書き込みがあるテーブルでは、書き込みをブロックしないよう CONCURRENTLY でインデックスを作成、削除します。これらの書き方はトランザクションブロックの中では実行できません。同時作成が失敗すると INVALID なインデックスが残るので、削除して作り直す必要があります。

CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
DROP INDEX CONCURRENTLY orders_status_idx;
REINDEX INDEX CONCURRENTLY orders_status_idx;  -- rebuild a bloated index

よくある質問

PostgreSQL はインデックスを自動で作りますか?

PRIMARY KEY、UNIQUE、排他制約のときだけです。外部キーの列にはインデックスが作られないので、その列で結合したり参照先のテーブルから行を削除したりするなら、自分で追加します。

PostgreSQL がインデックスを使わないのはなぜですか?

よくある理由は次のとおりです。テーブルが小さく、全体を読むほうが安い。クエリが行の大部分を返す。WHERE が列を関数やキャストで包んでいて、インデックスにその式がない。統計情報が古い (ANALYZE を実行します)。どの実行計画が選ばれたかは EXPLAIN でわかります。

インデックスはいくつから多すぎですか?

決まった数はありません。インデックスはどれも一部の読み込みを速くする一方で、そのテーブルへのすべての INSERT とほとんどの UPDATE を遅くし、ディスク容量も使います。pg_stat_user_indexes.idx_scan で各インデックスがどれだけ使われているかわかるので、0 のままのものは削除できます。

CREATE INDEX はテーブルをロックしますか?

通常の CREATE INDEX は、終わるまでそのテーブルへの INSERT、UPDATE、DELETE をブロックします。読み込みは続けられます。CREATE INDEX CONCURRENTLY は作成中も書き込みを許しますが、時間がかかり、トランザクションブロックの中では実行できません。

Coddyのプログラミング言語のイラスト

Coddyでコードを学ぼう

始める