「遅いクエリを見つけたら、まず EXPLAIN ANALYZE を実行しろ」佐藤CTOは言った。「実行計画を読めないエンジニアがインデックスを追加しても、ほとんどの場合うまくいかない。計画を読み、ボトルネックを理解してから対策を打つ。」

1. EXPLAIN ANALYZE の基本

PostgreSQL の実行計画の読み方

-- 基本的な EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'completed'
  AND o.created_at >= '2025-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

実行計画の出力例

Limit  (cost=1234.56..1234.78 rows=20 width=64) (actual time=45.123..45.234 rows=20 loops=1)
  ->  Sort  (cost=1234.56..1245.67 rows=4500 width=64) (actual time=45.120..45.130 rows=20 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: top-N heapsort  Memory: 28kB
        ->  Hash Join  (cost=100.00..1100.00 rows=4500 width=64) (actual time=5.123..40.456 rows=4500 loops=1)
              Hash Cond: (o.user_id = u.id)
              ->  Seq Scan on orders o  (cost=0.00..900.00 rows=4500 width=48) (actual time=0.015..30.234 rows=4500 loops=1)
                    Filter: ((status = 'completed') AND (created_at >= '2025-01-01'))
                    Rows Removed by Filter: 45500
                    Buffers: shared hit=500 read=200
              ->  Hash  (cost=50.00..50.00 rows=5000 width=20) (actual time=5.000..5.000 rows=5000 loops=1)
                    Buckets: 8192  Batches: 1  Memory Usage: 250kB
                    ->  Seq Scan on users u  (cost=0.00..50.00 rows=5000 width=20) (actual time=0.010..2.500 rows=5000 loops=1)
                          Buffers: shared hit=30
Planning Time: 0.250 ms
Execution Time: 45.500 ms

実行計画の主要ノード

2. 実行計画の分析手法

// クエリ実行計画の自動分析
interface PlanNode {
  nodeType: string;
  actualTime: number;
  actualRows: number;
  estimatedRows: number;
  buffers?: {
    sharedHit: number;
    sharedRead: number;
  };
  children?: PlanNode[];
}

class QueryPlanAnalyzer {
  analyze(plan: PlanNode): QueryPlanInsight[] {
    const insights: QueryPlanInsight[] = [];

    this.traverseNode(plan, insights);
    return insights;
  }

  private traverseNode(node: PlanNode, insights: QueryPlanInsight[]): void {
    // 1. Seq Scan の検出
    if (node.nodeType === 'Seq Scan' && node.actualRows > 10000) {
      insights.push({
        severity: 'warning',
        message: `Large sequential scan: ${node.actualRows} rows`,
        suggestion: 'Consider adding an index on the filtered columns',
      });
    }

    // 2. 推定行数と実行行数の乖離（カーディナリティ推定エラー）
    if (node.estimatedRows > 0) {
      const ratio = node.actualRows / node.estimatedRows;
      if (ratio > 10 || ratio < 0.1) {
        insights.push({
          severity: 'error',
          message: `Cardinality estimation error: estimated=${node.estimatedRows}, actual=${node.actualRows} (ratio: ${ratio.toFixed(1)})`,
          suggestion: 'Run ANALYZE on the table or check for correlated columns',
        });
      }
    }

    // 3. バッファ読み取りが多い
    if (node.buffers?.sharedRead && node.buffers.sharedRead > 1000) {
      insights.push({
        severity: 'info',
        message: `High disk reads: ${node.buffers.sharedRead} pages (${(node.buffers.sharedRead * 8 / 1024).toFixed(1)} MB)`,
        suggestion: 'Consider increasing shared_buffers or work_mem',
      });
    }

    // 子ノードの分析
    for (const child of node.children ?? []) {
      this.traverseNode(child, insights);
    }
  }
}

interface QueryPlanInsight {
  severity: 'error' | 'warning' | 'info';
  message: string;
  suggestion: string;
}

3. よくあるクエリ最適化パターン

パターン1: N+1 問題の解消

-- Bad: N+1 クエリ
-- 1回: SELECT * FROM orders WHERE user_id = 123;
-- N回: SELECT * FROM order_items WHERE order_id = ?; (注文ごとに)

-- Good: JOIN で一度に取得
SELECT o.*, oi.product_id, oi.quantity, oi.price
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 123;

-- Better: ORM のeager loading
-- Prisma: include: { orderItems: true }

パターン2: OFFSET の回避

-- Bad: 大きな OFFSET はパフォーマンスが劣化
SELECT * FROM products
ORDER BY created_at DESC
OFFSET 100000 LIMIT 20;
-- → 100,020行をスキャンして最後の20行を返す

-- Good: カーソルベースのページネーション
SELECT * FROM products
WHERE created_at < '2025-06-15T10:30:00Z'
ORDER BY created_at DESC
LIMIT 20;
-- → インデックスで直接該当位置にジャンプ

-- カーソルベースの実装（seek method / keyset pagination）
SELECT * FROM products
WHERE (created_at, id) < ('2025-06-15T10:30:00Z', 'last-seen-id')
ORDER BY created_at DESC, id DESC
LIMIT 20;

パターン3: EXISTS vs IN の使い分け

-- 外側テーブルが小さい場合: EXISTS が有利
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
  AND o.status = 'completed'
);

-- サブクエリ結果が小さい場合: IN が有利
SELECT * FROM orders
WHERE user_id IN (
  SELECT id FROM users WHERE region = 'tokyo'
);

パターン4: 集約クエリの最適化

-- Bad: 全レコードを走査して集約
SELECT COUNT(*), AVG(total_amount)
FROM orders
WHERE created_at >= '2025-01-01';

-- Good: マテリアライズドビューで事前集計
CREATE MATERIALIZED VIEW daily_order_stats AS
SELECT
  date_trunc('day', created_at) AS day,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_amount,
  AVG(total_amount) AS avg_amount
FROM orders
GROUP BY date_trunc('day', created_at);

-- 定期的にリフレッシュ
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_order_stats;

4. PostgreSQL のチューニングパラメータ

-- パフォーマンスに大きく影響するパラメータ
-- shared_buffers: メモリの25%程度
ALTER SYSTEM SET shared_buffers = '4GB';

-- work_mem: ソートやハッシュに使うメモリ（接続ごと）
ALTER SYSTEM SET work_mem = '256MB';

-- effective_cache_size: OS キャッシュを含む利用可能メモリ
ALTER SYSTEM SET effective_cache_size = '12GB';

-- random_page_cost: SSD の場合は低く設定
ALTER SYSTEM SET random_page_cost = 1.1;

-- enable_seqscan: テスト用に Seq Scan を無効化
SET enable_seqscan = off;  -- 本番では使わないこと

// スロークエリの自動検出と通知
class SlowQueryMonitor {
  constructor(
    private thresholdMs: number = 100,
    private notifier: AlertNotifier
  ) {}

  async monitorSlowQueries(): Promise<void> {
    // pg_stat_statements から遅いクエリを取得
    const slowQueries = await this.db.query(`
      SELECT
        query,
        calls,
        mean_exec_time,
        max_exec_time,
        stddev_exec_time,
        rows
      FROM pg_stat_statements
      WHERE mean_exec_time > $1
      ORDER BY mean_exec_time DESC
      LIMIT 20
    `, [this.thresholdMs]);

    for (const q of slowQueries.rows) {
      if (q.mean_exec_time > this.thresholdMs * 10) {
        await this.notifier.alert(
          `Critical slow query: ${q.query.substring(0, 100)}... ` +
          `avg=${q.mean_exec_time.toFixed(1)}ms, calls=${q.calls}`
        );
      }
    }
  }

  private db: any;
}

interface AlertNotifier {
  alert(message: string): Promise<void>;
}

-- 有効化
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 最も時間がかかっているクエリ（total_exec_time順）
SELECT
  query,
  calls,
  total_exec_time / 1000 AS total_time_sec,
  mean_exec_time AS avg_ms,
  rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 最もI/Oが多いクエリ
SELECT
  query,
  shared_blks_read + shared_blks_written AS total_io_blocks,
  calls
FROM pg_stat_statements
ORDER BY (shared_blks_read + shared_blks_written) DESC
LIMIT 10;

まとめ

チェックリスト

• EXPLAIN ANALYZE の出力を読み解ける
• カーディナリティ推定エラーの意味を理解した
• N+1 問題の検出と解消ができる
• カーソルベースのページネーションを実装できる
• pg_stat_statements でスロークエリを分析できる

次のステップへ

クエリ最適化の基本を学んだ。次は インデックス戦略 を学び、最適なインデックス設計ができるようになろう。

推定読了時間: 40分