この記事の要約
- SQLのWHERE節に
to_char(hoge_datetime, 'YYYY-MM-DD') = '2021-09-25'みたいな抽出条件を書くのはやめよう。性能がヤバくなるので。 - 性能が良いSQLを書くためにUse The Index, Lukeを読もう。
最低限、↓に書いてある2つのSQLをちょっとでも眺めてからブラウザバックしていただければ私は満足です。
OK（to_charを使わない）
SELECT
id
FROM
articles
WHERE
-- 投稿日時が2021-09-25のレコードを抽出
'2021-09-25T00:00:00' <= post_datetime and post_datetime < '2021-09-26T00:00:00'
;
NG（列に対してto_charを適用してる）
SELECT
id
FROM
articles
WHERE
-- 投稿日時が2021-09-25のレコードを抽出
to_char(post_datetime, 'YYYY-MM-DD') = '2021-09-25'
;
性能を比較してみる
環境
PostgreSQL 13.4
事前準備
テーブル
テーブル名：articles
| No. | 論理名 | 物理名 | 型 | 備考 |
|---|---|---|---|---|
| 1 | ID | id | BIGSERIAL | PK |
| 2 | 投稿日時 | post_datetime | TIMESTAMP | インデックスあり(btree) |
DDL
CREATE TABLE articles (
id BIGSERIAL
, post_datetime TIMESTAMPTZ NOT NULL
, CONSTRAINT articles_key PRIMARY KEY (id)
);
CREATE INDEX ON articles USING btree (post_datetime);
手順
投入データ
以下の要領でレコードをテーブルに投入する。
| 条件 | 値 |
|---|---|
| レコード数（概数） | 365 or 36,500 or 3,650,000 or 36,500,000 or 365,000,000 |
| 投稿日時の最小値 | 2021-01-01T00:00:00 |
| 投稿日時の最大値 | 2022-01-01T00:00:00 |
| 投稿日時の分布 | 最小値～最大値の間で一様に分布 |
計測方法
OK/NGのそれぞれのSQLについて、EXPLAINコマンドにANALYZEオプションをつけて実行し、実際のSQLの実行時間を計測する。
EXPLAINコマンドについては公式ドキュメントを参照すること。
OK(to_charを使ってない)
explain analyze
SELECT
id
FROM
articles
WHERE
-- 投稿日時が2021-09-25のレコードを抽出
'2021-09-25T00:00:00' <= post_datetime and post_datetime < '2021-09-26T00:00:00'
;
NG(to_charを使ってる)
explain analyze
SELECT
id
FROM
articles
WHERE
-- 投稿日時が2021-09-25のレコードを抽出
to_char(post_datetime, 'YYYY-MM-DD') = '2021-09-25'
;
計測結果
| レコード数（件） | OK（ミリ秒） | NG（ミリ秒） |
|---|---|---|
| 365 | 0.017 | 0.151 |
| 36,500 | 0.032 | 14.107 |
| 3,650,000 | 1.361 | 536.992 |
| 36,500,000 | 18.501 | 5,026.952 |
| 365,000,000 | 117.897 | 187,546.418 |
レコードが増えれば増えるほどヤバいことになるのがおわかりいただけただろうか。
どうしてここまで差がつくのか
EXPLAINコマンドで出力される実行計画を比較してみる。
OK(to_charを使ってない)
Index Scan using articles_post_datetime_idx on articles (cost=0.28..8.48 rows=10 width=8)
Index Cond: (('2021-09-25 00:00:00+09'::timestamp with time zone <= post_datetime) AND (post_datetime < '2021-09-26 00:00:00+09'::timestamp with time zone))
Index Scanをしてる。
（Index Scan……インデックスを使って検索するってこと）
NG(to_charを使ってる)
Seq Scan on articles (cost=0.00..74.77 rows=18 width=8)
Filter: (to_char(post_datetime, 'YYYY-MM-DD'::text) = '2021-09-25'::text)
Seq Scanをしてる。
（Seq Scan……テーブル内の全てのレコードを読み込んで検索するってこと）
NGパターンのSQLが実行時にインデックスが利用されない理由はこちらを参照。
Index ScanとかSeq Scanとかの説明はこちらを参照。
補足
to_charを使っても問題ないケース
式に対するインデックスが作成されている
式に対するインデックスを作成しておけばNGパターンのSQLでもインデックスを利用して検索できるらしい。（実験はしてない）
そもそも性能が必要ない
- 頻繁には実行しないとき（保守運用時のデータの調査とかテスト時のデータの検証とか）
- テーブル内のレコードが少ないとき
ぶっちゃけた話
こちらに書いてある内容を理解している人であれば、今回のNGパターンのようなSQLを書いてしまうことは絶対にありえない。
じゃあなんでこの記事を書いたの？
- 隣のチームが開発したアプリの中で動いてるSQLがこの記事のNGのパターンだった。
- どうやら
SQL 特定の日付 抽出でググるとこの記事のNGのパターンが結構出てくるらしく、それを真似したっぽい。 - 「こんな書き方をしたらインデックスが利かないじゃん！」と気付いて欲しいけど、そもそもインデックスの存在自体を知らない人もいる。
- そういう人が
SQL 特定の日付 抽出でググってこの記事にたどり着いて、この記事をきっかけにインデックスを意識してSQLを書くようになってくれたらいいなあ。
おすすめサイト
- Use The Index, Luke このサイトを知ってもらうためだけにこの記事は存在すると言っても過言ではない。