実行計画とは何か？

遅いSQLを改善したい！
そんなときにまず確認するのが「実行計画」です。

実行計画とは、DBMS（データベース管理システム）がSQL文をどのような手順で処理するかを示した「処理手順書」のようなものです。

DBMSは頭がいいので、SQLを受け取ると、どのような手順で処理するのが効率的かを考え、実行計画を作ってくれます。
しかし、時々テーブル構造やデータ量により、効率の悪い実行計画が選ばれてしまうことがあります。
その結果、SQLの実行に想定以上の時間を要してしまいます。

このようなSQLを改善するにあたり、実行計画を確認して、どこがボトルネックになっているのかを読み解くことが重要になります。

この記事では、SQL性能改善初心者向けに、PostgreSQLの実行計画の基本的な読み方と、ボトルネックを特定するためのポイントをまとめています。

本記事の実行計画は、AIによる生成例も含まれています。
実際の環境とは値が異なる場合がありますので、あくまで参考程度にご覧ください。

実行計画の取得方法

実行計画は、実行計画を取得したいSQLの先頭にEXPLAINを付けることで確認することができます。

EXPLAIN SELECT * FROM sample_table ;

-- EXPLAINした結果
Seq Scan on sample_table  (cost=0.00..22.70 rows=1270 width=36)

EXPLAINを付けてSQLを実行すると、DBMSが「このような手順でSQLを実行しよう」と考えた実行計画を確認することができます。
ただし、EXPLAINで取得できる内容はあくまでも推定に基づいた計画に過ぎません。
そのため、計画上は何の問題もないように見えて、実際に実行すると時間がかかってしまうようなケースもあります。

そのような場合には EXPLAIN ANALYZE を利用します。

EXPLAIN ANALYZE SELECT * FROM sample_table ;

-- EXPLAIN ANALYZEした結果
Seq Scan on sample_table
(cost=0.00..22.70 rows=1270 width=36) (actual time=0.070..0.071 rows=3 loops=1)
Planning Time: 0.045 ms
Execution Time: 0.082 ms

EXPLAIN ANALYZEを付けることで、実行計画に加えて、SQLを実際に実行した際の結果（実行時間や処理された行数など）も確認することができます。

注意点として、EXPLAIN ANALYZEを使用すると、実際にSQLが実行されてしまいます。
INSERT / UPDATE / DELETEはデータの更新が実際に行われますし、サーバーに高負荷をかけるようなSQLだった場合、環境に影響を与えてしまう可能性があります。
本番環境で実行する場合は十分に注意しましょう。

他にも、EXPLAINにはVERBOSEやCOSTSなど、様々なオプションがあるので、興味がある方は公式ドキュメントを見てみてください。
https://www.postgresql.jp/docs/16/sql-explain.html

実行計画を読んでみよう

たとえば、以下のような実行計画があったとします。

-- EXPLAIN ANALYZEした結果の実行計画
Nested Loop  (cost=0.56..245.32 rows=100 width=64)
             (actual time=0.120..15.300 rows=120 loops=1)
  -> Index Scan using idx_users_email on users u
       (cost=0.28..8.30 rows=10 width=32)
       (actual time=0.030..0.050 rows=10 loops=1)
       Index Cond: (email LIKE '%@example.com')
  -> Nested Loop  (cost=0.28..23.40 rows=10 width=32)
                  (actual time=0.020..1.200 rows=12 loops=10)
       -> Index Scan using idx_orders_user_id on orders o
            (cost=0.14..10.50 rows=5 width=32)
            (actual time=0.010..0.050 rows=5 loops=10)
            Index Cond: (user_id = u.id)
       -> Index Scan using idx_order_items_order_id on order_items oi
            (cost=0.14..2.50 rows=2 width=32)
            (actual time=0.005..0.010 rows=2 loops=50)
            Index Cond: (order_id = o.id)

Planning Time: 0.350 ms
Execution Time: 15.500 ms

実行計画は、一番上から読むのではなく、一番インデントが深い部分から読むのが基本的な読み方です。
同じ階層のノードが複数ある場合は、下から上に読みます。

上の実行計画を簡易的な図で表すとこのような形になります。

Nested Loop #1
 ├ Index Scan users
 └ Nested Loop #2
      ├ Index Scan orders
      └ Index Scan order_items

一番階層が深いのはorder_itemsのIndex Scanです。
ここから上に遡ると、処理順は次のように理解できます。

1. order_items (Index Scan)
2. orders (Index Scan)
3. order_items と orders を Nested Loop #2 で結合
4. users (Index Scan)
5. users と Nested Loop #2 の結果を Nested Loop #1 で結合

処理コストの確認方法

各ノードには以下のような情報が記載されています。

(cost=0.56..245.32 rows=100 width=64) (actual time=0.120..15.300 rows=120 loops=1)

EXPLAINで出力されるcostの意味

(cost=0.56..245.32 rows=100 width=64)はオプティマイザが推定したコストです。
オプティマイザとは、クエリの最適な実行方法を決定するための機能のことです。

cost=0.56..245.32：
推定コスト（始動..総コスト数）
- 左の値：最初の1件目のデータを返すまでの推定コスト
- 右の値：ノードの処理完了までにかかる推定総コスト（子ノードの処理も含む）
- コストは秒数ではなくあくまで相対的な推定値であり、実行時間ではありません
rows=100：
ノード毎の処理で返却される推定返却行数
width=64：
1行あたりの平均バイト数

EXPLAIN ANALYZEで出力されるactualの意味

(actual time=0.120..15.300 rows=120 loops=1)はANALYZEオプションにより出力された実際の実行結果です。

actual time=0.120..15.300：
最初の行が読み取り開始から、最後の行までかかった時間（ms単位）
rows=120：
ノード毎の処理で返却された実際の返却行数
loops=1：
このノードが何回実行されたか

※親ノードのactual time/costには子ノードの処理も含まれるため、単純に親の数字だけを見て重い軽いを判断しないよう気を付けましょう。

実行計画はなぜ推定なのか？

実行計画というのは、毎回テーブルの全データを見て算出しているのではなく、統計情報に基づいて算出されています。

統計情報とは、テーブルの行数やインデックス情報などをまとめたもので、オプティマイザはこれをもとに実行計画を算出しています。
統計情報は基本的に一定のタイミングで自動更新されますが、統計情報が何らかの原因で更新されないままでいると、実際のデータ量と乖離した古い情報で実行計画を算出してしまい、効率の悪い計画が選ばれてしまうことがあります。

例えば、costとactualのrowsに大幅な乖離がある場合は、統計情報が影響している可能性が高いです。

統計情報が原因で性能が悪い場合、以下のSQLで統計情報を更新できます。

ANALYZE table_name;

基本的に、どのような場合でも統計情報を疑ってANALYZEを試してみると良いでしょう。

長期的な改善を検討するならば、夜間のバッチで統計情報を定期更新などの運用も検討すると良いでしょう。

Scanの読み方

Seq Scan

Seq Scanとは、検索のために表の最初から順に読み込む方法です。
つまり、100万件のテーブルであれば、全行に対して順番に条件判定が行われます。

テーブルが小さい場合や、検索条件にヒットする行がテーブルの大部分を占める場合に効率的です。

-- 性能の良いSeq Scanの例
Seq Scan on users u
  (cost=0.00..800.00 rows=10000 width=64)
  (actual time=0.010..12.000 rows=10000 loops=1)
  Filter: (age >= 20)
  Rows Removed by Filter: 1000
Planning Time: 0.150 ms
Execution Time: 13.000 ms

Filter: (age >= 20)：
Seq Scanの条件。条件に合致する行を抽出。
Rows Removed by Filter: 1000：
条件で除外された行数。
Seq Scanの条件によって、1000行が除外されたという意味

実行計画から読み取れること

小規模なテーブルで実行されている
Filterで除外される行数が少ない
処理速度が速い

ボトルネックになるケース

大規模なテーブルや、インデックスを使うべき検索条件に対してインデックスが設定されていない場合、Seq Scanが選ばれて処理に時間を要すことがあります。

また、検索条件で除外される行数が多い場合、本来は少数の行だけ取得すれば良いにも関わらず、条件判定のために全行を読み込む必要があるため、無駄なI/Oが発生し、処理が遅くなります。

ただし、あくまでもSeq Scan自体が悪いわけではなく、Seq Scanが適さない検索条件にもかかわらず、「Seq Scanで全件読む方が速い」と誤って判断された場合に問題になりやすいということです。

-- ボトルネックになったSeq Scanの例
Seq Scan on users u
  (cost=0.00..50000.00 rows=1000 width=64)
  (actual time=0.050..2800.000 rows=1000 loops=1)
  Filter: (age >= 20)
  Rows Removed by Filter: 999000
Planning Time: 0.150 ms
Execution Time: 2805.000 ms

実行計画から読み取れること

テーブルが大規模（100万行）だが、条件にヒットするのはわずか1000行。
Filterで除外された件数が多い＝ヒット率が低いのにSeq Scanが選ばれている。

Seq Scanでこのようなボトルネックを見つけたら、以下の改善を検討しましょう。

統計情報の更新
Filterに記載されている条件列（WHERE条件）に対してインデックスを作成する
Rows Removed by Filterの値が極端に多い場合は、インデックスを検討するべきです。
必要なカラムだけ指定する（I/O削減）
検索時は、Filterで抽出する対象のカラムだけでなく、行単位でデータを読み込んだ上で条件判定を行います。
SELECT * 〜ではなく、SELECT col1,col2 など、必要なカラムだけ指定すると返すデータ量が減り、I/Oが削減されてパフォーマンスが向上します。

Index Scan

Index Scanはインデックスを利用して検索を行う手法です。
インデックスとは、カラムの値と物理的な格納位置を紐づけて整理している辞書のようなもので、これを用いることで、検索処理を高速で行うことができます。

テーブルが大きい場合や、検索条件にヒットする行が少ない場合に効率的です。

-- インデックス
CREATE INDEX idx_users_age ON users(age);
-- 性能の良いIndex Scanの例
Index Scan using idx_users_age on users u
  (cost=0.00..500.00 rows=10000 width=64)
  (actual time=0.005..15.000 rows=10000 loops=1)
  Index Cond: (age >= 20)
Planning Time: 0.200 ms
Execution Time: 15.500 ms

Index Cond：インデックスを使った検索条件

実行計画から読み取れること

Filter条件がインデックスと一致している。
ヒット件数が適度に少なく、全件スキャンが不要。

ボトルネックになるケース

Index Scanが極端に遅くなるケースはほとんどありません。
性能問題としてよく見られるのは、インデックスを貼っているにも関わらず、Index Scanが利用されない場合です。

インデックスカラムで演算や関数を使っている場合
```
関数(index column) = value
```
インデックスは「カラムの生の値」に対して作られていますが、クエリでは「関数適用後の値」を比較しています。
同様にインデックスカラムに対してLIKE（中間一致・後方一致）・型変換・暗黙キャストもNG。
ヒット件数が多い場合
ヒット件数が多い場合はPostgreSQLは「Seq Scanの方が速い」と判断してSeq Scanを選択しやすくなる傾向があります。
特にカーディナリティが低い場合にその傾向があります。
- カーディナリティとは、重複を除いた値の種類の多さのこと。
  - 例1：高カーディナリティ：ユーザー名（田中、佐藤、鈴木…）
  - 例2：低カーディナリティ：性別、フラグカラム（有効/無効）
低カーディナリティの場合、インデックスを貼っても「フルスキャン（Seq Scan）の方が早い」とオプティマイザに判断されやすいです。

このようなボトルネックを見つけたら、以下の改善を検討しましょう。

統計情報の更新

関数を使わず比較できないか検討する

-- 改善前
関数(index column) = value

-- 改善後
index column = 関数(value)

どうしても関数を使わなければならないなら、関数インデックスを検討する
関数インデックスとは、その列に特定の関数を適用した計算結果に対して作成するインデックスです。
絞り込み条件を増やす
インデックスが有効に使われるように、フィルタ条件を調整する。

インデックスは利用する時のことをしっかり考慮して設計する必要があります。

Scanの応用パターン

以下はIndex Scan/Seq Scanの派生形です。
基本的に高速処理が期待できるため、内容だけ押さえておきましょう。

Index Only Scan

インデックスだけで必要な情報がすべて取得できる場合に使われるIndex Scan。
SELECTするカラムがすべてインデックスに含まれており、インデックスがカバリングインデックス（SELECT句とWHERE句で必要な全ての列を含む）になっている場合に選択される。
テーブル本体を直接見に行かず、インデックスだけで検索する。
→ディスクI/Oを大幅に削減できるため、超高速。

Bitmap Index Scan

複数条件の検索や、ヒット件数が中程度の場合に効率的なIndex Scanの一種。
インデックスでヒットした行の位置（TID）をビットマップでまとめて保持し、検索処理を行う。
単純なIndex Scanよりもヒット率が高く、複雑な条件の場合に選択されやすい。

Parallel ○○ Scan

大規模テーブルに対して複数プロセス（ワーカー）が並列にScanを実行する方法。
Seq Scan / Index Scan のどちらにも適用可能。
CPUをフル活用するため、処理を高速化できる。

※パラレルクエリの場合の実行計画の読み方は、後述する「Gatherの読み方」を参照してください。

Joinの読み方

Hash Join

Hash Joinは、片方のテーブル（基本的には小さい方）を読み込み、結合キーをもとに「ハッシュテーブル」を作成します。
次に、もう片方のテーブルを順番に読み込み、ハッシュテーブルに対して検索を行うことで一致するデータを取得する結合方法です。
ハッシュテーブルを作ることにより、結合時の検索を高速（ほぼO(1)）に行うことができます。
※ O(1)（オーいち）は、「理論上、データ量に関わらず一定時間で処理できること」という意味です。

以下のような場合にHash Joinが有効です。

結合キーにインデックスが存在しない
結合するデータ量が非常に多い（巨大なテーブル同士の結合）
等価結合（=）である

-- 性能の良いHash Joinの例
Hash Join  (cost=1000.00..25000.00 rows=100000 width=64)
           (actual time=20.000..180.000 rows=100000 loops=1)
  -> Seq Scan on orders o
           (cost=0.00..15000.00 rows=100000 width=32)
           (actual time=0.010..50.000 rows=100000 loops=1)
  -> Hash
           (cost=800.00..800.00 rows=10000 width=32)
           (actual time=19.000..19.500 rows=10000 loops=1)
           Buckets: 16384  Batches: 1  Memory Usage: 2048kB
    -> Seq Scan on users u
           (cost=0.00..800.00 rows=10000 width=32)
           (actual time=0.005..10.000 rows=10000 loops=1)
Planning Time: 0.200 ms
Execution Time: 185.000 ms

Buckets：
ハッシュテーブルの区画数
Batches：
Hash Join実行時にハッシュテーブルの構築処理がwork_mem(設定によって指定された特定の量のメモリ)を超え、ディスクへ分割出力した回数。
1より大きいとディスクI/Oが発生している。

実行計画から読み取れること

Hashノードにあるusersテーブルがハッシュテーブルの構築対象。
Batchesが1でディスクI/Oが発生していない。
処理時間も短く、高速に処理が完了している。

ボトルネックになるケース

ハッシュテーブルの構築にはメモリを使用するため、work_memを超過する場合は、データがディスクに書き出され、ディスクI/Oが増加して性能が低下します。

-- ボトルネックになったHash Joinの例
Hash Join  (cost=1000.00..25000.00 rows=100000 width=64)
           (actual time=50.000..2500.000 rows=100000 loops=1)
  -> Seq Scan on orders o
           (cost=0.00..15000.00 rows=100000 width=32)
           (actual time=0.020..200.000 rows=100000 loops=1)
  -> Hash
           (cost=800.00..800.00 rows=10000 width=32)
           (actual time=40.000..1500.000 rows=10000 loops=1)
           Buckets: 1024  Batches: 32  Memory Usage: 1024kB
    -> Seq Scan on users u
           (cost=0.00..800.00 rows=10000 width=32)
           (actual time=0.010..20.000 rows=10000 loops=1)
Planning Time: 0.200 ms
Execution Time: 2505.000 ms

実行計画から読み取れること

Batchesが32で高頻度でディスクI/Oが発生していることが読み取れる。
Hashノードのactual timeが遅い。
つまり、メモリに収まらない → データを分割 → 何度も読み書きという処理が行われて遅くなっていることがわかる。

Hash Joinでこのようなボトルネックを見つけたら、以下の改善を検討しましょう。

ハッシュテーブルを作成している側のテーブルのデータ量を減らす。
データ量が多すぎる可能性があります。
→ 事前にWHEREで絞り込んだり、結合順序の見直しを行うなどの検討を行いましょう。
統計情報の更新
ハッシュテーブルに大きい側のテーブルが選ばれるなど、Join順序が不適切になっている場合は統計情報が古い可能性があります。
work_memを増やす（あくまでも最終手段）

Merge Join

Merge Joinは、結合キーの順に並べた2つのテーブルを先頭から順番に比較しながら結合する方法です。
並べ替え済みのテーブル同士を、一行ずつ見比べて条件が一致する行だけを結合します。
イメージとしては「2列に並べた名簿を先頭から順に照合する」ような感じです。

すでに結合キーでソート済の場合は、余計なソート処理が不要になるため、大量データでも高速に処理できます。

以下のような場合にMerge Joinが有効です。

すでに結合対象のテーブルが結合キーでソートされている。
ソート済みの場合はソートの処理コストを省略できるため有利
結合キーに適切なインデックスが存在する。
大量のレコードを結合する（特に等価結合）

-- 性能の良いMerge Joinの例
Merge Join  (cost=1000.00..25000.00 rows=100000 width=64)
            (actual time=15.000..200.000 rows=100000 loops=1)
  Merge Cond: (o.user_id = u.id)
  -> Index Scan using orders_user_id_idx on orders o
            (actual time=0.010..50.000 rows=100000 loops=1)
  -> Index Scan using users_id_idx on users u
            (actual time=0.005..10.000 rows=10000 loops=1)
Planning Time: 0.200 ms
Execution Time: 205.000 ms

Merge Cond：結合キー条件

実行計画から読み取れること

両テーブルでインデックスが使われているのでSortノードなし。
大量データでも、ソート済のテーブルを結合しているので高速。

ボトルネックになるケース

データがソートされていない場合は、Sort処理のコストがかかります。
Sort処理によってwork_memを超過すると、Hash Joinと同様にディスクI/Oが発生し、性能低下につながる可能性があります。

-- ボトルネックになったMerge Joinの例
Merge Join  (cost=1000.00..35000.00 rows=100000 width=64)
            (actual time=50.000..2500.000 rows=100000 loops=1)
  Merge Cond: (o.user_id = u.id)
  -> Sort  (cost=500.00..12000.00 rows=100000 width=32)
            (actual time=20.000..600.000 rows=100000 loops=1)
    Sort Key: o.user_id
    Sort Method: external merge  Disk: 10240kB
    -> Seq Scan on orders o
            (actual time=0.010..50.000 rows=100000 loops=1)
  -> Sort  (cost=400.00..8000.00 rows=10000 width=32)
            (actual time=5.000..50.000 rows=10000 loops=1)
    Sort Key: u.id
    Sort Method: quicksort  Memory: 2048kB
    -> Seq Scan on users u
            (actual time=0.005..10.000 rows=10000 loops=1)
Planning Time: 0.200 ms
Execution Time: 2550.000 ms

実行計画から読み取れること

Sort Method: external merge Disk → ディスクI/Oが発生している。

Merge Joinでボトルネックを発見した場合は以下の改善を検討しましょう。

データを事前にソートする。
インデックスを検討する。
ソート対象となるテーブルの行数を減らす
データ量が多すぎる可能性があります。
→ 事前にWHEREで絞り込んだり、結合順序の見直しを行うことで行数を減らすことを検討しましょう。
work_memを増やす（あくまでも最終手段）

Nested Loop

Nested Loopは、外側のテーブル（駆動表）の行を1行ずつ取り出し、その行に一致するデータを内側のテーブル（内部表）から検索する結合方法です。
内部表の結合キーにインデックスがある場合は、1行ごとにインデックスを使って高速に検索できるため、効率の良い結合方法になります。
よく「Nested Loopは重い」と思われがちですが、必ずしもそうとは限りません。

以下のような場合にNested Loopが有効です。

内部表にインデックスがある。
駆動表の行数が少ない。
結合条件が等価結合（=）で、インデックススキャンが有効。

-- 性能の良いNested Loopの例
Nested Loop  (cost=0.00..15000.00 rows=10000 width=64)
             (actual time=0.030..120.000 rows=10000 loops=1)
  -> Seq Scan on users u
             (cost=0.00..200.00 rows=10000 width=32)
             (actual time=0.010..10.000 rows=10000 loops=1)
  -> Index Scan using idx_orders_user_id on orders o
             (cost=0.00..1.20 rows=1 width=32)
             (actual time=0.002..0.005 rows=1 loops=10000)
Planning Time: 0.200 ms
Execution Time: 125.000 ms

実行計画から読み取れること

上側が「駆動表」、下側が「内部表」。
loopsは駆動表の行数分、内部表の検索処理が繰り返されることを示す。
内部表にインデックスがあり、効率よく検索できている。

ボトルネックになるケース

Nested Loopが遅くなるのは主に以下の場合です。

駆動表の行数が多い
内部表にインデックスがない（Seq Scanが使われている）

どうして駆動表の行数が多いと性能が悪くなるの？

駆動表：小 × 内部表：大
内部表を少ない回数（駆動表の行数分）スキャンするので効率が良い。
駆動表：大 × 内部表：小
内部表を大量に（駆動表の行数分）スキャンすることになり、処理が重くなる。

単純な検索回数（駆動表×内部表）だけを見ると同じように見えますが、駆動表が大きい場合はスキャンにかかるCPUやメモリの負荷が増え、実際の処理は重くなりやすいです。
また、内部表にインデックスがある場合は高速検索できるため問題になりにくですが、インデックスなしでSeq Scan、さらに駆動表が大きいと検索回数が増えて重くなります。

-- ボトルネックになったNested Loopの例
Nested Loop  (cost=0.00..250000.00 rows=10000 width=64)
             (actual time=0.050..8200.000 rows=10000 loops=1)
  -> Seq Scan on users u
             (cost=0.00..200.00 rows=10000 width=32)
             (actual time=0.010..15.000 rows=10000 loops=1)
  -> Seq Scan on orders o
             (cost=0.00..20.00 rows=100 width=32)
             (actual time=0.005..0.700 rows=1 loops=10000)
Planning Time: 0.200 ms
Execution Time: 8205.000 ms

実行計画から読み取れること

内部表への検索は10,000回繰り返される。
しかも内部表がSeq Scanなため処理も遅い。
結果、10,000回のSeq Scanで処理時間が長くなる。

Nested Loopでボトルネックを見つけたら、以下の改善を検討しましょう。

駆動表の行数を減らす
結合する前にWHERE句やサブクエリで絞り込んでおくことを検討しましょう。
内部表の結合キーにインデックスを作成する

Sortの読み方

Sortとは、並び替え（ソート）を行うノードです。

Sort  (cost=500.00..505.00 rows=1000 width=64)
      (actual time=5.000..6.500 rows=1000 loops=1)
  Sort Key: created_at
  Sort Method: quicksort  Memory: 120kB
  -> Index Scan using idx_events_created_at on events
      (cost=0.29..300.00 rows=1000 width=64)
      (actual time=0.050..2.000 rows=1000 loops=1)
Planning Time: 0.150 ms
Execution Time: 7.000 ms

Sort Key：
ソートに使用する条件
Sort Method：
ソートのアルゴリズムと、メモリ/ディスク使用状況

この実行計画は、「インデックスで絞ったデータをメモリ内でソートしている」と読み取ることができます。

Sort Methodの種類

quicksort
- メモリ内ソート
- データ量がwork_memに収まる場合に使用される
external merge
- ディスクを使うソート
- データ量がwork_memを超える場合に使用される
top-N heapsort
- LIMIT付きで最小/最大N件だけをソートする場合に使用される
- 原則はメモリ内ソートだが、足りなくなった場合はディスクが使用されることもある

ボトルネックになるケース

インデックスがない場合
全件スキャンしてソートする必要があるため処理が重くなります。
→ インデックスを作ることでソート自体を避けられる場合があります。
ソート対象データが巨大でメモリ不足の場合
external merge Diskになる場合、ディスクI/Oが発生し低速になります。
SELECT * 〜などで、不要な列も取得している場合も、I/O負荷が増えます。
→ SELECT col1,col2 など、必要なカラムだけ指定することでデータ量削減になります。

Aggregateの読み方

Aggregateとは集約処理（SUM, COUNT, AVG, MAX, MIN, GROUP BYなど）を実行するノードです。

--SQL
SELECT COUNT(*) FROM orders;
--実行計画
Aggregate  (cost=0.00..1.01 rows=1 width=8)
  -> Seq Scan on orders

この実行計画は、「Seq Scanで全件読み込み、Aggregateで全行をまとめてカウントしている」と読み取ることができます。

Aggregateの種類

Aggregate
- 単純な集約（SUM, COUNTなど）、GROUP BYなし、データ量が小さい場合に使用される
Aggregate (grouping=Sorted)
- GROUP BY + 集計関数の場合に使用される
HashAggregate
- データ量が大きい場合にハッシュテーブルを作って集約処理を行う
- work_memを超えるとディスクが使用される場合がある

ボトルネックになるケース

Sorted AggregateでSortが重い
GROUP BYカラムにインデックスがない場合など、Sort処理がボトルネックになります。
→ インデックス作成や列の絞り込みを検討しましょう。
HashAggregateでメモリ負荷が高い
GROUP BYキーが多いと、キーの組み合わせによるカーディナリティが高くなります。
その結果、ハッシュテーブルが巨大になり、CPUとメモリ負荷が増加します。
→ 集計処理を分割したり、データ件数を減らす改善を検討する
複雑な集計関数
ARRAY_AGG, STRING_AGGの集約関数はメモリ消費が大きく、メモリ負荷が増加することがあります。
→ 必要なカラムだけに絞り込んだり、データを分割して集計できないか検討しましょう。
不要な列も取得している
→ 必要なカラムだけ指定してデータ量を減らすことを検討しましょう。

上記を試したうえで、最終手段として、work_memを増やすことを検討してください。

Gatherの読み方

Gatherとはパラレルクエリを取りまとめるノードのことです。

Gather  (cost=1000.00..20000.00 rows=100000 width=64)
        (actual time=5.000..80.000 rows=100000 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on users u
        (cost=0.00..18000.00 rows=41667 width=64)
        (actual time=0.020..60.000 rows=33333 loops=3)
Planning Time: 0.300 ms
Execution Time: 85.000 ms

Workers Planned:
計画上の並列ワーカー数
Workers Launched:
実際動いた並列ワーカー数

この実行計画のParallel Seq Scanノードは、「Seq Scanをパラレルクエリで並列実行する」という意味です。

2つのワーカーでの実行が計画され、実際2つのワーカーが動いたと読み取ることができます。
ワーカーが2つなのに、Parallel Seq Scanのloop=3となっているのは、1つのリーダープロセス + 2つの並列ワーカープロセスが実際に処理に参加していることを意味します。
つまり、合計で3つのプロセスが並列に処理を行っていると読み取ることができます。

Gather

このParallel Seq Scanの上の階層にあるGatherノードは、並列実行された各ワーカーの結果を1つにまとめるためのノードです。
Gatherは、ワーカーの結果を単純に集約するだけで、結果の順序は保証しません。

Gather Merge

Gatherのほかに、Gather Mergeというものがあります。
これは各ワーカーの結果がソート済という前提で、順序を維持したままマージ（結合）するノードです。

以下の場合などに選ばれる可能性があります。

ORDER BYがある場合
Index Scanなどで各ワーカーが既にソート済みの場合

ボトルネックになるケース

パラレルクエリは常に高速になるわけではなく、並列化のオーバーヘッドや集約処理によって、逆に遅くなってしまうこともあります。

Gatherがボトルネックになる
データ量が多い場合に、最後に取りまとめる処理がボトルネックになることがあります。
→ WHERE句やサブクエリで返却する行数を減らすなどの改善を検討してください。
ワーカーが実際には動かない
EXPLAINでは並列実行が計画されていても、実行時にワーカーが起動されず、処理が遅くなる場合があります。
```
Workers Planned: 2
Workers Launched: 0
```
PostgreSQLの関数には並列実行に関する属性があり、PARALLEL SAFE（並列OK）でない関数を使用している場合や、並列実行が無効化されることがあります。
→ 元のSQLで使用している関数や、関数を使用している列を確認してみてください。

Limitの読み方

Limitとは返す行数を制限するノードです。

Limit  (rows=10)
  -> Index Scan using idx_events_created_at on events

この実行計画は、「インデックスを使って最終的に10行だけ返した」と読み取れます。
Limitノードは「返す行数を制御するだけ」のノードなので、基本的に性能ボトルネックになることは少ないです。

Top-N Heapsortとの違い

LIMIT：
「LIMIT 10」などで最終的に返す行数を制御する
Top-N Heapsort：
ORDER BY + LIMITの場合に使用される最適化ソートで、必要なN件だけを効率的に取得する

まとめ：実行計画を見るときのチェックリスト

長々と解説しましたが、実務上の要点をまとめます。
複雑なSQLでは実行計画も長くなるので、闇雲に下層ノードから読み解くよりも、まずは下記のポイントを重点的に確認し、一番ボトルネックになっているノードを突き止めることを優先すると効率的です。

ボトルネックのチェックリスト：

見積もりと実際の乖離が無いか？
- rowsとactual rowsの差
loopsの数が多い箇所はないか？
- loops × rows = 実処理量が多い
- Nested Loopの多重ループ
Seq Scanがボトルネックになっていないか？
大量データ処理（Sort・Hashなど）でディスクI/Oがボトルネックになっていないか？

原因の例：

インデックスが効いていないためSeq Scanがボトルネックになっている
結合方法によって処理量が増え、ボトルネックになっている
統計情報が古く、実行計画が最適化されていない

まずは上記のポイントを確認 → ボトルネックを特定 → 改善作業という流れが効率的です。

さいごに

本記事では、PostgreSQLの実行計画の読み方から、ボトルネックの見つけ方までを解説しました。

私自身が実際の業務でSQLの性能改善を行う中で学んだことをまとめた内容ですが、抜け漏れがあれば随時追記していく予定です。

この記事が、性能改善を始めた方の一助になれば幸いです。

参考

公式ドキュメント