PostgreSQL 14 min read

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の実行計画の見方（EXPLAIN） ／ PostgreSQLのALTER TABLE ／ PostgreSQLの「duplicate key value violates unique constraint」

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

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

技術評論社・2026年2月

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

Amazonで見る

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

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

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

Amazonで見る

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

RELATED PostgreSQL