経緯
「弊社のエンジニアであればSQLの軽いチューニングはできて当然」というレジェンドエンジニアのKさんの言葉を受け下記の本を読みましたのでそのアウトプット＆共有ができればと。
筆者について
おなかの弱いぴーぴーエンジニア。
刺激物、油もので当然おなかは壊します。それも1週間近くずっと。
また私ほどになると腹筋しただけでおなかを壊します。外部からの刺激でおなかを壊すってことが医学的にあるそうで。
SQLをカリカリにチューニングみたいな経験はないので、誤りがあれば教えてください。
当記事の内容
SQL実践入門──高速でわかりやすいクエリの書き方 の第7章に即した内容になります。
テーマは サブクエリ です。
そもそものサブクエリの欠点
-
サブクエリの計算コストが上乗せされる
サブクエリにアクセスする度にデータが上乗せされていきます。 -
データのI/Oコストがかかる
サブクエリの計算結果はメモリ上に保持されます。メモリで保持しきれなければ、Temp落ちしてアクセス速度が急激に低下して、パフォーマンスに影響がでます。 -
最適化が行われない
サブクエリで得る結果には制約やインデックスなどのメタ情報が含まれないため、サブクエリで得た結果に対しては最適化の恩恵が受けられません。
サブクエリを使ったパフォーマンスの悪いクエリと改善案
例①
以下の点数テーブルから生徒（student_id）ごとに連番（seq）が一番小さいレコードを取得します。
| student_id | seq | point |
|---|---|---|
| 1 | 1 | 50 |
| 1 | 2 | 72 |
| 1 | 3 | 41 |
| 2 | 5 | 20 |
| 2 | 6 | 92 |
| 2 | 7 | 78 |
| 2 | 9 | 19 |
| 3 | 4 | 9 |
| 3 | 15 | 49 |
| 3 | 32 | 33 |
サブクエリを使うクエリ
select
ori.student_id, ori.seq, ori.point
from
test_result ori
inner join
(select
student_id, min(seq) mini_seq
from
test_result
group by
student_id
) minimum_seq
on minimum_seq.student_id = ori.student_id
and minimum_seq.mini_seq = ori.seq
実行計画
test_resultテーブルを2回スキャンしています。（相関サブクエリを使っても結局2回スキャンしていることは変わりません）
また結合を用いているため、実行計画変動のリスクと結合アルゴリズムによってはTemp落ちのリスクがあります。
Hash Join (cost=47.60..88.71 rows=10 width=12) (actual time=0.058..0.062 rows=3 loops=1)
Hash Cond: ((ori.student_id = test_result.student_id) AND (ori.seq = (min(test_result.seq))))
-> Seq Scan on test_result ori (cost=0.00..30.40 rows=2040 width=12) (actual time=0.024..0.025 rows=10 loops=1)
-> Hash (cost=44.60..44.60 rows=200 width=8) (actual time=0.024..0.024 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> HashAggregate (cost=40.60..42.60 rows=200 width=8) (actual time=0.019..0.021 rows=3 loops=1)
Group Key: test_result.student_id
Batches: 1 Memory Usage: 40kB
-> Seq Scan on test_result (cost=0.00..30.40 rows=2040 width=8) (actual time=0.005..0.006 rows=10 loops=1)
Planning Time: 0.171 ms
Execution Time: 0.112 ms
改善クエリ
select
student_id,
seq,
point
from (
select
ori.student_id,
ori.seq,
row_number() over(partition by ori.student_id order by ori.seq) as row_num,
ori.point
from
test_result ori
) temp
where row_num = 1
;
実行計画
テーブルのスキャン回数が1回になりました。
レコード数が少ないためあまり改善されていないように見れますが、レコード数がもっと多くなった時に結合アルゴリズム変動のリスクやTemp落ちのリスクが減少していることの恩恵をうけられることになります。
Subquery Scan on temp (cost=142.54..208.84 rows=10 width=12) (actual time=0.052..0.065 rows=3 loops=1)
Filter: (temp.row_num = 1)
Rows Removed by Filter: 7
-> WindowAgg (cost=142.54..183.34 rows=2040 width=20) (actual time=0.051..0.062 rows=10 loops=1)
-> Sort (cost=142.54..147.64 rows=2040 width=12) (actual time=0.032..0.033 rows=10 loops=1)
Sort Key: ori.student_id, ori.seq
Sort Method: quicksort Memory: 25kB
-> Seq Scan on test_result ori (cost=0.00..30.40 rows=2040 width=12) (actual time=0.015..0.017 rows=10 loops=1)
Planning Time: 0.127 ms
Execution Time: 0.096 ms
例②
今度は「例①」で例示したテーブルについて生徒（student_id）ごとに連番（seq）の最大値と最小値の点（point）の差を求めます。
サブクエリを使うクエリ
select
min.student_id, max.point - min.point
from (
select ori.student_id, ori.seq, ori.point
from test_result ori
inner join
(
select student_id, min(seq) as seq
from test_result
group by student_id
) min_temp
on min_temp.student_id = ori.student_id
and min_temp.seq = ori.seq ) min
inner join (
select ori.student_id, ori.seq, ori.point
from test_result ori
inner join
(
select student_id, max(seq) as seq
from test_result
group by student_id
) max_temp
on max_temp.student_id = ori.student_id
and max_temp.seq = ori.seq ) max
on min.student_id = max.student_id
;
実行計画
テーブルのアクセス回数は例①からさらに増えて4回となりました。
合計3つの結合を行っているため、結合アルゴリズムとTemp落ちのリスクはますます高くなってます。
Hash Join (cost=136.44..176.04 rows=1 width=8) (actual time=0.098..0.104 rows=3 loops=1)
Hash Cond: ((test_result.student_id = test_result_1.student_id) AND (ori_1.seq = (max(test_result_1.seq))))
-> Hash Join (cost=88.84..127.91 rows=102 width=24) (actual time=0.046..0.051 rows=10 loops=1)
Hash Cond: (ori_1.student_id = test_result.student_id)
-> Seq Scan on test_result ori_1 (cost=0.00..30.40 rows=2040 width=12) (actual time=0.006..0.007 rows=10 loops=1)
-> Hash (cost=88.71..88.71 rows=10 width=12) (actual time=0.035..0.036 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Hash Join (cost=47.60..88.71 rows=10 width=12) (actual time=0.028..0.032 rows=3 loops=1)
Hash Cond: ((ori.student_id = test_result.student_id) AND (ori.seq = (min(test_result.seq))))
-> Seq Scan on test_result ori (cost=0.00..30.40 rows=2040 width=12) (actual time=0.005..0.006 rows=10 loops=1)
-> Hash (cost=44.60..44.60 rows=200 width=8) (actual time=0.017..0.018 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> HashAggregate (cost=40.60..42.60 rows=200 width=8) (actual time=0.013..0.014 rows=3 loops=1)
Group Key: test_result.student_id
Batches: 1 Memory Usage: 40kB
-> Seq Scan on test_result (cost=0.00..30.40 rows=2040 width=8) (actual time=0.007..0.008 rows=10 loops=1)
-> Hash (cost=44.60..44.60 rows=200 width=8) (actual time=0.044..0.044 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> HashAggregate (cost=40.60..42.60 rows=200 width=8) (actual time=0.038..0.040 rows=3 loops=1)
Group Key: test_result_1.student_id
Batches: 1 Memory Usage: 40kB
-> Seq Scan on test_result test_result_1 (cost=0.00..30.40 rows=2040 width=8) (actual time=0.021..0.022 rows=10 loops=1)
Planning Time: 0.564 ms
Execution Time: 0.219 ms
改善クエリ
select
student_id,
sum(case when max_seq = 1 then point else 0 end) - sum(case when min_seq = 1 then point else 0 end)
from
(
select
student_id,
point,
row_number() over(partition by student_id order by seq) as min_seq,
row_number() over(partition by student_id order by seq desc) as max_seq
from
test_result
) temp
group by
student_id;
実行計画
テーブルへのアクセス回数は1回になりました。
2回ソートを実施していますが、改善前のテーブル結合のコストに比べれば安価なコストです。
QUERY PLAN
GroupAggregate (cost=143.05..359.70 rows=200 width=12) (actual time=0.074..0.081 rows=3 loops=1)
Group Key: test_result.student_id
-> WindowAgg (cost=143.05..311.30 rows=2040 width=28) (actual time=0.065..0.072 rows=10 loops=1)
-> Incremental Sort (cost=143.05..275.60 rows=2040 width=20) (actual time=0.062..0.064 rows=10 loops=1)
Sort Key: test_result.student_id, test_result.seq
Presorted Key: test_result.student_id
Full-sort Groups: 1 Sort Method: quicksort Average Memory: 25kB Peak Memory: 25kB
-> WindowAgg (cost=142.54..183.34 rows=2040 width=20) (actual time=0.039..0.051 rows=10 loops=1)
-> Sort (cost=142.54..147.64 rows=2040 width=12) (actual time=0.030..0.031 rows=10 loops=1)
Sort Key: test_result.student_id, test_result.seq DESC
Sort Method: quicksort Memory: 25kB
-> Seq Scan on test_result (cost=0.00..30.40 rows=2040 width=12) (actual time=0.018..0.020 rows=10 loops=1)
Planning Time: 0.175 ms
Execution Time: 0.138 ms
それでもサブクエリを使った方がいいケース
対象のテーブルと求めたい情報
下記のテーブル2つから会社ID（company_id）、地域ID（region_id）、合計従業員数（employee_countの会社ID、地域IDごとの合計）
- 会社テーブル（company）
| company_id | region_id |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 3 |
- 事業所テーブル（branch）
| company_id | baranch_id | employee_count |
|---|---|---|
| 1 | 1 | 200 |
| 1 | 1 | 130 |
| 1 | 3 | 35 |
| 2 | 3 | 300 |
| 2 | 3 | 45 |
| 2 | 3 | 120 |
| 3 | 2 | 15 |
| 3 | 2 | 24 |
| 3 | 3 | 8 |
| 4 | 1 | 2 |
| 4 | 1 | 409 |
解法1（結合してから集計）
select
company.company_id,
company.region_id,
sum(branch.employee_count)
from
company
inner join
branch
on company.company_id = branch.company_id
group by
company.company_id, company.region_id
;
実行計画
QUERY PLAN
HashAggregate (cost=829.92..832.18 rows=226 width=16) (actual time=0.080..0.082 rows=4 loops=1)
Group Key: company.company_id, company.region_id
Batches: 1 Memory Usage: 40kB
-> Merge Join (cost=301.05..657.03 rows=23052 width=12) (actual time=0.065..0.071 rows=11 loops=1)
Merge Cond: (branch.company_id = company.company_id)
-> Sort (cost=142.54..147.64 rows=2040 width=8) (actual time=0.042..0.043 rows=11 loops=1)
Sort Key: branch.company_id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on branch (cost=0.00..30.40 rows=2040 width=8) (actual time=0.024..0.025 rows=11 loops=1)
-> Sort (cost=158.51..164.16 rows=2260 width=8) (actual time=0.018..0.019 rows=10 loops=1)
Sort Key: company.company_id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on company (cost=0.00..32.60 rows=2260 width=8) (actual time=0.008..0.008 rows=4 loops=1)
Planning Time: 0.412 ms
Execution Time: 0.160 ms
解法2（集計してから結合）
select
company.company_id,
company.region_id,
temp_sum.employee_sum
from (
select
company_id,
sum(employee_count) as employee_sum
from
branch
group by
company_id
) temp_sum
inner join
company
on
temp_sum.company_id = company.company_id
;
実行計画
解法1から集計、結合の処理順序が逆転していることがわかります。
Hash Join (cost=42.00..80.65 rows=2260 width=16) (actual time=0.067..0.070 rows=4 loops=1)
Hash Cond: (company.company_id = temp_sum.company_id)
-> Seq Scan on company (cost=0.00..32.60 rows=2260 width=8) (actual time=0.019..0.020 rows=4 loops=1)
-> Hash (cost=39.50..39.50 rows=200 width=12) (actual time=0.032..0.033 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Subquery Scan on temp_sum (cost=35.50..39.50 rows=200 width=12) (actual time=0.023..0.026 rows=4 loops=1)
-> HashAggregate (cost=35.50..37.50 rows=200 width=12) (actual time=0.023..0.025 rows=4 loops=1)
Group Key: branch.company_id
Batches: 1 Memory Usage: 40kB
-> Seq Scan on branch (cost=0.00..27.00 rows=1700 width=8) (actual time=0.008..0.009 rows=11 loops=1)
Planning Time: 0.180 ms
Execution Time: 0.134 ms
解放1と解法2はどう違うの？
解法2のいいところは結合コストが解法1の 4×10 から 4×4 に抑えられている点です。
今回は支店テーブル（branch）が10レコードのみなので恩恵が少ないですが、これが1000レコード、10000レコードと増えた場合は大きな意味を持ってきます。
ただTemp落ちのリスクもあるのでなんともなとこですね。。
ただチューニングの選択肢として覚えておいてもよいかと。
当章を読んで
なるべくメモリを取らない（Temp落ちリスク回避）、なるべく結合はしない（結合アルゴリズム変動リスク回避）ってのがSQLのパフォーマンスを安定させるうえで大事だと、ひたすらそれをしみこまされた章でした。