yu/logs/*

技術メモ　など

PostgreSQL to_char()で日付加工してるところをdate_trunc()に修正したらクエリのパフォーマンスが改善した

PostgreSQL

タイトルの通りですが、スロークエリの調査をしていて遭遇したので備忘のためのメモ

確認結果は途中のSQL Fiddleのリンクに置いてあります

修正前

修正前のテーブル・クエリは↓のような感じ

/* 
    測定値が収まっているテーブル
    日間粒度でデータ登録
 */
create table records(
  created_at timestamp
  , value integer
);

/* 
    上限値・下限値が収まっているテーブル
    月間粒度でデータ登録(monthに文字列として'2022/06'のような値を登録)
 */
create table limits( 
  month text
  , upper_limit integer
  , lower_limit integer
); 

/*
    月ごとに上下限値が決まるため、
    created_at側をto_char
*/
select * from   records r 
  left join limits l 
    on to_char(created_at, 'yyyy/MM') = month
;

http://sqlfiddle.com/#!17/f4ae07/3

SQL Fiddleでexplain analyzeの結果を確認すると Execution time: 0.215 ms 程度となった。

（実際はレコード数が多かったりなんだりでもっと時間が掛かって困っていた）

修正後

修正後のテーブル・クエリは↓のような感じ

/* 
    測定値が収まっているテーブル
    日間粒度でデータ登録
 */
create table records(
  created_at timestamp
  , value integer
);

/* 
    上限値・下限値が収まっているテーブル
    月間粒度でデータ登録
 */
create table limits( 
  month timestamp /* text -> timestampに型を変更 */
  , upper_limit integer
  , lower_limit integer
); 

/*
    月ごとに上下限値が決まるため、
    両テーブルの日付カラムに対してdate_truncする
*/
select * from   records r 
  left join limits l 
    on date_trunc('month', created_at) = date_trunc('month', month)
    /* ↑修正箇所。to_charをやめてdate_truncにした */
;

http://sqlfiddle.com/#!17/3efe5/1

SQL Fiddleでexplain analyzeの結果を確認すると Execution time: 0.096 ms 程度となった。

個人的なまとめ

  • 文字列型で扱うよりも日付型で扱った方がパフォーマンス的にはよいという話かな・・・？
  • date_trunc() で困らないようであれば、基本はこちらを使った方がよさそうか
  • (そもそも結合のキーに関数かませてるのはDB定義・設計がイマイチか・・・？)