5
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@yosukenaruse

SQL実行計画完全ガイド：EXPLAINから最適化まで

5
Posted at

SQL実行計画完全ガイド：EXPLAINから最適化まで

はじめに

SQLの実行計画（Execution Plan）は、データベースがクエリをどのように実行するかを示す重要な情報です。実行計画を理解することで、クエリのパフォーマンスを分析し、最適化の指針を得ることができます。この記事では、実行計画の読み方から実践的な最適化テクニックまで詳しく解説します。

実行計画とは

実行計画の基本概念

実行計画は、データベースのクエリオプティマイザが作成する、SQLクエリの実行手順書です。データベースは以下の手順でクエリを処理します：

  1. SQLの解析: 構文チェックと意味解析
  2. 実行計画の生成: 最適な実行手順の決定
  3. 実行計画の実行: 実際のデータアクセスと処理

実行計画の重要性

-- 実行計画なしでは、なぜクエリが遅いのかわからない
SELECT * FROM users WHERE email = 'user@example.com';

-- 実行計画を確認することで、問題点を特定できる
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

EXPLAINコマンドの基本

基本的なEXPLAIN

-- 基本的な実行計画の表示
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- 結果例：
-- Seq Scan on users  (cost=0.00..1000.00 rows=1 width=...)
--   Filter: (email = 'user@example.com'::text)

EXPLAIN ANALYZE

-- 実際の実行時間を含む詳細な実行計画
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM users WHERE email = 'user@example.com';

-- 結果例：
-- Seq Scan on users  (cost=0.00..1000.00 rows=1 width=...) (actual time=0.123..0.456 rows=1 loops=1)
--   Filter: (email = 'user@example.com'::text)
--   Rows Removed by Filter: 9999
--   Buffers: shared hit=100 read=50

EXPLAINのオプション

-- 詳細な情報を含む実行計画
EXPLAIN (
    ANALYZE,      -- 実際の実行時間を表示
    BUFFERS,      -- バッファ使用状況を表示
    VERBOSE,      -- より詳細な情報を表示
    FORMAT TEXT   -- 出力形式を指定
) SELECT * FROM users WHERE email = 'user@example.com';

実行計画の読み方

実行計画の構造

-- サンプルテーブル
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    amount DECIMAL(10,2),
    order_date DATE
);

-- 複雑なクエリの実行計画
EXPLAIN (ANALYZE, BUFFERS) 
SELECT u.name, COUNT(*), SUM(o.amount)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'
GROUP BY u.id, u.name
ORDER BY SUM(o.amount) DESC;

実行計画例：

Sort  (cost=... rows=... width=...) (actual time=... rows=... loops=1)
  Sort Key: (sum(o.amount)) DESC
  Sort Method: external merge  Disk: 1024kB
  ->  HashAggregate  (cost=... rows=... width=...) (actual time=... rows=... loops=1)
        Group Key: u.id, u.name
        ->  Hash Join  (cost=... rows=... width=...) (actual time=... rows=... loops=1)
              Hash Cond: (o.user_id = u.id)
              ->  Seq Scan on orders o  (cost=... rows=... width=...) (actual time=... rows=... loops=1)
              ->  Hash  (cost=... rows=... width=...) (actual time=... rows=... loops=1)
                    ->  Seq Scan on users u  (cost=... rows=... width=...) (actual time=... rows=... loops=1)
                          Filter: (created_at >= '2024-01-01'::date)

実行計画の要素

1. ノードタイプ

  • Seq Scan: シーケンシャルスキャン（全件スキャン）
  • Index Scan: インデックススキャン
  • Index Only Scan: インデックスのみスキャン
  • Hash Join: ハッシュ結合
  • Nested Loop: ネステッドループ結合
  • Merge Join: マージ結合
  • Sort: ソート
  • HashAggregate: ハッシュ集約

2. コスト情報

-- cost=開始コスト..終了コスト
-- 例：cost=0.00..1000.00
-- 0.00: 初期コスト
-- 1000.00: 総コスト

3. 行数情報

-- rows=推定行数
-- actual rows=実際の行数

4. 実行時間

-- actual time=開始時間..終了時間
-- 例：actual time=0.123..0.456

主要な実行計画ノード

1. スキャンノード

Seq Scan（シーケンシャルスキャン）

-- 全件スキャン
EXPLAIN SELECT * FROM users WHERE name LIKE '%田中%';

-- 結果例：
-- Seq Scan on users  (cost=0.00..1000.00 rows=100 width=...)
--   Filter: (name ~~ '%田中%'::text)

Index Scan（インデックススキャン）

-- インデックスを使用したスキャン
CREATE INDEX idx_users_email ON users(email);
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- 結果例：
-- Index Scan using idx_users_email on users  (cost=0.29..8.31 rows=1 width=...)
--   Index Cond: (email = 'user@example.com'::text)

Index Only Scan（インデックスのみスキャン）

-- インデックスから必要な情報を取得
EXPLAIN SELECT email FROM users WHERE email LIKE 'user%@example.com';

-- 結果例：
-- Index Only Scan using idx_users_email on users  (cost=0.29..8.31 rows=10 width=...)
--   Index Cond: (email ~~ 'user%@example.com'::text)

2. 結合ノード

Nested Loop（ネステッドループ結合）

-- 小さいテーブルとの結合
EXPLAIN SELECT u.name, d.name 
FROM users u 
JOIN departments d ON u.department_id = d.id;

-- 結果例：
-- Nested Loop  (cost=... rows=... width=...)
--   ->  Seq Scan on users u  (cost=... rows=... width=...)
--   ->  Index Scan using departments_pkey on departments d  (cost=... rows=... width=...)
--         Index Cond: (id = u.department_id)

Hash Join（ハッシュ結合）

-- 大きなテーブル間の結合
EXPLAIN SELECT u.name, COUNT(*)
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- 結果例：
-- HashAggregate  (cost=... rows=... width=...)
--   Group Key: u.id, u.name
--   ->  Hash Join  (cost=... rows=... width=...)
--         Hash Cond: (o.user_id = u.id)
--         ->  Seq Scan on orders o  (cost=... rows=... width=...)
--         ->  Hash  (cost=... rows=... width=...)
--               ->  Seq Scan on users u  (cost=... rows=... width=...)

Merge Join（マージ結合）

-- ソート済みデータの結合
EXPLAIN SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
ORDER BY u.id;

-- 結果例：
-- Merge Join  (cost=... rows=... width=...)
--   Merge Cond: (u.id = o.user_id)
--   ->  Index Scan using users_pkey on users u  (cost=... rows=... width=...)
--   ->  Sort  (cost=... rows=... width=...)
--         Sort Key: o.user_id
--         ->  Seq Scan on orders o  (cost=... rows=... width=...)

3. 集約・ソートノード

HashAggregate（ハッシュ集約）

-- GROUP BY句の処理
EXPLAIN SELECT department_id, COUNT(*)
FROM users
GROUP BY department_id;

-- 結果例：
-- HashAggregate  (cost=... rows=... width=...)
--   Group Key: department_id
--   ->  Seq Scan on users  (cost=... rows=... width=...)

Sort（ソート）

-- ORDER BY句の処理
EXPLAIN SELECT * FROM users ORDER BY name;

-- 結果例：
-- Sort  (cost=... rows=... width=...)
--   Sort Key: name
--   Sort Method: quicksort  Memory: 25kB
--   ->  Seq Scan on users  (cost=... rows=... width=...)

実行計画の分析と最適化

1. パフォーマンス問題の特定

スロークエリの分析

-- 問題のあるクエリの実行計画
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM users 
WHERE email LIKE '%@example.com' 
ORDER BY created_at DESC;

-- 問題点：
-- 1. Seq Scan（全件スキャン）
-- 2. 外部ソート（Disk使用）
-- 3. 大量のバッファ読み込み

最適化後の実行計画

-- インデックスを作成
CREATE INDEX idx_users_email_created ON users(email, created_at DESC);

-- 最適化されたクエリ
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM users 
WHERE email LIKE '%@example.com' 
ORDER BY created_at DESC;

-- 改善点：
-- 1. Index Scan（インデックス使用）
-- 2. メモリ内ソート
-- 3. バッファ使用量の削減

2. 結合の最適化

非効率的な結合

-- 非効率的な結合の例
EXPLAIN (ANALYZE, BUFFERS) 
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01';

-- 問題点：
-- 1. 大きなテーブル間のHash Join
-- 2. 大量のメモリ使用
-- 3. フィルタリングが結合後に実行

最適化された結合

-- インデックスを作成
CREATE INDEX idx_users_created_id ON users(created_at, id);
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 最適化されたクエリ
EXPLAIN (ANALYZE, BUFFERS) 
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01';

-- 改善点：
-- 1. Index Scan + Nested Loop
-- 2. フィルタリングが結合前に実行
-- 3. メモリ使用量の削減

3. 集約の最適化

非効率的な集約

-- 非効率的な集約の例
EXPLAIN (ANALYZE, BUFFERS) 
SELECT department_id, COUNT(*), AVG(salary)
FROM employees
GROUP BY department_id;

-- 問題点：
-- 1. Seq Scan（全件スキャン）
-- 2. 大きなHashAggregate
-- 3. 大量のメモリ使用

最適化された集約

-- インデックスを作成
CREATE INDEX idx_employees_dept_salary ON employees(department_id, salary);

-- 最適化されたクエリ
EXPLAIN (ANALYZE, BUFFERS) 
SELECT department_id, COUNT(*), AVG(salary)
FROM employees
GROUP BY department_id;

-- 改善点：
-- 1. Index Only Scan
-- 2. 効率的な集約処理
-- 3. メモリ使用量の削減

実行計画の監視と分析

1. 実行計画の比較

-- 実行計画の比較スクリプト
-- 最適化前
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 
SELECT u.name, COUNT(*), SUM(o.amount)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'
GROUP BY u.id, u.name
ORDER BY SUM(o.amount) DESC;

-- 最適化後
CREATE INDEX idx_users_created_id ON users(created_at, id);
CREATE INDEX idx_orders_user_amount ON orders(user_id, amount);

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 
SELECT u.name, COUNT(*), SUM(o.amount)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'
GROUP BY u.id, u.name
ORDER BY SUM(o.amount) DESC;

2. パフォーマンスメトリクス

-- 重要なメトリクス
-- 1. 実行時間（actual time）
-- 2. 行数（rows）
-- 3. バッファ使用量（Buffers）
-- 4. メモリ使用量（Memory）
-- 5. ディスク使用量（Disk）

3. 実行計画の自動分析

-- 実行計画の自動分析クエリ
WITH plan_analysis AS (
    SELECT 
        query,
        plan,
        execution_time,
        planning_time,
        buffers_read,
        buffers_hit
    FROM pg_stat_statements
    WHERE mean_time > 1000  -- 1秒以上
)
SELECT 
    query,
    execution_time,
    planning_time,
    buffers_read,
    buffers_hit,
    CASE 
        WHEN buffers_hit > 0 THEN 
            ROUND((buffers_hit::float / (buffers_read + buffers_hit)) * 100, 2)
        ELSE 0 
    END AS cache_hit_ratio
FROM plan_analysis
ORDER BY execution_time DESC;

実践的な最適化テクニック

1. インデックス戦略

-- 複合インデックスの最適化
-- よく使用されるクエリパターン
SELECT * FROM users WHERE email = 'user@example.com' AND status = 'active';
SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC;

-- 最適なインデックス設計
CREATE INDEX idx_users_email_status ON users(email, status);
CREATE INDEX idx_users_status_created ON users(status, created_at DESC);

2. クエリの書き方の最適化

-- 非効率的なクエリ
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- 効率的なクエリ
SELECT * FROM users WHERE email = 'user@example.com';

-- 関数インデックスの作成
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

3. 結合順序の最適化

-- 非効率的な結合順序
SELECT u.name, o.amount, p.name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
WHERE u.created_at >= '2024-01-01';

-- 効率的な結合順序
SELECT u.name, o.amount, p.name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
WHERE u.created_at >= '2024-01-01'
ORDER BY u.id;  -- 結合順序を明示的に指定

よくある問題と対処法

1. 全件スキャンの問題

-- 問題：全件スキャンが発生
EXPLAIN SELECT * FROM users WHERE name LIKE '%田中%';

-- 対処法：全文検索インデックスの作成
CREATE INDEX idx_users_name_gin ON users USING gin(to_tsvector('japanese', name));

-- 最適化されたクエリ
SELECT * FROM users WHERE to_tsvector('japanese', name) @@ plainto_tsquery('japanese', '田中');

2. 外部ソートの問題

-- 問題：外部ソートが発生
EXPLAIN SELECT * FROM users ORDER BY name;

-- 対処法：インデックスの作成
CREATE INDEX idx_users_name ON users(name);

-- 最適化されたクエリ
SELECT * FROM users ORDER BY name;

3. メモリ不足の問題

-- 問題：大量のメモリ使用
EXPLAIN (ANALYZE, BUFFERS) 
SELECT u.name, COUNT(*)
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- 対処法：work_memの調整
SET work_mem = '256MB';

-- または、クエリの分割
SELECT u.name, COUNT(*)
FROM users u
WHERE u.id IN (
    SELECT DISTINCT user_id 
    FROM orders 
    WHERE order_date >= '2024-01-01'
)
GROUP BY u.id, u.name;

まとめ

SQLの実行計画を理解し、適切に分析することで、クエリのパフォーマンスを大幅に向上させることができます。

主要なポイント

  1. 実行計画の読み方: ノードタイプ、コスト、行数、実行時間の理解
  2. パフォーマンス問題の特定: スロークエリの原因分析
  3. 最適化テクニック: インデックス、クエリ書き方、結合順序の最適化
  4. 継続的な監視: 実行計画の定期的な分析と改善

ベストプラクティス

  1. EXPLAIN ANALYZEの活用: 実際の実行時間を含む分析
  2. インデックス戦略: クエリパターンに基づく適切なインデックス設計
  3. クエリの最適化: 効率的な書き方と結合順序の考慮
  4. 定期的な見直し: パフォーマンスの継続的な監視と改善

実行計画をマスターすることで、データベースのパフォーマンスを最大限に引き出すことができます。

参考資料

5
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?