こんにちは、技術部のTです。
SQLを実装する際に複数パターンから最適解を選択したり、レスポンスの遅いSQLを改善しなければならないこともあると思います。今回はSQLの実行計画を取得し、どんなことが書いてあるのかを見ていきます。
RDBはPostgreSQLを使用します。
実行計画とは
DBMSがSQL文を実行する手順書です。
「どの順番にテーブルを結合するか」「ソート方法」「検索方法』などの作業内容や、想定される実行コストが記述されています。
この実行コストの削減を意識することがSQLの性能面に対して効果的な方法です。
実行計画を確認するコマンド
EXPLAIN [オプション] SQL文
postgres=# EXPLAIN ANALYZE SELECT * FROM Shops;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on shops (cost=0.00..1.60 rows=60 width=28) (actual time=0.024..0.029 rows=60 loops=1)
Planning Time: 0.093 ms
Execution Time: 0.053 ms
(3 行)
各項目の意味
Seq Scan・・・プラン演算子のひとつ。クエリを実行するための内部的な処理の種類。Seq Scanはテーブルフルスキャンを指します
cost(左)・・・初期コスト。検索結果の1行目を返すまでにかかる準備のコスト(0.00)
cost(右)・・・トータルコスト。初期コストを含めた処理完了までにかかるコスト(1.60)
rows・・・返却される検索結果の行数
width・・・返却される1行あたりの長さ
Planning Time・・・解析されたクエリから実行計画を生成し、最適化するのに要した時間(ANALYZEオプション使用時)
Execution Time・・・実行時間(ANALYZEオプション使用時)
ポイントはプラン演算子とトータルコスト、実行時間です。プラン演算子はテーブルスキャンや結合、ソートなどRDB内部でどのような処理を行うかを表しています。
SQLの組み方を変えることによって無駄なテーブルスキャンを無くしたり、処理を効率化してトータコスト、実行時間削減を目指します。
テーブルスキャンに使用する演算子
- Seq Scan・・・テーブルを最初から最後までアクセスする(テーブルフルスキャン)
- Index Scan・・・インデックスを利用したアクセスを行う
結合に使用する演算子
- Nested Loop・・・外側テーブル1行ごとに内側テーブルを1周ループしながら結合する方法
- Merge Join・・・2つのテーブルを結合キーでソート後、順番に突き合わせて結合する方法
- Hash Join・・・一方のテーブルから作られたハッシュ表を元に結合を行う方法
その他演算子
- Sort演算子など
実行順序
実行計画はツリー構造になっており、インデントや「->」で表示されます。
基本的に階層の深いところから実行されていくため、以下のルールで読んでいきます。
- 順番に子要素をたどり一番子孫の要素から実行し、親要素に遡っていく
- 兄弟要素がある場合は先に記述されている行から順に子要素をたどる
例としてShopsとReservationsを結合したSQLの実行計画を見ていきます。
postgres=# EXPLAIN SELECT shop_name
postgres-# FROM Shops S INNER JOIN Reservations R
postgres-# ON S.shop_id = R.shop_id;
QUERY PLAN
------------------------------------------------------------------------
Hash Join (cost=2.35..17.42 rows=120 width=8) ---①
Hash Cond: (r.shop_id = s.shop_id) ---②
-> Seq Scan on reservations r (cost=0.00..14.00 rows=400 width=24) ---③
-> Hash (cost=1.60..1.60 rows=60 width=14) ---④
-> Seq Scan on shops s (cost=0.00..1.60 rows=60 width=14) ---⑤
(5 行)
SQLの組み方による実行計画の違い
prefecture | sex | pop
------------+-----+-----
徳島 | 1 | 60
徳島 | 2 | 40
香川 | 1 | 90
香川 | 2 | 100
愛媛 | 1 | 100
愛媛 | 2 | 50
高知 | 1 | 100
高知 | 2 | 100
福岡 | 1 | 20
福岡 | 2 | 200
(10 行)
prefecture | pop_men | pop_wom
------------+---------+---------
愛媛 | 100 | 50
香川 | 90 | 100
高知 | 100 | 100
徳島 | 60 | 40
福岡 | 20 | 200
(5 行)
postgres=# EXPLAIN ANALYZE SELECT prefecture, SUM(pop_men) AS pop_men, SUM(pop_wom) AS pop_wom
postgres-# FROM ( SELECT prefecture, pop AS pop_men, null AS pop_wom
postgres(# FROM Population
postgres(# WHERE sex = '1' --男性
postgres(# UNION
postgres(# SELECT prefecture, NULL AS pop_men, pop AS pop_wom
postgres(# FROM Population
postgres(# WHERE sex = '2') TMP --女性
postgres-# GROUP BY prefecture;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=36.83..36.95 rows=6 width=98) (actual time=0.118..0.124 rows=5 loops=1)
Group Key: population.prefecture
-> Sort (cost=36.83..36.85 rows=6 width=90) (actual time=0.112..0.113 rows=10 loops=1)
Sort Key: population.prefecture
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=36.64..36.70 rows=6 width=90) (actual time=0.061..0.064 rows=10 loops=1)
Group Key: population.prefecture, population.pop, (NULL::integer)
Batches: 1 Memory Usage: 24kB
-> Append (cost=0.00..36.59 rows=6 width=90) (actual time=0.033..0.050 rows=10 loops=1)
-> Seq Scan on population (cost=0.00..18.25 rows=3 width=90) (actual time=0.032..0.034 rows=5 loops=1)
Filter: (sex = '1'::bpchar)
Rows Removed by Filter: 5
-> Seq Scan on population population_1 (cost=0.00..18.25 rows=3 width=90) (actual time=0.010..0.012 rows=5 loops=1)
Filter: (sex = '2'::bpchar)
Rows Removed by Filter: 5
Planning Time: 0.270 ms
Execution Time: 0.232 ms
(17 行)
Populationテーブルのフルスキャンを2度実行しています。
仮にテーブルサイズが大きくなることが想定される場合は読み取りコストが線形的に増大していきます。
2つ目はSELECT句での条件分岐を行う場合です。
postgres=# EXPLAIN ANALYZE SELECT prefecture,
postgres-# SUM(CASE WHEN sex = '1' THEN pop ELSE 0 END) AS pop_men,
postgres-# SUM(CASE WHEN sex = '2' THEN pop ELSE 0 END) AS pop_wom
postgres-# FROM Population
postgres-# GROUP BY prefecture;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
HashAggregate (cost=24.85..26.85 rows=200 width=98) (actual time=0.039..0.042 rows=5 loops=1)
Group Key: prefecture
Batches: 1 Memory Usage: 40kB
-> Seq Scan on population (cost=0.00..16.60 rows=660 width=94) (actual time=0.021..0.022 rows=10 loops=1)
Planning Time: 0.126 ms
Execution Time: 0.084 ms
(6 行)
まとめ
初めてではとっつきにくい印象がありますが、実行計画を取得することでSQLの性能面での改善点がわかります。
特に複雑な実装になるほどボトルネックを発見するために読み慣れておく必要があると思いました。
最後までお読みいただきありがとうございました。
参考
https://www.postgresql.jp/document/14/index.html
関連記事
- 2022-09-26
- テクノロジー