関数を使った条件はインデックスが効かない！ Timestamp型を時間だけで検索するときにインデックスを有効にする方法

こんにちは。広報担当の横尾です。

今回はエンジニアらしく、業務中に困った内容のご紹介をしたいと思います。

わたしは電力事業のシステム開発を担当しており、電力使用量や発電電力量などを1分値データとして取得して扱うことが多いです。その際、データベースの1分値テーブルの日時カラムにインデックスを作成し、検索コストを下げるようにしています。

……が、インデックスを作成したTimestamp型の日時カラムに対して、時間だけを条件として検索するとインデックスが効きません。レコード数が多い1分値を扱うテーブルだけに、インデックスは効いてほしいので、原因と対策を調べてみました。

開発環境

PostgreSQL

9.5.5

テーブルの構成

テーブル名：data_1

上記のテーブルに、約10万件のレコードを挿入した状態で、試してみます。

一般的なインデックスを使用した結果

従来のインデックスを、timestamp型のカラム、date_tmeに作成し、15時から18時の1分値を取得するSQLを実行したときの、検索コストを調べてみました。

インデックスの作成

CREATE INDEX data_1_index 
ON data_1 (date_time);

実行SQL

explain analyze 
select dt.date_time
from data_1 dt 
where to_char(dt.date_time, 'HH24MI') between '1500' and '1800'

実行結果

QUERY PLAN
Limit  (cost=0.00..3.26 rows=1 width=8) (actual time=0.081..0.081 rows=0 loops=1)
  ->  Seq Scan on data_1 dt  (cost=0.00..3.26 rows=1 width=8) (actual time=0.078..0.078 rows=0 loops=1)
        Filter: ((to_char(date_time, 'HH24MI'::text) >= '1500'::text) AND (to_char(date_time, 'HH24MI'::text) <= '1800'::text))
        Rows Removed by Filter: 13
Planning time: 2.042 ms
Execution time: 76.632 ms

タイムスタンプ型のdate_timeに対して、to_charを用いて、時刻を文字列として扱い検索する方法だと、作成したdate_timeのインデックスは使用せずに検索を実行するようです。１分値という、データ数がかなり多くなることが想定されるテーブルで、インデックスが使用されないのは、死活問題です。

関数インデックスを使用した結果

今度は関数インデックスを使用して、関数の結果に対してインデックスを作成しておく方法を試してみます。

関数（ストアド）作成

CREATE FUNCTION formatHH24MI(prm timestamp) RETURNS text
    LANGUAGE sql 
    IMMUTABLE STRICT 
    AS $$
        SELECT to_char($1, 'HH24MI')
    $$

インデックスを作成

CREATE INDEX index_date_time_hh24mi
on data_1(formatHH24MI(date_time))

実行SQL

explain analyze
select dt.date_time
from data_1 dt
where formatHH24MI(dt.date_time) between '1500' and '1800'

実行結果

QUERY PLAN
Limit  (cost=0.64..8.65 rows=1 width=8) (actual time=0.021..0.021 rows=0 loops=1)
  ->  Index Scan using index_date_time_hh24mi on data_1 dt  (cost=0.64..8.65 rows=1 width=8) (actual time=0.020..0.020 rows=0 loops=1)
        Index Cond: ((formathh24mi(date_time) >= '1500'::text) AND (formathh24mi(date_time) <= '1800'::text))
Planning time: 0.943 ms
Execution time: 3.266 ms

今回は２行目に、Index Scan using index_date_time_hh24miがしっかり入っているので、作成したインデックスが使用されていることが確認できました。また検索コストも下がっていることがわかります。

まとめ

タイムスタンプ型を使用した際の検索には、今まで苦しんできたので今回のインデックスの存在を知れて、ほんとによかったです。

この方法なら、タイムスタンプ型だけではなく、他の条件でも使用できるので、いろいろ試してみたいと思います。