UPDATE SET FROMの不定性

postgres DB 技術

久しぶりにポスグレの小ネタ。 大した内容ではないが、最近シャのお仕事中に使ったのでメモ。

お題

実験用テーブルとして、人物のid、ファーストネーム、ラストネーム、フルネームを保持するテーブルを作成する。

create table person(
    id int,
    first_name text,
    last_name text,
    full_name text
);

insert into person
(id, first_name, last_name, full_name)
values
(1, 'taro', 'yamada', null),
(2, 'hanako', 'suzuki', null);

これで以下のpersonテーブルが完成する。

id first_name last_name full_name
1 taro yamada NULL
2 hanako suzuki NULL

レコード作成時にはファーストネームとラストネームのみが登録され、フルネームは null になっている。
その後に(トリガなどで)ファーストネームとラストネームを結合してフルネームを作成する状況を想定する。

ダメなUPDATE

以下のupdate文を実行する。

update person
set full_name = p.first_name || ' ' || p.last_name
from person p;

結果は以下となり、意図どおりではない（suzuki hanakoさんのフルネームがtaro yamadaになっている）。

id first_name last_name full_name
1 taro yamada taro yamada
2 hanako suzuki taro yamada

ドキュメントには以下の記述がある。

FROM句が存在する場合、基本的に、対象テーブルとfrom_listで指定されたテーブルが結合され、この結合の出力行が対象テーブルの更新操作の結果となります。（中略）結合結果が複数行になった場合、対象行の更新には結合結果のいずれか1行のみが使用されますが、どの行が使用されるかは簡単には予測できません。

今回の例だと、まず、personテーブルとpersonテーブルをset句のカラムでクロス結合する。この時点で、idが1のレコードのfull_nameとしては、taro yamada と hanako suzuki の2通りがありえる。Postgresは、このうち1つをfull_nameに格納することになる。このときの選ばれ方が不定ってはなし。

というわけで、意図したフルネームを格納するためには、setに対応するレコードを確定させたい。

正しいUPDATE

サブクエリで絞る方法とWHEREで絞る方法がある。他にもあったら教えてください。
SQL Fiddleで実行計画も出してくれるので載せてみたが、統計情報が謎なことになっているっぽい。2行なのに630行とかなってる。

サブクエリで絞る

update person p1
set full_name = (
  select p2.first_name || ' ' || p2.last_name
   from person p2
  where p1.id = p2.id
);

Update on person p1 (cost=0.00..11287.00 rows=630 width=106) (actual time=0.038..0.038 rows=0 loops=1)
-> Seq Scan on person p1 (cost=0.00..11287.00 rows=630 width=106) (actual time=0.021..0.026 rows=2 loops=1)
SubPlan 1
-> Seq Scan on person p2 (cost=0.00..17.89 rows=3 width=32) (actual time=0.005..0.005 rows=1 loops=2)
Filter: (p1.id = id)
Rows Removed by Filter: 1
Planning time: 0.133 ms
Execution time: 0.070 ms

WHEREで絞る

update person p1
set full_name = p2.first_name || ' ' || p2.last_name
from person p2
where p1.id = p2.id;

Update on person p1 (cost=24.18..121.42 rows=1984 width=112) (actual time=0.075..0.075 rows=0 loops=1)
-> Hash Join (cost=24.18..121.42 rows=1984 width=112) (actual time=0.055..0.057 rows=2 loops=1)
Hash Cond: (p1.id = p2.id)
-> Seq Scan on person p1 (cost=0.00..16.30 rows=630 width=74) (actual time=0.016..0.017 rows=2 loops=1)
-> Hash (cost=16.30..16.30 rows=630 width=74) (actual time=0.009..0.009 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on person p2 (cost=0.00..16.30 rows=630 width=74) (actual time=0.002..0.002 rows=2 loops=1)
Planning time: 0.235 ms
Execution time: 0.113 ms

どっちがいいか

みためはWHERE句のほうがシンプルだが、実行速度はサブクエリで絞ったほうが速い。
サブクエリを使うと、p1をSeq Scanしたあとに、そこで取得したidをフィルタにしてp2をSeq Scanする。
一方WHERE句のほうは、p1とp2を平等にSeq Scanし、Hash Joinしてからidが一致する行を抽出する。プランナがp1とp2の主従関係を認識できないためと思われる。

データ量と傾向に合わせて適切なかんじにアレしてください。

おまけ:不正なUPDATE

ちなみに以下はダメ。

update person p1
set full_name = p2.first_name || ' ' || p2.last_name
from (select * from person p3 where p3.id = p1.id) p2;

ERROR: invalid reference to FROM-clause entry for table "p1"
Hint: There is an entry for table "p1", but it cannot be referenced from this part of the query.

FROM句の後はサブクエリではなくfrom_listとして解釈されるため、最初に実行される。
その時点でp1がスコープ内に存在しないため、文法エラーとなる。

参考