EXPLAIN を読んでみる

2025 7/04

2025年7月4日 2025年7月3日

この記事について

本記事は、2022年2月時点で社内向けに作成された技術資料をもとに再編集したものです。
掲載されている技術情報や仕様、価格などは当時の内容に基づいており、現在の状況とは異なる可能性があります。
ご利用の際は、各種公式ドキュメントにて最新情報をご確認のうえ、参考にしていただければ幸いです。

インデックスについて復習

論理的には

あらかじめデータを特定の順序で整理しておくものです。

エクセルで事前にソートしておくイメージに近いかもしれませんが、インデックスは複数作成でき、全体をスキャンせずに特定のデータを見つけることができます。

複合インデックスについて

※「エクセルで事前にソートしておくイメージ」の喩えを続けて使います。※

例えば「苗字」と「名前」で別々のカラムがあるとします。

このとき複合インデックスを作成すると、まず苗字でソートされ、同じ苗字の中ではさらに名前でソートされたような構造になります。（なので、名前だけでは検索することができません。）

また後述する「カバリングインデックス（Index Only Scan）」を意識して設計することもあります。

実装的には

インデックスの内部構造は、一般的に B木（B-Tree） で実装されています。
B木は、検索・挿入・削除といった操作を効率よく処理でき、検索時間の計算量は O(log n) です。

出典：PostgreSQL公式ドキュメント – 9.9. B-tree インデックス
https://www.postgresql.org/docs/current/indexes-types.html#INDEXES-TYPES-BTREE

EXPLAIN を読んでみる

実際にステートメントを実行したいときは EXPLAIN ANALYZE と書きます。
（ INSERT などを間違えて実行しないよう注意してください）

使用したDBはこちらです。

version: '3'
services:
  db:
    image: postgres:14
    container_name: 'db'
    environment:
      POSTGRES_USER: user
      POSTGRES_PASSWORD: user
      POSTGRES_DB: user
      TZ: Asia/Tokyo
    ports:
      - "5432:5432"
    volumes:
      - database:/var/lib/postgresql/data
volumes:
  database:
    driver: local

docker-compose.yml

スキーマとデータ

数字が入っているだけのシンプルなテーブルです。

name カラムは、説明のために便宜的に追加しています。

CREATE TABLE IF NOT EXISTS entries (
  n INTEGER NOT NULL,
  name TEXT
);

CREATE INDEX idx_n ON entries (n);
-- CREATE INDEX idx_n_name ON entries (n, name); -- 複合インデックス

INSERT INTO entries(n, name) SELECT n, '太郎' AS name FROM generate_series(1, 10000000) AS n;
INSERT INTO entries(n, name) VALUES(1000, '花子');
INSERT INTO entries(n, name) SELECT n, 'john' AS name FROM generate_series(1, 10000000) AS n;

Index Scan

インデックスを使用した通常の検索です。

EXPLAIN ANALYZE SELECT
  n, name
FROM
  entries
WHERE
  n = 123456
;

↓

                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Index Scan using idx_n on entries  (cost=0.44..11.44 rows=2 width=10) (actual time=0.029..0.035 rows=2 loops=1)
   Index Cond: (n = 123456)
 Planning Time: 0.257 ms
 Execution Time: 0.052 ms
(4 rows)

IN 句に多くの値を含めた場合

EXPLAIN ANALYZE SELECT
  n, name
FROM
  entries
WHERE
  n IN (10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 130, 140, 150, 160, 170, 180, 190, 200, 10000, 10001, 10002, 10003, 10004, 10005, 10006);

↓

user=# EXPLAIN ANALYZE SELECT n, name FROM entries WHERE n IN (10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120, 130, 140, 150, 160, 170, 180, 190, 200, 10000, 10001, 10002, 10003, 10004, 10005, 10006);

QUERY PLAN                                 
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_n on entries  (cost=0.44..291.23 rows=57 width=10) (actual time=0.016..0.068 rows=54 loops=1)
   Index Cond: (n = ANY ('{10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,10000,10001,10002,10003,10004,10005,10006}'::integer[]))
 Planning Time: 0.062 ms
 Execution Time: 0.081 ms
(4 rows)

Index Scan (range)

EXPLAIN ANALYZE SELECT
  n, name
FROM
  entries
WHERE
  n BETWEEN 1000000 AND 1000100
;

↓

user=# EXPLAIN ANALYZE SELECT n, name FROM entries WHERE n BETWEEN 1000000 AND 1000100;

QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_n on entries  (cost=0.44..628.10 rows=208 width=10) (actual time=0.058..0.218 rows=202 loops=1)
   Index Cond: ((n >= 1000000) AND (n <= 1000100))
 Planning Time: 0.262 ms
 Execution Time: 0.267 ms
(4 rows)

下記は範囲が複数ある場合（今回は2つ）です。

EXPLAIN ANALYZE SELECT
  n, name
FROM
  entries
WHERE
  n BETWEEN 1000000 AND 1000100
  OR n < 100
;

↓

user=# EXPLAIN ANALYZE SELECT n, name FROM entries WHERE n BETWEEN 1000000 AND 1000100 OR n < 100;

QUERY PLAN                                                  
------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on entries  (cost=12.78..1632.80 rows=423 width=10) (actual time=0.091..0.197 rows=400 loops=1)
   Recheck Cond: (((n >= 1000000) AND (n <= 1000100)) OR (n < 100))
   Heap Blocks: exact=5
   ->  BitmapOr  (cost=12.78..12.78 rows=423 width=0) (actual time=0.072..0.073 rows=0 loops=1)
         ->  Bitmap Index Scan on idx_n  (cost=0.00..6.52 rows=208 width=0) (actual time=0.049..0.050 rows=202 loops=1)
               Index Cond: ((n >= 1000000) AND (n <= 1000100))
         ->  Bitmap Index Scan on idx_n  (cost=0.00..6.05 rows=215 width=0) (actual time=0.021..0.021 rows=198 loops=1)
               Index Cond: (n < 100)
 Planning Time: 0.250 ms
 Execution Time: 0.275 ms
(10 rows)

Index Scan2つを組み合わせて使われているようです。

Seq Scan

インデックスを使用しない検索では、 Seq Scan と表示されます。

EXPLAIN ANALYZE SELECT
  n, name
FROM
  entries
WHERE
  name = '花子';

↓

user=# EXPLAIN ANALYZE SELECT n, name FROM entries WHERE name = '花子';

QUERY PLAN                                                 
--------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..213276.62 rows=1 width=10) (actual time=842.915..846.399 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on entries  (cost=0.00..212276.52 rows=1 width=10) (actual time=703.329..770.147 rows=0 loops=3)
         Filter: (name = '花子'::text)
         Rows Removed by Filter: 6666667
 Planning Time: 0.451 ms
 JIT:
   Functions: 6
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 2.176 ms, Inlining 0.000 ms, Optimization 0.631 ms, Emission 9.292 ms, Total 12.100 ms
 Execution Time: 847.586 ms
(12 rows)

インデックスが存在していても、使われるとは限りません。

EXPLAIN ANALYZE SELECT
  n, name
FROM
  entries
WHERE
  n < 9000000
;

↓

user=# EXPLAIN ANALYZE SELECT n, name FROM entries WHERE n < 9000000;

QUERY PLAN                                                  
------------------------------------------------------------------------------------------------------------------------
 Seq Scan on entries  (cost=0.00..358111.05 rows=17929078 width=10) (actual time=2.639..1346.411 rows=17999999 loops=1)
   Filter: (n < 9000000)
   Rows Removed by Filter: 2000002
 Planning Time: 0.088 ms
 JIT:
   Functions: 2
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 0.405 ms, Inlining 0.000 ms, Optimization 0.214 ms, Emission 2.301 ms, Total 2.920 ms
 Execution Time: 1751.380 ms
(9 rows)

Index Only Scan

クエリに必要なすべてのカラムがインデックスに含まれているとき（これを「カバリングインデックス」と呼びます）、PostgreSQLは「Index Only Scan」という効率的な実行計画を選択できます。

ですが、インデックスを増やしすぎると、メモリ使用量の増加によりキャッシュ効率が下がりパフォーマンスが低下することがあります。

EXPLAIN ANALYZE SELECT
  n
FROM
  entries
WHERE
  n = 32768;

↓

user=# EXPLAIN ANALYZE SELECT n FROM entries WHERE n = 32768;

QUERY PLAN                                                    
--------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_n on entries  (cost=0.44..4.47 rows=2 width=4) (actual time=0.161..0.167 rows=2 loops=1)
   Index Cond: (n = 32768)
   Heap Fetches: 0
 Planning Time: 0.174 ms
 Execution Time: 0.204 ms
(5 rows)

Rails で EXPLAIN する

今回調べていて初めて知ったのですが、ActiveRecord では #explain メソッドが使えるようです。

> Item.where(id: 1).explain
Item Load (0.4ms)  SELECT "items".* FROM "items" WHERE "items"."id" = $1  [["id", 1]]
=> EXPLAIN for: SELECT "items".* FROM "items" WHERE "items"."id" = $1 [["id", 1]]
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on items  (cost=0.00..1.09 rows=1 width=1401)
   Filter: (id = '1'::bigint)
(2 rows)

このケースではデータが少ないのでインデックスが使われず、 Seq Scan となっています。