そーくのつれづれぶろぐ

web系エンジニアの勉強したことなど

【RDBS】やっぱり実行計画を読めるようになりたい！実行計画の読み方実践編(初級)

はじめに

下の記事の実践編、ということで簡単なSQLの実行計画を読むことで「実行計画の読み方を理解する→SQLがどんな処理をしているのかがざっくりわかる」状態になることを目指します。読み方のポイントをまず知りたい、という方は以下のブログを参照してみてください。

soachr.hatenablog.com

対象読者

  • 一度実行計画を学んだけど挫折してそのままにしている人
  • 実行計画のcost/actual time以外の情報が読み取れない...いう人

(復習)実行計画の読み方ポイント3つ

  1. 実行計画のネストに注目する
  2. ノードがSQL文のどの句にあたるかを覚える&処理の内容を理解する
  3. 実行計画からSQLとリンクするキーワードを探す

です。詳しい説明は前記事を参照くださいませ。

今回はこのポイントを踏まえながら実際に基本的な4種類の処理ごとのSQLの実行計画を読んでいきます。

  1. 結合処理
  2. 探索処理
  3. 演算処理
  4. ソート処理

実行計画をみてみる

使用するデータベースは以下の記事で使用した以下のサンプルデータベースを用います。*1

tech-blog.rakus.co.jp

PostgreSQL Sample Database

チェーン展開しているDVDレンタルシステムのデータベース、という設定で様々なテーブルが存在します。

  • 映画の情報
  • 俳優の情報
  • レンタル屋さん(支店)の情報
  • 顧客の情報
  • レンタルの履歴情報など

ケース1: 結合処理 「顧客名と住所の一覧を取得する」

SQL

select c.first_name, c.last_name, a.address
 from customer c left outer join address a on (a.address_id = c.address_id);

実行計画

                               QUERY PLAN                                
-------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..4545.49 rows=361197 width=33)
   ->  Seq Scan on customer c  (cost=0.00..14.99 rows=599 width=13)
   ->  Materialize  (cost=0.00..17.05 rows=603 width=20)
         ->  Seq Scan on address a  (cost=0.00..14.03 rows=603 width=20)
               Filter: (address_id IS NOT NULL)
(5 rows)

読んでいく

まず、ポイント1"実行計画のネストに注目する"をみていきます。 一番ネストの外側にあるノードはNested Loop Left Joinのみなのでここから「今回のSQLは結合処理のみをするんだ」というのがわかります。(簡単なSQLなのでそもそもわかりきったことですが...) Nested Loop Left Joinというノードを実行するためにはSeq Scan, Materializeの2つのノードを実行しないとNested Loop Left Joinが実行できないので登場しているというわけです。更にMaterialize(知らないノードがでてきましたね)を実行するにはSeq Scanを実行する必要がある。というようにノードを実行するには別のノードを実行する必要がある、といった依存関係をネストが表しています。逆にいえば同じ階層にいるノードは依存していない独立した関係ということですね。そのため、「顧客名と住所の一覧を取得する」には以下の手順で実行されているということがわかります。

  1. addressテーブルに対してSeq Scanを実行する
  2. 1の結果からMatterializeを実行する
  3. customerテーブルを結合するためにSeq Scanを実行する
  4. 2,3の結果を用いてNested Loop Left Joinを実行する

次にポイント2"ノードがSQL文のどの句にあたるかを覚える&処理の内容を理解する"をみていきます。 なぜNested Loop Left Joinの実行にSeq Scan/Materializeというのがでてきたのかということはそのノードがどんな処理を実際しているかを知らないとわからないです。 Nested Loop Join は内部テーブルの一行にある結合キーの値が外部テーブルにあるか探索する(2重for文みたいな感じの)ものなのでScanが2回登場する、というようにノードの処理の性質を理解すると長い実行計画でもおおよその処理が理解できるようになります。

最後にポイント3"実行計画からSQLとリンクするキーワードを探す"をみていきます。実行計画には処理対象のテーブルや結合条件のカラム名がでてきます。今回の場合はcustomer c,address a, address_idがでてきているのでそのノードが処理する対象テーブル・カラムがわかります。

ケース2: 探索処理「姓が'White'である顧客を検索する」

SQL

select * from customer where last_name = 'White';

実行計画

                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_last_name on customer  (cost=0.28..8.29 rows=1 width=70) (actual time=0.046..0.047 rows=1 loops=1)
   Index Cond: ((last_name)::text = 'White'::text)
 Planning Time: 0.675 ms
 Execution Time: 0.099 ms
(4 rows)

読んでいく

単一テーブルのみ・Where句のみの場合は結構シンプルな実行計画になります。ポイント1. 「実行計画のネストに注目する」もノードはIndex Scanの1つしかありません。Scan(探索)する、の1つだけです。インデントを下げて記載されているIndex CondというのはノードではなくScanに対する目的語、探索対象のキーワードなどが記載されます。Scanは走査していき目的のものを探し出すノードのため、シンプルではありますが、結合処理の内部処理で出てきたりするためポイント1でネストのどこにいるのかを把握してWhere句か結合処理の内部処理かアタリをつけてから、ポイント3の"実行計画からSQLとリンクするキーワードを探す"ようにするとSQLとの対応関係が見えてきます。

ケース3: 演算処理「支店IDごとの顧客数を取得する」

SQL

select store_id, count(customer_id) from customer group by store_id;

実行計画

                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=17.98..18.00 rows=2 width=10) (actual time=0.510..0.512 rows=2 loops=1)
   Group Key: store_id
   ->  Seq Scan on customer  (cost=0.00..14.99 rows=599 width=6) (actual time=0.026..0.230 rows=599 loops=1)
 Planning Time: 10.837 ms
 Execution Time: 0.771 ms
(5 rows)

読んでいく

store_idが一緒のレコード情報に対して集約(今回はcount)をする処理です。実行計画に登場するノードはHash Aggregate,その下のネストにあるSeq Scanの2つしかないシンプルな実行計画になっています。

支店IDごとのレコードのカウントを行う処理ですが、カウントを行うためには支店IDが一緒のものを探索(=Scan)する必要があるのでSeq Scanノードが出現しています。またこの支店IDが一緒のものというのは実行計画ではGroup Key: store_idで表されています。

ケース4: ソート処理「映画のカテゴリをアルファベット順で取得する」

SQL

select name from category order by name

実行計画

                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Sort  (cost=1.48..1.52 rows=16 width=68) (actual time=0.072..0.074 rows=16 loops=1)
   Sort Key: name
   Sort Method: quicksort  Memory: 25kB
   ->  Seq Scan on category  (cost=0.00..1.16 rows=16 width=68) (actual time=0.009..0.011 rows=16 loops=1)
 Planning Time: 0.407 ms
 Execution Time: 0.118 ms
(6 rows)

読んでいく

ノードじゃない行が複数行でていますが、今回もSortとその下のネストにあるSeq Scanの2つのノードのみがでてきています。

与えられたソートのキー(order by|Sort Key)で並び替えるためにはレコードをそのキーで探索(=Scan)する必要があるためSeq Scan`が出現しています。(集約と似ていますね)

ソートは複数の方法があり、プランナがテーブルの統計情報(データ量・キーのカーディナリティなど)をもとにソートの方法を選択しています。今回は実行計画からクイックソートを用いていることがわかります。(sort Method: quicksort Memory: 25kB)

最後に

簡単なSQLで読んでみましたがいかがでしたでしょうか。基本的には今回みていった4つSQLの処理の組み合わせたものをSQLで組み立てるので、どの処理のときにどんなノードがでてくるのか・どんなキーワードがでてくるのかを頭に入れておくと複雑なSQLの実行計画に直面したときでもアタリがつきやすくなるのではないかと思います。

次回は中級編(上級編？)の複雑なSQLの実行計画の読み方を書きたいと思います。

2022/11/30:筆者より
この記事を2019年度内に投稿するはずだったんですがなぜ2022年度になったのかはあまり深く考えないでいただきたいです。

*1:名義が違いますがnorth_mkyと私は同一人物です。以後はsoachrで活動しています