PostgreSQL インデックス設計とクエリチューニング実践ガイド
遅いクエリを特定し、適切なインデックスで高速化する手順を実務視点で解説。EXPLAIN の読み方、インデックス種別の使い分け、N+1 問題の対処まで網羅した実践ガイド。
PostgreSQL インデックス設計とクエリチューニング実践ガイド
受託開発や自社プロダクトで「本番環境でクエリが遅い」という問題は避けて通れません。データ量が増えるにつれ、開発時には気づかなかったボトルネックが顕在化します。
この記事では、PostgreSQL を使ったアプリケーション開発で実務上よく遭遇するクエリパフォーマンス問題を、インデックス設計とクエリチューニングの両面から解決する手順を解説します。
想定読者
- バックエンドエンジニア・フルスタックエンジニア
- SQL は書けるが、EXPLAIN の読み方が分からない
- 本番環境でクエリが遅くなり始めた
- インデックスを「なんとなく」張っている
1. パフォーマンス問題の特定フロー
1-1. スロークエリログの有効化
まずは「どのクエリが遅いのか」を可視化します。PostgreSQL のスロークエリログを有効にしましょう。
-- postgresql.conf または ALTER SYSTEM で設定
ALTER SYSTEM SET log_min_duration_statement = 1000; -- 1秒以上のクエリをログ出力
ALTER SYSTEM SET log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ';
SELECT pg_reload_conf(); -- 設定リロード
実務 Tips
- 開発環境では
100msに設定して早期発見 - 本番環境では
1000ms(1秒) から始め、徐々に閾値を下げる - ログローテーションを必ず設定（ディスク容量圧迫に注意）
1-2. pg_stat_statements による定量分析
pg_stat_statements 拡張を有効化すると、クエリごとの実行回数・平均実行時間・総実行時間を集計できます。
-- 拡張の有効化（スーパーユーザー権限が必要）
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 実行時間が長いクエリ TOP 10
SELECT
calls,
mean_exec_time::numeric(10,2) AS avg_ms,
total_exec_time::numeric(10,2) AS total_ms,
query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
チェックポイント
| 項目 | 確認内容 |
|------|----------|
| calls が多く mean_exec_time が高い | 頻繁に実行される遅いクエリ → 最優先で対処 |
| total_exec_time が突出 | 少数回だが極端に遅いクエリ → バッチ処理やレポート生成の可能性 |
| query にパラメータが含まれない | プリペアドステートメントが使われていない可能性 |
2. EXPLAIN (ANALYZE) の読み方
2-1. 基本的な実行計画の確認
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2026-01-01'
GROUP BY u.id, u.name;
出力例（簡略版）
HashAggregate (cost=1234.56..1345.67 rows=100 width=44) (actual time=12.345..12.567 rows=98 loops=1)
-> Hash Left Join (cost=56.78..1123.45 rows=5000 width=36) (actual time=1.234..10.567 rows=4850 loops=1)
Hash Cond: (u.id = o.user_id)
-> Seq Scan on users u (cost=0.00..890.12 rows=5000 width=32) (actual time=0.012..5.678 rows=4850 loops=1)
Filter: (created_at >= '2026-01-01'::date)
Rows Removed by Filter: 150
-> Hash (cost=45.67..45.67 rows=890 width=8) (actual time=1.123..1.123 rows=890 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 40kB
-> Seq Scan on orders o (cost=0.00..45.67 rows=890 width=8) (actual time=0.003..0.567 rows=890 loops=1)
Planning Time: 0.234 ms
Execution Time: 12.890 ms
2-2. 読み取るべき重要指標
| 項目 | 意味 | 対処の目安 |
|------|------|------------|
| Seq Scan | テーブル全行スキャン | rows が大きい場合はインデックス検討 |
| actual time | 実測値（ms） | cost より大幅に大きい場合は統計情報が古い |
| rows=X (actual rows=Y) | 見積もり vs 実測 | 乖離が大きい場合は ANALYZE 実行 |
| Buffers: shared hit=X read=Y | キャッシュヒット率 | read が多い場合はメモリ不足やインデックス不足 |
| loops=N | ループ回数 | N > 1 の場合、Nested Loop の内側で何度も実行されている |
実務でよくある問題パターン
- Seq Scan on large_table → WHERE 句の列にインデックスがない
- Nested Loop → Seq Scan → 結合キーにインデックスがない（N+1 に近い状態）
- actual rows が見積もりの 10 倍以上 → 統計情報が古い、または複合条件の相関が考慮されていない
3. インデックスの種類と使い分け
3-1. B-tree インデックス（デフォルト）
用途: 等価検索・範囲検索・ソート
-- 基本的な単一列インデックス
CREATE INDEX idx_users_email ON users(email);
-- 複合インデックス（順序が重要）
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);
複合インデックスのルール
| 原則 | 説明 | 例 |
|------|------|----|
| 等価条件を前に | WHERE a = X AND b > Y の場合、(a, b) の順 | (status, created_at) |
| カーディナリティが高い列を前に | 絞り込み効果が高い列を先頭に | (email, name) より (email) 単独が有効な場合も |
| 範囲検索は最後 | WHERE a = X AND b > Y AND c = Z の場合、(a, c, b) とする | (user_id, status, created_at) |
実務 Tips
(user_id, created_at)と(created_at, user_id)は別物WHERE user_id = 123のクエリには(user_id, created_at)が使えるが、(created_at, user_id)は使えない- PostgreSQL 13 以降は B-tree インデックスの重複削除機能が強化され、サイズが削減される
3-2. 部分インデックス（Partial Index）
用途: 特定条件のデータのみをインデックス化してサイズ削減
-- アクティブユーザーのみをインデックス化
CREATE INDEX idx_users_active_email ON users(email)
WHERE status = 'active';
-- 未完了の注文のみ
CREATE INDEX idx_orders_pending ON orders(user_id, created_at)
WHERE status IN ('pending', 'processing');
メリット
- インデックスサイズが小さくなり、更新コストも下がる
- クエリに
WHERE status = 'active'が含まれる場合のみ使用される
3-3. 式インデックス（Expression Index）
用途: 計算結果や関数適用後の値で検索
-- メールアドレスの小文字検索
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- JSON フィールド内の値
CREATE INDEX idx_users_metadata_plan ON users((metadata->>'plan'));
-- 日付の年月だけ
CREATE INDEX idx_orders_year_month ON orders(DATE_TRUNC('month', created_at));
注意点
- クエリ内でも全く同じ式を使わないとインデックスが使われない
WHERE LOWER(email) = 'test@example.com'→ OKWHERE email ILIKE 'test@example.com'→ NG（別の関数）
3-4. GIN インデックス（汎用転置インデックス）
用途: 配列・全文検索・JSON
-- 配列の要素検索
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);
-- SELECT * FROM posts WHERE tags @> ARRAY['postgresql', 'performance'];
-- JSON フィールドの全文検索
CREATE INDEX idx_products_attributes ON products USING GIN(attributes jsonb_path_ops);
-- SELECT * FROM products WHERE attributes @> '{"color": "red"}';
-- 全文検索
CREATE INDEX idx_articles_search ON articles USING GIN(to_tsvector('english', body));
3-5. インデックス種別の選択フローチャート
検索対象が配列・JSON・全文検索？
YES → GIN インデックス
NO ↓
特定条件のデータだけ頻繁に検索？
YES → 部分インデックス (Partial Index)
NO ↓
関数や計算結果で検索？
YES → 式インデックス (Expression Index)
NO ↓
複数列の組み合わせで検索？
YES → 複合 B-tree インデックス（順序に注意）
NO ↓
単一列の等価・範囲検索
→ 単一列 B-tree インデックス
4. よくあるクエリパターンとチューニング例
4-1. N+1 問題の検出と対処
問題のあるコード例（ORM 使用）
// ❌ N+1 が発生するパターン
const users = await db.user.findMany();
for (const user of users) {
const orderCount = await db.order.count({
where: { userId: user.id }
});
console.log(`${user.name}: ${orderCount} orders`);
}
対処法 1: JOIN で一度に取得
// ✅ 1 回のクエリで取得
const usersWithOrderCount = await db.user.findMany({
include: {
_count: {
select: { orders: true }
}
}
});
対処法 2: WHERE IN による一括取得
-- ユーザー一覧取得（1 回目）
SELECT id, name FROM users WHERE status = 'active';
-- 注文件数を一括集計（2 回目）
SELECT user_id, COUNT(*) AS order_count
FROM orders
WHERE user_id IN (1, 2, 3, ..., 100) -- 1 回目の結果
GROUP BY user_id;
必要なインデックス
CREATE INDEX idx_orders_user_id ON orders(user_id);
4-2. ページネーション（OFFSET vs Cursor-based）
OFFSET 方式の問題点
-- ❌ ページ数が大きくなるほど遅くなる
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000; -- 10000 行スキップするコストが高い
Cursor-based ページネーション（推奨）
-- ✅ 前回の最後の created_at を基準にする
SELECT * FROM posts
WHERE created_at < '2026-05-20 12:34:56' -- 前回の最後の値
ORDER BY created_at DESC
LIMIT 20;
-- 必要なインデックス
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);
実務での実装例（TypeScript）
type PaginationCursor = {
createdAt: string;
id: number; // 同一時刻が複数ある場合の tie-breaker
};
async function getPostsAfterCursor(cursor?: PaginationCursor, limit = 20) {
const whereClause = cursor
? {
OR: [
{ createdAt: { lt: new Date(cursor.createdAt) } },
{
createdAt: new Date(cursor.createdAt),
id: { lt: cursor.id }
}
]
}
: {};
const posts = await db.post.findMany({
where: whereClause,
orderBy: [{ createdAt: 'desc' }, { id: 'desc' }],
take: limit + 1 // 次ページの有無を判定
});
const hasNext = posts.length > limit;
const items = hasNext ? posts.slice(0, -1) : posts;
const nextCursor = hasNext
? { createdAt: items[items.length - 1].createdAt.toISOString(), id: items[items.length - 1].id }
: null;
return { items, nextCursor, hasNext };
}
4-3. EXISTS vs COUNT の使い分け
パフォーマンス比較
-- ❌ 遅い：全件カウント
SELECT u.name
FROM users u
WHERE (
SELECT COUNT(*)
FROM orders o
WHERE o.user_id = u.id
) > 0;
-- ✅ 速い：存在チェック（1 件見つかった時点で終了）
SELECT u.name
FROM users u
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
);
原則
- 「1 件以上あるか」だけ知りたい →
EXISTS - 「何件あるか」の数値が必要 →
COUNT
5. インデックスのメンテナンスと運用
5-1. 不要なインデックスの検出
-- 使われていないインデックスを検出
SELECT
schemaname,
tablename,
indexname,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE '%_pkey' -- 主キーは除外
ORDER BY pg_relation_size(indexrelid) DESC;
削除の判断基準
| 条件 | アクション |
|------|------------|
| idx_scan = 0 かつサイズが大きい | 削除を検討 |
| バッチ処理でのみ使用 | pg_stat_reset() 後に再確認 |
| 複合インデックスの prefix として使える | 単一列インデックスを削除 |
5-2. 統計情報の更新
-- テーブル全体の統計情報を更新
ANALYZE users;
-- データベース全体
ANALYZE;
-- 自動バキュームの設定確認
SHOW autovacuum;
SELECT schemaname, tablename, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_analyze NULLS FIRST;
実務 Tips
- 大量データ投入後は必ず
ANALYZEを実行 autovacuumが無効になっていないか定期確認- パーティショニングテーブルは親テーブルも
ANALYZEが必要
5-3. インデックスの再構築（REINDEX）
肥大化したインデックスの再構築
-- オンライン再構築（PostgreSQL 12 以降推奨）
REINDEX INDEX CONCURRENTLY idx_users_email;
-- テーブル全体のインデックス再構築
REINDEX TABLE CONCURRENTLY users;
注意点
CONCURRENTLYなしのREINDEXはテーブルをロックするため、本番環境では使用禁止- ディスク容量がインデックスサイズの 2 倍以上必要
6. チェックリスト：インデックス設計の実務観点
6-1. 新規テーブル作成時
- [ ] 主キーは
BIGSERIALまたはUUIDで定義 - [ ] 外部キー制約には自動的にインデックスが張られない → 手動作成
- [ ]
WHERE句で頻繁に使う列にインデックス作成 - [ ]
JOINのキーになる列にインデックス作成 - [ ] 複合インデックスの列順序は「等価条件 → 範囲条件」の順
- [ ] 部分インデックスで容量削減できないか検討
6-2. 本番リリース前
- [ ]
EXPLAIN ANALYZEで主要クエリの実行計画を確認 - [ ] スロークエリログの閾値を設定済み
- [ ]
pg_stat_statements拡張を有効化済み - [ ] インデックスサイズがテーブルサイズの 50% を超えていないか確認
- [ ]
autovacuumの設定が適切か確認
6-3. 運用フェーズ
- [ ] 月次で
pg_stat_user_indexesから未使用インデックスを確認 - [ ] クエリ実行時間の P95/P99 をモニタリング
- [ ] スロークエリログを定期的にレビュー
- [ ] データ量増加に伴う実行計画の変化を監視
- [ ] パーティショニングの検討（1000万行超のテーブル）
7. まとめ
PostgreSQL のクエリパフォーマンス改善は、以下の 3 ステップで進めます。
- 可視化: スロークエリログと
pg_stat_statementsで遅いクエリを特定 - 分析:
EXPLAIN ANALYZEで実行計画を読み、ボトルネックを把握 - 最適化: 適切なインデックスを追加し、クエリを書き換え
実務で押さえるべきポイント
- 複合インデックスの列順序は「絞り込み効果が高い列 → 範囲検索列」
EXISTSを活用して不要なCOUNTを避ける- Cursor-based ページネーションで大規模データに対応
- 定期的に未使用インデックスを削除し、メンテナンスコストを削減
受託開発では、クライアントのデータ量増加を見越した設計が重要です。MVP フェーズでは気づかなかった問題が、数万〜数十万レコードで顕在化します。この記事で紹介したチェックリストを活用し、早い段階からパフォーマンスを意識した開発を進めてください。
Yureate へのお問い合わせ
Yureate では、PostgreSQL を含むバックエンド設計・パフォーマンスチューニングの技術支援を行っています。「既存システムのボトルネック調査」「新規プロダクトの DB 設計レビュー」など、お気軽にご相談ください。