スピード対決 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);
}