PostgreSQLクエリパフォーマンスのEXPLAIN ANALYZEによる解読

はじめに

リレーショナルデータベースの世界では、低速なクエリはアプリケーションの応答性やユーザーエクスペリエンスに影響を与える、重大なボトルネックとなる可能性があります。これらのパフォーマンス問題の根本原因を特定することは、しばしば、 haystack（干し草）の中から針を探すようなものです。幸いなことに、PostgreSQLはEXPLAIN ANALYZEという強力なツールを提供しており、これは詳細な診断X線のように機能し、データベースが特定のクエリをどのように実行するかを正確に明らかにします。この出力を理解することは、効率的なSQLを記述し、データベースパフォーマンスを最適化することを目指すすべての開発者またはDBAにとって不可欠です。この記事では、EXPLAIN ANALYZEの実用的な応用について解説し、その出力を解き明かし、情報に基づいた最適化の決定を下せるようにします。

クエリ実行計画の理解

EXPLAIN ANALYZEに飛び込む前に、いくつかの基本的な概念を明確にしましょう。

主要な用語

• クエリオプティマイザ（Query Optimizer）： データベース管理システム（DBMS）のコンポーネントで、与えられたSQLクエリに対して最も効率的な実行計画を生成する役割を担います。データ分布、利用可能なインデックス、テーブル統計などのさまざまな要因を考慮して、最適な戦略を選択します。
• 実行計画（Execution Plan）： データベースがクエリを実行する手順を記述したものです。これはツリー構造であり、各ノードは操作（例：スキャン、結合、ソート）を表し、そのブランチは操作間のデータフローを表します。
• EXPLAIN： ステートメントの計画された実行戦略を示すPostgreSQLコマンドです。クエリを実際に実行せずに、コスト（CPUサイクル、ディスクI/Oなど）の見積もりを提供します。
• EXPLAIN ANALYZE： EXPLAINの拡張バージョンで、クエリを実際に実行し、その後に実行計画と、実行時間、処理された行数、ループ回数などの各ステップに関する実際の統計情報を提供します。この「実際値と推定値」の比較は、不一致やパフォーマンスのボトルネックを特定するために非常に重要です。
• ノード/操作（Node/Operation）： 実行計画ツリーの各ステップです。一般的な操作には以下が含まれます：
  • シーケンシャルスキャン（Sequential Scan）： テーブルのすべての行を読み取ります。
  • インデックススキャン（Index Scan）： インデックスを使用して特定の行を効率的に取得します。
  • ビットマップヒープスキャン（Bitmap Heap Scan）： 2段階のプロセスです。まず、インデックスを使用してページポインタ（ビットマップインデックススキャン）を見つけ、次にそれらのページをヒープ（テーブルデータ）から取得します。
  • 結合タイプ（Join Types）（ネストループ結合、ハッシュ結合、マージ結合）： 2つ以上のテーブルの行を結合するための戦略です。
  • ソート（Sort）： 指定された列に従って行を並べ替えます。
  • 集計（Aggregate）： 集計関数（例：SUM、COUNT、AVG）を実行します。
• コスト（Cost）： 操作の相対的な費用を示す単位のない推定メトリックです。CPUコストとディスクI/Oを表します。一般に、コストが低いほど良いです。実行計画は通常、(cost=start..total rows=count width=bytes)のように表示されます。
  • start：最初の行が返される前に見積もられたコスト。
  • total：すべての行を返すための総推定コスト。
• 行数（Rows）： 操作によって処理または返された行の推定数。
• 幅（Width）： 操作によって処理された行の平均幅（バイト単位）の推定値。
• 実際時間（Actual Time）： 操作の実際の経過時間（ミリ秒単位）。EXPLAIN ANALYZEでは、(actual time=start..total rows=count loops=num_loops)のように表示されます。
  • start：最初の行が返されるまでの実際の時間。
  • total：すべての行を取得するための実際の総時間。
• ループ回数（Loops）： 特定の操作が実行された回数。これは、ネストループ結合の内側の側面など、ループ内の操作にとって特に役立ちます。

EXPLAIN ANALYZEの仕組み

SQLクエリの前にEXPLAIN ANALYZEを付けると、PostgreSQLは以下のことを行います：

1. クエリの実行： データベースは通常どおりクエリを実行します。
2. 統計情報の収集： 実行中、選択された実行計画の各ステップの詳細なタイミングと行数統計を収集します。
3. 計画と統計情報の出力： 最後に、収集された実際の統計情報とともに実行計画を提示します。推定値と実際値のこの並置比較が、まさに魔法が起こる場所です。大きな不一致は、欠落または古い統計情報、あるいは最適でないクエリ計画をしばしば示します。

実用的な適用と例

架空のusersテーブルとordersテーブルを使用して、いくつかの実用的な例を説明しましょう。

-- これらのテーブルが存在すると仮定します：
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id),
    amount DECIMAL(10, 2) NOT NULL,
    order_date TIMESTAMP DEFAULT NOW(),
    status VARCHAR(50)
);

-- いくつかのデータで生成します
INSERT INTO users (name, email)
SELECT 'User ' || i, 'user' || i || '@example.com' FROM generate_series(1, 100000) i;

INSERT INTO orders (user_id, amount, status)
SELECT TRUNC(random() * 100000) + 1, random() * 1000, 
       CASE WHEN random() < 0.5 THEN 'completed' ELSE 'pending' END
FROM generate_series(1, 500000) i;

-- 後で効果を示すためにインデックスを追加します
CREATE INDEX idx_orders_order_date ON orders (order_date);
CREATE INDEX idx_users_email ON users (email);

例1：単純なSELECT - シーケンシャルスキャン

すべてのユーザーを選択する単純なクエリを分析してみましょう。

EXPLAIN ANALYZE SELECT * FROM users WHERE created_at < '2023-01-01';

出力（簡略化）：

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on users  (cost=0.00..2020.00 rows=50000 width=128) (actual time=0.063..28.543 rows=100000 loops=1)
   Filter: (created_at < '2023-01-01 00:00:00'::timestamp without time zone)
   Rows Removed by Filter: 0
 Planning Time: 0.089 ms
 Execution Time: 34.502 ms

解釈：

• Seq Scan on users: データベースはシーケンシャルスキャンを実行しました。これは、usersテーブルのすべての行を読み取ったことを意味します。created_atにインデックスがないため、これは予想されます。
• (cost=0.00..2020.00 rows=50000 width=128): プランナは、50000行を返すのに2020.00のコストを推定しました。
• (actual time=0.063..28.543 rows=100000 loops=1): 実際の実行は、100000行を返すのに28.543 msかかりました。
• 不一致： 推定行数（50000）と実際行数（100000）に注目してください。これは、created_at列のプランナの統計情報が最新でないか、不十分である可能性を示しており、不正確なコスト見積もりにつながります。データベースは、より少ない行を取得し、したがって「より安価な」計画になると考えていました。これが非常に異なる計画（例：異なる結合戦略を使用する）につながった場合、深刻な問題を示す可能性があります。単純なSeq Scanでは、それほど重要ではありません。
• Filter: (created_at < '2023-01-01...'): これは、スキャン後に適用されるWHERE句を示しています。

最適化の洞察： このクエリが頻繁に実行され、created_atが非常に選択的である場合、created_atへのインデックスが有益になります。

例2：インデックススキャン

インデックスを追加して、再度実行してみましょう。

CREATE INDEX idx_users_created_at ON users (created_at);
ANALYZE users; -- 新しいインデックスの統計情報を更新します
EXPLAIN ANALYZE SELECT * FROM users WHERE created_at < '2023-01-01';

出力（簡略化）：

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_users_created_at on users  (cost=0.42..362.46 rows=50000 width=128) (actual time=0.026..1.879 rows=100 loops=1)
   Index Cond: (created_at < '2023-01-01 00:00:00'::timestamp without time zone)
 Planning Time: 0.158 ms
 Execution Time: 2.222 ms

解釈：

• Index Scan using idx_users_created_at on users: 成功！データベースは新しいインデックスを使用しています。
• cost=0.42..362.46: 推定コストは大幅に低くなりました。
• actual time=0.026..1.879: 実際の実行時間は大幅に速くなりました（Seq Scanの28.543 msに対して1.879 ms）。
• 不一致（再び）： プランナは50000行が返されると推定しましたが、実際には100行しか見つかりませんでした。これは、created_at < '2023-01-01'条件がプランナの予測よりもはるかに選択的であったことを示しています。計画は良好でしたが（インデックスを使用）、このような大きな不一致は、より複雑なシナリオではプランナを誤解させる可能性があります。定期的にANALYZEを実行したり、autovacuumに頼って統計情報を更新したりすることが重要です。

例3：結合クエリ

usersとordersの結合を分析してみましょう。

EXPLAIN ANALYZE
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.order_date > '2024-01-01' AND u.email LIKE '%@example.com'
ORDER BY o.amount DESC
LIMIT 10;

出力（主要ノードを強調表示、簡略化）：

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1000.00..1000.25 rows=10 width=116) (actual time=14.542..14.549 rows=10 loops=1)
   ->  Sort  (cost=1000.00..1000.75 rows=30 width=116) (actual time=14.540..14.540 rows=10 loops=1)
         Sort Key: o.amount DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Merge Join  (cost=0.86..999.00 rows=30 width=116) (actual time=0.089..14.502 rows=33 loops=1)
               Merge Cond: (u.id = o.user_id)
               ->  Index Scan using users_pkey on users u  (cost=0.43..37.38 rows=1000 width=108) (actual time=0.038..0.540 rows=1000 loops=1)
                     Filter: (email ~~ '%@example.com'::text)
                     Rows Removed by Filter: 0
               ->  Sort  (cost=0.43..0.44 rows=30 width=16) (actual time=0.047..0.528 rows=33 loops=1)
                     Sort Key: o.user_id
                     Sort Method: quicksort  Memory: 25kB
                     ->  Index Scan using idx_orders_order_date on orders o  (cost=0.43..0.98 rows=30 width=16) (actual time=0.016..0.439 rows=33 loops=1)
                           Index Cond: (order_date > '2024-01-01 00:00:00'::timestamp without time zone)
 Planning Time: 0.567 ms
 Execution Time: 14.602 ms

解釈：

• 最上位のLimit: ソート後に最初の10行を返すために適用されます。
• Sort: o.amount DESCで結果をソートする必要がありました。top-N heapsortは、Nが小さい場合には効率的です。
• Merge Join: 両方の結合条件（u.idとo.user_id）が事前にソートされていたか、効率的にソートできるため、データベースはマージ結合戦略を選択しました。
  • 左側のブランチ（usersテーブル）：Index Scan using users_pkey on users u: PostgreSQLはusersの主キーインデックスをスキャンし、その後email LIKE '%@example.com'でフィルタリングしました。actual timeは1000行に対して0.540 msです。
  • 右側のブランチ（ordersテーブル）：Sort -> Index Scan using idx_orders_order_date on orders o：
    • まず、order_date > '2024-01-01'条件のためにordersのidx_orders_order_dateを使用してIndex Scanを実行しました。これは33行を0.439 msで返しました。
    • 次に、これらの33行は、Merge Joinを容易にするためにo.user_idでSortされました。これは0.528 msかかりました。
• 全体： 計画は妥当に見えます。インデックスは、該当するフィルタリングおよび結合に使用されています。Merge Joinは、データがすでにソートされている場合には通常効率的です。
  • Planning Time: クエリ最適化プログラムがこの計画を選択するのにかかった時間。
  • Execution Time: クエリの実行にかかった総時間。

最適化の洞察：

• email LIKE '%@example.com'が非常に選択的であったとしても、多くのユーザーが一致した場合、email上のGINインデックスが高速になる可能性がありますが、LIKEクエリは、先頭のワイルドカードが回避されない限り、または特定の拡張機能が使用されない限り、インデックスで苦労することがよくあります。この単純なワイルドカードの場合、あまりにも多くのメールがパターンに一致すると、シーケンシャルスキャンが最終的に選択される可能性があります。
• usersのIndex Scanで処理された実際のrows（1000）は、多くのユーザーがemail LIKE '%@example.com'パターンに適合することを示唆しています。このフィルタが非常に選択的であった場合、インデックスだけでは多くのページを回避するのに十分でない場合、Bitmap Heap Scanが好まれることもあります。

EXPLAIN ANALYZE出力を読むための主要なポイント：

1. 下から上へ、右から左へ読む： 最も内側の操作またはスキャンノードが最初に実行されます。
2. 高コストノードに焦点を当てる： 高いactual timeを持つノードを探します。パフォーマンスのボトルネックはここにあります。
3. 推定と実際を比較する：
   • rowsの不一致： 大きな違いは、不正確な統計情報（関連テーブルでANALYZEまたはVACUUM ANALYZEを実行）を示していることが多いです。不正確な推定は、オプティマイザが最適でない計画を選択する原因となる可能性があります。
   • costとactual timeの不一致： コストは理論的なものですが、予想よりも実際時間が大幅に長い場合は、問題の兆候となる可能性があります。
4. 高コストな操作を特定する：
   • 大規模テーブルでのSeq Scan： 通常は警戒信号です。インデックスの追加を検討してください。
   • 大規模データセットでのSort： ディスクへのスピル（Sort Method: external merge Disk: XMB）が発生する場合、非常にコストがかかる可能性があります。データが事前にソートされていること（例：インデックスまたは異なる結合戦略による）を確認するか、ソート前にデータセットを制限することで回避できます。
   • 高コストなJoin操作： 効率的なインデックスなしで内側のループが多数回大規模テーブルに対して実行される場合、Nested Loop Joinは遅くなる可能性があります。Hash JoinとMerge Joinは、大規模データセットに対して一般によりスケーラブルです。
5. FilterまたはIndex Condを探す： WHERE句がいつ適用されるかを理解します。Index Condはインデックススキャン中に適用されるため、非常に効率的です。Filterはデータが取得された後に適用されるため、必要以上に多くの行が読み取られた可能性があることを意味します。
6. Loopsカウント： 特にネストされた操作で役立ち、内側の操作が実行された回数を示します。内側の操作が遅く、ループ回数が多いと、問題が倍増します。

結論

EXPLAIN ANALYZEを習得することは、PostgreSQLを扱うすべての人にとって不可欠なスキルです。データベースの内部動作を比類のない窓を提供し、パフォーマンスのボトルネックを正確に診断できるようにします。その詳細な出力を体系的に解釈し、推定統計情報と実際統計情報を比較し、コストのかかる操作を特定することにより、低速で非効率的なクエリを、応答性が高くスケーラブルなままであることを保証する、超高速で最適化された強力なクエリに変えることができます。最終的に、EXPLAIN ANALYZEはPostgreSQLクエリパフォーマンスを最適化するための主要なツールです。