正しいインデックスを作成する - EatSmartシステム部ブログ

今回は、クチコミ検索のインデックスを設計するうえで失敗した話です

もぐナビでは商品のクチコミを集めています新たな機能としてクチコミ一覧ページて"追記順"でソートすることになりましたこの時に作成したインデックスが期待したように動かなかったことと、どのように改修したかを書きます

クチコミテーブルは以下のような構成です作成日時がcreated_atで、追記日時がupdated_atになります

Table "public.user_comment"
            Column            |            Type             | Modifiers
------------------------------+-----------------------------+-----------
 user_comment_id              | numeric(15,0)               |
 user_id                      | numeric(15,0)               |
 food_id                      | numeric(13,0)               |
 comment                      | character varying(4000)     |
 created_at                   | timestamp without time zone |
 updated_at                   | integer                     |

作成日時が新しい順序でソートするため、以下のインデックスが存在していました同時に作られる可能性を考慮して、このテーブルの主キー(user_comment_id)も指定されています

CREATE INDEX idx1 ON user_comment USING btree (created_at, user_comment_id);

実行計画を確認すると以下のようになり、期待した動作をしています

mognavi=> EXPLAIN SELECT * FROM user_comment ORDER BY created_at DESC, user_comment_id DESC OFFSET 0 LIMIT 20;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..4.59 rows=20 width=723)
   ->  Index Scan Backward using idx1 on user_comment  (cost=0.42..71396.56 rows=342890 width=723)
(2 rows)

ここに、あらたに更新日時でソートするためのインデックスを作成します注意点として、更新日時は作成日時と違いnullになる可能性があります nullの扱いに関しては別の記事にあるように、注意が必要です

eatsmart.hatenablog.com

そこで、既存のインデックスを参考に以下のようなインデックスを作成しました

CREATE INDEX idx2 ON user_comment USING btree (updated_at, user_comment_id);

実行計画を確認すると以下のようになり、期待した動作をしていません

mognavi=> EXPLAIN SELECT * FROM user_comment ORDER BY updated_at DESC NULLS LAST, user_comment_id DESC OFFSET 0 LIMIT 20;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Limit  (cost=52899.61..52899.66 rows=20 width=22)
   ->  Sort  (cost=52899.61..53756.12 rows=342604 width=22)
         Sort Key: updated_at, user_comment_id
         ->  Seq Scan on user_comment  (cost=0.00..43783.04 rows=342604 width=22)
(4 rows)

そこで、ドキュメントを確認しました

www.postgresql.jp

何も指定せずインデックスを作成した場合、"ORDER BY x ASC NULLS LAST"と"ORDER BY x DESC NULLS FIRST"に対して適用されるようです作成日時でのソートでは"NULLS LAST"を指定していないので、この条件に合致しますそこで今回は、以下のように明示してみました

DOP INDEX idx2;
CREATE INDEX idx2 ON user_comment USING btree (updated_at DESC NULLS LAST, user_comment_id DESC);

実行計画を確認すると以下のようになり、期待した動作をしています

mognavi=> EXPLAIN SELECT * FROM user_comment ORDER BY updated_at DESC NULLS LAST, user_comment_id DESC OFFSET 0 LIMIT 20;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..4.37 rows=20 width=725)
   ->  Index Scan using idx1 on user_comment  (cost=0.42..67649.59 rows=342487 width=725)
(2 rows)

まとめ

データベースのディスクにSSDを利用するようになってから、インデックスが利用されなくてもある程度のパフォーマンスが出るようになりましたこのようなミスが起こらないよう、実装時は実行計画を確認する、運用時はテーブルスキャンを把握する、などの対策が必要でした