経緯

「弊社のエンジニアであればSQLの軽いチューニングはできて当然」というレジェンドエンジニアのKさんの言葉を受け下記の本を読みましたのでそのアウトプット＆共有ができればと。

SQL実践入門──高速でわかりやすいクエリの書き方

筆者について

おなかの弱いぴーぴーエンジニア。
刺激物、油もので当然おなかは壊します。それも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のパフォーマンスを安定させるうえで大事だと、ひたすらそれをしみこまされた章でした。