日付データ格納型に関するベンチマーク
自分も常々気になっていた
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:???
http://pc11.2ch.net/test/read.cgi/db/1224318817/l50
100万件くらいで計測してみた、とかあったらブクマする
テスト方法
- テキスト型の日付テーブル
- DATE型の日付テーブル
- TIMESTAMP型の日付テーブル
- INTEGER型の日付テーブル
を１テーブルに辺り600万件追加し、
- 完全一致検索(col = '20090131')
- 範囲検索(col between '20090701' and '20090731')
のベンチマークを取ってみました。
ベンチマークはexplain analyzeで１つのSQLを５回実行し、
その内最初の１回を除いた４回の平均を取ってみました。
結果
準備で使った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を起点にし、そこからランダムで１年分（365日）のデータを追加しています。
１回の実行につき100万件作成されますので、１日当たりの平均は2739件くらいになっているはず。
適当に作ったので、コメント付けたり外したりでテーブル毎のテストデータ作ってます。
引数で処理変えてもよかったけど、たいしたスクリプトじゃないし、面倒だったので...。
