PostgreSQLのインデックス作成 ― CREATE INDEXとCONCURRENTLY、作ったのに使われないとき

※この記事にはアフィリエイト広告（プロモーション）が含まれます。

PostgreSQL の索引（インデックス）は CREATE INDEX で作ります。作ってよいかは、作成中に表の更新が止まるかどうかと、作った索引が実際に使われるかどうかの2点で決めます。この記事の出力は PostgreSQL 18.6（Ubuntu 26.04）で採りました。

まず見る（結論）

CREATE INDEX idx_orders_customer ON orders (customer_id);             -- 作成中は INSERT/UPDATE/DELETE が待たされる
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id); -- 待たせない。トランザクション内では使えない

使われているかは EXPLAIN で確かめる。Index Scan / Bitmap Index Scan に索引名が出れば使われている
CONCURRENTLY が失敗すると INVALID な索引が残る。IF NOT EXISTS を付けて作り直すと、壊れた索引が残っているためにスキップされる
列を関数で包む・型変換する・前方一致でない LIKE は、普通の索引を使わない

作成と確認

CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_customer ON orders (customer_id);
ERROR:  relation "idx_orders_customer" already exists
CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders (customer_id);
NOTICE:  relation "idx_orders_customer" already exists, skipping

表についている索引は psql の \d 表名 の Indexes: 欄に出ます。SQL で見るなら pg_indexes です。

\d orders
...
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
    "idx_orders_customer" btree (customer_id)

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';
      indexname      |                                  indexdef
---------------------+-----------------------------------------------------------------------------
 orders_pkey         | CREATE UNIQUE INDEX orders_pkey ON public.orders USING btree (id)
 idx_orders_customer | CREATE INDEX idx_orders_customer ON public.orders USING btree (customer_id)

大きさと使われた回数は pg_stat_user_indexes で見られます。idx_scan が増えていかない索引は、更新を遅くするだけで検索には使われていません。

SELECT indexrelid::regclass AS index, pg_size_pretty(pg_relation_size(indexrelid)) AS size, idx_scan
  FROM pg_stat_user_indexes WHERE relname = 'orders';
        index        |  size   | idx_scan
---------------------+---------+----------
 orders_pkey         | 7696 kB |        6
 idx_orders_customer | 2240 kB |        4

主キー以外の一意な索引は CREATE UNIQUE INDEX です。重複があると ERROR: could not create unique index "orders_cust_uq" と DETAIL: Key (customer_id)=(7920) is duplicated. が出て作られません。表の所有者でないユーザーが作ろうとすると ERROR: must be owner of table customers が出ます（SELECT や INSERT の権限があっても作れません）。

作成中に INSERT が止まる

300万行の表で、CREATE INDEX をトランザクション内で実行し、コミットする前に別のセッションから書き込みました（lock_timeout = '2s'）。

SELECT relation::regclass, mode, granted FROM pg_locks WHERE relation = 'big'::regclass;
 relation |   mode    | granted
----------+-----------+---------
 big      | ShareLock | t

INSERT INTO big VALUES (0, 'x');
ERROR:  canceling statement due to lock timeout

SELECT count(*) FROM big WHERE id < 10;      -- こちらは通る（84 ms）

CREATE INDEX は表に ShareLock を取り、終わるまで（トランザクション内ならコミットまで）INSERT・UPDATE・DELETE を待たせます。

CONCURRENTLY

CREATE INDEX CONCURRENTLY big_h2 ON big (h);

作成中に別のセッションから流した INSERT は 2.681 ms で終わりました。進み具合は pg_stat_progress_create_index で見られます。

SELECT phase, blocks_done, blocks_total FROM pg_stat_progress_create_index;
             phase              | blocks_done | blocks_total
--------------------------------+-------------+--------------
 building index: scanning table |       19005 |        25024

BEGIN;
CREATE INDEX CONCURRENTLY big_h3 ON big (h);
ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

psql -c "CREATE INDEX CONCURRENTLY ...; SELECT 1;" のように1つの -c に複数の文を書いた場合も、まとめて1つのトランザクションになるので、同じ cannot run inside a transaction block になります。マイグレーションツールが文をトランザクションで包む場合も同じです。

失敗すると INVALID が残る

重複のある列に CREATE UNIQUE INDEX CONCURRENTLY を実行しました。

CREATE UNIQUE INDEX CONCURRENTLY big_id_uq ON big (id);
ERROR:  could not create unique index "big_id_uq"
DETAIL:  Key (id)=(1) is duplicated.

\d big
Indexes:
    "big_h2" btree (h)
    "big_id_uq" UNIQUE, btree (id) INVALID

pg_index.indisvalid は f で、検索には使われません。

この状態で CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS big_id_uq ... を流すと、NOTICE: relation "big_id_uq" already exists, skipping で終わり、INVALID のままです。重複を消してから REINDEX INDEX CONCURRENTLY big_id_uq; を実行すると、indisvalid が t になりました。DROP INDEX して作り直しても構いません。

作ったのに使われない

どれも索引を作った後に EXPLAIN ANALYZE で確かめた結果です。

条件	計画	使わせるには
`lower(email) = 'user42@example.com'`（索引は `email`）	Seq Scan	式の索引 `CREATE INDEX ... ON customers (lower(email))`
`email LIKE 'User42%'`（索引は `email`）	Seq Scan	`(email text_pattern_ops)` で作る
`email LIKE '%42@Example.com'`	Seq Scan	B-tree では不可
`created::date = '2026-03-01'`（索引は `created`）	Seq Scan	`created >= '2026-03-01 00:00+09' AND created < '2026-03-02 00:00+09'`
`customer_id = 42 OR amount = 10.5`（`amount` に索引なし）	Seq Scan	—（片側に索引が無い）
`status = 'done'`（表の9割）	Seq Scan	Seq Scan のままでよい

前方一致の LIKE で索引が使えるかは、照合順序によります。この DB（datcollate が C.UTF-8）では通常の索引は使われず、text_pattern_ops で作った索引で Index Cond: ((email ~>=~ 'User42'::text) AND (email ~<~ 'User43'::text)) と範囲検索に変わりました。

created::date で包んだ条件と範囲で書いた条件は、同じ 1440 行を返しました。実行時間はそれぞれ 14.114 ms と 0.240 ms です。

複合索引は先頭の列から

CREATE INDEX idx_orders_cust_created ON orders (customer_id, created);

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY created DESC LIMIT 3;
 Limit  (cost=0.42..12.87 rows=3 width=30)
   ->  Index Scan Backward using idx_orders_cust_created on orders  (...)
         Index Cond: (customer_id = 42)

customer_id での絞り込みと created の逆順の並べ替えを、1つの索引で済ませています。一方、created だけの条件（created 単独の索引は削除済み）では、この複合索引は使われず Parallel Seq Scan でした。2番目以降の列だけで絞る検索には、その列の索引が別に要ります。

PostgreSQL をもっと深く知るための本

PostgreSQL実践入門 ──アーキテクチャ、運用監視、性能改善

技術評論社・2026年2月

アーキテクチャから運用監視・性能改善までを扱う。付録に PostgreSQL 18 の新機能と設定項目の差分がある。

Amazonで見る

［改訂3版］内部構造から学ぶPostgreSQL ―設計・運用計画の鉄則

技術評論社・2022年11月・PostgreSQL 14 ベース

内部構造の理解から、設計と運用計画の立て方を学ぶ。

Amazonで見る

Amazonのアソシエイトとして、FIRE SE は適格販売により収入を得ています。

まず見る（結論）

作成と確認

作成中に INSERT が止まる

CONCURRENTLY

失敗すると INVALID が残る

作ったのに使われない

複合索引は先頭の列から

RELATED PostgreSQL

PostgreSQLの起動・停止・再起動 ― systemctlとpg_ctlcluster、reloadで済む設定と起動しないとき

PostgreSQLのtext型 ― varchar(n)・char(n)との違いと長さの上限

PostgreSQLの「duplicate key value violates unique constraint」 ― setvalとON CONFLICT