PostgreSQL のスロークエリを特定して実行速度を3倍にする方法

本記事では、PostgreSQL のパフォーマンスチューニングにおいて、実務で即座に効果が出やすい手法を紹介します。スロークエリログの有効化、インデックス設計、実行計画の読み方を習得することで、クエリ実行時間を大幅に短縮できます。

PostgreSQL パフォーマンスチューニングが必要な理由

システム構築初期はクエリの速度問題が目立たなくても、データ量が増加する段階で顕在化します。本番環境で突然レスポンス時間が 10 秒を超えるようなケースは珍しくありません。予防的なチューニングと、問題発生時の迅速な対応が重要です。

ステップ 1: スロークエリログを有効化する

最初の診断ステップは「遅いクエリが何か」を特定することです。推測ではなく、データに基づいて問題を可視化します。

postgresql.conf でスロークエリログを設定

以下の設定を postgresql.conf に追加してください。

# ログが保存されるディレクトリを指定
log_directory = 'pg_log'

# スロークエリの定義: 1000ms 以上を記録
log_min_duration_statement = 1000

# クエリと実行計画の両方をログに出力
log_statement = 'all'
log_duration = on

# メモリ使用量なども記録
log_checkpoints = on
log_connections = on
log_disconnections = on

設定を反映させるには PostgreSQL の再起動が必要です。開発環境では reload で対応できる場合もありますが、本番環境での変更は十分なテストを経た上で実施してください。

設定反映とログの確認

# PostgreSQL を再起動
sudo systemctl restart postgresql

# ログファイルをリアルタイム監視
tail -f /var/log/postgresql/postgresql.log

# または、ログディレクトリの確認
psql -U postgres -c "SHOW log_directory;"

ステップ 2: EXPLAIN で実行計画を読む

クエリが遅い原因の大半は「フルテーブルスキャン」にあります。EXPLAIN と EXPLAIN ANALYZE コマンドで実行計画を確認し、改善点を特定します。

EXPLAIN ANALYZE の基本的な使い方

-- 実行計画を表示（実際には実行しない）
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 実際に実行して、予測値と実績値を比較
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

出力例を見てみましょう。以下は典型的な「遅いクエリ」の実行計画です。

Seq Scan on users  (cost=0.00..35.50 rows=1 width=200)
  Filter: (email = 'test@example.com')
  Planning Time: 0.105 ms
  Execution Time: 12.345 ms

Seq Scan（シーケンシャルスキャン）は全行をスキャンしていることを意味し、これが遅さの原因です。

インデックス追加後の改善を確認

-- email 列にインデックスを作成
CREATE INDEX idx_users_email ON users(email);

-- 再度実行計画を確認
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

インデックス追加後の出力は以下のように変わります。

Index Scan using idx_users_email on users  (cost=0.29..8.30 rows=1 width=200)
  Index Cond: (email = 'test@example.com')
  Planning Time: 0.234 ms
  Execution Time: 0.456 ms

実行時間が 12.345 ms から 0.456 ms へと大幅に短縮されました。

ステップ 3: 効果的なインデックス戦略

単一列インデックスの設計

WHERE 句でよく使用される列に優先的にインデックスを作成します。

-- よくある検索条件に対応
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);
CREATE INDEX idx_products_category_id ON products(category_id);

複合インデックスの活用

複数の列で同時に検索する場合は、複合インデックスが効果的です。列の順序が重要です。

-- WHERE status = 'active' AND created_at > '2025-01-01'
-- のようなクエリに対応
CREATE INDEX idx_users_status_created ON users(status, created_at);

-- 良い順序: カーディナリティの高い順（重複が少ない順）
-- 悪い例: CREATE INDEX idx_users_created_status ON users(created_at, status);

部分インデックスで容量を削減

削除フラグ付きテーブルでは、有効なレコードだけにインデックスを作成すると容量が削減できます。

-- deleted_at が NULL（有効）なレコードのみインデックス
CREATE INDEX idx_users_active_email ON users(email) 
  WHERE deleted_at IS NULL;

よくあるハマりポイント: インデックスが使われない場合

-- ❌ これはインデックスが使われない
EXPLAIN SELECT * FROM users WHERE UPPER(email) = 'TEST@EXAMPLE.COM';

-- ✅ 関数を避けて直接比較
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- ❌ OR 条件で片方にインデックスがない場合も使われない可能性
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com' OR phone = '09012345678';

-- ✅ 両方にインデックスを作成
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_phone ON users(phone);

ステップ 4: クエリ最適化の実践テクニック

JOIN の最適化

複数テーブルを結合する際は、結合順序と条件が重要です。

-- 非効率な例
SELECT u.id, u.name, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.order_date > '2025-01-01'
AND u.status = 'active';

-- 改善版: フィルター条件を JOIN 前に適用
SELECT u.id, u.name, o.order_date
FROM users u
WHERE u.status = 'active'
JOIN orders o ON u.id = o.user_id
WHERE o.order_date > '2025-01-01';

N+1 クエリ問題の回避

アプリケーション側で複数のクエリを発行するパターンを避けます。

-- ❌ 非効率（ユーザー数 × 2 回のクエリ）
SELECT * FROM users;  -- 100 行
-- その後、各ユーザーについて
SELECT * FROM orders WHERE user_id = ?;

-- ✅ 1 回のクエリで済ませる
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

LIMIT の活用

不要なデータ取得を避けることで、メモリ使用量を削減できます。

-- ページング処理
SELECT * FROM products 
WHERE category_id = 5
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

ステップ 5: PostgreSQL サーバー設定の調整

メモリ設定の最適化

# postgresql.conf

# サーバーの全メモリの 25% が目安（専用サーバーなら 50% も可）
shared_buffers = 4GB

# ソート・ハッシュテーブル用
work_mem = 256MB

# 全クエリの総メモリ上限
maintenance_work_mem = 1GB

接続数の制限

# 接続オーバーヘッドを削減
max_connections = 200

# コネクションプーリングツール（PgBouncer）の利用
# 参考: https://www.pgbouncer.org/

実装環境と動作確認

テスト環境: Ubuntu 22.04 LTS / PostgreSQL 15.2 / psql

本記事のすべてのコマンドと SQL は上記環境で動作確認済みです。異なるバージョンでは構文が若干異なる可能性があります。

この手法を使うべき場面と避けるべき場面

使うべき場面

• データ量が数百万行を超えるテーブルでクエリが遅い
• ユーザーからレスポンス遅延の報告がある
• 新機能追加時に事前にパフォーマンスを検証したい
• 定期的なバッチ処理が時間超過している

避けるべき場面・注意点

• 小規模テーブル（数千行以下）では劇的な効果は期待できない
• インデックス作成時に排他ロックが発生し、本番環境では CONCURRENT オプションを必須にする: CREATE INDEX CONCURRENTLY
• 過剰なインデックスは INSERT/UPDATE のパフォーマンスを低下させる

関連リソース

公式ドキュメント: EXPLAIN — PostgreSQL Documentation