[pgsql-jp: 36835] Re: 別テーブルを条件にしたUPDATEの性能(Re: WHEREにSELECTふたつ)

[根岸 智幸]

こうもとさん

根岸です。

ベンチやExplainの結果をレポートしていただき、ありがとうご 
ざいました。
僕も勉強になりました。



On 2006/03/07, at 15:27, 河本陽一 wrote:

> こうもとです。
>
> TANIDA Yutaka さんの書いたこと:
>> 谷田です。
>> お久しぶりです:)
>
> 　うわぉ。お久しぶりです。
>
>
>>> 案１(PostgreSQL専用)：根岸さんと、海北さんの方法
>>> UPDATE t1 SET f3=f3+1
>>>     FROM t2 WHERE t1.f1=t2.f1 AND t1.f2=t2.f2 AND t2.f5='a01';
>>>
>>> 案２(汎用)：石田さんの方法
>>> UPDATE t1 SET f3=f3+1
>>>     WHERE (f1,f2)=(SELECT f1,f2 FROM t2 WHERE f5='a01');
>>>
>>> 　結果は、圧倒的に案１の方早いことがわかりました。
>>
>> explain analyzeの結果はどうなっていますか？あとanalyze 
>> はしてますか？
>
> 　EXPLAIN ANALYZE の結果です。
>
> ●案１
> # EXPLAIN ANALYZE UPDATE t1 SET f3=f3+1 FROM t2 WHERE t1.f1=t2.f1  
> AND t1.f2
> =t2.f2 AND t2.f5='text00019780';
>                                                      QUERY PLAN
> ---------------------------------------------------------------------- 
> ----------------------------------------------
>  Nested Loop  (cost=0.00..10.05 rows=1 width=18) (actual  
> time=0.299..0.491 rows=1 loops=1)
>    ->  Index Scan using t2_f5_key on t2  (cost=0.00..3.01 rows=1  
> width=8) (actual time=0.118..0.157 rows=1 loops=1)
>          Index Cond: (f5 = 'text00019780'::text)
>    ->  Index Scan using t1_pkey on t1  (cost=0.00..7.01 rows=1  
> width=18) (actual time=0.058..0.098 rows=1 loops=1)
>          Index Cond: ((t1.f1 = "outer".f1) AND (t1.f2 = "outer".f2))
>  Total runtime: 0.790 ms
> (6 rows)
>
> ●案２
> # EXPLAIN ANALYZE UPDATE t1 SET f3=f3+1 WHERE (f1,f2)=(SELECT f1,f2  
> FROM t2 WHERE f5='text00019780');
>                                                       QUERY PLAN
> ---------------------------------------------------------------------- 
> ------------------------------------------------
>  Seq Scan on t1  (cost=3.01..1766.52 rows=1 width=18) (actual  
> time=518.590..518.650 rows=1 loops=1)
>    Filter: ((f1 = $0) AND (f2 = $1))
>    InitPlan
>      ->  Index Scan using t2_f5_key on t2  (cost=0.00..3.01 rows=1  
> width=8) (actual time=5.275..5.318 rows=1 loops=1)
>            Index Cond: (f5 = 'text00019780'::text)
>  Total runtime: 519.751 ms
> (6 rows)
>
>
> 　ANALYZEは、Debianの場合、インストール時に自動で 
> 定期的に実行するよ
> うになりました。
> ＃月曜から土曜の0,5,10,15,20時の2分に。
> 　ログにもANALYZEされた形跡が残っています。
>
>
>> たぶん案１は何かJOIN、案２は普通にループになっているの 
>> ではと思います。で
>> あれば、案１が圧倒的に早いのは理の当然ではないかな、と思いま 
>> す。
>
> 　確かに、案２だとシーケンシャルアクセスになっているようです。
> 　これが時間のかかる原因だったのですね。
>
>
>> IN/NOT INを使った副問い合わせをより効率的に実行できるようにな 
>> りました。
>> 　7.3以前のバージョンではこれらの副問い合わせはより上位 
>> の問い合わせとシー
>> ケンシャルスキャンを利用して結合するようにしていました。 
>> 7.4ではたとえば
>> 副問い合わせを通常のJOINに変換するような方法で、これら 
>> の検索は非常に高速
>> 化されました。
>
> 　これを見てふと思い、kuniyoshiさんの in を使う方 
> 法を試してみると、
> こちらは早く終わりました。
>
> ●案２’
> # EXPLAIN ANALYZE UPDATE t1 SET f3=f3+1 WHERE (f1,f2)in(SELECT  
> f1,f2 FROM t2 WHERE f5='text00019780');
>                                                         QUERY PLAN
> ---------------------------------------------------------------------- 
> ----------------------------------------------------
>  Nested Loop  (cost=3.02..10.05 rows=1 width=18) (actual  
> time=0.412..0.602 rows=1 loops=1)
>    ->  HashAggregate  (cost=3.02..3.02 rows=1 width=8) (actual  
> time=0.233..0.270 rows=1 loops=1)
>          ->  Index Scan using t2_f5_key on t2  (cost=0.00..3.01  
> rows=1 width=8) (actual time=0.073..0.112 rows=1 loops=1)
>                Index Cond: (f5 = 'text00019780'::text)
>    ->  Index Scan using t1_pkey on t1  (cost=0.00..7.01 rows=1  
> width=18) (actual time=0.057..0.096 rows=1 loops=1)
>          Index Cond: ((t1.f1 = "outer".f1) AND (t1.f2 = "outer".f2))
>  Total runtime: 0.873 ms
> (7 rows)
>
>
> 　案１は、文句のつけようのない内容に思えます。頭で思った処理が 
> そのま
> ま書いてある感じで気持ちがよいです。
> 　案２は、t1を総なめし、その一つ一つでSELECTして条 
> 件にあっているか調
> べているのでしょうか。
> 　案２’は、複数(かもしれない)の結果から条件にあっ 
> たものを取り出すた
> めに一度まとめ、それを元にt1を検索しているのでしょうか。 
> 一度まとめる
> 手間はあるものの、ほとんど案１と同じ処理ですね。
>
> 　結果としては、案２’の強烈な追い上げもわずかに届かず、案１の 
> 優勝と
> いうことになりました。
>
>
> 　EXPLAINが、これほど役に立つコマンドとは思いませんでし 
> た。
> 　これからは積極的に使っていこうと思います。
>
>
> ======================================================================
> 河本陽一(こうもとよういち)
> mailto:komoto.yoichi ＠ kcc.co.jp
>
>
>
>