Feedforce Developer Blog

フィードフォース開発者ブログ

explain での SQL 改善検証の実例

こんにちは! id:kano-e です。 SQL って可愛いですよね!

さて、先日プロダクトコード内で ActiveRecord を使ったコードに対して改善を行い、その際にその前後で explain の結果がどのように変わるかを検証しました。

元は PR に改善内容と共にまとめたものなのですが、改善と explain のわかりやすい実例になったので公開しようと思います。

PostgreSQL 10系です。

はじめに(注意書きのようなもの)

この記事では explain の見方についての詳細な解説はありません。 explain について書いていたら、だいぶ分量が増えてしまったので割愛しました。ご了承ください。 explain の解説記事を求めている方は、文末に参考リンクを用意しましたので、ご活用ください。

以下のコードや SQL はプロダクトで使われているそのままではなく、一部改変を加えています。 改変内容は、プロダクト固有の単語から一般的な単語を使うようにテーブル名やカラム名を変更しているのと、話の本筋に不要なカラム情報について省略しているのが主なものです。

以下の explain を実行した検証用のデータベースには、本番のデータを一部マスキングした状態で投入しています。 マスキングした本番データはデータベースのバックアップから生成しているので、リアルタイムなものではありませんが、本番環境とほぼ変わらないと考えて良いものになっています。

改善対象のコード

改善対象のコードは、以下のようなものでした。

UserSetting.eager_load(:user).where(updated_at: yesterday.all_day)

UserSettingupdated_at が前日のもの、つまり前日に更新された UserSetting を対象に操作したい、というコードです。

さらに、 User 情報も必要になるため eager_load(:user) であらかじめ User の情報を読み込んでおくようにしています。 これは N+1 問題への対策ですね。

UserUserSetting の関係は User has_one :user_setting です。 ただし、 UserSetting は、特定の操作が発生してから作成されるので UserSetting を持っていない User は存在します。 User.countUserSetting.count を実行した場合、 User.count の方が多くなる状態です。

ちなみに UserSetting.count は現状で 750 件程です。 後ほど explain の結果で具体的な件数は出てきます。

また usersuser_settings テーブルは、基本的にレコード数が減ることはほとんどなく、将来的にももっとレコード数が増える見込みのテーブルです。

このコードの問題点は 2 つありました。

  • user_settings.updated_at カラムに対するインデックスが作られていない
  • eager_load が発行する left joinusers テーブルのフルスキャンが発生していると思われる

where に使われているカラムにインデックスがない

なんでもインデックスを使えば良いというものではありませんが、今回のコードの場合、インデックスが効果的である見込みがあるものです。

  • インデックスがないので user_setting テーブルに対してフルスキャンになってしまっている
  • user_setting テーブル全件に対して「昨日更新された件数」が限られている
    • 何日分か確認した感じ、多くても 10 件程度でした

という状況で、フルスキャンで 750 件から 10 件を見つけ出すよりかは、インデックスで 10 件見つける方がそりゃ早いだろう、という見込みでした。

さらには、今は 750 件程度の検索で負荷など大きな問題は発生していませんが、今後件数が増えてきたらどこかのタイミングで問題が発生するだろうと予想しました。

eager_load で全件 join してしまってる

今回の eager_load は、 N+1 問題の対策のためのものです。 確かに N+1 問題は回避できているのですが、代わりに left join を実行することによる全件 join が発生してしまっています。

SQL で joinwhere を合わせて使った場合、 where での絞り込みとは無関係に join の処理はテーブル全件に対して行われます。 つまり、今回何が問題かというと、 where の結果が 10 件であっても users テーブルに 750 件のレコードがあれば users テーブルに対する 750 件分の照合が行われます。

この 750 件というのも現時点のもので、今後増える予定の数字です。 今後テーブルのレコード数が増えていった場合、この left join も大きな問題になるだろうと予想できます。

検証

改善前の状態の explain 結果

改善前に explain を実行して、自分が問題点だと思った箇所が見込み違いでないかを確認しました。

staging::DATABASE=> explain SELECT "user_settings"."id" AS t0_r0, "user_settings"."user_id" AS t0_r1, (中略), "users"."id" AS t1_r0, (中略) FROM "user_settings" LEFT OUTER JOIN "users" ON "users"."id" = "user_settings"."user_id" WHERE "user_settings"."updated_at" BETWEEN '2019-07-23 15:00:00' AND '2019-07-24 14:59:59.999999';
                                                                                             QUERY PLAN                                                                                             
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Right Join  (cost=36.07..54.77 rows=6 width=445)
   Hash Cond: (users.id = user_settings.user_id)
   ->  Seq Scan on users  (cost=0.00..18.29 rows=763 width=127)
   ->  Hash  (cost=36.05..36.05 rows=6 width=318)
         ->  Seq Scan on user_settings  (cost=0.00..36.05 rows=6 width=318)
               Filter: ((updated_at >= '2019-07-23 15:00:00'::timestamp without time zone) AND (updated_at <= '2019-07-24 14:59:59.999999'::timestamp without time zone))
(6 rows)

ここで見るポイントは、

  • 最終的な結果は 6 行
  • Seq Scan on user_settings なので user_settings テーブルに対するフルスキャンになっている
  • Seq Scan on users で、こちらも同じく users テーブルに対するフルスキャンになっている
    • rows を見ると、ここで 763 件のレコードが join のために用意されている

辺りになります。

最終的な 6 件の結果のために 2 つのテーブルに対してフルスキャンが行われているのがわかりました。 最初に述べた問題点のポイントは間違っていなさそう、という感触を得ました。

一方、今時点ではすごく遅いというほどのクエリではありません。 コストだけ見ると、今時点で改善をしてもそこまで効果的な内容にはならなさそうです。

ただ、今回の 2 つの問題点はどちらも「テーブルのフルスキャン」が原因のものです。 テーブルのフルスキャンというのは、当然テーブルのレコード数が増えるとそのコストも増えます。

例えば今回の join 対象テーブルが 10 件か 20 件程度の件数しかなく、それ以上大きく増える見込みもないものであれば、対策を考える必要性は薄いかと思います。 何度か書いた通り、今回登場する 2 つのテーブルはどちらも件数が増える想定のものであるので、いずれはフルスキャンのコストが問題になるものと予想できます。

例えば、もっと複雑な処理でコードの改修コストが非常に高い場合やギリギリのチューニングが必要な場面であれば、より件数を増やした状態のデータを用意して検証するなど、検証にもう少し時間をかける必要も出てくると思います。 今回の場合はコードの改修コストはそれほどではないので、ひどい性能劣化がなければ、問題になっていない今のうちに対応しておいても良いだろうと考え、検証を続けました。

eager_load をやめる

さて、まずは eager_load をやめて preload を使うようにしてみました。

eager_load では left join が発行されますが、 preload の場合は最初の SQL の結果を元に合わせて取得したいテーブルへの select を別途発行します。 SQL を 2 回実行することになりますが、 join のコストが大きい場合には preload の方が結果的には速くなるでしょう。

SQL を 2 回実行しているので、 explain も両方の SQL に対して実行しています。

staging::DATABASE=> explain SELECT "user_settings".* FROM "user_settings" WHERE "user_settings"."updated_at" BETWEEN '2019-07-23 15:00:00' AND '2019-07-24 14:59:59.999999';
                                                                                       QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on user_settings  (cost=0.00..36.05 rows=6 width=318)
   Filter: ((updated_at >= '2019-07-23 15:00:00'::timestamp without time zone) AND (updated_at <= '2019-07-24 14:59:59.999999'::timestamp without time zone))
(6 rows)

staging::DATABASE=> explain select "users".* from "users" where id in ('3d952861-1ccc-4992-95f1-3ab9b1867ed7', 'f400180e-b221-4f92-b3d3-23f72170c412', '08f16292-aa9d-454b-b99b-83565a7761d0', '210343d4-5b40-49a6-abe0-cf5d5ca2a05a', '43c81134-d0c8-445f-8ea5-d43c44369453', '6e0b9588-bd0b-496d-be5e-0f3b59ee51d8');
                                                                                                                                 QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on users  (cost=8.34..16.69 rows=6 width=127)
   Recheck Cond: (id = ANY ('{3d952861-1ccc-4992-95f1-3ab9b1867ed7,f400180e-b221-4f92-b3d3-23f72170c412,08f16292-aa9d-454b-b99b-83565a7761d0,210343d4-5b40-49a6-abe0-cf5d5ca2a05a,43c81134-d0c8-445f-8ea5-d43c44369453,6e0b9588-bd0b-496d-be5e-0f3b59ee51d8}'::uuid[]))
   ->  Bitmap Index Scan on users_pkey  (cost=0.00..8.34 rows=6 width=0)
         Index Cond: (id = ANY ('{3d952861-1ccc-4992-95f1-3ab9b1867ed7,f400180e-b221-4f92-b3d3-23f72170c412,08f16292-aa9d-454b-b99b-83565a7761d0,210343d4-5b40-49a6-abe0-cf5d5ca2a05a,43c81134-d0c8-445f-8ea5-d43c44369453,6e0b9588-bd0b-496d-be5e-0f3b59ee51d8}'::uuid[]))
(4 rows)

改善前のコストと比べて、SQL を 2 回実行するコストも含めて考えると、今時点では効果のある改善とは言い難い結果です。

この辺り、対象となる users.id の件数は毎日変動するので、もしかしたらその数によって結果も変わってくるかとも思います。 より丁寧に検証をする場合は、対象の日時や users.id を変えて explain して見比べてみたりするのも良いと思います。

updated_at のインデックスを作る

次は user_settings.updated_at に対してインデックスを作成してみました。 eager_load はそのままです。

staging::DATABASE=> explain SELECT "user_settings"."id" AS t0_r0, "user_settings"."user_id" AS t0_r1, (中略), "users"."id" AS t1_r0, (中略) FROM "user_settings" LEFT OUTER JOIN "users" ON "users"."id" = "user_settings"."user_id" WHERE "user_settings"."updated_at" BETWEEN '2019-07-23 15:00:00' AND '2019-07-24 14:59:59.999999';
                                                                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Right Join  (cost=11.59..30.28 rows=6 width=445)
   Hash Cond: (users.id = user_settings.user_id)
   ->  Seq Scan on users  (cost=0.00..18.29 rows=763 width=127)
   ->  Hash  (cost=11.57..11.57 rows=6 width=318)
         ->  Bitmap Heap Scan on user_settings  (cost=2.07..11.57 rows=6 width=318)
               Recheck Cond: ((updated_at >= '2019-07-23 15:00:00'::timestamp without time zone) AND (updated_at <= '2019-07-24 14:59:59.999999'::timestamp without time zone))
               ->  Bitmap Index Scan on index_user_settings_on_updated_at  (cost=0.00..2.07 rows=6 width=0)
                     Index Cond: ((updated_at >= '2019-07-23 15:00:00'::timestamp without time zone) AND (updated_at <= '2019-07-24 14:59:59.999999'::timestamp without time zone))
(8 rows)

一番最初の改善前と比べて、コストが減っているのがわかるかと思います。 単純にインデックスを作成しただけで効果が出るので、改善の方法として良さそうに見えます。 また、今時点では微々たる数値ですが、今後件数が増えてきた時にはその改善がより大きな差になってくるでしょう。

また、レコード数が増えたテーブルに対してインデックスを追加すると、インデックス作成に時間がかかります。 通常はインデックス作成ではテーブルへの書き込みはロックされるので、あまりに時間がかかる場合には、サービスの稼働に影響が出る可能性も考慮する必要が出てきます。

その点も考慮し、レコード数が多すぎない今の時点でインデックスを作成しておくのは悪くないと判断しました。

両方対応した場合

さて、せっかくなので最後に preload に変更してインデックスも作成した状態で explain しました。

staging::DATABASE=> explain SELECT "user_settings".* FROM "user_settings" WHERE "user_settings"."updated_at" BETWEEN '2019-07-23 15:00:00' AND '2019-07-24 14:59:59.999999';
                                                                                            QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on user_settings  (cost=2.07..11.57 rows=6 width=318)
   Recheck Cond: ((updated_at >= '2019-07-23 15:00:00'::timestamp without time zone) AND (updated_at <= '2019-07-24 14:59:59.999999'::timestamp without time zone))
   ->  Bitmap Index Scan on index_user_settings_on_updated_at  (cost=0.00..2.07 rows=6 width=0)
         Index Cond: ((updated_at >= '2019-07-23 15:00:00'::timestamp without time zone) AND (updated_at <= '2019-07-24 14:59:59.999999'::timestamp without time zone))
(4 rows)

staging::DATABASE=> explain select "users".* from "users" where id in ('3d952861-1ccc-4992-95f1-3ab9b1867ed7', 'f400180e-b221-4f92-b3d3-23f72170c412', '08f16292-aa9d-454b-b99b-83565a7761d0', '210343d4-5b40-49a6-abe0-cf5d5ca2a05a', '43c81134-d0c8-445f-8ea5-d43c44369453', '6e0b9588-bd0b-496d-be5e-0f3b59ee51d8');
                                                                                                                                 QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on users  (cost=8.34..16.69 rows=6 width=127)
   Recheck Cond: (id = ANY ('{3d952861-1ccc-4992-95f1-3ab9b1867ed7,f400180e-b221-4f92-b3d3-23f72170c412,08f16292-aa9d-454b-b99b-83565a7761d0,210343d4-5b40-49a6-abe0-cf5d5ca2a05a,43c81134-d0c8-445f-8ea5-d43c44369453,6e0b9588-bd0b-496d-be5e-0f3b59ee51d8}'::uuid[]))
   ->  Bitmap Index Scan on users_pkey  (cost=0.00..8.34 rows=6 width=0)
         Index Cond: (id = ANY ('{3d952861-1ccc-4992-95f1-3ab9b1867ed7,f400180e-b221-4f92-b3d3-23f72170c412,08f16292-aa9d-454b-b99b-83565a7761d0,210343d4-5b40-49a6-abe0-cf5d5ca2a05a,43c81134-d0c8-445f-8ea5-d43c44369453,6e0b9588-bd0b-496d-be5e-0f3b59ee51d8}'::uuid[]))
(4 rows)

ここまでの結果の組み合わせなので、特に目新しい発見はないかと思います。

結論

ここまでで、最初に問題点として考えた

  • user_settings.updated_at カラムに対するインデックスが作られていない
  • eager_load が発行する left joinusers テーブルのフルスキャンが発生していると思われる

について、改善についての仮説を考え、問題点が正しいかの確認と、改善についての検証を行いました。

検証の結果、インデックス作成については、今時点でも効果があり、今後も改善が見込めると判断できました。 一方 eager_loadpreload にする修正ついては、今時点では効果は微妙です。

この時点ではインデックス作成のみに留めて、 eager_load の対応は放っておいてもしばらくは問題ないでしょう。 ただ、そのしばらくがいつまでかを知るためには、より大量のデータでの検証が必要です。 あるいは、何もせずに問題が発生するまで待ってから対応でも良いかもしれません。

この辺りは、今時点でのクエリ実行時間や検証と改修のコスト、テーブルやプロダクトの性質など、様々な状況を判断して対応有無を考えるところかと思います。

という前置きの上、今回に関しての話をします。 今回の eager_load のようなものは、後から気付く難易度が高くなりがちです。 特に今回に関しては、今の時点で多少の無駄があるとしても、レコード数が増えた時のことを考えてコードを書いておく方が、後々の問題を回避するという点では良いと判断しました。

おわりに

普段だと、このくらいの改修だと explain まではせずに修正してしまうことの方が多いのですが、最近、社内で explain についての質問をいくつか見かけたので、良い実例になりそうと思って explain してみました。

explain そのものの解説がないのでわかりにくい記事になってしまったと思いますが、実際のコードから何を考えて explain の結果をどのような判断材料にするのか、参考になれば幸いです。

参考

explain の結果は RDBMS でだいぶ違いがあるので、お使いのデータベースシステムに合わせて情報を探す必要があります。

PostgreSQL の explain についての参考記事を紹介します。 社内で「explain の見方を知りたい」という話題が出た時に、自分が参考として渡している URL です。

lets.postgresql.jp

www.slideshare.net

また、最近ことあるごとに話しているのですが、インデックスについてはこちらの本がオススメです。

SQLパフォーマンス詳解

SQLパフォーマンス詳解