【PostgresSQL】条件分岐を高パフォーマンスで行う

目的

SQLで条件分岐を行うには、WHEREやUNIONを使った方法の他にCASEを使った方法もある。
UNIONなども理解しやすく、使いたくなるが、CASEを使った方が基本的にパフォーマンスが高いようなので、実際に本当に違うのか試してみることにした。

環境

PostgreSQL 14.1 (Ubuntu 14.1-1.pgdg18.04+1) on x86_64-pc-linux-gnu

UNIONを条件分岐で行わない

絶対にダメというわけではないが、基本的に下記のようにUNIONで条件分岐は行うべきではないようだ。
なぜならテーブルスキャンが二回行われてしまうため。

select is_adult = false
where age < 20
union all
select is_adult = true
where age => 20

• UNIONはわかりやすくて使いたくなるが、条件分岐用に作られたものではなく、パフォーマンス的によくない
• 長い文になって、ミスも増えるし、読みづらい
• 内部的に複数のSELECTを実行することになるため、I/Oが増える

WHERE内で条件分岐を行わない

WHEREではなく、SELECT内で条件分岐をしたほうが速度は確実に早い

WHERE+UNIONを使って条件分岐をする

explain 
select *, '50以上' as test
from basketball_team where profile_id >= 50
union
select *, '50以下' as test
from basketball_team where profile_id < 50 ;

                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
# レコードをグルーピングしている
 HashAggregate  (cost=2.30..2.36 rows=6 width=84)
   Group Key: basketball_team.member_id, basketball_team."position", basketball_team.profile_id, ('50以上'::text)
   ->  Append  (cost=0.00..2.24 rows=6 width=84)
         # 一度目のbasketball_teamテーブルのフルスキャン
         ->  Seq Scan on basketball_team  (cost=0.00..1.07 rows=3 width=51)
               Filter: (profile_id >= 50)
         # 二度目のbasketball_teamテーブルのフルスキャン
         ->  Seq Scan on basketball_team basketball_team_1  (cost=0.00..1.07 rows=3 width=51)
               Filter: (profile_id < 50)
(7 rows)

 member_id |  position  | profile_id |  test
-----------+------------+------------+--------
         4 | center     |          4 | 50以下
         3 | guard      |          3 | 50以下
         2 | foward     |          2 | 50以下
         1 | center     |          1 | 50以下
         5 | foward     |       1000 | 50以上
         6 | center     |        100 | 50以上

HashAggregateとは

レコードをグルーピングするのに一時的なハッシュテーブルを使います。 HashAggregate処理では、データセットは事前にソートされている必要はありませんが、 中間結果(パイプライン化されていないもの)をマテリアライズするために非常に多くのメモリを必要とします。出力は、 一定のルールに沿って並べられているわけではないものになります。
参考: こちら

SELECTでCASEを使って条件分岐をする

explain 
select *,
case when profile_id >= 50
     then '50以上'
     when profile_id < 50
     then '50以下'
     else null
end as test
from basketball_team;

                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on basketball_team  (cost=0.00..1.09 rows=6 width=51)
(1 row)

 member_id |  position  | profile_id |  test
-----------+------------+------------+--------
         1 | center     |          1 | 50以下
         2 | foward     |          2 | 50以下
         3 | guard      |          3 | 50以下
         4 | center     |          4 | 50以下
         5 | foward     |       1000 | 50以上
         6 | center     |        100 | 50以上

結果

# WHERE + UNION
Execution Time: 0.199 ms
# SELECT + CASE
Execution Time: 0.032 ms

８倍以上実行速度に差が出た。