【PostgreSQL】Select結果でUpdateする３パターン

概要

PostgreSQLを使っているシステムで、Select結果でUpdateしたい機能があったので、当初は相関副問い合わせを使ったSQLで実装していました。ところが、総合テストの段階まで来て、速度パフォーマンスが良くないことがわかり、SQLを書き直した経過を紹介します。

前提となるテーブル

実際のシステム内ではもっと多数の列を持つテーブルなのですが、説明のため簡単にしていますが、おおよそ下記のようなテーブルがあることにします。

ユーザマスタ(m_users)：ユーザごとに、過去のテストの最高得点を持っている。

テスト成績データ(d_records)：テスト毎に、ユーザの得点を持っている。テストを受けていないユーザについてはレコードがない。

相関副問い合わせ

今までOracleを使うことが多かったので、Oracleでも使えていた相関副問い合わせを使って実装していました。

update m_users
   set best_point = coalesce((
         select max(point)
           from d_records
          where user_id = m.user_id
            and test_id = 1
            and point >= m.best_point
       ), best_point)

coalesceを使うことで、Select結果がない場合には既存の値で更新するようにしています。これでSelect結果のみが更新されているように見えます。Oracleの場合だと、NVLを使うと同じ事ができるはずです。ただし、m_usersテーブルにtrigger関数がついている場合には、全行についてtrigger関数が動作してしまうので良くないこともあります。そのような場合には、次のexists句を使ったSQLが使えます。

相関副問い合わせ（exists句）

exists句を使って、Select結果がある行に限ってUpdateするようにしたのが下記です。これだと、余計な行まで更新しません。

update m_users
   set best_point = (
         select max(point)
           from d_records
          where user_id = m.user_id
            and test_id = 1
            and point >= m.best_point
       )
 where exists (
         select *
           from d_records
          where user_id = m.user_id
            and test_id = 1
            and point >= m.best_point
       )

当初はこのSQLで実装していたのですが、思ったよりも速度パフォーマンスが良くないことがわかりました。Select結果1行につき、副問い合わせが1回実行されるため、Select結果数が多い場合（私の例では100万件程度）にはパフォーマンスが悪くなります。ほぼ全行Selectされてしまう場合には、先のcoalesceを使った場合との違いはそれほどありませんでした。

with句 + Update From

Select結果をいったんwith句を使って評価しておいた後で、Updateするというのが解決策となりました。

with d_target_records as (
  select user_id
       , point
    from d_records d
   inner join m_users m
           on d.user_id = m.user_id
   where d.test_id = 1
     and d.point >= m.best_point
)
update m_users m
   set best_point = d.point
  from d_target_records d
 where m.user_id = d.user_id

このSQLも、Select結果が0件の場合は更新結果も0件となるので、余計な行を更新することはありません。
Update From という構文は、PostgreSQLでは使えますがOracleでは使えないので注意が必要です。