MoTLab -GO Inc. Engineering Blog-MoTLab -GO Inc. Engineering Blog-

PostgreSQLの実行計画を読む

バックオフィスSQL
December 06, 2023

バックオフィス基盤第2グループの名嘉眞です。私の関わっているプロダクトで利用しているDBはほぼPostgreSQLを利用しています。今回は業務でよく利用するようなSQLを例にPostgreSQLの実行計画について記事を書こうと思います。書いてあることの技術的な点は普段PostgreSQLを利用しているとおそらく知っている方も多い内容ですが、少しバックオフィス基盤第2グループの業務も含めて書いてみました。

きっかけ

これは私の経験がMySQL(ver 5.7)ばかりで業務でPostgreSQLを使用したことがなく、PostgreSQLの実行計画を見たとき「あ、フォーマット違う」となりました。 初見でも実行計画の内容と読み方をざっと調べればなんとなくわかるのですが、チーム内での対面レビューをお願いして問題解決したのがきっかけです。 若干、備忘録も兼ねていますがチーム内で実行計画を眺めるという機会がもあったので私自信が調べたことも含めてblogにしたいと考えました。 ちなみにMySQLの8.0以降はPostgreSQLと完全に同じというわけではないですが、実行計画が木構造形式で出力できます。(format=tree オプション)

支払い請求基盤の特徴

バックオフィス基盤第2グループでは主に支払い請求基盤を開発しています。 支払い請求基盤は名前の通り、タクシー事業者様や、経理などに向けた支払いと請求の金額や取引の件数を集計し、その結果を管理画面向けのAPIとして提供したり、 会計システムに連携したりしています。 「GO Pay」や、電子マネー、QRコードで決済したデータを溜め込んで、その用途に合わせて集計します。 今回は主に毎月の締め日に合わせてタクシー事業者様ごとに金額を集計するようなSQLを題材にします。

集計SQLの例

以下のような集計SQLをバッチ処理で実行してタクシー事業者などにお支払い(または請求)する金額を算出したりします。 ※あくまで実際のSQLに近い例です。実際に利用されているSQLではないです。

EXPLAIN ANALYZE
SELECT
  store_id AS store_id,
  COUNT(payments.id) AS count,
  SUM(amount) AS amount
FROM
  payments AS payments
  JOIN payment_stores AS stores
    ON payments.store_id = stores.store_id AND store_type = '0'
WHERE
  payment_brand_type_id = $1
  AND settlement_at >= $2
  AND settlement_at < $3
  AND CASE
    WHEN $1 IN (1, 2, 4) THEN payment_status_id IN (1, 2)
    ELSE payment_status_id = 1
  END
  AND payment_refund_status_id = 1
GROUP BY
  store_id
ORDER by
  store_id;

上記のSQLの実行計画が以下です。

QUERY PLAN
Sort  (cost=6303.44..6305.01 rows=630 width=24) (actual time=276.802..276.839 rows=593 loops=1)
  Sort Key: stores.store_id
  Sort Method: quicksort  Memory: 71kB
  ->  HashAggregate  (cost=6267.84..6274.14 rows=630 width=24) (actual time=276.527..276.637 rows=593 loops=1)
        Group Key: stores.store_id
        Batches: 1  Memory Usage: 121kB
        ->  Hash Join  (cost=34.76..5978.34 rows=38601 width=20) (actual time=0.386..248.455 rows=119314 loops=1)
              Hash Cond: ((payments.store_id)::text = (stores.store_id)::text)
              ->  Index Scan using payments_settlement_at_idx on payments  (cost=0.43..5808.20 rows=51488 width=33) (actual time=0.066..204.891 rows=157574 loops=1)
                    Index Cond: ((settlement_at >= '2023-06-01 00:00:00+09'::timestamp with time zone) AND (settlement_at < '2023-06-16 00:00:00+09'::timestamp with time zone))
                    Filter: ((payment_brand_type_id = 6) AND (payment_status_id = 1) AND (payment_refund_status_id = 1))
                    Rows Removed by Filter: 73503
              ->  Hash  (cost=26.20..26.20 rows=650 width=28) (actual time=0.313..0.314 rows=650 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 47kB
                    ->  Seq Scan on payment_stores_offices stores  (cost=0.00..26.20 rows=650 width=28) (actual time=0.011..0.196 rows=650 loops=1)
                          Filter: (store_type = '0'::bit varying)
                          Rows Removed by Filter: 6
Planning Time: 2.328 ms
Execution Time: 276.997 ms

実行計画の内容からどれくらい時間がかかったかを確認する

最後の行の Execution Time がSQLの稼働時間になります。

実行計画は木構造となっています。今回の場合、 Sort が一番上位のノードで、その直下に集計を実施した HashAggregate 、さらにその下に Hash Join 、さらに下位のノードに Index Scan と Hash があり Hash の下位に Seq Scan の記載があります。 ノードごとに記載されている actual time のそれぞれの値は上位ノードの場合下位のノードも含めた時間になります。そのため各ノードごとでかかるコストや時間を確認したい場合は、含まれる下位ノードの値を除いて算出する必要があります。 actual time 自体の表記については以下のようになります。

最初の1行目を返却した時間..最後の行を返却した時間(単位はミリ秒)

ちなみに、今回は EXPLAIN ANALYZE を実行していますが、 ANALYZE はオプションで、実際にクエリを実行して実行計画を出力します。そのため本番DBで実行するとその分の負荷がかかります。

各表記について

ノードの種類(例)

全ての種類ではないですが以下のようなノードの種類があります。

  • Seq Scan
    • シーケンシャルスキャン。 テーブルを最初から最後までシーケンシャルにアクセス。インデックスが利用できても対象となるレコードが多い場合は利用される。
  • Index Scan
    • インデックススキャン。 インデックスとテーブル(ヒープ)を交互にランダムアクセス。WHERE句による絞り込みで取り出す件数が限定される場合や目的のデータにピンポイントでアクセスしたい場合に有効。
  • Index Only Scan
    • インデックスオンリースキャン。 問い合わせに対してインデックスのみで回答できる場合に有効。列xとyにインデックスがある場合で回答もxとyのみのようなクエリで有効。
    • (例: SELECT x, y FROM tab WHERE x = 'key';)
  • Bitmap Index Scan
    • ビットマップインデックススキャン。検索に用いられる列に対してその値とレコードのビットマップを使ってレコードを検索する。PostgreSQLの場合インデックス作成時などに明示的に指定するのではなくSQL実行時にメモリ内にビットマップを作成して検索速度を上げるようにプランナが判断した場合に有効。カーディナリティが低い列を検索条件にした場合に選択されることがある。
  • Nested Loop
    • 結合方法。片方のテーブルAに対してもう片方のテーブルBをループしながら結合する、名前のとおりループをネストして結合する方法。
    • ループでいう外側のテーブルの行数が少なく、内側のテーブルにインデックスがあると処理が高速になる。
  • Merge Join
    • 結合方法。2つのテーブルを結合キーでソート後、順番に突き合わせて結合する方法。結合キーが主キー、または結合キーにインデックスを定義することにより、ソート済みの状態を突き合わせる場合高速になり有効になる。
    • 大きなテーブル同士の結合の場合有効になる。
  • Hash Join
    • 結合方法。内側テーブルの結合キーでハッシュを作成し、ハッシュと外側テーブルの行を突き合わせて結合する方法。ハッシュはメモリーに作成し結合は高速にできるが、ハッシュがメモリーサイズより大きくなってしまう場合は、ファイルアクセスが発生するため遅くなる。
    • 比較的小さなテーブルと大きなテーブルを結合する場合に有効になる。
  • Sort
    • ORDER BY 句を利用した明示的ソートや、Merge Joinなどで行われる暗黙的なソートが実行される場合などに記載される。ソートに利用されたkeyやソート処理方法、使用したメモリ量も記載される。ソート処理方法によっては効率が悪くなっている場合もある。開始コストがあり最初の値はすぐに返却されない。
  • Hash
    • Hash Joinで利用されるハッシュテーブルを作成する際に記載される。ハッシュテーブルを作成するのもコストがかかる。
  • Aggregate
    • 単純な集約関数(GROUP BY句を利用しないCOUNT)で記載される。
  • GroupAggregate
    • GROUP BY句を利用してCOUNT, SUMなど集計関数で有効になる。事前にデータをソートするコストが発生する。
  • HashAggregate
    • 集計の際メモリにハッシュテーブルを作成して集計を行う。GroupAggregateより高速。ハッシュテーブルがメモリに収まらない場合GroupAggregateになる。
  • Append
    • UNION ALLで記載される

推定コスト

ノード名の次の括弧が推定コストの表記になります。

  • cost
    • 初期処理の推定コスト...全体推定コスト
  • rows
    • ノードが出力する行数（推定値）
  • width
    • ノードが出力する行の平均サイズ（推定値）（バイト単位）

実際の実行結果

推定コストの次の括弧が実際の実行結果に関する表記になります。

  • actual time
    • 最初の1行目を返却した時間..最後の行を返却した時間(単位はミリ秒)
  • rows
    • 実際に処理した行数
  • loops
    • ノードを実行した回数

参考資料

※ 上記の各表記に関しては以下の資料を参考・引用させて頂きました。ありがとうございます。

実行計画の内容を理解する

では先ほどの実行計画をもう一度見てその内容を読み解いてみます。

EXPLAIN ANALYZE
SELECT
  store_id AS store_id,
  COUNT(payments.id) AS count,
  SUM(amount) AS amount
FROM
  payments AS payments
  JOIN payment_stores AS stores
    ON payments.store_id = stores.store_id AND store_type = '0'
WHERE
  payment_brand_type_id = $1
  AND settlement_at >= $2
  AND settlement_at < $3
  AND CASE
    WHEN $1 IN (1, 2, 4) THEN payment_status_id IN (1, 2)
    ELSE payment_status_id = 1
  END
  AND payment_refund_status_id = 1
GROUP BY
  store_id
ORDER by
  store_id;
---------------------------------------------------------------------------------------------------------------
QUERY PLAN
① Sort  (cost=6303.44..6305.01 rows=630 width=24) (actual time=276.802..276.839 rows=593 loops=1)
  Sort Key: stores.store_id
  Sort Method: quicksort  Memory: 71kB
  ->  ② HashAggregate  (cost=6267.84..6274.14 rows=630 width=24) (actual time=276.527..276.637 rows=593 loops=1)
        Group Key: stores.store_id
        Batches: 1  Memory Usage: 121kB
        -> ③ Hash Join  (cost=34.76..5978.34 rows=38601 width=20) (actual time=0.386..248.455 rows=119314 loops=1)
              Hash Cond: ((payments.store_id)::text = (stores.store_id)::text)
              -> ④ Index Scan using payments_settlement_at_idx on payments  (cost=0.43..5808.20 rows=51488 width=33) (actual time=0.066..204.891 rows=157574 loops=1)
                    Index Cond: ((settlement_at >= '2023-06-01 00:00:00+09'::timestamp with time zone) AND (settlement_at < '2023-06-16 00:00:00+09'::timestamp with time zone))
                    Filter: ((payment_brand_type_id = 6) AND (payment_status_id = 1) AND (payment_refund_status_id = 1))
                    Rows Removed by Filter: 73503
              -> ⑤ Hash  (cost=26.20..26.20 rows=650 width=28) (actual time=0.313..0.314 rows=650 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 47kB
                    -> ⑥ Seq Scan on payment_stores_offices stores  (cost=0.00..26.20 rows=650 width=28) (actual time=0.011..0.196 rows=650 loops=1)
                          Filter: (store_type = '0'::bit varying)
                          Rows Removed by Filter: 6
Planning Time: 2.328 ms
Execution Time: 276.997 ms

実行計画の各ノードに上から番号を記載しました。 まず実行の流れについて読み解いてみると以下のようになると考えられます。

  1. ④ Index Scan で payments テーブルを payments_settlement_at_idx インデックスを利用して絞り込んでいます。インデックスとなっている settlement_at カラムで絞り込んだあとその他のカラムで更に絞り込んでいます。
  2. ⑥ Seq Scan で、payment_stores_offices テーブルを全件スキャンし store_type = 0 のレコードを絞り込んでいます。Seq Scanが有効になった理由はおそらく payment_stores_offices 全体のうち store_type = 0 のレコード数が多くテーブル全件を読んだ方が効率が良いと判断されたことによります。(Rows Removed by Filter: 6 の記載からも推測できる)
  3. ⑤ Hash で、⑥ Seq Scan で取得したpayment_stores_offices テーブルのレコードをもとにハッシュテーブルを作成しています。⑥ Seq Scan のrowsの件数と同じrowsの件数になっており、使用されたメモリに関しても記載されています。
  4. ③ Hash Join で、④ Index Scan で取得した payments テーブルのデータと⑤ ⑥ で作成されたハッシュテーブルを利用してJOINしています。
  5. ② HashAggregate で、③ Hash Join で結合されたテーブルを利用して集計しています。
  6. ① Sort で、② HashAggregate で集計されたテーブルを store_id でソートしています。

実行の流れはわかったので、その詳細を読み解いてみます。

まず① Sort や② HashAggregate はSQLも内容もシンプルで、actual timeでもここで時間がかかっているわけではないです。 これは① Sort と② HashAggregate のactual timeで最初と最後の差がほとんどないことや、② HashAggregate の最後の行を返したactual timeの値とと① Sort の最初の行を返したactual timeの差が少ないことで判断できます。 このSQLで一番時間がかかっているのは、③ Hash Join になります(actual time=0.386..248.455)。さらにその中でも④ Index Scan ノードで時間がかかっていることがわかります。(204ms) paymentsテーブル検索では payments_settlement_at_idx インデックスは利用できていますが、その後の絞り込みで時間を要している可能性があります。対象のレコード数の多さも要因になっているかも知れません。 更に効率良くしたい場合は別のインデックスの追加などを検討することになりますが、例えば payment_brand_type_id = 6 のレコードが payments テーブル全体の大部分を占めている場合などはインデックスの追加で解決しない可能性もあります。

最後に

ネット上には多くの同じような内容のblogもありますが、実際の業務で使うようなクエリを題材にして書きました。 そういえば、チームのテックリードも勉強会で「DBの気持ちになる」って言ってたことを思い出しました。 今回のblogで書いたような実行計画を理解することは、もしかすると少しDBの気持ちに近づくことかも知れません。 既にご存知の方も多いような内容ですが誰かの参考になったり、チームの業務や雰囲気が伝わると幸いです。

We're Hiring!

📢
GO株式会社ではともに働くエンジニアを募集しています。

興味のある方は 採用ページ も見ていただけると嬉しいです。

Twitter @goinc_techtalk のフォローもよろしくお願いします！