読者です 読者をやめる 読者になる 読者になる

アルパカDiary Pro

はてなブログProではありません

日付データ格納型に関するベンチマーク

Postgres

自分も常々気になっていた

2chにこんな話があったので試してみた。

865 :NAME IS NULL:2009/09/18(金) 18:15:56 ID:???
頻繁に日付の完全一致検索を行うのですが、
その場合、データ型は date, text, int のどれが速いとかありますか?

text/int なら 20090918 と登録して検索する、ということです。


866 :NAME IS NULL:2009/09/19(土) 00:58:57 ID:???
>>865
素直にdateで良いと思う。データサイズはintと同じ4byteだし、日付関数も使える。
intのほうが日付のパースをスキップできる関係で、入出力は若干速いとは思うけど、
そこにこだわるくらいなら、チューニングすべきものは他にあると思う。
んで、たぶんtextは一番効率が悪い。


867 :NAME IS NULL:2009/09/19(土) 08:23:46 ID:???
>>866
なるほど!すごく勉強になりました。
そうか、サイズもあるのですね。
ありがとうございました。


868 :NAME IS NULL:2009/09/19(土) 17:17:45 ID:???
数値型で最高速はinteger
文字列型で最高速はtext
そのうえでinteger>textなのは確かなんだが
dateはどうなんだろうね


869 :NAME IS NULL:2009/09/20(日) 12:16:29 ID:???
たぶん integer > date > text。date は内部的には integer と同じなので、比較や計算の速度は
整数と同じと考えていい。文字列から/への変換は整数よりは複雑だろうから、そこは遅いのでは。


870 :NAME IS NULL:2009/09/20(日) 12:21:23 ID:???
100万件くらいで計測してみた、とかあったらブクマする

http://pc11.2ch.net/test/read.cgi/db/1224318817/l50

実行環境

こちらベンチマークで使用したデータベースを使いました。
バージョンはpostgres8.3.7です。

テスト方法

  • テキスト型の日付テーブル
  • DATE型の日付テーブル
  • TIMESTAMP型の日付テーブル
  • INTEGER型の日付テーブル

を1テーブルに辺り600万件追加し、

  • 完全一致検索(col = '20090131')
  • 範囲検索(col between '20090701' and '20090731')

ベンチマークを取ってみました。
ベンチマークはexplain analyzeで1つのSQLを5回実行し、
その内最初の1回を除いた4回の平均を取ってみました。

結果

準備で使ったSQLこちら
インデックスなしの結果ログはこちら
インデックスありの結果ログはこちら


インデックスなし

完全一致検索 範囲検索
text 1800ms 3100ms
date 1320ms 2250ms
timestamp 2650ms 4000ms
integer 1310ms 3050ms


インデックスあり

完全一致検索 範囲検索
text 53ms 2580ms
date 58ms 1950ms
timestamp 85ms 4100ms
integer 73ms 3227ms

考察

上記結果より、年月日を格納するときはDATE型のパフォーマンスが良さそうです。
意外だったのがtimestamp型とintegerの遅さ
timestampはtextより遅い?やはりキャストのオーバーヘッドがあるんでしょうか?
integerが遅いのはようわかりません。


とはいえ、型による違いは思っていたほど大きくはなさそうです。
好みで使っても問題なさそうですね。

おまけ:テストデータ作成スクリプト

http://toritori0318.sakura.ne.jp/pgsql_test/testdate.pl
2008/1/1を起点にし、そこからランダムで1年分(365日)のデータを追加しています。
1回の実行につき100万件作成されますので、1日当たりの平均は2739件くらいになっているはず。
適当に作ったので、コメント付けたり外したりでテーブル毎のテストデータ作ってます。
引数で処理変えてもよかったけど、たいしたスクリプトじゃないし、面倒だったので...。