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

[河本陽一]

こうもとです。

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