PostgreSQL EXPLAIN ANALYZE入門 - 実行計画を読み解く

はじめに

「クエリが遅い」という問題に直面したとき、あなたはどのように原因を特定していますか。勘でインデックスを追加したり、クエリを書き換えたりしても、根本的な解決に至らないことがあります。

PostgreSQLには、クエリの実行方法を可視化する強力な機能「EXPLAIN」があります。EXPLAINを使えば、クエリがどのような順序で、どのようなアルゴリズムで実行されるかを確認でき、さらにEXPLAIN ANALYZEを使えば実際の実行時間やスキャンした行数まで把握できます。

本記事では、EXPLAIN/EXPLAIN ANALYZEの使い方から、実行計画の読み方、スキャン方式（Seq Scan、Index Scan、Index Only Scan）やJOINアルゴリズム（Nested Loop、Hash Join、Merge Join）の理解、そしてボトルネックの特定方法までを解説します。

この記事を読むことで、以下のことができるようになります。

• EXPLAINとEXPLAIN ANALYZEの違いを理解し、適切に使い分けられる
• 実行計画の階層構造とコスト・実行時間の読み方を理解できる
• Seq Scan、Index Scan、Index Only Scanの違いを説明できる
• Nested Loop、Hash Join、Merge Joinのアルゴリズムと使い分けを理解できる
• 実行計画からボトルネックを特定し、改善方針を立てられる

前提条件

• PostgreSQL 14以降がインストールされていること
• psqlまたはGUIツール（pgAdmin、DBeaver等）でデータベースに接続できること
• SELECT文、JOIN、インデックスの基本を理解していること

サンプルデータの準備

本記事で使用するサンプルテーブルとデータを準備します。実行計画の違いを明確に確認するため、十分な量のデータを生成します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

-- 従業員テーブル（10万件）
CREATE TABLE employees (
    employee_id   SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    email         VARCHAR(255) NOT NULL,
    department_id INTEGER NOT NULL,
    hire_date     DATE NOT NULL,
    salary        INTEGER NOT NULL,
    is_active     BOOLEAN DEFAULT TRUE
);

-- 部署テーブル（100件）
CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    location      VARCHAR(100) NOT NULL,
    budget        BIGINT NOT NULL
);

-- プロジェクトテーブル（1000件）
CREATE TABLE projects (
    project_id    SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    department_id INTEGER REFERENCES departments(department_id),
    start_date    DATE NOT NULL,
    end_date      DATE,
    status        VARCHAR(20) NOT NULL
);

-- 部署データの生成
INSERT INTO departments (name, location, budget)
SELECT
    '部署' || i,
    (ARRAY['東京', '大阪', '名古屋', '福岡', '札幌'])[1 + (i % 5)],
    10000000 + (i * 100000)
FROM generate_series(1, 100) AS i;

-- 従業員データの生成（10万件）
INSERT INTO employees (name, email, department_id, hire_date, salary, is_active)
SELECT
    '従業員' || i,
    'employee' || i || '@example.com',
    1 + (i % 100),
    DATE '2015-01-01' + (i % 3650),
    300000 + (i % 20) * 25000,
    i % 10 != 0
FROM generate_series(1, 100000) AS i;

-- プロジェクトデータの生成
INSERT INTO projects (name, department_id, start_date, end_date, status)
SELECT
    'プロジェクト' || i,
    1 + (i % 100),
    DATE '2024-01-01' + (i % 365),
    DATE '2024-01-01' + (i % 365) + (30 + i % 180),
    (ARRAY['計画中', '進行中', '完了', '保留'])[1 + (i % 4)]
FROM generate_series(1, 1000) AS i;

-- 統計情報の更新
ANALYZE employees;
ANALYZE departments;
ANALYZE projects;

EXPLAINとEXPLAIN ANALYZEの基本

EXPLAINとは

EXPLAINは、PostgreSQLがクエリをどのように実行するかの計画（実行計画）を表示するコマンドです。クエリを実際に実行せずに、プランナ（クエリ最適化エンジン）が選択した実行戦略を確認できます。

1

EXPLAIN SELECT * FROM employees WHERE department_id = 10;

実行結果の例：

                              QUERY PLAN
----------------------------------------------------------------------
 Seq Scan on employees  (cost=0.00..2137.00 rows=1000 width=52)
   Filter: (department_id = 10)

EXPLAIN ANALYZEとは

EXPLAIN ANALYZEは、クエリを実際に実行し、実行計画に加えて実際の実行時間やスキャンした行数を表示します。

1

EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 10;

実行結果の例：

                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Seq Scan on employees  (cost=0.00..2137.00 rows=1000 width=52) (actual time=0.021..15.432 rows=1000 loops=1)
   Filter: (department_id = 10)
   Rows Removed by Filter: 99000
 Planning Time: 0.089 ms
 Execution Time: 15.678 ms

EXPLAINとEXPLAIN ANALYZEの違い

注意点: EXPLAIN ANALYZEでINSERT、UPDATE、DELETEを実行すると、実際にデータが変更されます。テスト時はトランザクション内で実行し、ROLLBACKするのが安全です。

1
2
3

BEGIN;
EXPLAIN ANALYZE UPDATE employees SET salary = salary + 1000 WHERE department_id = 10;
ROLLBACK;

EXPLAINのオプション

EXPLAINには出力形式や詳細度を調整するオプションがあります。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- BUFFERS: ディスクI/O情報を表示
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM employees WHERE department_id = 10;

-- FORMAT: 出力形式を指定（TEXT/XML/JSON/YAML）
EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM employees WHERE department_id = 10;

-- VERBOSE: カラム情報など詳細を表示
EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM employees WHERE department_id = 10;

-- SETTINGS: デフォルト以外の設定値を表示
EXPLAIN (ANALYZE, SETTINGS) SELECT * FROM employees WHERE department_id = 10;

BUFFERSオプションは特に重要で、共有バッファからの読み取り（shared hit）とディスクからの読み取り（read）を区別できます。

Buffers: shared hit=1024 read=128

この例では、1024ブロックがキャッシュから、128ブロックがディスクから読み込まれたことを示しています。

実行計画の構造を理解する

階層構造の読み方

実行計画は、ツリー構造で表示されます。最も深くインデントされたノードから順に実行され、結果が上位のノードに渡されます。

1
2
3
4
5

EXPLAIN ANALYZE
SELECT e.name, d.name AS department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 400000;

実行結果の例：

                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=3.25..2215.25 rows=33333 width=222) (actual time=0.098..28.456 rows=35000 loops=1)
   Hash Cond: (e.department_id = d.department_id)
   ->  Seq Scan on employees e  (cost=0.00..2137.00 rows=33333 width=126) (actual time=0.015..12.345 rows=35000 loops=1)
         Filter: (salary > 400000)
         Rows Removed by Filter: 65000
   ->  Hash  (cost=2.00..2.00 rows=100 width=104) (actual time=0.067..0.068 rows=100 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 16kB
         ->  Seq Scan on departments d  (cost=0.00..2.00 rows=100 width=104) (actual time=0.008..0.032 rows=100 loops=1)
 Planning Time: 0.234 ms
 Execution Time: 29.876 ms

この実行計画を下から読み解くと：

1. Seq Scan on departments d: departmentsテーブルを全件スキャン（100行）
2. Hash: スキャン結果からハッシュテーブルを構築
3. Seq Scan on employees e: employeesテーブルをスキャンしてsalary > 400000でフィルタ
4. Hash Join: employeesの各行に対してハッシュテーブルを参照して結合

flowchart BT
    A["Seq Scan on departments<br/>100行"]
    B["Hash<br/>ハッシュテーブル構築"]
    C["Seq Scan on employees<br/>フィルタ後35000行"]
    D["Hash Join<br/>35000行"]
    E["結果"]
    
    A --> B
    B --> D
    C --> D
    D --> E

コスト（cost）の読み方

コストは(cost=開始コスト..総コスト rows=推定行数 width=推定行幅)の形式で表示されます。

Seq Scan on employees  (cost=0.00..2137.00 rows=33333 width=126)

コストは相対的な値であり、単位は「ディスクページを順次読み込む時間」を1として算出されます。絶対的な秒数ではありませんが、コストが高いほど処理に時間がかかることを示します。

actual time（実測時間）の読み方

EXPLAIN ANALYZEを使うと、実測値が追加されます。

Seq Scan on employees  (cost=0.00..2137.00 rows=33333 width=126) (actual time=0.015..12.345 rows=35000 loops=1)

推定値と実測値の乖離に注意してください。 上記の例ではrows=33333（推定）に対してrows=35000（実測）となっており、若干の誤差があります。大きな乖離がある場合は、統計情報が古い可能性があります。

Planning TimeとExecution Time

Planning Time: 0.234 ms
Execution Time: 29.876 ms

通常、Execution Timeがパフォーマンス改善の主要なターゲットです。Planning Timeが異常に長い場合は、テーブル数やJOINが多すぎる可能性があります。

スキャン方式を理解する

PostgreSQLがテーブルからデータを取得する方法には複数の種類があります。それぞれの特徴と使い分けを理解することが、パフォーマンスチューニングの基本です。

Seq Scan（シーケンシャルスキャン）

テーブルの全行を先頭から順番にスキャンする方式です。インデックスを使用しません。

1

EXPLAIN ANALYZE SELECT * FROM employees WHERE is_active = true;

Seq Scan on employees  (cost=0.00..2387.00 rows=90000 width=52) (actual time=0.012..18.234 rows=90000 loops=1)
  Filter: (is_active = true)
  Rows Removed by Filter: 10000

Seq Scanが選択されるケース:

• 取得する行がテーブルの大部分（目安として10〜20%以上）を占める場合
• 該当するインデックスが存在しない場合
• テーブルが小さい場合（数百行程度）

Index Scan（インデックススキャン）

インデックスを使用して条件に一致する行を特定し、テーブルから該当行を取得する方式です。

1
2
3
4

-- まずインデックスを作成
CREATE INDEX idx_employees_department ON employees(department_id);

EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 10;

Index Scan using idx_employees_department on employees  (cost=0.29..45.67 rows=1000 width=52) (actual time=0.023..0.456 rows=1000 loops=1)
  Index Cond: (department_id = 10)

Index Scanの動作:

1. インデックスから条件に一致する行のポインタ（CTID）を取得
2. テーブル（ヒープ）にアクセスして実際の行データを取得

flowchart LR
    A["クエリ<br/>department_id = 10"]
    B["インデックス<br/>idx_employees_department"]
    C["テーブル<br/>employees"]
    D["結果行"]
    
    A --> B
    B -->|"CTIDリスト"| C
    C --> D

Index Only Scan（インデックスオンリースキャン）

クエリで必要なカラムがすべてインデックスに含まれている場合、テーブルにアクセスせずインデックスだけで結果を返せる方式です。

1
2
3
4

-- 複合インデックスを作成
CREATE INDEX idx_employees_dept_salary ON employees(department_id, salary);

EXPLAIN ANALYZE SELECT department_id, salary FROM employees WHERE department_id = 10;

Index Only Scan using idx_employees_dept_salary on employees  (cost=0.29..35.12 rows=1000 width=8) (actual time=0.021..0.234 rows=1000 loops=1)
  Index Cond: (department_id = 10)
  Heap Fetches: 0

Heap Fetches: 0 は、テーブルへのアクセスが一切発生しなかったことを示します。Index Only Scanが成立するためには、テーブルのVisibility Map（可視性マップ）が最新である必要があります。VACUUMを定期的に実行することで、Index Only Scanの効率が向上します。

Bitmap Index Scan / Bitmap Heap Scan

複数の条件がある場合や、Index Scanでは効率が悪い（取得行が多い）場合に使用される方式です。

1
2
3

EXPLAIN ANALYZE
SELECT * FROM employees
WHERE department_id IN (10, 20, 30) AND salary > 400000;

Bitmap Heap Scan on employees  (cost=25.67..1456.78 rows=1500 width=52) (actual time=0.234..2.345 rows=1400 loops=1)
  Recheck Cond: (department_id = ANY ('{10,20,30}'::integer[]))
  Filter: (salary > 400000)
  Heap Blocks: exact=890
  ->  Bitmap Index Scan on idx_employees_department  (cost=0.00..25.30 rows=3000 width=0) (actual time=0.189..0.189 rows=3000 loops=1)
        Index Cond: (department_id = ANY ('{10,20,30}'::integer[]))

Bitmap Scanの動作:

1. Bitmap Index Scan: インデックスから条件に一致する行のビットマップを作成
2. Bitmap Heap Scan: ビットマップに基づいてテーブルブロックを効率的に読み込み

flowchart LR
    A["インデックス"]
    B["ビットマップ<br/>（どのブロックに<br/>該当行があるか）"]
    C["テーブルブロック<br/>を順番に読む"]
    D["結果"]
    
    A -->|"Bitmap Index Scan"| B
    B -->|"Bitmap Heap Scan"| C
    C --> D

スキャン方式の比較

JOINアルゴリズムを理解する

複数テーブルを結合する際、PostgreSQLは状況に応じて最適なアルゴリズムを選択します。

Nested Loop Join（ネステッドループ結合）

外側テーブルの各行に対して、内側テーブルを繰り返しスキャンする方式です。

1
2
3
4
5

EXPLAIN ANALYZE
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id < 100;

Nested Loop  (cost=0.29..456.78 rows=99 width=222) (actual time=0.034..0.567 rows=99 loops=1)
  ->  Index Scan using employees_pkey on employees e  (cost=0.29..12.34 rows=99 width=126) (actual time=0.012..0.089 rows=99 loops=1)
        Index Cond: (employee_id < 100)
  ->  Index Scan using departments_pkey on departments d  (cost=0.14..4.16 rows=1 width=104) (actual time=0.003..0.003 rows=1 loops=99)
        Index Cond: (department_id = e.department_id)

Nested Loop Joinの特徴:

• 外側テーブルの行数 × 内側テーブルのスキャン回数
• 内側テーブルにインデックスがあると効率的
• 外側テーブルの行数が少ない場合に有利
• loops=99は内側スキャンが99回実行されたことを示す

flowchart LR
    subgraph outer["外側（employees）"]
        O1["行1"] --> O2["行2"] --> O3["行3"] --> O4["..."] --> O5["行99"]
    end
    
    subgraph inner["内側（departments）"]
        I["インデックス検索<br/>×99回"]
    end
    
    O1 -.-> I
    O2 -.-> I
    O3 -.-> I
    O5 -.-> I

Hash Join（ハッシュ結合）

小さい方のテーブルからハッシュテーブルを構築し、大きい方のテーブルをスキャンしながらハッシュテーブルを参照して結合する方式です。

1
2
3
4

EXPLAIN ANALYZE
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Hash Join  (cost=3.25..2215.25 rows=100000 width=222) (actual time=0.098..32.456 rows=100000 loops=1)
  Hash Cond: (e.department_id = d.department_id)
  ->  Seq Scan on employees e  (cost=0.00..1887.00 rows=100000 width=126) (actual time=0.012..8.234 rows=100000 loops=1)
  ->  Hash  (cost=2.00..2.00 rows=100 width=104) (actual time=0.067..0.068 rows=100 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 16kB
        ->  Seq Scan on departments d  (cost=0.00..2.00 rows=100 width=104) (actual time=0.008..0.032 rows=100 loops=1)

Hash Joinの特徴:

• 2つのフェーズ: Build（ハッシュテーブル構築）とProbe（検索）
• メモリに収まる場合は非常に高速
• 等価結合（=）でのみ使用可能
• Memory Usage: 16kBはハッシュテーブルのメモリ使用量

flowchart TB
    subgraph build["Buildフェーズ"]
        D["departments<br/>100行"] --> H["ハッシュテーブル<br/>構築"]
    end
    
    subgraph probe["Probeフェーズ"]
        E["employees<br/>10万行"] --> P["ハッシュテーブル<br/>を参照して結合"]
    end
    
    H --> P
    P --> R["結果"]

Merge Join（マージ結合）

両方のテーブルを結合キーでソートし、並行してスキャンしながら結合する方式です。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- ソート済みのデータを強制的にMerge Joinで結合させる例
SET enable_hashjoin = off;
SET enable_nestloop = off;

EXPLAIN ANALYZE
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
ORDER BY e.department_id;

-- 設定を元に戻す
RESET enable_hashjoin;
RESET enable_nestloop;

Merge Join  (cost=9876.54..12345.67 rows=100000 width=222) (actual time=89.123..156.789 rows=100000 loops=1)
  Merge Cond: (e.department_id = d.department_id)
  ->  Sort  (cost=9800.00..10050.00 rows=100000 width=126) (actual time=78.234..98.456 rows=100000 loops=1)
        Sort Key: e.department_id
        Sort Method: external merge  Disk: 5432kB
        ->  Seq Scan on employees e  (cost=0.00..1887.00 rows=100000 width=126) (actual time=0.012..12.345 rows=100000 loops=1)
  ->  Sort  (cost=5.32..5.57 rows=100 width=104) (actual time=0.045..0.056 rows=100 loops=1)
        Sort Key: d.department_id
        Sort Method: quicksort  Memory: 32kB
        ->  Seq Scan on departments d  (cost=0.00..2.00 rows=100 width=104) (actual time=0.008..0.023 rows=100 loops=1)

Merge Joinの特徴:

• 両テーブルが結合キーでソート済みである必要がある
• ソート済みでない場合、事前にソートが必要（コストが高い）
• 大量データの結合で、結果もソート順で必要な場合に有利
• 範囲結合（<、>、BETWEEN）でも使用可能

JOINアルゴリズムの比較

ボトルネックの特定方法

実行計画を分析してパフォーマンス問題を特定する実践的な方法を解説します。

パターン1: 推定値と実測値の大きな乖離

Seq Scan on employees  (cost=0.00..2137.00 rows=1 width=52) (actual time=0.012..18.456 rows=50000 loops=1)
  Filter: (some_column = 'value')

rows=1（推定）に対してrows=50000（実測）と大きく異なっています。

原因:

• 統計情報が古い
• 特殊な値の分布

対処法:

1
2
3
4
5
6

-- 統計情報を更新
ANALYZE employees;

-- より詳細な統計情報を収集（サンプル数を増やす）
ALTER TABLE employees ALTER COLUMN some_column SET STATISTICS 1000;
ANALYZE employees;

パターン2: Seq Scanが選択されているが、Index Scanが期待される

Seq Scan on employees  (cost=0.00..2387.00 rows=50000 width=52) (actual time=0.012..25.678 rows=50000 loops=1)
  Filter: (department_id = 10)
  Rows Removed by Filter: 50000

確認ポイント:

1. インデックスが存在するか

1
2
3

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'employees';

2. インデックスが効く条件になっているか

1
2
3
4
5

-- NGパターン: 関数を適用している
SELECT * FROM employees WHERE UPPER(email) = 'TEST@EXAMPLE.COM';

-- OKパターン: そのままの値で比較
SELECT * FROM employees WHERE email = 'test@example.com';

3. 取得行数が多すぎないか（全体の10〜20%以上だとSeq Scanが有利と判断されることがある）

パターン3: Nested Loopのloopsが非常に大きい

Nested Loop  (cost=0.29..45678.90 rows=10000 width=222) (actual time=0.034..234.567 rows=10000 loops=1)
  ->  Seq Scan on table_a  (cost=0.00..123.00 rows=10000 width=100) (actual time=0.012..5.678 rows=10000 loops=1)
  ->  Index Scan using idx_b on table_b  (cost=0.29..4.50 rows=1 width=122) (actual time=0.015..0.015 rows=1 loops=10000)
        Index Cond: (a_id = table_a.id)

内側のIndex Scanが10000回実行されています。1回あたり0.015msでも、合計150msになります。

対処法:

• Hash Joinへの切り替えを検討
• 外側テーブルの行数を減らすフィルタ条件を追加
• work_memを増やしてHash Joinを促す

1

SET work_mem = '256MB';

パターン4: Sortがディスクに溢れている

Sort  (cost=9800.00..10050.00 rows=100000 width=126) (actual time=78.234..98.456 rows=100000 loops=1)
  Sort Key: department_id
  Sort Method: external merge  Disk: 5432kB

Sort Method: external merge Disk: 5432kBは、メモリに収まらずディスクを使用したことを示しています。

対処法:

1
2
3
4
5

-- work_memを増やす
SET work_mem = '64MB';

-- または、ソートを避けるインデックスを作成
CREATE INDEX idx_employees_dept ON employees(department_id);

パターン5: Bitmap Heap ScanのRecheck Condで大量に除外

Bitmap Heap Scan on employees  (cost=25.67..1456.78 rows=100 width=52) (actual time=0.234..15.678 rows=100 loops=1)
  Recheck Cond: (department_id = 10)
  Rows Removed by Index Recheck: 50000
  Filter: (salary > 500000)
  Rows Removed by Filter: 900
  Heap Blocks: exact=890 lossy=1234

lossyブロックが多い場合、ビットマップがメモリに収まらずに精度が落ちています。

対処法:

1
2
3
4
5

-- work_memを増やす
SET work_mem = '64MB';

-- または、より選択性の高い複合インデックスを作成
CREATE INDEX idx_employees_dept_salary ON employees(department_id, salary);

ボトルネック特定のチェックリスト

実行計画を分析する際のチェックリストです。

実践的なパフォーマンス改善例

実際のシナリオに基づいた改善例を紹介します。

ケース1: 遅いレポートクエリの改善

問題のクエリ：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

EXPLAIN ANALYZE
SELECT
    d.name AS department_name,
    COUNT(*) AS employee_count,
    AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.hire_date >= '2023-01-01'
GROUP BY d.name
ORDER BY avg_salary DESC;

実行計画（改善前）：

Sort  (cost=2345.67..2346.00 rows=100 width=140) (actual time=45.678..45.690 rows=100 loops=1)
  Sort Key: (avg(e.salary)) DESC
  Sort Method: quicksort  Memory: 32kB
  ->  HashAggregate  (cost=2340.00..2342.50 rows=100 width=140) (actual time=45.234..45.456 rows=100 loops=1)
        Group Key: d.name
        ->  Hash Join  (cost=3.25..2215.25 rows=25000 width=108) (actual time=0.098..35.456 rows=25000 loops=1)
              Hash Cond: (e.department_id = d.department_id)
              ->  Seq Scan on employees e  (cost=0.00..2137.00 rows=25000 width=12) (actual time=0.021..28.234 rows=25000 loops=1)
                    Filter: (hire_date >= '2023-01-01'::date)
                    Rows Removed by Filter: 75000
              ->  Hash  (cost=2.00..2.00 rows=100 width=104) (actual time=0.067..0.068 rows=100 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 16kB
                    ->  Seq Scan on departments d  (cost=0.00..2.00 rows=100 width=104) (actual time=0.008..0.032 rows=100 loops=1)
Planning Time: 0.345 ms
Execution Time: 45.890 ms

問題点の分析:

• employeesのSeq Scanで75000行が除外されている（Rows Removed by Filter: 75000）
• hire_dateにインデックスがない

改善策:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- hire_dateにインデックスを作成
CREATE INDEX idx_employees_hire_date ON employees(hire_date);

-- 再度実行
EXPLAIN ANALYZE
SELECT
    d.name AS department_name,
    COUNT(*) AS employee_count,
    AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.hire_date >= '2023-01-01'
GROUP BY d.name
ORDER BY avg_salary DESC;

実行計画（改善後）：

Sort  (cost=890.12..890.45 rows=100 width=140) (actual time=12.345..12.356 rows=100 loops=1)
  Sort Key: (avg(e.salary)) DESC
  Sort Method: quicksort  Memory: 32kB
  ->  HashAggregate  (cost=885.00..887.50 rows=100 width=140) (actual time=12.123..12.234 rows=100 loops=1)
        Group Key: d.name
        ->  Hash Join  (cost=58.67..760.00 rows=25000 width=108) (actual time=0.456..8.234 rows=25000 loops=1)
              Hash Cond: (e.department_id = d.department_id)
              ->  Bitmap Heap Scan on employees e  (cost=55.42..680.00 rows=25000 width=12) (actual time=0.345..4.567 rows=25000 loops=1)
                    Recheck Cond: (hire_date >= '2023-01-01'::date)
                    Heap Blocks: exact=1234
                    ->  Bitmap Index Scan on idx_employees_hire_date  (cost=0.00..49.17 rows=25000 width=0) (actual time=0.234..0.234 rows=25000 loops=1)
                          Index Cond: (hire_date >= '2023-01-01'::date)
              ->  Hash  (cost=2.00..2.00 rows=100 width=104) (actual time=0.067..0.068 rows=100 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 16kB
                    ->  Seq Scan on departments d  (cost=0.00..2.00 rows=100 width=104) (actual time=0.008..0.032 rows=100 loops=1)
Planning Time: 0.456 ms
Execution Time: 12.567 ms

改善結果: 45.890ms → 12.567ms（約3.6倍高速化）

ケース2: カバリングインデックスによる最適化

問題のクエリ：

1
2
3
4
5
6

EXPLAIN ANALYZE
SELECT employee_id, salary
FROM employees
WHERE department_id = 50
ORDER BY salary DESC
LIMIT 10;

改善策：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- カバリングインデックスを作成（必要なカラムをすべて含む）
CREATE INDEX idx_employees_dept_salary_id
ON employees(department_id, salary DESC)
INCLUDE (employee_id);

-- 再度実行
EXPLAIN ANALYZE
SELECT employee_id, salary
FROM employees
WHERE department_id = 50
ORDER BY salary DESC
LIMIT 10;

Limit  (cost=0.42..1.23 rows=10 width=8) (actual time=0.023..0.034 rows=10 loops=1)
  ->  Index Only Scan using idx_employees_dept_salary_id on employees  (cost=0.42..85.67 rows=1000 width=8) (actual time=0.021..0.031 rows=10 loops=1)
        Index Cond: (department_id = 50)
        Heap Fetches: 0
Planning Time: 0.123 ms
Execution Time: 0.056 ms

Index Only ScanとHeap Fetches: 0により、テーブルへのアクセスが完全に不要になりました。

まとめ

EXPLAIN ANALYZEは、PostgreSQLクエリのパフォーマンス問題を診断するための必須ツールです。

本記事で学んだ主要ポイント：

1. EXPLAIN vs EXPLAIN ANALYZE: EXPLAINは計画のみ、EXPLAIN ANALYZEは実測値も取得。UPDATE/DELETEにはトランザクションで保護
2. 実行計画の読み方: 下から上へ、インデント深い方から実行。コストは相対値、actual timeが実測
3. スキャン方式: Seq Scan（全件）、Index Scan（インデックス→テーブル）、Index Only Scan（インデックスのみ）
4. JOINアルゴリズム: Nested Loop（少量×インデックス）、Hash Join（中〜大規模の等価結合）、Merge Join（ソート済み大規模）
5. ボトルネック特定: 推定値と実測値の乖離、不要なSeq Scan、多すぎるloops、ディスクソートに注目

実行計画を読み解くスキルは、一朝一夕では身につきません。日常的にEXPLAIN ANALYZEを実行し、さまざまなパターンを経験することで、クエリのボトルネックを瞬時に見抜けるようになります。

参考リンク

• PostgreSQL公式ドキュメント - EXPLAIN
• PostgreSQL公式ドキュメント - 実行計画の利用
• PostgreSQL公式ドキュメント - インデックスの種類
• PostgreSQL公式ドキュメント - プランナ/オプティマイザ
• PostgreSQL公式ドキュメント - パフォーマンスのヒント