はじめに

最近クエリチューニングを行ないました。
その際調べてもMySQLに比べると情報が少ないなと感じたので、メモとして残しておきたいと思いました。

スロークエリ→負荷の高いクエリの特定→検証用の環境でチューニングという流れで行いました。

どんな状況だったか

ざっくりとこんなクエリが流れていたのですが、検証用の環境でこのクエリを投げてみたら下記のような感じでした。
1クエリで9秒くらいかかっている・・・

explain analyze select * from target_table
where id = 1 and target > 0 and delete_flg = 0
order by date desc limit 10 offset 0;
                                                                 QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------
---------------------
Limit  (cost=5274.83..5274.88 rows=10 width=194) (actual time=9256.846..9256.856 rows=10 loops=1)
   ->  Sort  (cost=5274.83..5278.10 rows=1308 width=194) (actual time=9256.844..9256.848 rows=10 loops=1)
         Sort Key: (date)
         Sort Method: top-N heapsort  Memory: 30kB
         ->  Bitmap Heap Scan on target_table  (cost=26.78..5240.03 rows=1308 width=194) (actual time=377.452..9251.68
1 rows=5597 loops=1)
               Recheck Cond: (id = 1)
               Filter: ((target > 0) AND (delete_flg = 0))
               Rows Removed by Filter: 91
               ->  Bitmap Index Scan on idx_id  (cost=0.00..26.45 rows=1318 width=0) (actual time=376.085..376.08
5 rows=5688 loops=1)
                     Index Cond: (id = 1)
 Total runtime: 9256.909 ms
(11 行)

インデックスは使われているようですが、テーブルからデータを取得してくるところで時間がかかっていそうでした。
実際にはsortしたデータの中から10件取得をすれば良いだけなので、どうにかならないかとPostgreSQLのドキュメントを読んでいたらこんなことが書いてありました。
https://www.postgresql.jp/document/9.3/html/indexes-ordering.html

11.4. インデックスとORDER BY
「ORDER BYとLIMIT nが組み合わされた場合が、重要かつ特別です。先頭のn行を識別するために、明示的なソートを全データに対して行う必要があります。しかし、もしORDER BYに合うインデックスが存在すれば、残りの部分をスキャンすることなく、先頭のn行の取り出しを直接行うことができます。」

実際にチューニングする

ドキュメントにあった通り、ORDER BYに合うインデックスを作成してテーブル全体をスキャンさせないようにします。
また、where句で指定されているカラムと複合インデックスにしたかったので、一旦クエリがこのままで良いのか見直します。

クエリの変更

そもそもdelete_flgは使用されておらず、offset0も必要がない、target > 0も仕様的に削除して問題がなかったので削除しました。

explain analyze select * from target_table where id = 1 order by date desc limit 10;

インデックスの変更

次にインデックスを変更していきます。

CREATE INDEX test_idx ON target_table(id, date DESC);

explain analyze select * from target_table
where id = 1 order by date desc limit 10;
                                                                     QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------
------------------------------
 Limit  (cost=0.56..81.34 rows=10 width=194) (actual time=0.048..0.204 rows=10 loops=1)
   ->  Index Scan using test_idx on target_table  (cost=0.56..5323.61 rows=1318 width=194) (actual time=
0.047..0.196 rows=10 loops=1)
         Index Cond: (id = 1)
 Total runtime: 0.229 ms
(4 行)

早くなりました。実行計画を見ると余計なfilterとsortがなくなっています。
(キャッシュの影響で実行時間が短くならないように、実際にはidを変更しています。)

ORDER BYに合うインデックスが存在したので、残りの部分をスキャンすることなく、先頭のn行の取り出しを直接行ったのでしょう。
LimitとIndex Scanの初期コストが同じく0.56ですが、トータルコストはLimitが81.34に対してIndex Scanが5323.1となっています。

インデックスにNULLS LASTを指定していたらできなかった

CREATE INDEX test_idx ON target_table(id, date DESC NULLS LAST);

最初はNULLS LASTを設定したのですが、explain文を見てもsortを省略することができませんでした。
クエリがorder by date descなのに対して、インデックスにNULLS LASTが指定されていたため
インデックスとorder byの指定が揃っていないと判断されてしまった可能性があります。

まとめ

order byとlimitがクエリに含まれている場合には、order byに合うインデックスを作成することで、limitで指定をした数以上のスキャンすることがなくなります。

PostgreSQLでorder byとlimitが含まれたクエリのチューニングを行う