PostgreSQLのEXPLAIN ANALYZEの見方と、インデックスが効かない条件

PostgreSQL
スポンサーリンク

PostgreSQLの EXPLAIN ANALYZE の出力の読み方と、インデックスが使われない条件をまとめました。50万行のテーブルを generate_series で作り、条件を1つずつ変えて、実行計画がどう変わるかを記録しています。PostgreSQL 18 で EXPLAIN の出力が変わった点(BUFFERS が既定で出るなど)も、17 と並べて確かめました。

結論

  • まず、見積もりの rows と実際の rows を比べます。大きくずれていたら、統計が古いか、ない可能性があります(ANALYZE で集め直す)。
  • 速さは actual time だけでなく Buffers(読んだブロックの数)でも比べます。時間は実行するたびに変わりますが、ブロックの数は変わりにくいからです。
  • インデックスがあっても、ヒットする行が多い条件、列に関数をかけた条件、LIKE の中間一致では、使われませんでした。前方一致の LIKE も、データベースの照合順序が C でないと、ふつうのインデックスでは使われませんでした。
  • 実行計画は、PostgreSQL の版、データの分布、統計で変わります。この記事の結果は、この記事のデータでの一例です。

この記事で確かめた結果の一覧です。ブロックの数は、Buffers の hit と read の合計です(計画のいちばん上のノードの値)。時間は1回の実行の Execution Time で、実行ごとに変わります。

条件インデックス実行計画ブロック時間
customer_id = 42(50行)なしParallel Seq Scan5,15511.620 ms
customer_id = 42(50行)(customer_id)Bitmap Heap Scan(Bitmap Index Scan)530.123 ms
status = 'pending'(全体の0.1%)(status)Index Scan5042.570 ms
status = 'canceled'(5%)(status)Index Scan5,17925.788 ms
status = 'done'(94.9%)(status)Seq Scan5,15537.255 ms
同じ条件で、統計がない(ANALYZE の前)(status)Bitmap Heap Scan(見積もり1,856行)5,55741.221 ms
lower(email) = '...'(email)Parallel Seq Scan5,15534.856 ms
lower(email) = '...'(lower(email))Index Scan40.022 ms
created_at::date = '2026-03-01'(created_at)Parallel Seq Scan5,15515.181 ms
同じ日を範囲で書く(created_at)Index Scan230.196 ms
email LIKE 'user12345%'(email)Parallel Seq Scan5,15513.050 ms
email LIKE 'user12345%'(email text_pattern_ops)Index Scan50.045 ms
email LIKE '%12345%'B-tree だけParallel Seq Scan5,15516.820 ms
email LIKE '%12345%'pg_trgm の GINBitmap Heap Scan(Bitmap Index Scan)160.145 ms
status と created_at の範囲(1か月)(status, created_at)Index Scan480.083 ms
同じ条件(created_at, status)Index Scan2361.422 ms

動作確認した環境

  • PostgreSQL 18.6(Dockerイメージ postgres:18.6、digest sha256:e3d24f6906d0760bf4da0dae872415391ededefad23fd471c47dc53b0ddf3c42)
  • 比較用に PostgreSQL 17.11(Dockerイメージ postgres:17.11、digest sha256:ff87138949ad9a46f32ff23bd6852b52ce024a13dffe9b4087be98292cdd3b4f)
  • Docker 29.7.2(Windows 11 の Docker Desktop、Linuxコンテナの設定、x86_64)
  • 設定はイメージの既定のまま(shared_buffers などは変えていない)
  • 確認日: 2026年11月9日

この記事は、MacとWindowsの両方の読者を対象にしています。PostgreSQL は Docker のコンテナで動かすので、ホストで打つのは docker のコマンドだけで、MacとWindowsで同じです。この記事のコマンドは、Claude(AI)が実行して確認しました。本文の出力は、Windows 11 のGit Bashから新しいコンテナで上から順に実行した結果です。docker run、docker restart、docker rm と、docker exec で psql に SQL を渡すことは、Windows の PowerShell 7 でも確かめました。対話で使う docker exec -it は、Claude の実行環境に端末がないため確かめていません(本文の SQL は、psql に標準入力から渡して実行しました)。Macでは確認していません。

時間はPCの性能や、そのときの負荷で変わります。Apple シリコンのMacでは Arm のイメージが使われるので、時間や細かい数字は今回と同じにならない見込みです(未確認)。

準備

PostgreSQL のコンテナを起動します。ホストのポートは公開しません(コンテナの中の psql を使います)。パスワードは確認用の仮の値です。イメージは digest で固定しましたが、postgres:18.6 だけでも同じ版が使えます。$ はシェルのプロンプトで、打つのはその後ろです。2行目はコンテナのIDです。

$ docker run -d --name tb-explain-pg -e POSTGRES_PASSWORD=dummy-password-for-demo postgres:18.6@sha256:e3d24f6906d0760bf4da0dae872415391ededefad23fd471c47dc53b0ddf3c42
cc3c2cd6f566ada2f8b71be92bb2547458083b3dd41ee6d249ad298c8b9a6977

起動してから接続できるまで、数秒かかります。次のコマンドで psql に入ります(Mac のターミナルでも、Windows の PowerShell でも同じです)。

$ docker exec -it tb-explain-pg psql -U postgres

以下の SQL は、psql で順に実行したものです。SQL のすぐ下に、その出力を続けて載せています(確認では SQL のファイルを psql -a に渡したので、その表示の形です。対話で打つと、プロンプトの postgres=# などが付きます)。まずテーブルを2つ作ります。orders は50万行で、status は pending が0.1%、canceled が5%、残りが done になるようにしました。created_at は1分ずつ増えます。乱数を使っていないので、何度作っても同じデータになります。

CREATE TABLE customers (
  id   integer PRIMARY KEY,
  name text NOT NULL
);
CREATE TABLE
INSERT INTO customers
SELECT g, 'customer' || g FROM generate_series(1, 10000) AS g;
INSERT 0 10000
CREATE TABLE orders (
  id          integer PRIMARY KEY,
  customer_id integer NOT NULL,
  status      text NOT NULL,
  email       text NOT NULL,
  created_at  timestamptz NOT NULL,
  amount      integer NOT NULL
);
CREATE TABLE
INSERT INTO orders
SELECT g,
       g % 10000 + 1,
       CASE WHEN g % 1000 = 0 THEN 'pending'
            WHEN g % 20 = 1 THEN 'canceled'
            ELSE 'done' END,
       'user' || g || '@example.com',
       timestamptz '2026-01-01 00:00:00+00' + g * interval '1 minute',
       g * 37 % 10000
FROM generate_series(1, 500000) AS g;
INSERT 0 500000

データを入れたら、VACUUM ANALYZE で統計を集めておきます(統計がない場合は、あとの章で見ます)。

VACUUM ANALYZE customers, orders;
VACUUM
SELECT status, count(*) FROM orders GROUP BY status ORDER BY count(*) DESC;
  status  | count  
----------+--------
 done     | 474500
 canceled |  25000
 pending  |    500
(3 rows)

SELECT pg_size_pretty(pg_table_size('orders')) AS orders_size;
 orders_size 
-------------
 40 MB
(1 row)

EXPLAIN の出力の読み方

EXPLAIN だけ: cost、rows、width

EXPLAIN だけだと、SQL は実行されず、プランナーの見積もりだけが出ます。

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Gather  (cost=1000.00..8764.17 rows=50 width=47)
   Workers Planned: 2
   ->  Parallel Seq Scan on orders  (cost=0.00..7759.17 rows=21 width=47)
         Filter: (customer_id = 42)
(4 rows)
  • 1行が1つのノード(処理の単位)です。-> の付いた行が子で、子の結果を親が受け取ります。下(内側)から読むと、処理の順番が分かります。
  • cost=0.00..7759.17: 左が最初の行を出せるまでのコスト(開始コスト)、右がすべての行を出し終えるまでのコスト(総コスト)です。単位は時間ではなく、ページを1枚順に読むコスト(seq_page_cost、既定で1.0)を基準にした相対的な値です。
  • rows=50: そのノードが出力すると見積もった行数です。読んだ行数ではありません。
  • width=47: 出力する1行の平均の大きさ(バイト)の見積もりです。
  • Gather と Parallel Seq Scan: テーブルを複数のプロセスで分けて読み(パラレルクエリ)、Gather で集めています。Gather の開始コストの1000は、ワーカーを起動するコスト(parallel_setup_cost、既定で1000)です。

コストは、設定の値から計算されます。たとえば、あとの章の status = 'done' の Seq Scan の総コスト11405.00は、5,155ページ × 1.0(seq_page_cost)+ 500,000行 × 0.01(cpu_tuple_cost)+ 500,000行 × 0.0025(cpu_operator_cost。条件を1回ずつ調べるぶん)で、ちょうど同じ値になります。5,155はこのテーブルのページ数で、Seq Scan の Buffers の数と同じです。

EXPLAIN ANALYZE: actual time、rows、loops

ANALYZE を付けると、SQL を実際に実行して、かかった時間と行数が加わります。

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..8764.17 rows=50 width=47) (actual time=0.215..11.602 rows=50.00 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=5155
   ->  Parallel Seq Scan on orders  (cost=0.00..7759.17 rows=21 width=47) (actual time=0.337..7.573 rows=16.67 loops=3)
         Filter: (customer_id = 42)
         Rows Removed by Filter: 166650
         Buffers: shared hit=5155
 Planning Time: 0.030 ms
 Execution Time: 11.620 ms
(10 rows)
  • actual time=0.337..7.573: 単位はミリ秒です。左が最初の行を返すまで、右がそのノードを終えるまでの時間です。cost とは単位が違うので、数字どうしは比べられません。
  • loops=3: そのノードを実行した回数です。actual time と rows は1回あたりの平均で、合計は loops を掛けて求めます。ここでは2つのワーカーに、まとめ役のプロセス(リーダー)も加わって3回になりました。16.67行 × 3 = 50行です。時間に loops を掛けた値は、並列に動いた3つのプロセスの時間の合計なので、クエリ全体の経過時間とは一致しません。
  • Rows Removed by Filter: 166650: 読んだけれど条件に合わず捨てた行の数です(これも1回あたりの平均)。この数が多いのに残る行が少ないときは、インデックスが効く余地があります。
  • Planning Time は実行計画を作る時間、Execution Time は実行の時間です。結果の行はクライアントに送られないので、送る時間は含まれません。

customer_id にインデックスを作ると、計画が変わりました。

CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=4.81..190.66 rows=50 width=47) (actual time=0.047..0.107 rows=50.00 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=50
   Buffers: shared hit=50 read=3
   ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.80 rows=50 width=0) (actual time=0.038..0.038 rows=50.00 loops=1)
         Index Cond: (customer_id = 42)
         Index Searches: 1
         Buffers: shared read=3
 Planning:
   Buffers: shared hit=20 read=1
 Planning Time: 0.148 ms
 Execution Time: 0.123 ms
(12 rows)

Bitmap Index Scan でインデックスから行の場所を集め、親の Bitmap Heap Scan が、その場所をテーブルの物理的な順番に並べてから読みます。Heap Blocks: exact=50 は、テーブルのページを50枚読んだことを表します。読んだブロックは5,155から53に、時間は11.620ミリ秒から0.123ミリ秒になりました。

loops は、結合でよく見ます。顧客3人の注文を取る SQL です。

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name, o.id, o.amount
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.id <= 3;
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=5.10..581.82 rows=150 width=20) (actual time=0.016..0.110 rows=150.00 loops=1)
   Buffers: shared hit=162
   ->  Index Scan using customers_pkey on customers c  (cost=0.29..8.34 rows=3 width=16) (actual time=0.002..0.003 rows=3.00 loops=1)
         Index Cond: (id <= 3)
         Index Searches: 1
         Buffers: shared hit=3
   ->  Bitmap Heap Scan on orders o  (cost=4.81..190.66 rows=50 width=12) (actual time=0.007..0.031 rows=50.00 loops=3)
         Recheck Cond: (c.id = customer_id)
         Heap Blocks: exact=150
         Buffers: shared hit=159
         ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.80 rows=50 width=0) (actual time=0.002..0.002 rows=50.00 loops=3)
               Index Cond: (customer_id = c.id)
               Index Searches: 3
               Buffers: shared hit=9
 Planning:
   Buffers: shared hit=105 read=3
 Planning Time: 0.247 ms
 Execution Time: 0.123 ms
(18 rows)

Nested Loop は、外側(上の子)の1行ごとに、内側(下の子)を1回実行します。外側が3行なので、内側の Bitmap Heap Scan は loops=3 で、1回あたり50行、合計150行です。Index Searches: 3 は、インデックスを探した回数の合計(すべての loops の合計)です。外側が0行のときは、内側は一度も実行されず、(never executed) と表示されました。

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name, o.id, o.amount
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.id <= 3 AND c.name = 'nobody';
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=5.10..199.51 rows=50 width=20) (actual time=0.004..0.004 rows=0.00 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using customers_pkey on customers c  (cost=0.29..8.35 rows=1 width=16) (actual time=0.004..0.004 rows=0.00 loops=1)
         Index Cond: (id <= 3)
         Filter: (name = 'nobody'::text)
         Rows Removed by Filter: 3
         Index Searches: 1
         Buffers: shared hit=3
   ->  Bitmap Heap Scan on orders o  (cost=4.81..190.66 rows=50 width=12) (never executed)
         Recheck Cond: (c.id = customer_id)
         ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.80 rows=50 width=0) (never executed)
               Index Cond: (customer_id = c.id)
               Index Searches: 0
 Planning:
   Buffers: shared hit=25
 Planning Time: 0.095 ms
 Execution Time: 0.011 ms
(17 rows)

EXPLAIN ANALYZE は SQL を本当に実行するので、UPDATE や DELETE ではデータが変わります。試すだけなら、トランザクションの中で実行して ROLLBACK します。

BEGIN;
BEGIN
EXPLAIN (ANALYZE, BUFFERS) DELETE FROM orders WHERE id = 1;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Delete on orders  (cost=0.42..8.44 rows=0 width=0) (actual time=0.015..0.015 rows=0.00 loops=1)
   Buffers: shared hit=6
   ->  Index Scan using orders_pkey on orders  (cost=0.42..8.44 rows=1 width=6) (actual time=0.006..0.007 rows=1.00 loops=1)
         Index Cond: (id = 1)
         Index Searches: 1
         Buffers: shared hit=4
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.033 ms
 Execution Time: 0.072 ms
(10 rows)

ROLLBACK;
ROLLBACK
SELECT count(*) FROM orders WHERE id = 1;
 count 
-------
     1
(1 row)

Buffers: hit と read

Buffers は、そのノード(と子のノード)が扱ったブロック(1ブロックは通常8KB)の数です。同じブロックを2回読めば2と数えます。

  • shared hit: PostgreSQL の共有バッファー(メモリのキャッシュ)にあったブロック
  • shared read: 共有バッファーになく、読み込んだブロック。PostgreSQL は OS のキャッシュも使うので、ディスクを読んだとは限りません
  • dirtied、written: それまで変更されていなかった状態から、このクエリが変更したブロック、このクエリのプロセスが共有バッファーから追い出して書き出したブロック(0のときは表示されません)
  • Planning: の下の Buffers: 実行計画を作るときに読んだブロック(システムカタログなど)

コンテナを再起動して共有バッファーを空にし、同じ SQL を2回実行しました。再起動すると psql の接続が切れるので、\q で抜けてから再起動し、もう一度 psql に入ります。

$ docker restart tb-explain-pg
tb-explain-pg
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=4.81..190.66 rows=50 width=47) (actual time=0.161..0.656 rows=50.00 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=50
   Buffers: shared read=53
   ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.80 rows=50 width=0) (actual time=0.021..0.022 rows=50.00 loops=1)
         Index Cond: (customer_id = 42)
         Index Searches: 1
         Buffers: shared read=3
 Planning:
   Buffers: shared hit=70 read=22
 Planning Time: 0.346 ms
 Execution Time: 0.702 ms
(12 rows)

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=4.81..190.66 rows=50 width=47) (actual time=0.013..0.029 rows=50.00 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=50
   Buffers: shared hit=53
   ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.80 rows=50 width=0) (actual time=0.007..0.007 rows=50.00 loops=1)
         Index Cond: (customer_id = 42)
         Index Searches: 1
         Buffers: shared hit=3
 Planning Time: 0.033 ms
 Execution Time: 0.038 ms
(10 rows)

1回目は53ブロックすべてが read、2回目はすべて hit でした。ブロックの数(53)は同じで、時間は0.702ミリ秒から0.038ミリ秒に変わりました。時間はキャッシュの状態で大きく変わるので、計画を比べるときは、ブロックの合計も見るようにしています。

PostgreSQL 18 で変わった点

PostgreSQL 18 のリリースノートには、EXPLAIN について次の変更があります(ほかにもあります)。

  • EXPLAIN ANALYZE に BUFFERS の出力が自動で含まれる。不要なら BUFFERS OFF で消せる
  • 行数が小数で出る(rows=16.67 など)
  • インデックスを使うノードに、インデックスを探した回数(Index Searches)が出る
  • B-tree のスキップスキャン(複合インデックスの先頭の列に条件がなくても使える場合が増えた。あとの章で確かめます)

同じデータを PostgreSQL 17.11 と 18.6 に入れ、BUFFERS を付けずに EXPLAIN ANALYZE を実行して比べました。まず 18.6 です。

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=4.81..190.66 rows=50 width=47) (actual time=0.007..0.018 rows=50.00 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=50
   Buffers: shared hit=53
   ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.80 rows=50 width=0) (actual time=0.003..0.003 rows=50.00 loops=1)
         Index Cond: (customer_id = 42)
         Index Searches: 1
         Buffers: shared hit=3
 Planning Time: 0.016 ms
 Execution Time: 0.023 ms
(10 rows)

17.11 では、Buffers と Index Searches の行がなく、行数は整数でした。

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=4.81..190.66 rows=50 width=47) (actual time=0.031..0.096 rows=50 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=50
   ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.80 rows=50 width=0) (actual time=0.023..0.023 rows=50 loops=1)
         Index Cond: (customer_id = 42)
 Planning Time: 0.238 ms
 Execution Time: 0.129 ms
(7 rows)

17以前でもブロックの数を見るには、EXPLAIN (ANALYZE, BUFFERS) と書きます。18でもそのまま使えるので、この記事ではこの書き方にしています。

ヒットする行が多いと、インデックスは使われない(選択率)

プランナーは、統計(pg_stats)から、条件に合う行の割合(選択率)を見積もります。status の統計には、よく出る値とその割合が入っていました。

SELECT most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';
    most_common_vals     |  most_common_freqs   
-------------------------+----------------------
 {done,canceled,pending} | {0.95,0.0489,0.0011}
(1 row)

統計は標本から集めるので、実際の割合(0.949、0.05、0.001)と少し違います。status にインデックスを作り、値を変えて実行しました。

CREATE INDEX orders_status_idx ON orders (status);
CREATE INDEX
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending';
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using orders_status_idx on orders  (cost=0.42..395.70 rows=550 width=47) (actual time=0.052..2.538 rows=500.00 loops=1)
   Index Cond: (status = 'pending'::text)
   Index Searches: 1
   Buffers: shared hit=46 read=458
 Planning:
   Buffers: shared hit=69 read=1
 Planning Time: 0.239 ms
 Execution Time: 2.570 ms
(8 rows)

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'canceled';
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using orders_status_idx on orders  (cost=0.42..4411.43 rows=24450 width=47) (actual time=0.031..25.152 rows=25000.00 loops=1)
   Index Cond: (status = 'canceled'::text)
   Index Searches: 1
   Buffers: shared hit=693 read=4486
 Planning Time: 0.038 ms
 Execution Time: 25.788 ms
(6 rows)

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'done';
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..11405.00 rows=475000 width=47) (actual time=0.009..25.768 rows=474500.00 loops=1)
   Filter: (status = 'done'::text)
   Rows Removed by Filter: 25500
   Buffers: shared hit=5155
 Planning Time: 0.055 ms
 Execution Time: 37.255 ms
(6 rows)

見積もりの行数は、統計の割合 × 50万行です(0.0011 × 500,000 = 550)。0.1%の pending と5%の canceled は Index Scan、94.9%の done は Seq Scan になりました。

canceled は5%ですが、読んだブロックは5,179で、テーブル全体(5,155ページ)とほぼ同じでした。このデータでは canceled が20行に1行ずつ散らばっているので、ほとんどのページに1行以上あります。行の数の割合が小さくても、行がテーブル全体に散らばっていると、インデックスを使ってもほぼ全ページを読むことになります。プランナーは、列の値の順番とテーブルの物理的な順番の相関(pg_stats の correlation)もコストの計算に使います。

done で、Seq Scan が選ばれにくくなる設定にして(enable_seqscan = off。確認のための設定で、Seq Scan を完全に禁止するものではありません)、インデックスを使う計画と比べました。

SET enable_seqscan = off;
SET
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'done';
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=5305.67..16398.17 rows=475000 width=47) (actual time=7.064..29.290 rows=474500.00 loops=1)
   Recheck Cond: (status = 'done'::text)
   Heap Blocks: exact=5155
   Buffers: shared hit=5159 read=398
   ->  Bitmap Index Scan on orders_status_idx  (cost=0.00..5186.92 rows=475000 width=0) (actual time=6.609..6.610 rows=474500.00 loops=1)
         Index Cond: (status = 'done'::text)
         Index Searches: 1
         Buffers: shared hit=4 read=398
 Planning Time: 0.061 ms
 Execution Time: 40.756 ms
(10 rows)

RESET enable_seqscan;
RESET

インデックスを使うと、テーブルの全ページ(5,155)に加えてインデックスも読むので、ブロックは5,557に増え、時間も37.255ミリ秒から40.756ミリ秒になりました。ほとんどの行を読むなら、テーブルを先頭から読むほうが速いので、プランナーはインデックスを使いません。

統計がないと、見積もりが外れる(ANALYZE の前後)

同じデータの新しいテーブルを作り、ANALYZE の前と後で比べました。ふつうは autovacuum がしばらくして自動で ANALYZE するので、確かめるためにこのテーブルだけ autovacuum を止めています(autovacuum_enabled = off)。大量のデータを入れた直後に、自動の ANALYZE の前に SQL を実行した場合と同じ状態です。

CREATE TABLE orders_new (LIKE orders) WITH (autovacuum_enabled = off);
CREATE TABLE
INSERT INTO orders_new SELECT * FROM orders;
INSERT 0 500000
CREATE INDEX orders_new_status_idx ON orders_new (status);
CREATE INDEX
SELECT count(*) FROM pg_stats WHERE tablename = 'orders_new';
 count 
-------
     0
(1 row)

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders_new WHERE status = 'done';
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders_new  (cost=26.81..3735.25 rows=1856 width=84) (actual time=7.210..29.713 rows=474500.00 loops=1)
   Recheck Cond: (status = 'done'::text)
   Heap Blocks: exact=5155
   Buffers: shared hit=5155 read=402
   ->  Bitmap Index Scan on orders_new_status_idx  (cost=0.00..26.34 rows=1856 width=0) (actual time=6.765..6.766 rows=474500.00 loops=1)
         Index Cond: (status = 'done'::text)
         Index Searches: 1
         Buffers: shared read=402
 Planning:
   Buffers: shared hit=23 read=1
 Planning Time: 0.083 ms
 Execution Time: 41.221 ms
(12 rows)

統計がない(pg_stats が0行)ので、プランナーは done を1,856行と見積もりました。実際は474,500行です。width も84で、ANALYZE の後の47と違います。少ないと見積もったので、インデックスを使う計画を選んでいます。ANALYZE の後です。

ANALYZE orders_new;
ANALYZE
SELECT count(*) FROM pg_stats WHERE tablename = 'orders_new';
 count 
-------
     6
(1 row)

SELECT relname, last_analyze IS NOT NULL AS analyzed, last_autoanalyze
FROM pg_stat_user_tables WHERE relname = 'orders_new';
  relname   | analyzed | last_autoanalyze 
------------+----------+------------------
 orders_new | t        | 
(1 row)

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders_new WHERE status = 'done';
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Seq Scan on orders_new  (cost=0.00..11405.00 rows=474933 width=47) (actual time=0.009..29.401 rows=474500.00 loops=1)
   Filter: (status = 'done'::text)
   Rows Removed by Filter: 25500
   Buffers: shared hit=5155
 Planning:
   Buffers: shared hit=22
 Planning Time: 0.063 ms
 Execution Time: 41.039 ms
(8 rows)

見積もりが474,933行になり、Seq Scan に変わりました。pg_stat_user_tables の last_analyze(手動)と last_autoanalyze(autovacuum)で、最後に統計を集めた日時が分かります。見積もりの rows と実際の rows が桁で違うときは、まずここを見ます。

列に関数をかけると、インデックスは使われない

email にインデックスを作ります。email = '...' では使われますが、lower(email) = '...' では使われませんでした。インデックスに入っているのは email の値で、lower(email) の値ではないからです。

CREATE INDEX orders_email_idx ON orders (email);
CREATE INDEX
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE email = 'user12345@example.com';
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Index Scan using orders_email_idx on orders  (cost=0.42..8.44 rows=1 width=47) (actual time=0.025..0.026 rows=1.00 loops=1)
   Index Cond: (email = 'user12345@example.com'::text)
   Index Searches: 1
   Buffers: shared hit=1 read=3
 Planning:
   Buffers: shared hit=84 read=1
 Planning Time: 0.299 ms
 Execution Time: 0.052 ms
(8 rows)

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE lower(email) = 'user12345@example.com';
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..9530.00 rows=2500 width=47) (actual time=2.269..34.834 rows=1.00 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=5155
   ->  Parallel Seq Scan on orders  (cost=0.00..8280.00 rows=1042 width=47) (actual time=20.174..29.989 rows=0.33 loops=3)
         Filter: (lower(email) = 'user12345@example.com'::text)
         Rows Removed by Filter: 166666
         Buffers: shared hit=5155
 Planning Time: 0.033 ms
 Execution Time: 34.856 ms
(10 rows)

lower(email) の式インデックスを作ると、使われるようになりました。ただし、作った直後は見積もりが2,500行のままでした。

CREATE INDEX orders_lower_email_idx ON orders (lower(email));
CREATE INDEX
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE lower(email) = 'user12345@example.com';
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=71.80..4387.55 rows=2500 width=47) (actual time=0.030..0.031 rows=1.00 loops=1)
   Recheck Cond: (lower(email) = 'user12345@example.com'::text)
   Heap Blocks: exact=1
   Buffers: shared hit=1 read=3
   ->  Bitmap Index Scan on orders_lower_email_idx  (cost=0.00..71.17 rows=2500 width=0) (actual time=0.025..0.026 rows=1.00 loops=1)
         Index Cond: (lower(email) = 'user12345@example.com'::text)
         Index Searches: 1
         Buffers: shared read=3
 Planning:
   Buffers: shared hit=18 read=1
 Planning Time: 0.161 ms
 Execution Time: 0.043 ms
(12 rows)

式インデックスを作った後に ANALYZE すると、その式の統計が集まり、見積もりが1行になって、Index Scan に変わりました。

ANALYZE orders;
ANALYZE
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE lower(email) = 'user12345@example.com';
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using orders_lower_email_idx on orders  (cost=0.42..8.44 rows=1 width=47) (actual time=0.012..0.012 rows=1.00 loops=1)
   Index Cond: (lower(email) = 'user12345@example.com'::text)
   Index Searches: 1
   Buffers: shared hit=4
 Planning:
   Buffers: shared hit=27
 Planning Time: 0.100 ms
 Execution Time: 0.022 ms
(8 rows)

式インデックスは、行を追加・更新するたびに式を計算するので、書き込みは少し重くなります。関数をかけずに書き換えられる条件なら、書き換えるほうが簡単です。よくあるのが、日時の列を日付に変換して比べる書き方です。created_at にインデックスを作って比べました。

CREATE INDEX orders_created_at_idx ON orders (created_at);
CREATE INDEX
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at::date = '2026-03-01';
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..9530.00 rows=2500 width=47) (actual time=3.394..15.123 rows=1440.00 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=5155
   ->  Parallel Seq Scan on orders  (cost=0.00..8280.00 rows=1042 width=47) (actual time=1.790..10.802 rows=480.00 loops=3)
         Filter: ((created_at)::date = '2026-03-01'::date)
         Rows Removed by Filter: 166187
         Buffers: shared hit=5155
 Planning:
   Buffers: shared hit=22 read=1
 Planning Time: 0.107 ms
 Execution Time: 15.181 ms
(12 rows)

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE created_at >= '2026-03-01' AND created_at < '2026-03-02';
                                                                        QUERY PLAN                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using orders_created_at_idx on orders  (cost=0.42..69.06 rows=1482 width=47) (actual time=0.034..0.154 rows=1440.00 loops=1)
   Index Cond: ((created_at >= '2026-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-03-02 00:00:00+00'::timestamp with time zone))
   Index Searches: 1
   Buffers: shared hit=16 read=7
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.072 ms
 Execution Time: 0.196 ms
(8 rows)

同じ1,440行が返りますが、::date で変換した条件は Parallel Seq Scan(5,155ブロック)、範囲で書いた条件は Index Scan(23ブロック)でした。timestamptz の値を日付にするときと、'2026-03-01' を日時として読むときは、どちらもセッションのタイムゾーン(ここでは Etc/UTC)が使われます。

LIKE の前方一致と中間一致

B-tree のインデックスは、LIKE 'abc%' のような前方一致には使えますが、データベースの照合順序(ロケール)が C でないときは、ふつうのインデックスでは使えません。公式の Docker イメージで作ったデータベースは en_US.utf8 でした。

SELECT datlocprovider, datcollate FROM pg_database WHERE datname = current_database();
 datlocprovider | datcollate 
----------------+------------
 c              | en_US.utf8
(1 row)

前の章で作った email のインデックスがある状態で、前方一致を実行しました。

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE email LIKE 'user12345%';
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..8764.17 rows=50 width=47) (actual time=0.861..13.004 rows=11.00 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=5155
   ->  Parallel Seq Scan on orders  (cost=0.00..7759.17 rows=21 width=47) (actual time=5.427..8.575 rows=3.67 loops=3)
         Filter: (email ~~ 'user12345%'::text)
         Rows Removed by Filter: 166663
         Buffers: shared hit=5155
 Planning:
   Buffers: shared hit=147
 Planning Time: 0.339 ms
 Execution Time: 13.050 ms
(12 rows)

インデックスは使われず、Parallel Seq Scan でした。演算子クラス text_pattern_ops を指定したインデックスを作ると、使われました。

CREATE INDEX orders_email_pattern_idx ON orders (email text_pattern_ops);
CREATE INDEX
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE email LIKE 'user12345%';
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using orders_email_pattern_idx on orders  (cost=0.42..8.44 rows=50 width=47) (actual time=0.024..0.027 rows=11.00 loops=1)
   Index Cond: ((email ~>=~ 'user12345'::text) AND (email ~<~ 'user12346'::text))
   Filter: (email ~~ 'user12345%'::text)
   Index Searches: 1
   Buffers: shared hit=2 read=3
 Planning:
   Buffers: shared hit=63 read=4
 Planning Time: 0.282 ms
 Execution Time: 0.045 ms
(9 rows)

Index Cond を見ると、LIKE 'user12345%' が「user12345 以上、user12346 未満」の範囲に置き換えられています。text_pattern_ops のインデックスは、文字を1文字ずつ比べる順番で並べるので、この範囲で探せます。そのかわり、ふつうの < や > の比較には使えません(= には使えます)。範囲の比較も使うなら、ふつうのインデックスも残します。

中間一致(LIKE '%12345%')は、先頭が決まらないので、B-tree のインデックスでは探せません。

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE email LIKE '%12345%';
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..8764.17 rows=50 width=47) (actual time=0.972..16.794 rows=15.00 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=5155
   ->  Parallel Seq Scan on orders  (cost=0.00..7759.17 rows=21 width=47) (actual time=3.779..11.788 rows=5.00 loops=3)
         Filter: (email ~~ '%12345%'::text)
         Rows Removed by Filter: 166662
         Buffers: shared hit=5155
 Planning Time: 0.047 ms
 Execution Time: 16.820 ms
(10 rows)

中間一致にインデックスを使いたいときは、拡張機能の pg_trgm の GIN インデックスが使えます。pg_trgm は PostgreSQL に付属していて、公式の Docker イメージでも CREATE EXTENSION だけで使えました。

CREATE EXTENSION pg_trgm;
CREATE EXTENSION
CREATE INDEX orders_email_trgm_idx ON orders USING gin (email gin_trgm_ops);
CREATE INDEX
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE email LIKE '%12345%';
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=46.94..232.79 rows=50 width=47) (actual time=0.123..0.131 rows=15.00 loops=1)
   Recheck Cond: (email ~~ '%12345%'::text)
   Heap Blocks: exact=6
   Buffers: shared hit=16
   ->  Bitmap Index Scan on orders_email_trgm_idx  (cost=0.00..46.92 rows=50 width=0) (actual time=0.113..0.113 rows=15.00 loops=1)
         Index Cond: (email ~~ '%12345%'::text)
         Index Searches: 1
         Buffers: shared hit=10
 Planning:
   Buffers: shared hit=25
 Planning Time: 0.154 ms
 Execution Time: 0.145 ms
(12 rows)

文字列を3文字ずつに分けた組(トライグラム)で探すので、検索する文字列から3文字の組が1つも取り出せないとき(短すぎる文字列など)は、インデックス全体を読むことになります(公式ドキュメントより)。

複合インデックスの列の順番

(status, created_at) の場合

status が pending で、3月に作られた注文を探します(45行)。status と created_at の単独のインデックスを消してから、(status, created_at) の順に複合インデックスを作りました。

DROP INDEX orders_status_idx, orders_created_at_idx;
DROP INDEX
CREATE INDEX orders_status_created_at_idx ON orders (status, created_at);
CREATE INDEX
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE status = 'pending'
  AND created_at >= '2026-03-01' AND created_at < '2026-04-01';
                                                                                       QUERY PLAN                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using orders_status_created_at_idx on orders  (cost=0.42..100.44 rows=43 width=47) (actual time=0.028..0.056 rows=45.00 loops=1)
   Index Cond: ((status = 'pending'::text) AND (created_at >= '2026-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-04-01 00:00:00+00'::timestamp with time zone))
   Index Searches: 1
   Buffers: shared hit=45 read=3
 Planning:
   Buffers: shared hit=148 read=1
 Planning Time: 0.462 ms
 Execution Time: 0.083 ms
(8 rows)

読んだブロックは48でした。Index Cond の両方の条件で、status = 'pending' の中の3月の範囲だけを読んでいます。

先頭の列に条件がない場合(PostgreSQL 18 のスキップスキャン)

同じ (status, created_at) のインデックスのまま、先頭の列(status)に条件がなく、created_at だけで1日分を探す SQL を実行しました。

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE created_at >= '2026-03-01' AND created_at < '2026-03-02';
                                                                        QUERY PLAN                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using orders_status_created_at_idx on orders  (cost=0.42..2881.63 rows=1482 width=47) (actual time=0.051..0.277 rows=1440.00 loops=1)
   Index Cond: ((created_at >= '2026-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-03-02 00:00:00+00'::timestamp with time zone))
   Index Searches: 6
   Buffers: shared hit=50 read=15
 Planning Time: 0.043 ms
 Execution Time: 0.320 ms
(6 rows)

PostgreSQL 18 では、先頭の列に条件がなくても、インデックスが使われました。Index Searches: 6 は、インデックスを6回探し直したことを表します。先頭の列の値ごとに探し直すことで、先頭の列に条件がなくても後ろの列の条件で読む範囲を狭めるのが、18 で入ったスキップスキャンです。先頭の列の値の種類が少ない(ここでは3種類)ときに選ばれやすく、種類が多いと Seq Scan のほうが選ばれやすい、と公式ドキュメントにあります。6回の内訳までは確かめていません。

同じデータに同じ (status, created_at) のインデックスを作った PostgreSQL 17.11 では、Parallel Seq Scan になりました。

CREATE INDEX orders_status_created_at_idx ON orders (status, created_at);
CREATE INDEX
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE created_at >= '2026-03-01' AND created_at < '2026-03-02';
                                                                         QUERY PLAN                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..9423.50 rows=1435 width=47) (actual time=2.789..12.511 rows=1440 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=5155
   ->  Parallel Seq Scan on orders  (cost=0.00..8280.00 rows=598 width=47) (actual time=0.930..7.864 rows=480 loops=3)
         Filter: ((created_at >= '2026-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-03-02 00:00:00+00'::timestamp with time zone))
         Rows Removed by Filter: 166187
         Buffers: shared hit=5155
 Planning:
   Buffers: shared hit=30 read=1
 Planning Time: 0.226 ms
 Execution Time: 12.573 ms
(12 rows)

版を上げると、同じ SQL とインデックスでも計画が変わることがあります。

(created_at, status) と比べる

インデックスを、列の順番を逆にした (created_at, status) に作り直して、最初と同じ SQL を実行しました。

DROP INDEX orders_status_created_at_idx;
DROP INDEX
CREATE INDEX orders_created_at_status_idx ON orders (created_at, status);
CREATE INDEX
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE status = 'pending'
  AND created_at >= '2026-03-01' AND created_at < '2026-04-01';
                                                                                       QUERY PLAN                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using orders_created_at_status_idx on orders  (cost=0.42..1344.10 rows=43 width=47) (actual time=0.054..1.396 rows=45.00 loops=1)
   Index Cond: ((created_at >= '2026-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-04-01 00:00:00+00'::timestamp with time zone) AND (status = 'pending'::text))
   Index Searches: 1
   Buffers: shared hit=60 read=176
 Planning:
   Buffers: shared hit=23 read=1
 Planning Time: 0.152 ms
 Execution Time: 1.422 ms
(8 rows)

どちらも Index Scan で、Index Cond に両方の条件が入っていますが、読んだブロックは48と236で、5倍近く違いました。公式ドキュメントでは、B-tree の複合インデックスで読む範囲を必ず狭めるのは、先頭から続く列の等号(=)の条件と、等号の条件がない最初の列の範囲などの条件です。それより後ろの列の条件は、インデックスの中で行を確かめるのには使われますが、読む範囲を狭めるとは限りません(PostgreSQL 18 では、上のスキップスキャンで狭められる場合もあります)。今回の計画は、次のようでした。

  • (status, created_at): status = 'pending' の中の、3月の範囲だけを読んだ(48ブロック)
  • (created_at, status): Index Searches: 1 で、3月の範囲(すべての status。データの作り方から約44,640件)を読み、その中で status を確かめた(236ブロック)

等号で比べる列を先に、範囲で比べる列を後にすると、読む範囲が狭くなります。スキップスキャンがあっても、先頭の列の種類が多ければ使われにくいので、よく使う条件に合わせて列の順番を決めるのが基本です。

実行計画は、版・データの分布・統計で変わる

この記事の結果は、PostgreSQL 18.6、既定の設定、このデータでの一例です。今回の確認の中でも、次のように計画や見積もりが変わりました。

  • 版: 先頭の列に条件がない複合インデックスは、18.6 では Index Scan、17.11 では Parallel Seq Scan だった
  • データの分布: 5%の行でも、行が散らばっていたので、ほぼ全ページを読んだ
  • 統計: ANALYZE の前は1,856行、後は474,933行と見積もり、計画が変わった。ANALYZE は大きいテーブルでは標本を取るので、同じデータでも実行するたびに見積もりが少し変わる(1日分の範囲の見積もりは、この記事の確認では1,482行、比較の確認では1,344行。実際は1,440行)

本番のデータベースで調べるときは、本番と同じ版で、本番に近い量と分布のデータを使います。小さいテーブルでの結果を、大きいテーブルにそのまま当てはめることはできない、と公式ドキュメントにもあります。

後片付け

psql を \q で抜けてから、コンテナを消します。-v で、コンテナが作った名前のないボリュームも一緒に消えます。

$ docker rm -f -v tb-explain-pg
tb-explain-pg

参考

スポンサーリンク
PostgreSQLデータベース
スポンサーリンク
シェアする
hayoをフォローする

コメント

タイトルとURLをコピーしました