ひさびさの投稿になります。
少し前に仕事の関係でDBの検索の日付検索における性能劣化があり、どの形式での記述が実行速度が早いのか気になり、実際に行い実行結果をまとめました。
使用したDBは標題の通りPostgreSQL、バージョンは9.4を使用しました。
背景として日付の検索はいろいろなSQLの書き方があるが、どの方法が一番早く検索されるのか確かめたことがなかったので、今回思いつく数パターンの方法で確かめました。
ひとまず１秒間隔の時間を過去5年分のデータを作りました。
レコード数が1億5千万件以上のテストデータを準備したのは流石に久しぶりです。そこから2019/4/30のデータを抽出してみることととする。
[テーブル定義]
CREATE TABLE test_table_20190501
(
time timestamp with time zone DEFAULT now(),
no_1 integer DEFAULT nextval(‘シーケンス名’::regclass)
)
時間を保存するカラムと数値を保存するカラムの２カラムだけの単純なテーブルです。
１）最も時間がかかった検索方法はこれ。
time >to_timestamp(‘20190430000000′,’yyyymmddhh24miss’) and time <to_timestamp(‘20190430235959′,’yyyymmddhh24miss’)
実行時間はこれ
421773ms
526579ms
558849ms
500056ms
529945ms
２）ここから日付の指定を短くしてみる
time >to_timestamp(‘20190430′,’yyyymmdd’) and time <to_timestamp(‘20190501′,’yyyymmdd’)
結果はこれ
241299ms
289130ms
271331ms
278553ms
273838ms
だいたい半分ぐらいになった。ちなみに私がよく検索で使っていたのはこの形式でした。
３）日付型で比較していたが、文字列に変えて比較してみる
to_char(time,’yyyymmdd’)=’20190430′
結果はこれ
229973ms
227100ms
222459ms
309312ms
224336ms
日付型の比較から負荷にもよるかもしれませんがだいたい60sぐらい短くなった。
４）between使って見る
time between ‘20190430’ and ‘20190501’;
結果はこれ
93205ms
137047ms
149337ms
192002ms
127179ms
3)より60s~100s短くなった
５）単純に条件をandにしてみる
time >’20190430′ and time <‘20190501′
結果はこれ
66803ms
69137ms
65424ms
68306ms
66339ms
4)よりさらに60s程度短くなった。betweenより短くなると思わなかった。1)と比較すると1/8になる結果
６）条件をちょっと変えてみる
time >’2019-04-30 00:00:00’ and time <‘2019-05-01 00:00:00’
結果はこれ
64799ms
72942ms
71403ms
68298ms
66261ms
これは5)とあまり変わらない結果でした。
・まとめ
検索結果の速度は以下の順位
5) = 6) > 4) > 3) >2) >> 1)
この結果からSQLで日付で絞り込む場合の方針は以下の通り
。to_timestamp(‘20190430000000′,’yyyymmddhh24miss’)の指定は使わない（時間指定がある場合だけ使う）
・betweenよりandで行ったほうが速度は早い
・速度から考えるとtime >’20190430′ and time <‘20190501’や
time >’2019-04-30 00:00:00’ and time <‘2019-05-01 00:00:00’の記述を使用するのがよい。（両者の実行速度に差はない）
今回できれば式インデックスを使って検索する速度が早くなるかを確かめようとしたのですが、準備した環境の容量の問題と時間的な問題でできませんでした。
すでに５年分のデータで、テーブルサイズが8G弱あり、それに3の形式変換の関数を作りそれを適用した式インデックスを作成してみた。
[関数]
CREATE FUNCTION formatyyyymmdd(prm timestamp with time zone ) RETURNS text
LANGUAGE sql
IMMUTABLE STRICT
AS $$
SELECT to_char($1, ‘yyyymmdd’)
$$
[インデックス]
CREATE INDEX index_date_time_yyyymmmdd
on test_table_20190501(formatyyyymmdd(time))
結果、1億5千万レコードあるテーブルにインデックスを追加していると、容量が８G以上になり、２時間たってもCREATE INDEX文が返ってこなかったので今回は諦めました。
こんな書き方もあるのでは、といったコメントいただけると嬉しいです。
以上