RAKUS Developers Blog | ラクス エンジニアブログ

株式会社ラクスのITエンジニアによる技術ブログです。

トップ > PostgreSQL > 【PostgreSQL】初心者でも読める実行計画の基礎知識

【PostgreSQL】初心者でも読める実行計画の基礎知識

PostgreSQL

はじめに

こんにちは。新卒3年目のchoreii です。
今回はPostgreSQLの実行計画について記事を書こうと思います。
私が初めて実行計画について知った時は難しそうなイメージが先行しており、実際に調べてみても情報量が多くハードルが高かったです。ですが調べていくうちに自分が難しく感じていた理由がわかりました。
それは、多くの記事が「実行計画の基礎知識」と「実行計画をどのようにパフォーマンス改善に活かすか」という2種類の情報を織り交ぜて記載されていたからです。
今回はできるだけ情報量を削減し、「実行計画の基礎知識」にフォーカスした記事を作成しました。これから実行計画を学ぶ人の最初の一歩となれば幸いです。

実際のパフォーマンスのチューニング方法や、検証するための大量データの登録に興味がある方は下記のブログもご覧ください。

tech-blog.rakus.co.jp

tech-blog.rakus.co.jp

目次

1. 実行計画とは

実行計画とはユーザが問い合わせたクエリ（SQL文）を実行する手順書になります。「どの順番にテーブルを結合するか」「ソート方法」「検索方法』などの作業内容や、想定される実行コストが記述されています。

2. 実行計画の出し方

実行計画の出し方ですが、実行計画を確認したいSQL文の先頭にEXPLAINと付け足すだけです。
例として、itemテーブルから全件取得するSQLはSELECT * FROM item;となりますが、このSQLの実行計画を知りたい場合はEXPLAIN SELECT * FROM item;とするだけで実行計画が取得できます。
※ オプションで出力内容や方法を設定できますがここでは割愛します。

EXPLAIN SELECT * FROM item;

                       QUERY PLAN
---------------------------------------------------------
Seq Scan on item (cost=0.00..12.20 rows=220 width=328)

3. 実行計画の読み方

前項の通り実行計画を表示することはできました。次はもう少し複雑なSQLの実行計画を使って実行計画の読む順番や、それぞれの行が何を意味しているかを解説していきます。以下が解説に使用するSQL文とその実行計画です。

EXPLAIN SELECT * FROM item INNER JOIN customer ON item.customer_id = customer.id WHERE customer.name = '佐藤' ORDER BY item.id;

                       QUERY PLAN
---------------------------------------------------------
Sort (cost=27.07..27.08 rows=1 width=574)
  Sort Key: item.id
  -> Hash Join (cost=14.03..27.06 rows=1 width=574)
       Hash Cond: (item.customer_id = customer.id)
       -> Seq Scan on item (cost=0.00..12.20 rows=220 width=332)
       -> Hash (cost=14.00..14.00 rows=2 width=222)
           -> Seq Scan on customer (cost=0.00..14.00 rows=2 width=222)
                Filter: ((name)::text = '佐藤'::text)

3-1. 実行計画を読む順番

実行計画はよく見るとツリー構造になっており、インデント下げや「->」などで表されます。（出力方法によって異なります。）以下に先の実行計画のツリー構造を簡単に図にしたものがあります。

図1: 実行計画のツリー構造

読む順番のルールは2つだけです。

  1. 順番に子要素をたどり一番子孫の要素から実行し、親要素に遡っていく
  2. 兄弟要素がある場合は先に記述されている行から順に子要素をたどる

今回の場合、一番深い子要素は7,8行目になりますが、5行目と6行目が兄弟要素にあたります。そのため最初に5行目を実行してから6行目の子要素をたどっていくことになります。図に読む順番を追記すると以下のようになります。

図2：実行計画のツリー構造（読む順番）

読む順番に迷った際はツリー構造がどうなっているかを一度図にしてみるとわかりやすいと思います。

3-2. スキャン演算子

次に個別の行の解説をします。最初に実行される以下の行の先頭にSeq Scanと書かれています。

       -> Seq Scan on item (cost=0.00..12.20 rows=220 width=332)

これは、単語の意味通りitemテーブルの中から必要な行を順番に検索することを意味する行で、もっとも基本的な検索（スキャン）方法になります。スキャン演算子はSeq Scan以外にもインデックスが利用できる場合に選択されるIndex Scanなどがあります。

3-3. 結合演算子

3,4行目ではテーブルの結合を行っています。ハッシュとついているのは一方のテーブルから作られたハッシュ表を元に結合しているからで、最初のハッシュ表の作成は6行目でおこなわれています。メモリ上にハッシュ表が作成されるため、メモリに余裕がある場合は高速に動きます。結合演算子は他にNested LoopやMerge Joinが存在します。

  -> Hash Join (cost=14.03..27.06 rows=1 width=574)
       Hash Cond: (item.customer_id = customer.id)

3-4. その他の演算子（検索結果の整形など）

1,2行目ではソートを行っています。スキャンや結合よりも直感的に何をしているかが分かりやすいと思います。（ここではSQLのORDER BY句で指定したようにitemテーブルの‪‪id列でソートがかかっています。）

Sort (cost=27.07..27.08 rows=1 width=574)
  Sort Key: item.id

他にもGroupやLimitなど、特定のSQLを記述した際に検索結果を整形するための演算子が存在します。

4. おまけ（コストとANALYZE）

演算子の意味について解説しましたが、演算子の後ろにカッコ書きで数値などが記載されています。

       -> Seq Scan on item (cost=0.00..12.20 rows=220 width=332)

これは、実行計画通りにSQLを実行した場合の推測値で、左から順に以下の数値になっています。

  1. 初期コスト：検索結果の1行目を返すまでにかかる準備のコスト（秒数ではない）
  2. 総コスト：初期コストを含めた処理完了までにかかるコスト（秒数ではない）
  3. 行数：返却される検索結果の行数
  4. 行の長さ：返却される1行あたりの長さ（横幅）

パフォーマンスを確認する際は総コストを確認して、SQLの組み方を変更することによってコストを抑えることができないかを調節することが多いです。

しかし、出てくる結果はあくまで推測値なので実際にSQLを実行してみた結果と食い違うことがあります。その場合はANALYZEオプションを使用し実際にSQLを実行してみた結果、実行計画のどの部分にどれだけの時間やコストがかかったかを確認する必要があります。（以下のような出力になります）

※ 「EXPLAIN」の直後に「ANALYZE」と付け足す

EXPLAIN ANALYZE SELECT * FROM item INNER JOIN customer ON item.customer_id = customer.id WHERE customer.name = '佐藤' ORDER BY item.id;

                       QUERY PLAN
---------------------------------------------------------
Sort (cost=27.07..27.08 rows=1 width=574) (actual time=0.037..0.037 rows=4 loops=1)
  Sort Key: item.id
  Sort Method: quicksort Memory: 25kB
    -> Hash Join (cost=14.03..27.06 rows=1 width=574) (actual time=0.025..0.027 rows=4 loops=1)
         Hash Cond: (item.customer_id = customer.id)
         -> Seq Scan on item (cost=0.00..12.20 rows=220 width=332) (actual time=0.006..0.006 rows=8 loops=1)
         -> Hash (cost=14.00..14.00 rows=2 width=222) (actual time=0.008..0.008 rows=1 loops=1)
                Buckets: 1024 Batches: 1 Memory Usage: 9kB
                -> Seq Scan on customer (cost=0.00..14.00 rows=2 width=222) (actual time=0.005..0.005 rows=1 loops=1)
                       Filter: ((name)::text = '佐藤'::text)
                       Rows Removed by Filter: 1
Planning time: 0.159 ms
Execution time: 0.062 ms

(actual time=0.008..0.008 rows=1 loops=1)が行末に追加されたり、QUERY PLANの最後に実際の実行時間などが出力されるようになっています。この記事では詳細は割愛しますが、このように実際に実行してみた結果を取得することも可能です。

おわりに

今回は実行計画の最低限の知識に絞って解説してみました。できるだけ初心者の人が最初に疑問に思うことをピックアップしたつもりなので、この記事を読んでからさらに詳しい解説記事を見ていただければ学習がはかどると思います。私自身まだ浅い基礎知識しか定着しておらず、業務で実行計画を使いこなせているとは言い難いです。今回、基本的な情報を整理する中での気づきもありましたので、さらに学習を深め身につけていきたいと思います。

参考文献

EXPLAIN

PostgreSQLクエリ実行の基礎知識 ～Explainを読み解こう～

PostgreSQLの実行計画について調べてみた | キャスレーコンサルティング株式会社

◆TECH PLAY
techplay.jp

◆connpass
rakus.connpass.com

ラクスの技術 / デザイン情報サイト
Copyright © RAKUS Co., Ltd. All rights reserved.