PostgreSQLインデックス入門 - 検索を高速化する仕組み

はじめに

データベースの検索速度は、アプリケーションのユーザー体験に直結する重要な要素です。数万件程度のデータでは問題にならなくても、数百万件、数千万件と増えていくと、検索に数秒、数十秒かかることも珍しくありません。

この問題を解決する最も効果的な手段が「インデックス」です。インデックスとは、書籍の索引のように、データベース内の特定のカラムに対して検索を高速化するためのデータ構造です。適切なインデックスを設計することで、検索速度を数百倍から数千倍に改善できることもあります。

本記事では、PostgreSQLにおけるインデックスの仕組みから実践的な活用方法までを解説します。B-Treeインデックスの構造、CREATE INDEX文による作成、単一カラムインデックスと複合インデックスの使い分け、インデックスが効く条件・効かない条件、そしてインデックスのトレードオフについて、EXPLAIN ANALYZEによる実際の検証を交えながら学んでいきます。

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

• インデックスの仕組み（特にB-Tree）を理解し、なぜ高速化できるのか説明できる
• CREATE INDEX文でインデックスを作成・削除できる
• 単一カラムインデックスと複合インデックスを適切に使い分けられる
• インデックスが効く条件・効かない条件を理解し、効果的なインデックスを設計できる
• インデックスのトレードオフを理解し、適切な判断ができる

前提条件

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

サンプルデータの準備

本記事で使用するサンプルテーブルとデータを準備します。インデックスの効果を体感するため、10万件のデータを生成します。

 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

-- 従業員テーブルの作成
CREATE TABLE employees (
    employee_id   SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    email         VARCHAR(255) NOT NULL,
    department    VARCHAR(50) NOT NULL,
    hire_date     DATE NOT NULL,
    salary        INTEGER NOT NULL,
    is_active     BOOLEAN DEFAULT TRUE,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 10万件のテストデータを生成
INSERT INTO employees (name, email, department, hire_date, salary, is_active)
SELECT
    '従業員' || i,
    'employee' || i || '@example.com',
    (ARRAY['営業', '開発', '人事', '経理', 'マーケティング'])[1 + (i % 5)],
    DATE '2015-01-01' + (i % 3650),
    300000 + (i % 10) * 50000,
    i % 10 != 0
FROM generate_series(1, 100000) AS i;

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

データが正しく作成されたことを確認します。

1
2
3
4

SELECT COUNT(*) FROM employees;
-- 結果: 100000

SELECT * FROM employees LIMIT 5;

インデックスとは何か

インデックスの基本概念

インデックス（索引）とは、テーブルの特定のカラムに対して作成する、検索を高速化するためのデータ構造です。書籍の巻末にある索引をイメージしてください。

書籍で特定の用語を探す場合、2つの方法があります。

1. 全ページを順番に読む: 最初のページから最後まで順に見ていく（フルスキャン）
2. 索引を使う: 巻末の索引で用語を探し、そこに記載されたページ番号に直接ジャンプする

明らかに後者の方が効率的です。データベースのインデックスも同じ原理で動作します。

flowchart LR
    subgraph without["インデックスなし（Seq Scan）"]
        A1[行1] --> A2[行2] --> A3[行3] --> A4[...] --> A5[行N]
    end
    
    subgraph with["インデックスあり（Index Scan）"]
        B1[インデックス] --> B2[該当行へ直接アクセス]
    end

インデックスがない場合の検索

インデックスがない状態で、特定のメールアドレスを持つ従業員を検索してみましょう。

1
2

EXPLAIN ANALYZE
SELECT * FROM employees WHERE email = 'employee50000@example.com';

実行結果の例：

Seq Scan on employees  (cost=0.00..2137.00 rows=1 width=76) (actual time=12.345..24.567 loops=1)
  Filter: ((email)::text = 'employee50000@example.com'::text)
  Rows Removed by Filter: 99999
Planning Time: 0.123 ms
Execution Time: 24.789 ms

「Seq Scan」と表示されています。これは「Sequential Scan（シーケンシャルスキャン）」の略で、テーブルの全行を順番に読み込んで条件に一致するか確認していることを意味します。10万件のデータをすべてチェックしているため、約25ミリ秒かかっています。

インデックスを作成した場合

emailカラムにインデックスを作成してみましょう。

1
2
3
4
5
6

-- emailカラムにインデックスを作成
CREATE INDEX idx_employees_email ON employees (email);

-- 再度検索を実行
EXPLAIN ANALYZE
SELECT * FROM employees WHERE email = 'employee50000@example.com';

実行結果の例：

Index Scan using idx_employees_email on employees  (cost=0.42..8.44 rows=1 width=76) (actual time=0.045..0.047 loops=1)
  Index Cond: ((email)::text = 'employee50000@example.com'::text)
Planning Time: 0.234 ms
Execution Time: 0.078 ms

「Index Scan」に変わり、実行時間が約0.08ミリ秒に短縮されました。約300倍の高速化です。

B-Treeインデックスの仕組み

PostgreSQLのインデックス種類

PostgreSQLは複数のインデックス種類をサポートしていますが、デフォルトで最も広く使われるのがB-Treeインデックスです。

本記事では、最も汎用的なB-Treeインデックスに焦点を当てて解説します。

B-Treeとは

B-Tree（Balanced Tree）は、データを階層的に整理した木構造のデータ構造です。「Balanced」という名前の通り、木の各枝の深さが均等に保たれるため、どのデータを検索しても同程度の時間で到達できます。

B-Treeは以下の3つの層で構成されます。

1. ルートノード: 木の最上位にある1つのノード
2. 内部ノード: ルートとリーフの間にあるノード（ガイドポストの役割）
3. リーフノード: 実際のデータへのポインタを持つ最下層のノード

flowchart TB
    subgraph root["ルートノード"]
        R["50 | 100"]
    end
    
    subgraph internal["内部ノード"]
        I1["10 | 30"]
        I2["60 | 80"]
        I3["120 | 150"]
    end
    
    subgraph leaf["リーフノード"]
        L1["1,5,8"]
        L2["12,20,28"]
        L3["35,42,48"]
        L4["52,55,58"]
        L5["65,70,78"]
        L6["82,90,95"]
        L7["105,110,118"]
        L8["125,140,148"]
        L9["160,170,180"]
    end
    
    R --> I1
    R --> I2
    R --> I3
    
    I1 --> L1
    I1 --> L2
    I1 --> L3
    
    I2 --> L4
    I2 --> L5
    I2 --> L6
    
    I3 --> L7
    I3 --> L8
    I3 --> L9

B-Treeの検索プロセス

例えば、値「70」を検索する場合の流れを追ってみましょう。

1. ルートノード: 「50 | 100」を確認。70は50より大きく100より小さいので、中央の子ノードへ
2. 内部ノード: 「60 | 80」を確認。70は60より大きく80より小さいので、中央のリーフへ
3. リーフノード: 「65, 70, 78」の中から70を発見

このように、10万件のデータがあっても、B-Treeでは数回のノード探索で目的のデータに到達できます。計算量は $O(\log n)$ となり、データ量が増えても検索時間の増加は緩やかです。

B-Treeが効率的な演算子

B-Treeインデックスは以下の演算子で効率的に動作します。

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

-- 等価検索
SELECT * FROM employees WHERE email = 'employee100@example.com';

-- 範囲検索
SELECT * FROM employees WHERE salary > 500000;
SELECT * FROM employees WHERE salary BETWEEN 400000 AND 600000;
SELECT * FROM employees WHERE hire_date >= '2020-01-01';

-- LIKE前方一致（後方一致は効かない）
SELECT * FROM employees WHERE email LIKE 'employee1%';

-- IS NULL / IS NOT NULL
SELECT * FROM employees WHERE department IS NOT NULL;

-- IN句
SELECT * FROM employees WHERE department IN ('営業', '開発');

CREATE INDEX文によるインデックス作成

基本構文

インデックスを作成するCREATE INDEX文の基本構文は以下の通りです。

1

CREATE INDEX インデックス名 ON テーブル名 (カラム名);

インデックス名の命名規則

インデックス名には、一般的に以下の命名規則が使われます。

idx_テーブル名_カラム名

複合インデックスの場合は、カラム名をアンダースコアで繋げます。

idx_テーブル名_カラム1_カラム2

単一カラムインデックスの作成

特定の1つのカラムに対してインデックスを作成します。

1
2
3
4
5
6
7
8

-- 部署カラムにインデックスを作成
CREATE INDEX idx_employees_department ON employees (department);

-- 給与カラムにインデックスを作成
CREATE INDEX idx_employees_salary ON employees (salary);

-- 入社日カラムにインデックスを作成
CREATE INDEX idx_employees_hire_date ON employees (hire_date);

ユニークインデックスの作成

重複を許さないインデックスを作成できます。これはデータの一意性制約も同時に保証します。

1
2

-- emailカラムにユニークインデックスを作成
CREATE UNIQUE INDEX idx_employees_email_unique ON employees (email);

ユニークインデックスを作成すると、重複した値を挿入しようとするとエラーになります。

1
2
3
4

-- 重複挿入を試みる（エラーになる）
INSERT INTO employees (name, email, department, hire_date, salary)
VALUES ('テスト', 'employee1@example.com', '営業', '2024-01-01', 300000);
-- ERROR: duplicate key value violates unique constraint "idx_employees_email_unique"

インデックスの確認

テーブルに作成されているインデックスを確認する方法です。

1
2
3
4
5
6
7
8
9

-- psqlの\dコマンド
\d employees

-- システムカタログから確認
SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'employees';

実行結果の例：

         indexname          |                                    indexdef
----------------------------+--------------------------------------------------------------------------------
 employees_pkey             | CREATE UNIQUE INDEX employees_pkey ON public.employees USING btree (employee_id)
 idx_employees_email        | CREATE INDEX idx_employees_email ON public.employees USING btree (email)
 idx_employees_department   | CREATE INDEX idx_employees_department ON public.employees USING btree (department)
 idx_employees_salary       | CREATE INDEX idx_employees_salary ON public.employees USING btree (salary)

インデックスの削除

不要になったインデックスはDROP INDEX文で削除できます。

1
2
3
4
5

-- インデックスの削除
DROP INDEX idx_employees_salary;

-- 存在しない場合でもエラーにしない
DROP INDEX IF EXISTS idx_employees_salary;

複合インデックス（マルチカラムインデックス）

複合インデックスとは

複合インデックス（Composite Index、Multicolumn Index）は、複数のカラムを組み合わせて作成するインデックスです。

1
2

-- 部署と入社日の複合インデックスを作成
CREATE INDEX idx_employees_dept_hiredate ON employees (department, hire_date);

複合インデックスが有効なケース

複合インデックスは、複数カラムを使った検索条件が頻繁に使われる場合に効果的です。

1
2
3
4

-- 複合インデックスが効くクエリ
EXPLAIN ANALYZE
SELECT * FROM employees
WHERE department = '開発' AND hire_date >= '2023-01-01';

実行結果の例：

Index Scan using idx_employees_dept_hiredate on employees  (cost=0.42..156.78 rows=890 width=76) (actual time=0.034..0.567 loops=1)
  Index Cond: (((department)::text = '開発'::text) AND (hire_date >= '2023-01-01'::date))
Planning Time: 0.234 ms
Execution Time: 0.789 ms

カラム順序の重要性

複合インデックスでは、カラムの順序が非常に重要です。B-Treeインデックスは左端のカラム（先頭カラム）から順に使用されます。

1

CREATE INDEX idx_employees_dept_hiredate ON employees (department, hire_date);

このインデックスが効くパターンと効かないパターンを見てみましょう。

1
2
3
4
5
6
7

-- 先頭カラムのみの条件（インデックスが効く）
EXPLAIN ANALYZE
SELECT * FROM employees WHERE department = '開発';

-- 2番目のカラムのみの条件（インデックスが効きにくい）
EXPLAIN ANALYZE
SELECT * FROM employees WHERE hire_date >= '2023-01-01';

複合インデックス設計のベストプラクティス

複合インデックスを設計する際の指針を示します。

1. 選択性の高いカラムを先頭に: カーディナリティ（値の種類）が多いカラムを先頭に配置
2. 等価条件のカラムを先に: 範囲条件（>, <, BETWEEN）より等価条件（=）のカラムを先に
3. よく使う検索条件を分析: アプリケーションの実際のクエリパターンを把握

1
2
3
4
5
6

-- 良い例: 等価条件 → 範囲条件の順
CREATE INDEX idx_employees_dept_salary ON employees (department, salary);

-- クエリ例: 部署が「開発」で給与が50万以上
SELECT * FROM employees
WHERE department = '開発' AND salary >= 500000;

インデックスが効く条件・効かない条件

インデックスが効く条件

以下の条件ではインデックスが効果的に使用されます。

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

-- 1. 等価検索（=）
SELECT * FROM employees WHERE email = 'employee100@example.com';

-- 2. 範囲検索（>, <, >=, <=, BETWEEN）
SELECT * FROM employees WHERE salary BETWEEN 400000 AND 600000;

-- 3. 前方一致のLIKE検索
SELECT * FROM employees WHERE email LIKE 'employee1%';

-- 4. IS NULL / IS NOT NULL
SELECT * FROM employees WHERE is_active IS TRUE;

-- 5. IN句（値の数が少ない場合）
SELECT * FROM employees WHERE department IN ('営業', '開発');

-- 6. ORDER BY（インデックスカラムでのソート）
SELECT * FROM employees ORDER BY hire_date DESC LIMIT 10;

インデックスが効かない条件

以下の条件ではインデックスが使用されない、または効果が低下します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

-- 1. カラムに関数を適用
SELECT * FROM employees WHERE LOWER(email) = 'employee100@example.com';
-- 対策: 式インデックスを作成
-- CREATE INDEX idx_employees_email_lower ON employees (LOWER(email));

-- 2. 後方一致・中間一致のLIKE
SELECT * FROM employees WHERE email LIKE '%@example.com';
SELECT * FROM employees WHERE email LIKE '%employee%';

-- 3. 否定条件（<>, !=, NOT IN）
SELECT * FROM employees WHERE department <> '営業';

-- 4. OR条件（複合インデックスの場合）
SELECT * FROM employees WHERE department = '営業' OR salary > 500000;

-- 5. 暗黙の型変換
SELECT * FROM employees WHERE employee_id = '100';  -- 文字列で検索
-- employee_idはINTEGER型なので型変換が発生

-- 6. データの大部分を取得する場合
SELECT * FROM employees WHERE is_active = TRUE;  -- 90%がTRUEの場合、Seq Scanの方が効率的

関数インデックス（式インデックス）

カラムに関数を適用した検索が頻繁に行われる場合、式インデックスを作成できます。

1
2
3
4
5
6

-- 小文字変換した値でのインデックス
CREATE INDEX idx_employees_email_lower ON employees (LOWER(email));

-- このインデックスが使われる検索
EXPLAIN ANALYZE
SELECT * FROM employees WHERE LOWER(email) = 'employee100@example.com';

実行結果の例：

Index Scan using idx_employees_email_lower on employees  (cost=0.42..8.44 rows=1 width=76) (actual time=0.056..0.058 loops=1)
  Index Cond: (lower((email)::text) = 'employee100@example.com'::text)
Planning Time: 0.189 ms
Execution Time: 0.089 ms

部分インデックス

特定の条件を満たす行だけにインデックスを作成できます。これにより、インデックスのサイズを小さく保ちながら、必要な検索を高速化できます。

1
2
3
4
5
6
7
8
9

-- アクティブな従業員のみにインデックスを作成
CREATE INDEX idx_employees_active_salary
ON employees (salary)
WHERE is_active = TRUE;

-- このインデックスが使われる検索
EXPLAIN ANALYZE
SELECT * FROM employees
WHERE is_active = TRUE AND salary > 500000;

部分インデックスは以下のようなケースで特に有効です。

• 特定のステータス（有効/無効、公開/非公開など）のデータのみを頻繁に検索する場合
• 特定の期間のデータのみを頻繁に検索する場合

インデックスのトレードオフ

インデックスは検索を高速化しますが、デメリットもあります。適切なバランスを見極めることが重要です。

インデックスのメリットとデメリット

書き込み性能への影響

インデックスがあると、データの挿入・更新・削除時にインデックスも更新する必要があるため、書き込み性能が低下します。

 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

-- インデックスなしでの大量挿入テスト
DROP INDEX IF EXISTS idx_employees_email;
DROP INDEX IF EXISTS idx_employees_department;

\timing on

INSERT INTO employees (name, email, department, hire_date, salary)
SELECT
    '新規従業員' || i,
    'new' || i || '@example.com',
    '営業',
    CURRENT_DATE,
    400000
FROM generate_series(1, 10000) AS i;
-- Time: 約50ms

-- インデックスを再作成
CREATE INDEX idx_employees_email ON employees (email);
CREATE INDEX idx_employees_department ON employees (department);

-- インデックスありでの大量挿入テスト
INSERT INTO employees (name, email, department, hire_date, salary)
SELECT
    '追加従業員' || i,
    'add' || i || '@example.com',
    '開発',
    CURRENT_DATE,
    450000
FROM generate_series(1, 10000) AS i;
-- Time: 約120ms（インデックス更新のオーバーヘッド）

ストレージへの影響

インデックスはテーブルとは別に保存され、追加のディスク容量を消費します。

1
2
3
4
5
6
7
8

-- テーブルとインデックスのサイズを確認
SELECT
    relname AS name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS indexes_size
FROM pg_catalog.pg_statio_user_tables
WHERE relname = 'employees';

実行結果の例：

   name    | total_size | table_size | indexes_size
-----------+------------+------------+--------------
 employees | 22 MB      | 12 MB      | 10 MB

この例では、テーブル本体が12MBに対して、インデックスが10MBを占めています。インデックスを増やしすぎると、ストレージコストが大きくなります。

適切なインデックス設計の指針

以下の指針に従って、インデックスを適切に設計しましょう。

1. 検索頻度の高いカラムに作成: 頻繁にWHERE句で使われるカラム
2. カーディナリティを考慮: 値の種類が多いカラムほど効果的
3. 書き込み頻度とのバランス: 頻繁に更新されるテーブルはインデックスを最小限に
4. 実際のクエリを分析: EXPLAIN ANALYZEで効果を確認
5. 使われていないインデックスは削除: 定期的に見直し

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- インデックスの使用状況を確認
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS index_scans,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'employees'
ORDER BY idx_scan DESC;

idx_scanが0またはごく少数のインデックスは、使われていない可能性が高いため、削除を検討しましょう。

本番環境でのインデックス作成

CONCURRENTLY オプション

本番環境で稼働中のテーブルにインデックスを作成する場合、通常のCREATE INDEX文はテーブルへの書き込みをロックします。これを回避するため、CONCURRENTLYオプションを使用できます。

1
2

-- 書き込みをブロックせずにインデックスを作成
CREATE INDEX CONCURRENTLY idx_employees_salary ON employees (salary);

CONCURRENTLYオプションの特徴は以下の通りです。

INVALIDインデックスの対処

CREATE INDEX CONCURRENTLYが途中で失敗すると、INVALIDステータスのインデックスが残ることがあります。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- INVALIDインデックスの確認
SELECT
    indexrelid::regclass AS index_name,
    indisvalid
FROM pg_index
WHERE NOT indisvalid;

-- INVALIDインデックスの削除と再作成
DROP INDEX idx_employees_salary;
CREATE INDEX CONCURRENTLY idx_employees_salary ON employees (salary);

REINDEXによるインデックス再構築

インデックスは時間とともに断片化が進み、効率が低下することがあります。REINDEXで再構築できます。

1
2
3
4
5
6
7
8

-- 特定のインデックスを再構築
REINDEX INDEX idx_employees_email;

-- テーブルの全インデックスを再構築
REINDEX TABLE employees;

-- 本番環境向け（ロックを最小化）
REINDEX INDEX CONCURRENTLY idx_employees_email;

EXPLAIN ANALYZEによるインデックス効果の確認

EXPLAINの基本

EXPLAIN文は、クエリの実行計画を表示します。EXPLAIN ANALYZEは実際にクエリを実行して、実測値も表示します。

1
2

EXPLAIN ANALYZE
SELECT * FROM employees WHERE department = '開発';

実行計画の読み方

実行計画の主要な要素を解説します。

Index Scan using idx_employees_department on employees  (cost=0.42..789.12 rows=20000 width=76) (actual time=0.034..5.678 loops=1)
  Index Cond: ((department)::text = '開発'::text)
Planning Time: 0.234 ms
Execution Time: 8.901 ms

スキャン方法の種類

Index Only Scanの活用

必要なカラムがすべてインデックスに含まれている場合、テーブル本体へのアクセスを省略できます。

1
2
3
4
5
6
7

-- カバリングインデックスの作成
CREATE INDEX idx_employees_dept_salary_covering
ON employees (department) INCLUDE (salary);

-- Index Only Scanが使われる
EXPLAIN ANALYZE
SELECT department, salary FROM employees WHERE department = '開発';

実行結果の例：

Index Only Scan using idx_employees_dept_salary_covering on employees  (cost=0.42..523.12 rows=20000 width=18) (actual time=0.023..2.345 loops=1)
  Index Cond: (department = '開発'::text)
  Heap Fetches: 0
Planning Time: 0.156 ms
Execution Time: 3.456 ms

Heap Fetches: 0は、テーブル本体へのアクセスが0回だったことを示しています。

まとめ

本記事では、PostgreSQLのインデックスについて基礎から実践的な活用方法まで解説しました。

学んだ内容の振り返り

1. インデックスの基本概念: 検索を高速化するためのデータ構造
2. B-Treeの仕組み: 階層的な木構造で$O(\log n)$の計算量を実現
3. CREATE INDEX文: 単一カラム、複合インデックス、ユニークインデックスの作成
4. 複合インデックス: カラム順序の重要性と設計指針
5. インデックスが効く/効かない条件: 関数適用、後方一致、否定条件などの注意点
6. トレードオフ: 検索高速化 vs 書き込み性能・ストレージ
7. 本番環境での運用: CONCURRENTLYオプション、REINDEX

インデックス設計のチェックリスト

インデックスを設計する際は、以下のチェックリストを活用してください。

• WHERE句で頻繁に使用されるカラムか
• カーディナリティ（値の種類）は十分に高いか
• 書き込み頻度とのバランスは適切か
• 複合インデックスのカラム順序は適切か
• EXPLAIN ANALYZEで効果を確認したか
• 使われていないインデックスはないか

適切なインデックスを設計することで、アプリケーションの検索性能を大幅に向上させることができます。まずは実際のクエリをEXPLAIN ANALYZEで分析し、ボトルネックとなっている箇所を特定することから始めましょう。

参考リンク

• PostgreSQL公式ドキュメント - インデックス
• PostgreSQL公式ドキュメント - インデックスの種類
• PostgreSQL公式ドキュメント - 複合インデックス
• PostgreSQL公式ドキュメント - CREATE INDEX
• PostgreSQL公式ドキュメント - EXPLAINの使用