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

[河本陽一]

こうもとです。

ISHIDA Akio さんの書いたこと:
> > # UPDATE t1 SET f3=f3+1 where (f1)=(SELECT f1 FROM t2 WHERE f5='a02');
> > UPDATE 2
> 
> いや、kuniyoshi さんがおっしゃっているのは、
> SELECT f1 FROM t2 WHERE f5='a02'
> が複数の行を返すケースですよね。

　あ、いわれてみるとそのとおりですね。
　WHEREが複数と勘違いしていました。



　やりたいことの解決方法に2通りの方法があったので、どちらを使えばよ
いかの判断のためにベンチマークをとってみました。
　t1に900件、t2に27000件のレコードを入力し、ランダムに500回のUPDATE
を10回行いました。
＃テーブル構成は変更ありません。

案１(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');

　結果は、圧倒的に案１の方早いことがわかりました。

----------------------------------------------------------------------
$ ./benchdb.pl bench
bench1:11 wallclock secs ( 0.00 usr +  0.23 sys =  0.23 CPU) @ 21.74/s (n=5)
bench2:1316 wallclock secs ( 0.04 usr +  0.36 sys =  0.40 CPU) @ 12.50/s (n=5)
RET=[0]
----------------------------------------------------------------------

環境：
OS:Debian sarge (coLinux上)
DB:PostgreSQL 7.4.7

＃テストに使用したソースはメールの最後に貼りました。

　案２が異常に遅いのですが、何かインデックスが必要なのでしょうか。
　案２のログの処理時間を見ると、1回で500ms程度もかかっていました。

----------------------------------------------------------------------
statement: UPDATE t1 SET f3=f3+1 WHERE (f1,f2)=(SELECT f1,f2 FROM t2 WHERE f5='text00019780');
duration: 509.891 ms
statement: UPDATE t1 SET f3=f3+1 WHERE (f1,f2)=(SELECT f1,f2 FROM t2 WHERE f5='text00019780');
duration: 606.183 ms
statement: UPDATE t1 SET f3=f3+1 WHERE (f1,f2)=(SELECT f1,f2 FROM t2 WHERE f5='text00019780');
duration: 520.638 ms
----------------------------------------------------------------------


　切り分けのため、部分的に実行してみたつもりでしたが、どれもそれほど
時間はかかっていません。
＃ばらつきは大きいですが。
----------------------------------------------------------------------
statement: SELECT f1,f2 FROM t2 WHERE f5='text00019780';
duration: 0.487 ms
statement: SELECT f1,f2 FROM t2 WHERE f5='text00019780';
duration: 0.488 ms
statement: SELECT f1,f2 FROM t2 WHERE f5='text00019780';
duration: 0.489 ms
statement: UPDATE t1 SET f3=f3+1 WHERE f1=21 AND f2=29;
duration: 31.646 ms
statement: UPDATE t1 SET f3=f3+1 WHERE f1=21 AND f2=29;
duration: 14.880 ms
statement: UPDATE t1 SET f3=f3+1 WHERE f1=21 AND f2=29;
duration: 0.833 ms
statement: UPDATE t1 SET f3=f3+1 WHERE (f1,f2)=(21,29);
duration: 20.654 ms
statement: UPDATE t1 SET f3=f3+1 WHERE (f1,f2)=(21,29);
duration: 0.825 ms
statement: UPDATE t1 SET f3=f3+1 WHERE (f1,f2)=(21,29);
duration: 31.404 ms
----------------------------------------------------------------------

　なぜこれほど処理時間に違いが出てくるのでしょうか。


======================================================================
河本陽一(こうもとよういち)
mailto:komoto.yoichi ＠ kcc.co.jp


test.sql
----------------------------------------------------------------------
DROP TABLE t2;
DROP TABLE t1;

CREATE TABLE t1 (
	f1		int,
	f2		int,
	f3		int,
	PRIMARY KEY (f1, f2)
);
GRANT SELECT,INSERT,UPDATE,DELETE ON t1 TO youichi;

CREATE TABLE t2 (
	f1		int,
	f2		int,
	f4		int,
	f5		text UNIQUE,
	FOREIGN KEY (f1, f2) REFERENCES t1,
	PRIMARY KEY (f1, f2, f4)
);
GRANT SELECT,INSERT,UPDATE,DELETE ON t2 TO youichi;
----------------------------------------------------------------------

benchdb.pl
----------------------------------------------------------------------
#!/usr/bin/perl

use strict;
use Benchmark;
use Pg;

my $DBNAME			= 'test';
my $DBMAX1			= 30;
my $DBMAX2			= 30;
my $DBMAX3			= 30;
my $BLOOP			= 500;
my $FMT_VAL			= 'text%08d';

&main($ARGV[0]);

#############################################################################
sub main($)
{
	my($mode) = @_;
	my $ret;

	if($mode eq 'make'){
		$ret = &make();
	}
	elsif($mode eq 'bench'){
		timeit(1, sub {&bench(1)}); #準備運動
		my $time;
		$time = timeit(5, sub {&bench(1)});
		print("bench1:".timestr($time)."\n");
		$time = timeit(5, sub {&bench(2)});
		print("bench2:".timestr($time)."\n");
		$ret = 0;
	}
	else{
		$ret = "No option";
	}

	print "RET=[$ret]\n";
}

#############################################################################
sub make()
{
	my $sql;

	my $db = Pg::connectdb("dbname=$DBNAME");
	if($db->status != PGRES_CONNECTION_OK){
		return 1;
	}
	
	my $cnt = 0;
	for(my $i = 0; $i < $DBMAX1; $i++){
		for(my $j = 0; $j < $DBMAX2; $j++){
			if(&SqlExe($db, "INSERT INTO t1 VALUES ($i, $j, 0)")){
				return 1;
			}
			for(my $k = 0; $k < $DBMAX3; $k++){
				my $val = sprintf($FMT_VAL, $cnt);
				$cnt++;
				if(&SqlExe($db, "INSERT INTO t2 VALUES ($i, $j, $k,'$val')")){
					return 1;
				}
			}
		}
	}
	printf("db=%d.\n", $DBMAX1 * $DBMAX2 * $DBMAX3);

	return 0;
}

#############################################################################
sub bench()
{
	my($type) = @_;

	my $db = Pg::connectdb("dbname=$DBNAME");
	if($db->status != PGRES_CONNECTION_OK){
		print "db open error.\n";
		return 1;
	}

	my $sqlfmt = "UPDATE t1 SET f3=f3+1 ";
	if($type == 1){
		$sqlfmt .= "FROM t2 WHERE t1.f1=t2.f1 AND t1.f2=t2.f2 AND t2.f5='%s'";
	}
	elsif($type == 2){
		$sqlfmt .= "WHERE (f1,f2)=(SELECT f1,f2 FROM t2 WHERE f5='%s')";
	}
	else{
		return 1;
	}
#	srand(12345);
	my $max = $DBMAX1 * $DBMAX2 * $DBMAX3;
	for(my $i = 0; $i < $BLOOP; $i++){
		my $val = sprintf($FMT_VAL, int(rand() * $max));
		if(&SqlExe($db, sprintf($sqlfmt, $val))){
			return 1;
		}
	}

	return 0;
}

#############################################################################
sub SqlExe($$)
{
	my($db,$sql) = @_;

	my $res = $db->exec($sql);
	if($res->resultStatus == PGRES_COMMAND_OK){
		return 0;
	}
	print $db->errorMessage;
	return 1;
}
----------------------------------------------------------------------