経緯

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

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

筆者について

クレヨンしんちゃんが大好きなちゃらんぽらん文系出身エンジニア。
この記事もhuluのクレヨンしんちゃんを見ながら書いてます。
SQLをカリッカリにチューニングしたりという経験はないので、誤りがあれば教えてください。

当記事の内容

SQL実践入門──高速でわかりやすいクエリの書き方の第3章に即した内容になります。
テーマは WHERE句やUNIONで分岐させずにSELECTで分岐させよう！ です

UNIONを使ったSQLの改善

今回はpostgreSQLを使って色々改善例を挙げていきます。
実行計画の見方がわからない場合はコチラを参考に。

①UNIONを使った基礎的な条件分岐

メニューテーブルから2018年以前はprice_8_taxの値を、2019年以降はprice_10_taxの値を取得するSQLを考えます。

対象テーブルのデータ

オリジナルのデータ

id	name	year	price_8_tax	price_10_tax
1	お子様ランチ	2017	700	756
2	お子様ランチ	2018	720	778
3	お子様ランチ	2019	750	810
4	お子様ランチ	2020	780	842
5	ランチセット	2017	1200	1296
6	ランチセット	2018	1250	1350
7	ランチセット	2019	1300	1404
8	ランチセット	2020	1400	1512
9	モーニング	2017	850	918
10	モーニング	2018	880	950
11	モーニング	2019	900	972
12	モーニング	2020	910	983

取得したいデータ

name	year	price
お子様ランチ	2017	700
お子様ランチ	2018	720
お子様ランチ	2019	810
お子様ランチ	2020	842
ランチセット	2017	1200
ランチセット	2018	1250
ランチセット	2019	1404
ランチセット	2020	1512
モーニング	2017	850
モーニング	2018	880
モーニング	2019	972
モーニング	2020	983

改善前のSQL(UNIONで分岐させている)

2018年以前と2019年以降でそれぞれテーブルを作ってunion allで統合しています。
UNIONだとソートやらしてしまうのでUNION ALLで統合。

select name, year, price_8_tax as price
 from menu
 where year <= 2018
union all
select name, year, price_10_tax as price
 from menu
 where year >= 2019
 ;

実行計画

当然といえば当然ですが、テーブルを2回フルスキャンしてますね。

Append  (cost=0.00..37.51 rows=334 width=126) (actual time=0.011..0.018 rows=12 loops=1)
  ->  Seq Scan on menu  (cost=0.00..16.25 rows=167 width=126) (actual time=0.011..0.013 rows=6 loops=1)
        Filter: (year <= 2018)
        Rows Removed by Filter: 6
  ->  Seq Scan on menu menu_1  (cost=0.00..16.25 rows=167 width=126) (actual time=0.003..0.004 rows=6 loops=1)
        Filter: (year >= 2019)
        Rows Removed by Filter: 6
Planning Time: 0.084 ms
Execution Time: 0.036 ms

改善後のSQL(SELECT句内で分岐)

SELECT句内でCASE文を使って出力する値を変えてます。
改善前のSQLとレコードの順番は違えど内容はもちろん同じです。

select
    name,year,
    case
        when year <= 2018 then price_8_tax
        else price_10_tax
    end as price
 from menu
;

実行計画

テーブルのスキャン回数が1回になりました。
単純に考えたらテーブルスキャンの回数が2回から1回になったので性能が2倍になったって解釈ができますね。

Seq Scan on menu  (cost=0.00..16.25 rows=500 width=126) (actual time=0.014..0.016 rows=12 loops=1)
Planning Time: 0.066 ms
Execution Time: 0.031 ms

②分岐したうえで集計

各プロジェクトについて男女数（member_count）を1レコードにまとめて取得するSQLで考えてみます。
1が男性で0が女性です。
~~現実のプロジェクトの男女比に近い。。。~~

対象テーブルのデータ

オリジナルのデータ

id	project_name	sex	member_count
1	プロジェクトA	1	15
2	プロジェクトA	0	2
3	プロジェクトB	1	38
4	プロジェクトB	0	4
5	プロジェクトC	1	8
6	プロジェクトC	0	0

取得したいデータ

project_name	male_count	female_count
プロジェクトA	15	2
プロジェクトB	38	4
プロジェクトC	8	0

改善前のSQL（男女を別々に集計後にunion allで統合して集計）

select project_name, sum(male_count) as male_count, sum(female_count) as female_count
from (
    select project_name, member_count as male_count, null as female_count
     from project
     where sex = '1' -- 男性
    union all
    select project_name, null as male_count,member_count as female_count
     from project
     where sex = '0' -- 女性
) temp
group by project_name;

実行計画

当然ですがテーブルを2回フルスキャンして、、、結合して、、、という実行計画になっております。

GroupAggregate  (cost=32.60..32.68 rows=4 width=134) (actual time=0.034..0.036 rows=3 loops=1)
  Group Key: project.project_name
  ->  Sort  (cost=32.60..32.61 rows=4 width=126) (actual time=0.029..0.029 rows=6 loops=1)
        Sort Key: project.project_name
        Sort Method: quicksort  Memory: 25kB
        ->  Append  (cost=0.00..32.56 rows=4 width=126) (actual time=0.013..0.019 rows=6 loops=1)
              ->  Seq Scan on project  (cost=0.00..16.25 rows=2 width=126) (actual time=0.012..0.013 rows=3 loops=1)
                    Filter: (sex = '1'::bpchar)
                    Rows Removed by Filter: 3
              ->  Seq Scan on project project_1  (cost=0.00..16.25 rows=2 width=126) (actual time=0.004..0.004 rows=3 loops=1)
                    Filter: (sex = '0'::bpchar)
                    Rows Removed by Filter: 3
Planning Time: 0.131 ms
Execution Time: 0.067 ms

改善後のSQL（select句内で条件分岐して集計）

今度はselect句内で男女それぞれを条件分岐させて集計しています。
直感的にわかりやすいSQLで可読性も高くなっているかと思います。

select
    project_name,
    sum(case when sex='1' then member_count else 0 end) as male_count,
    sum(case when sex='0' then member_count else 0 end) as female_count
from project
group by project_name
;

実行計画

テーブルスキャンが1度になってだいぶスッキリした実行計画になりました。

HashAggregate  (cost=21.25..23.25 rows=200 width=134) (actual time=0.024..0.025 rows=3 loops=1)
  Group Key: project_name
  Batches: 1  Memory Usage: 40kB
  ->  Seq Scan on project  (cost=0.00..15.00 rows=500 width=130) (actual time=0.011..0.013 rows=6 loops=1)
Planning Time: 0.096 ms
Execution Time: 0.053 ms

③集計したうえで分岐

各従業員が所属しているプロジェクトを管理するテーブルで、
1プロジェクトのみに所属している場合はプロジェクト名
2プロジェクトに所属している場合は「2プロジェクトを兼務」
3プロジェクト以上に所属している場合は「3プロジェクト以上を兼務」
と表示

対象テーブルのデータ

オリジナルのデータ

employee_id	employee_name	project
1	なおき	A
2	だいき	A
3	だいき	B
4	ともき	A
5	ともき	B
6	ともき	C
7	まさき	A
8	まさき	B
9	まさき	C

取得したいデータ

employee_name	project
なおき	A
だいき	2プロジェクトを兼務
ともき	3プロジェクト以上を兼務
まさき	3プロジェクト以上を兼務

改善前のSQL（集計したテーブルを条件分岐してUNIONでくっつける）

    select employee_name, max(project) as project
     from employee
     group by employee_name
     having count(1) = 1
union all
    select employee_name, '2プロジェクトを兼務'
     from employee
     group by employee_name
     having count(1) = 2
union all
    select employee_name, '3プロジェクト以上を兼務'
     from employee
     group by employee_name
     having count(1) >= 3;

実行計画

テーブルへのアクセスとハッシュ演算による集計が3回おこなわれています。
明らかにコストがよくないです。
これがプロジェクト数が1,2,3の場合以上に条件分岐があれば、その回数だけテーブルへのアクセス回数をふやすことになってしまいます。

Append  (cost=15.25..52.79 rows=69 width=150) (actual time=0.025..0.041 rows=4 loops=1)
  ->  HashAggregate  (cost=15.25..17.75 rows=1 width=150) (actual time=0.024..0.025 rows=1 loops=1)
        Group Key: employee.employee_name
        Filter: (count(1) = 1)
        Batches: 1  Memory Usage: 40kB
        Rows Removed by Filter: 3
        ->  Seq Scan on employee  (cost=0.00..13.00 rows=300 width=236) (actual time=0.011..0.012 rows=9 loops=1)
  ->  HashAggregate  (cost=14.50..17.00 rows=1 width=150) (actual time=0.007..0.007 rows=1 loops=1)
        Group Key: employee_1.employee_name
        Filter: (count(1) = 2)
        Batches: 1  Memory Usage: 40kB
        Rows Removed by Filter: 3
        ->  Seq Scan on employee employee_1  (cost=0.00..13.00 rows=300 width=118) (actual time=0.003..0.004 rows=9 loops=1)
  ->  HashAggregate  (cost=14.50..17.00 rows=67 width=150) (actual time=0.007..0.008 rows=2 loops=1)
        Group Key: employee_2.employee_name
        Filter: (count(1) >= 3)
        Batches: 1  Memory Usage: 40kB
        Rows Removed by Filter: 2
        ->  Seq Scan on employee employee_2  (cost=0.00..13.00 rows=300 width=118) (actual time=0.004..0.005 rows=9 loops=1)
Planning Time: 0.156 ms
Execution Time: 0.091 ms

改善後のSQL（select句内で集計結果を分岐）

select 
    employee_name,
    case
        when count(1) = 1 then max(project)
        when count(1) = 2 then '2プロジェクトを兼務'
        when count(1) >= 3 then '3プロジェクト以上を兼務'
    end as project
 from employee
 group by employee_name
;

実行計画

テーブルへのアクセスおよび集計のハッシュ演算のコストを1/3にできています。

HashAggregate  (cost=15.25..18.75 rows=200 width=150) (actual time=0.024..0.026 rows=4 loops=1)
  Group Key: employee_name
  Batches: 1  Memory Usage: 40kB
  ->  Seq Scan on employee  (cost=0.00..13.00 rows=300 width=236) (actual time=0.011..0.012 rows=9 loops=1)
Planning Time: 0.095 ms
Execution Time: 0.057 ms

UNIONが必要になる、もしくはUNIONも選択肢に入るケース

そもそもUNION対象のテーブルが異なるケース
　→これはどうしようもないです。
indexの関係等でテーブルへのアクセスが複数回発生してもUNIONのほうがパフォーマンスがいいケース

本節を読んでの感想

筆者が
条件分岐をWHERE句で行うのは素人のやること。プロはSELECT句で分岐させる。
というSQLの格言を紹介してます。
少しはプロに近づけたかな？