⚡

PostgreSQLのパフォーマンスチューニング入門: インデックスの威力を体感せよ

に公開
Docker
PostgreSQL
Database
performance
zennfes2025free
tech

はじめに

データベースのパフォーマンスチューニングって難しそう…と思っていませんか？
今回は、シーケンシャルスキャン（Seq Scan）になっているクエリをインデックススキャン（Index Scan）に変えることで、クエリを劇的に高速化する手順を実測値つきでまとめます。
途中で実行計画の読み方やParallel Seq Scan / Gatherのポイントも押さえます。

EXPLAIN ANALYZE は計測オーバーヘッドが乗るので、素の実行より遅くなることがあります。数値は目安として見てください。(PostgreSQL)

環境構築（Docker）

docker-compose.yaml

docker-compose.yaml
version: '3.8'

services:
  postgres:
    image: postgres:16-alpine
    container_name: postgres-tuning
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: tuning_db
    ports:
      - "5432:5432"
    volumes:
      - ./init:/docker-entrypoint-initdb.d
      - postgres_data:/var/lib/postgresql/data
    command: >
      postgres
      -c shared_buffers=256MB
      -c work_mem=4MB
      -c maintenance_work_mem=64MB
      -c effective_cache_size=1GB
      -c random_page_cost=1.1
      -c log_statement=all
      -c log_duration=on

volumes:
  postgres_data:

サンプルデータ投入

100万件のダミーデータを流し込みます。

init/01_create_tables.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    username VARCHAR(100) NOT NULL,
    age INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP,
    is_active BOOLEAN DEFAULT true,
    score INTEGER DEFAULT 0
);

INSERT INTO users (email, username, age, created_at, last_login, is_active, score)
SELECT 
    'user' || i || '@example.com',
    'user_' || i,
    (random() * 60 + 18)::INTEGER,
    CURRENT_TIMESTAMP - (random() * INTERVAL '365 days'),
    CURRENT_TIMESTAMP - (random() * INTERVAL '30 days'),
    random() > 0.2,
    (random() * 1000)::INTEGER
FROM generate_series(1, 1000000) AS i;

ANALYZE users;

起動して投入が終わるまで待機：

docker-compose up -d

docker exec -it postgres-tuning psql -U postgres -d tuning_db

チューニング前：Seq Scan（実測）

まずはメールアドレスで1件引くクエリ。psql を開いて \timing on を有効化してから実行計画を取ります。

DISCARD ALL;

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users
WHERE email = 'user500000@example.com';

実行計画（チューニング前）

Gather  (cost=1000.00..18544.43 rows=1 width=62) (actual time=56.846..58.366 rows=1 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=32 read=12304
  ->  Parallel Seq Scan on users  (cost=0.00..17544.33 rows=1 width=62) (actual time=41.229..49.634 rows=0 loops=3)
        Filter: ((email)::text = 'user500000@example.com'::text)
        Rows Removed by Filter: 333333
        Buffers: shared hit=32 read=12304
Planning:
  Buffers: shared hit=45 read=3 dirtied=2
Planning Time: 1.279 ms
Execution Time: 58.499 ms

ここで押さえるポイント

  • Parallel Seq Scan
    100万行を3プロセス（リーダ + ワーカー2）で分割して全表走査しています。
    各ワーカーが約33万行ずつ「条件に合うか」をチェックし、最終的に Gather ノードが結果を集約します。(PostgreSQL, PostgreSQL日本語ドキュメント)
  • Gather
    並列部分の親ノード。ワーカーから届いた行を集めて上位ノードへ渡します。
  • Rows Removed by Filter: 333333（×3ワーカー）
    「99.999% が無駄読み」＝典型的に遅い。
  • Buffers: shared hit=32 read=12304
    ディスク読み（read）が多め → I/O も効いて遅くなりやすい。
  • Execution Time: 58.499 ms
    私の環境ではこのくらい。並列化しても全表走査は全表走査です。

インデックス作成 → Index Scan へ

検索条件そのものの email にB-treeインデックスを作成します。

CREATE INDEX idx_users_email ON users(email);
ANALYZE users;

なぜ email に B-tree？

  • 条件が等価比較：email = '...' は等価条件。text 型の等価・順序は B-tree が最速です。
  • ヒットが1行だけ（高選択度）：全体のごく一部だけ欲しいので、インデックスで一直線に到達できるほうが有利（Index Cond が付く）。
  • I/Oが激減：全表走査は大量読み取り、Index Scan は必要ページだけ読む → 実時間が大幅短縮。
  • BitmapよりIndex Scan：件数が極小なら、まとめ読みの利点より直接拾うほうが速い。

インデックスは性能を上げる主要手段ですが、不適切に作ると逆効果になる場合があります（書き込みコスト増、プランナーの探索空間拡大など）。(PostgreSQL)

再計測（チューニング後）

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users
WHERE email = 'user500000@example.com';

実行計画（チューニング後）

Index Scan using idx_users_email on users  (cost=0.42..2.64 rows=1 width=62) (actual time=0.241..0.246 rows=1 loops=1)
  Index Cond: ((email)::text = 'user500000@example.com'::text)
  Buffers: shared read=4
Planning:
  Buffers: shared hit=38 read=1
Planning Time: 0.896 ms
Execution Time: 0.293 ms

ここで押さえるポイント

  • Index Scan
    索引（B-tree）から該当行へ一直線。全表をなめないため、極端に速い。
    インデックススキャンの内部的な考え方は公式の「Index Scanning」が簡潔です。(PostgreSQL, PostgreSQL日本語ドキュメント)
  • Buffers: read=4
    必要最小限のブロックだけを読めば良い。
  • Execution Time: 0.293 ms
    Before: 58.499 ms → After: 0.293 ms。約200倍（199.7x）高速化。

Before/After 比較（実測ベース）

観点 チューニング前 チューニング後 メモ
スキャン種別 Parallel Seq Scan（Gather あり） Index Scan 並列でも全表走査は本質的に重い
実行時間（Execution Time） 58.499 ms 0.293 ms 約 200x 高速化
Rows Removed by Filter ≈ 333,333 × 3 workers 0 無駄読みが消える
Buffers（read） 12,304 4 I/O 負荷が激減
コスト（cost 2nd 値） 18,544.43 2.64 目安（相対値）

実行計画の読み方（超要点）

  • ノード名：Seq Scan / Index Scan / Bitmap Heap Scan / Nested Loop など
  • cost=Start..Total：プランナーの推定コスト（相対値）
  • actual time=Start..End：EXPLAIN ANALYZE の実測時間（オーバーヘッドあり）(PostgreSQL)
  • Rows Removed by Filter：無駄読みの目安
  • Buffers：hit（メモリ）/ read（ディスク）でI/Oの傾向が見える
  • Parallel系（Gather / Gather Merge / Parallel Seq Scan）：並列に読んで最後に集約する。ヒットが極少なら並列でも結局「全表を複数人で探す」構図になりやすい。(PostgreSQL, PostgreSQL日本語ドキュメント)

片付け

docker-compose down -v

まとめ

  • Before：Parallel Seq Scan（Gather）で全表走査 → 58.499 ms
  • After：email に B-tree インデックス → Index Scan → 0.293 ms
  • 効果：実測で約200倍の高速化
  • 見るべきポイントは「スキャン種別」「Rows Removed by Filter」「Buffers」「Execution Time」
  • 「まず測る → 計画を読む → 最小の変更（インデックス）」が鉄板

Discussion