タイトルの通りですが、スロークエリの調査をしていて遭遇したので備忘のためのメモ
確認結果は途中の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定義・設計がイマイチか・・・?)