AMG Solution

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

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

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

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

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

開発環境

PostgreSQL
9.5.5

テーブルの構成

テーブル名:data_1

カラム名 論理名 データ型
id ID bigint
date_time データ日時 timestamp
value numeric

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

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

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

インデックスの作成

実行SQL

実行結果

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

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

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

関数(ストアド)作成

インデックスを作成

実行SQL

実行結果

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

まとめ

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

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

YOKOO'S BLOG

横尾有美の記事

横尾有美の記事の最新情報をお届けいたします。

SAME CATEGORY BLOG

この記事と同様のカテゴリー記事

LOADING