スピード対決 TIMESTAMP vs INTEGER
時間のカラムに TIMESTAMP を使って、PostgreSQL (8.3.7) に時間を計算させるのと、INTEGER でエポックを入れておいて、perl 側で時間計算をして SELECT するのと、どちらが速いのかふと気になってベンチマークを取ってみました。
10万件のランダムな日時なレコードを生成して、インデックスを張り、ランダムに30日間の時間幅のレコードを取得してみました。その結果。
Benchmark: timing 10000 iterations of INTEGER, TIMESTAMP... INTEGER: 4 wallclock secs ( 0.58 usr + 0.03 sys = 0.61 CPU) @ 16393.44/s (n=10000) TIMESTAMP: 6 wallclock secs ( 0.67 usr + 0.04 sys = 0.71 CPU) @ 14084.51/s (n=10000) Rate TIMESTAMP INTEGER TIMESTAMP 14085/s -- -14% INTEGER 16393/s 16% --
予想通り INTEGER の方が速かったのですが、TIMESTAMP とそこまで差が出ていないのが予想外でした。
TIMESTAMP 型は、エポックから計算するのは厳しい、誕生日が何月生まれの人など指定して SELECT できるので、このスピード差なら常に TIMESTAMP 型で充分ですね。
ベンチマークコードは以下です。
#!/usr/bin/perl use strict; use warnings; use Benchmark ':all'; use DBI; $| = 1; my $dbh = DBI->connect('dbi:Pg:dbname=test'); $dbh->do('CREATE TABLE table1 ( time TIMESTAMP )'); $dbh->do('CREATE TABLE table2 ( time INTEGER )'); $dbh->do('CREATE INDEX i1 ON table1 ( time )'); $dbh->do('CREATE INDEX i2 ON table2 ( time )'); insert($_) for ( 1 .. 100 ); my $sth1 = $dbh->prepare('SELECT * FROM table1 WHERE time BETWEEN ? AND ?'); my $sth2 = $dbh->prepare('SELECT * FROM table2 WHERE time BETWEEN ? AND ?'); cmpthese( timethese( 10000, { TIMESTAMP => sub { my $time = int rand 2000000000; $sth1->execute( scalar localtime $time - 3600*24*30, scalar localtime $time ); }, INTEGER => sub { my $time = int rand 2000000000; $sth2->execute( $time - 3600*24*30, $time ); }, })); $dbh->do('DROP TABLE table1'); $dbh->do('DROP TABLE table2'); sub insert { my ( @time1, @time2 ); print "\r$_[0]"; for ( 1 .. 1_000 ) { my $time = rand(2000000000); push @time1, scalar localtime $time; push @time2, $time; } my $insert1 = 'INSERT INTO table1 VALUES ' . join ',', map { "('$_')" } @time1; my $insert2 = 'INSERT INTO table2 VALUES ' . join ',', map { "($_)" } @time2; $dbh->do($insert1); $dbh->do($insert2); }