PostgreSQLのチューニング記録(関数INDEX)
はじめに
データベースにPostgreSQLを使用したシステム開発を行っています。
一覧画面から、ある条件で検索を実施するとタイムアウトとなり、データが取得できないとのIssueが上がってきたため調査することとなりました。
現状把握
現在開発中のシステムでは、検索条件に年月(2024-12など)を指定すると、対象月のデータを取得するという機能があります。その検索条件を設定した際にタイムアウトになってしまうことが確認できました。
早速クエリを確認すると、上記検索に対して以下のSQLが発行されていることが確認できました。
EXTRACT(YEAR FROM orders.actual_delivery) = 2024 AND EXTRACT(MONTH FROM orders.actual_delivery) IN (11, 12)
actual_deliveryカラムはtimestamp型で、INDEXが付与されていました。
しかし実行計画(EXPLAIN ANALYZE)を確認したところ、INDEXが使われていないことがわかりました。
解決方法
EXTRACTなど関数を使った検索の場合、該当のカラムにINDEXが付与されていたとしても、効果がありません。そのため、以下2つの解決方法が検討できました。
1.関数INDEXを使用する
PostgreSQLには関数INDEXというものが存在し、クエリ内で使用する関数をそのままINDEXとして追加することで、機能するというものでした。
以下のようにINDEXを追加することで、6分かかっていたクエリが、1秒に短縮されました。
CREATE INDEX idx_extract_year_month on orders (EXTRACT(YEAR FROM orders.actual_delivery), EXTRACT(MONTH FROM orders.actual_delivery))
参考資料：https://www.postgresql.jp/document/7.2/user/indexes-functional.html
2. クエリを修正する
EXTRACTの関数を使用せず、以下のように範囲検索にすることで、クエリ実行時間の短縮ができました。
orders.actual_delivery >= xxxx AND orders.actual_delivery < (xxxx::date + interval '1 month'))
まとめ
INDEXが貼られていても、正しく機能しているか？を疑うことが大事ということが再認識できました。
おまけ
これらの調査の中で、PostgreSQLには範囲型という型があることを知りました。
予約機能とかで使うと便利なのかな〜と思いました。
Discussion