SQLの条件分岐について検証してみた

はじめまして、AIR Design for Marketing 事業部 バックエンドエンジニアの大田です。 好きな言語はC#、好きなRDBMSはPostgreSQLです :)

前置き

RDBから何かしらのリストを取得する際にユーザが指定した任意の条件で絞り込むというのは一般的なユースケースだと思いますが、それを実現するクエリの構築方法はいくつかあるかと思います。 私がパッと思いつくのは以下のような方法です。

※ コードはイメージです。動作確認などはしていません 🙏🏼

パターン1: 条件毎にクエリを分割し、文字列結合で WHERE 句を組み立てる (以下 "文字列結合")

conditions = list("TRUE")
params = dict()

if name != "":
  conditions.append("name LIKE '%' || :name || '%'")
  params.append(name)

if email != "":
  conditions.append("email = :email")
  params.append(email)

condition = " AND ".join(conditions)
query = "SELECT * FROM account WHERE " + condition
result = connection.execute(text(query), params)

パターン2: クエリビルダを使って WHERE 句を組み立てる (以下 "クエリビルダ")

query = account.select()

if name != "":
  query = query.where(account.c.name.like("%" + name + "%")

if email != "":
  query = query.where(account.c.email == email)

result = connection.execute(query)

パターン3: WHERE 区の中で条件分岐を使う (以下 "条件分岐")

query = """
SELECT * FROM account
WHERE
  (:name = '' OR name LIKE '%' || :name || '%')
  AND (:email = '' OR email = :email)
"""

params = dict(name=name, email=email)
result = connection.execute(text(query), params)

私はSQLインジェクションのリスクを減らすという観点から クエリビルダ もしくは 条件分岐 を使っているのですが、JOIN するテーブルが多くなるようなケースでは比較的見通しが良くなるという理由で 条件分岐 を採用することが多いです。

この 条件分岐 ですが、他の選択肢と異なる点として「ユーザが指定していない条件に関する式もクエリに含まれる」ことが挙げられると思います。 これによって、他の選択肢では考慮する必要のない「評価しなくていい式も評価してしまっているかも？」という懸念が(私の中で)出てきました 🤔

そうは言っても RDBMS は賢いので問題ないとは思いつつ、せっかくなので評価しなくていい式が無視されているかどうか検証してみることにしました。

検証結果

先に書いてしまいますが、単純な条件分岐を用いれば評価しなくていい式は無視されます 😃

RDBMS は賢いです。これからも信頼して使っていきましょう。

検証

今回は PostgreSQL 14.5 を使用します。

pg_sleep 関数

pg_sleep 関数は名前の通り指定した秒数 sleep してくれる関数で、クエリの結果が返ってくる時間が指定した秒数かかっているかどうかによって式が評価されているかどうか分かるという、検証に際して難しいことを考えなくていいありがたい選択肢です 😃

(余談ですが、この関数は PostgreSQL ドキュメント内の Date/Time Functions and Operators ページに載っています。そこかぁ 🤔)

実行時間

(クエリの実行時間を見るために、 \timing on (doc)を使っています。)

postgres=# SELECT 1 WHERE TRUE OR (SELECT pg_sleep(1)) IS NOT NULL;
 ?column? 
----------
        1
(1 row)

Time: 4.354 ms

期待通り、1秒経たずに結果が返ってきていますね 😄

postgres=# SELECT 1 WHERE FALSE OR (SELECT pg_sleep(1)) IS NOT NULL;
 ?column? 
----------
        1
(1 row)

Time: 1015.302 ms (00:01.015)

こちらは pg_sleep(1) が評価された結果、1秒以上経ってから結果が返ってきていますね。

実行計画

念の為、実行計画も見ておきましょう。

postgres=# EXPLAIN SELECT 1 WHERE TRUE OR (SELECT pg_sleep(1)) IS NOT NULL;
                QUERY PLAN                
------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=4)
(1 row)

postgres=# EXPLAIN SELECT 1 WHERE FALSE OR (SELECT pg_sleep(1)) IS NOT NULL;
                    QUERY PLAN                    
--------------------------------------------------
 Result  (cost=0.01..0.02 rows=1 width=4)
   One-Time Filter: ($0 IS NOT NULL)
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.01 rows=1 width=4)
(4 rows)

FALSE OR ... では右辺が評価されているのがわかりますね 👀

単純な条件分岐であれば、しっかりと短絡処理が効いてくれているようです 😃

最後に

ガラパゴスでは一緒に働く仲間を募集しています！

ガラパゴスでの働き方に関する私のインタビュー記事もありますので、もし良ければ読んでみてください 😃

note.com

ご興味のある方はぜひご応募いただけますと嬉しいです。