PostgreSQLのクエリ最適化——EXPLAIN ANALYZEでボトルネックを見つける

なぜクエリ最適化が必要なのか

PostgreSQLはデータ量が増えるにつれてクエリのパフォーマンスが低下しやすくなります。しかし多くの場合、適切なインデックスの追加やクエリの書き直しで劇的に改善できます。EXPLAIN ANALYZEはその第一歩であり、クエリがどのように実行されているかを可視化するツールです。

EXPLAIN ANALYZEの基本

-- クエリの実行計画と実際の実行時間を表示
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= '2024-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 20;

出力例：

Sort  (cost=1234.56..1234.60 rows=20 width=45) (actual time=234.123..234.156 rows=20 loops=1)
  Sort Key: (count(o.id)) DESC
  Sort Method: top-N heapsort  Memory: 26kB
  ->  HashAggregate  (cost=1220.00..1230.00 rows=1000 width=45) (actual time=230.456..233.789 rows=850 loops=1)
        Group Key: u.id
        ->  Hash Left Join  (cost=450.00..1100.00 rows=25000 width=12) (actual time=15.234..180.456 rows=25000 loops=1)
              Hash Cond: (o.user_id = u.id)
              ->  Seq Scan on orders o  (cost=0.00..500.00 rows=25000 width=8) (actual time=0.012..80.234 rows=25000 loops=1)
              ->  Hash  (cost=400.00..400.00 rows=5000 width=36) (actual time=14.567..14.567 rows=5000 loops=1)
                    ->  Seq Scan on users u  (cost=0.00..400.00 rows=5000 width=36) (actual time=0.008..10.234 rows=5000 loops=1)
                          Filter: (created_at >= '2024-01-01')
                          Rows Removed by Filter: 8000
Planning Time: 2.345 ms
Execution Time: 234.567 ms

読み方のポイント

インデックスの追加

Seq Scan が遅さの原因になっている場合、インデックスを追加します。

-- users.created_at にインデックスを追加
CREATE INDEX CONCURRENTLY idx_users_created_at ON users (created_at);

-- 複合インデックス（よく一緒に使うカラム）
CREATE INDEX CONCURRENTLY idx_orders_user_status
    ON orders (user_id, status)
    WHERE deleted_at IS NULL; -- 部分インデックス

-- 追加後に再度EXPLAIN ANALYZEで確認
EXPLAIN ANALYZE
SELECT ... -- 同じクエリ

よくある遅いパターンと対策

パターン1：N+1クエリ

-- NG：ループ内でクエリ（LaravelのN+1問題と同じ）
-- PHPコードで100回以下のクエリが発行されている場合

-- OK：JOINで1回に
SELECT u.name, o.order_number, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
ORDER BY o.created_at DESC;

パターン2：SELECT * の乱用

-- NG：不要なカラムも全て取得
SELECT * FROM orders;

-- OK：必要なカラムだけ
SELECT id, order_number, total, status, created_at FROM orders;

パターン3：関数をカラムに適用するとインデックス無効

-- NG：DATE()関数でインデックスが使えない
WHERE DATE(created_at) = '2024-01-15'

-- OK：範囲クエリでインデックスを使う
WHERE created_at >= '2024-01-15 00:00:00'
  AND created_at <  '2024-01-16 00:00:00'

LaravelからEXPLAIN ANALYZEを実行

// Laravelのクエリビルダーで実行計画を確認
$query = DB::table('orders')
    ->join('users', 'users.id', '=', 'orders.user_id')
    ->where('users.created_at', '>=', '2024-01-01')
    ->select('users.name', DB::raw('COUNT(orders.id) as order_count'))
    ->groupBy('users.id', 'users.name')
    ->orderByDesc('order_count')
    ->limit(20);

// 生成されるSQLを確認
dd($query->toSql(), $query->getBindings());

// EXPLAIN ANALYZEを実行
$plan = DB::select('EXPLAIN ANALYZE ' . $query->toSql(), $query->getBindings());
foreach ($plan as $row) {
    echo $row->{'QUERY PLAN'} . "\n";
}

slow_query_log で遅いクエリを自動検出

# postgresql.conf
log_min_duration_statement = 1000  # 1秒以上のクエリをログに記録
log_line_prefix = '%t [%p]: [%l-1] '

Laravelでも遅いクエリを検出できます。

// AppServiceProvider.php
DB::listen(function ($query) {
    if ($query->time > 1000) { // 1秒以上
        Log::warning('Slow query detected', [
            'sql'      => $query->sql,
            'bindings' => $query->bindings,
            'time'     => $query->time . 'ms',
        ]);
    }
});

まとめ

EXPLAIN ANALYZEを使ってクエリの実行計画を読み解き、Seq Scanを発見してインデックスを追加するだけで、10倍〜100倍のクエリ高速化を実現できるケースは珍しくありません。弊社ではPostgreSQLを使った大規模データ処理の最適化経験を持ちます。

データベース設計・クエリ最適化のご相談はお気軽にどうぞ。