bootcamp
bootcamp copied to clipboard
Published
20 hours ago •
fjordllc
fjordllc
プラクティスページの表示速度が遅い
概要
プラクティスページの表示速度が遅いのが気になったので調べてみました。
api/courses/1/practices のレスポンスに 1.64s かかっています。
原因（推測）
learnings.status にインデックスが無いのが原因だと推測しています。
https://github.com/fjordllc/bootcamp/blob/af01eb03b410c5c736dd3e22e9566077bb6126e8/db/schema.rb#L374-L383
詳細
APIのコントローラーで includes に started_students を指定している。
https://github.com/fjordllc/bootcamp/blob/af01eb03b410c5c736dd3e22e9566077bb6126e8/app/controllers/api/courses/practices_controller.rb#L8-L12
この実装は app/models/practice.rb に書かれています。
https://github.com/fjordllc/bootcamp/blob/af01eb03b410c5c736dd3e22e9566077bb6126e8/app/models/practice.rb#L25-L28
開発環境の rails console で雑に includes した時の explain の結果は以下の通りです。
Practice.all.includes(:started_students).explain
Practice Load (1.0ms) SELECT "practices".* FROM "practices"
SQL (1.4ms) SELECT "learnings"."id" AS t0_r0, "learnings"."user_id" AS t0_r1, "learnings"."practice_id" AS t0_r2, "learnings"."status" AS t0_r3, "learnings"."created_at" AS t0_r4, "learnings"."updated_at" AS t0_r5, "learnings"."completion_message_displayed" AS t0_r6, "users"."id" AS t1_r0, "users"."login_name" AS t1_r1, "users"."email" AS t1_r2, "users"."crypted_password" AS t1_r3, "users"."salt" AS t1_r4, "users"."created_at" AS t1_r5, "users"."updated_at" AS t1_r6, "users"."remember_me_token" AS t1_r7, "users"."remember_me_token_expires_at" AS t1_r8, "users"."twitter_account" AS t1_r9, "users"."facebook_url" AS t1_r10, "users"."blog_url" AS t1_r11, "users"."company_id" AS t1_r12, "users"."description" AS t1_r13, "users"."accessed_at" AS t1_r14, "users"."github_account" AS t1_r15, "users"."adviser" AS t1_r16, "users"."nda" AS t1_r17, "users"."reset_password_token" AS t1_r18, "users"."reset_password_token_expires_at" AS t1_r19, "users"."reset_password_email_sent_at" AS t1_r20, "users"."mentor" AS t1_r21, "users"."graduated_on" AS t1_r22, "users"."course_id" AS t1_r23, "users"."retired_on" AS t1_r24, "users"."admin" AS t1_r25, "users"."job" AS t1_r26, "users"."organization" AS t1_r27, "users"."os" AS t1_r28, "users"."experience" AS t1_r29, "users"."retire_reason" AS t1_r30, "users"."trainee" AS t1_r31, "users"."free" AS t1_r32, "users"."customer_id" AS t1_r33, "users"."job_seeking" AS t1_r34, "users"."subscription_id" AS t1_r35, "users"."mail_notification" AS t1_r36, "users"."job_seeker" AS t1_r37, "users"."github_id" AS t1_r38, "users"."github_collaborator" AS t1_r39, "users"."name" AS t1_r40, "users"."name_kana" AS t1_r41, "users"."satisfaction" AS t1_r42, "users"."opinion" AS t1_r43, "users"."retire_reasons" AS t1_r44, "users"."unsubscribe_email_token" AS t1_r45, "users"."mentor_memo" AS t1_r46, "users"."after_graduation_hope" AS t1_r47, "users"."training_ends_on" AS t1_r48, "users"."sad_streak" AS t1_r49, "users"."last_sad_report_id" AS t1_r50, "users"."last_activity_at" AS t1_r51, "users"."hibernated_at" AS t1_r52, "users"."profile_name" AS t1_r53, "users"."profile_job" AS t1_r54, "users"."profile_text" AS t1_r55, "users"."feed_url" AS t1_r56, "users"."sent_student_followup_message" AS t1_r57, "users"."country_code" AS t1_r58, "users"."subdivision_code" AS t1_r59, "users"."auto_retire" AS t1_r60 FROM "learnings" LEFT OUTER JOIN "users" ON "users"."id" = "learnings"."user_id" WHERE "learnings"."status" = $1 AND "users"."admin" = $2 AND "users"."mentor" = $3 AND "users"."adviser" = $4 AND "users"."graduated_on" IS NULL AND "users"."hibernated_at" IS NULL AND "users"."retired_on" IS NULL AND "learnings"."practice_id" IN ($5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64) [["status", 1], ["admin", false], ["mentor", false], ["adviser", false], ["practice_id", 315059988], ["practice_id", 198065840], ["practice_id", 1019809339], ["practice_id", 581802908], ["practice_id", 363506445], ["practice_id", 212073145], ["practice_id", 1000655396], ["practice_id", 723244977], ["practice_id", 471656230], ["practice_id", 773049398], ["practice_id", 420805799], ["practice_id", 1944863], ["practice_id", 924491146], ["practice_id", 696125485], ["practice_id", 511260862], ["practice_id", 124807430], ["practice_id", 813128081], ["practice_id", 549995524], ["practice_id", 399471769], ["practice_id", 87990259], ["practice_id", 842641256], ["practice_id", 724631260], ["practice_id", 473419339], ["practice_id", 39017452], ["practice_id", 894724993], ["practice_id", 744307397], ["practice_id", 458885716], ["practice_id", 199563205], ["practice_id", 1021454166], ["practice_id", 472231604], ["practice_id", 723689001], ["practice_id", 841731997], ["practice_id", 86834956], ["practice_id", 457730731], ["practice_id", 743397952], ["practice_id", 893782916], ["practice_id", 37829395], ["practice_id", 318645894], ["practice_id", 637081111], ["practice_id", 325335160], ["practice_id", 610470115], ["practice_id", 1030379867], ["practice_id", 174942668], ["practice_id", 336198769], ["practice_id", 588172540], ["practice_id", 973577540], ["practice_id", 218148309], ["practice_id", 499101766], ["practice_id", 716734677], ["practice_id", 176097593], ["practice_id", 1031289252], ["practice_id", 611411996], ["practice_id", 326523021], ["practice_id", 219335984], ["practice_id", 974519739], ["practice_id", 589081603], ["practice_id", 337353876], ["practice_id", 77889799], ["practice_id", 866365846], ["practice_id", 559045366]]
=>
EXPLAIN for: SELECT "practices".* FROM "practices"
QUERY PLAN
------------------------------------------------------------
Seq Scan on practices (cost=0.00..2.60 rows=60 width=147)
(1 row)
EXPLAIN for: SELECT "learnings"."id" AS t0_r0, "learnings"."user_id" AS t0_r1, "learnings"."practice_id" AS t0_r2, "learnings"."status" AS t0_r3, "learnings"."created_at" AS t0_r4, "learnings"."updated_at" AS t0_r5, "learnings"."completion_message_displayed" AS t0_r6, "users"."id" AS t1_r0, "users"."login_name" AS t1_r1, "users"."email" AS t1_r2, "users"."crypted_password" AS t1_r3, "users"."salt" AS t1_r4, "users"."created_at" AS t1_r5, "users"."updated_at" AS t1_r6, "users"."remember_me_token" AS t1_r7, "users"."remember_me_token_expires_at" AS t1_r8, "users"."twitter_account" AS t1_r9, "users"."facebook_url" AS t1_r10, "users"."blog_url" AS t1_r11, "users"."company_id" AS t1_r12, "users"."description" AS t1_r13, "users"."accessed_at" AS t1_r14, "users"."github_account" AS t1_r15, "users"."adviser" AS t1_r16, "users"."nda" AS t1_r17, "users"."reset_password_token" AS t1_r18, "users"."reset_password_token_expires_at" AS t1_r19, "users"."reset_password_email_sent_at" AS t1_r20, "users"."mentor" AS t1_r21, "users"."graduated_on" AS t1_r22, "users"."course_id" AS t1_r23, "users"."retired_on" AS t1_r24, "users"."admin" AS t1_r25, "users"."job" AS t1_r26, "users"."organization" AS t1_r27, "users"."os" AS t1_r28, "users"."experience" AS t1_r29, "users"."retire_reason" AS t1_r30, "users"."trainee" AS t1_r31, "users"."free" AS t1_r32, "users"."customer_id" AS t1_r33, "users"."job_seeking" AS t1_r34, "users"."subscription_id" AS t1_r35, "users"."mail_notification" AS t1_r36, "users"."job_seeker" AS t1_r37, "users"."github_id" AS t1_r38, "users"."github_collaborator" AS t1_r39, "users"."name" AS t1_r40, "users"."name_kana" AS t1_r41, "users"."satisfaction" AS t1_r42, "users"."opinion" AS t1_r43, "users"."retire_reasons" AS t1_r44, "users"."unsubscribe_email_token" AS t1_r45, "users"."mentor_memo" AS t1_r46, "users"."after_graduation_hope" AS t1_r47, "users"."training_ends_on" AS t1_r48, "users"."sad_streak" AS t1_r49, "users"."last_sad_report_id" AS t1_r50, "users"."last_activity_at" AS t1_r51, "users"."hibernated_at" AS t1_r52, "users"."profile_name" AS t1_r53, "users"."profile_job" AS t1_r54, "users"."profile_text" AS t1_r55, "users"."feed_url" AS t1_r56, "users"."sent_student_followup_message" AS t1_r57, "users"."country_code" AS t1_r58, "users"."subdivision_code" AS t1_r59, "users"."auto_retire" AS t1_r60 FROM "learnings" LEFT OUTER JOIN "users" ON "users"."id" = "learnings"."user_id" WHERE "learnings"."status" = $1 AND "users"."admin" = $2 AND "users"."mentor" = $3 AND "users"."adviser" = $4 AND "users"."graduated_on" IS NULL AND "users"."hibernated_at" IS NULL AND "users"."retired_on" IS NULL AND "learnings"."practice_id" IN ($5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64) [["status", 1], ["admin", false], ["mentor", false], ["adviser", false], ["practice_id", 315059988], ["practice_id", 198065840], ["practice_id", 1019809339], ["practice_id", 581802908], ["practice_id", 363506445], ["practice_id", 212073145], ["practice_id", 1000655396], ["practice_id", 723244977], ["practice_id", 471656230], ["practice_id", 773049398], ["practice_id", 420805799], ["practice_id", 1944863], ["practice_id", 924491146], ["practice_id", 696125485], ["practice_id", 511260862], ["practice_id", 124807430], ["practice_id", 813128081], ["practice_id", 549995524], ["practice_id", 399471769], ["practice_id", 87990259], ["practice_id", 842641256], ["practice_id", 724631260], ["practice_id", 473419339], ["practice_id", 39017452], ["practice_id", 894724993], ["practice_id", 744307397], ["practice_id", 458885716], ["practice_id", 199563205], ["practice_id", 1021454166], ["practice_id", 472231604], ["practice_id", 723689001], ["practice_id", 841731997], ["practice_id", 86834956], ["practice_id", 457730731], ["practice_id", 743397952], ["practice_id", 893782916], ["practice_id", 37829395], ["practice_id", 318645894], ["practice_id", 637081111], ["practice_id", 325335160], ["practice_id", 610470115], ["practice_id", 1030379867], ["practice_id", 174942668], ["practice_id", 336198769], ["practice_id", 588172540], ["practice_id", 973577540], ["practice_id", 218148309], ["practice_id", 499101766], ["practice_id", 716734677], ["practice_id", 176097593], ["practice_id", 1031289252], ["practice_id", 611411996], ["practice_id", 326523021], ["practice_id", 219335984], ["practice_id", 974519739], ["practice_id", 589081603], ["practice_id", 337353876], ["practice_id", 77889799], ["practice_id", 866365846], ["practice_id", 559045366]]
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=28.48..36.42 rows=1 width=937)
Hash Cond: (users.id = learnings.user_id)
-> Seq Scan on users (cost=0.00..7.72 rows=54 width=904)
Filter: ((NOT admin) AND (NOT mentor) AND (NOT adviser) AND (graduated_on IS NULL) AND (hibernated_at IS NULL) AND (retired_on IS NULL))
-> Hash (cost=28.46..28.46 rows=2 width=33)
-> Bitmap Heap Scan on learnings (cost=18.28..28.46 rows=2 width=33)
Recheck Cond: (status = 1)
Filter: (practice_id = ANY ('{315059988,198065840,1019809339,581802908,363506445,212073145,1000655396,723244977,471656230,773049398,420805799,1944863,924491146,696125485,511260862,124807430,813128081,549995524,399471769,87990259,842641256,724631260,473419339,39017452,894724993,744307397,458885716,199563205,1021454166,472231604,723689001,841731997,86834956,457730731,743397952,893782916,37829395,318645894,637081111,325335160,610470115,1030379867,174942668,336198769,588172540,973577540,218148309,499101766,716734677,176097593,1031289252,611411996,326523021,219335984,974519739,589081603,337353876,77889799,866365846,559045366}'::integer[]))
-> Bitmap Index Scan on index_learnings_on_user_id_and_status (cost=0.00..18.13 rows=7 width=0)
Index Cond: (status = 1)
(10 rows)
以下の行を読んで、 status のインデックスが無いのが原因だと考えました。 （DBあまり詳しくないので間違っていたらすみません）
-> Bitmap Heap Scan on learnings (cost=18.28..28.46 rows=2 width=33)
Recheck Cond: (status = 1)
schema.rb を読むと、 learnings のインデックスは user_id が絡むものしか存在しない。
ただ、このAPIでは practice に紐づく learnings を取ってきているので、インデックスが効かなそう。
備考
@komagata 推測した根拠は前述の通りですが、自信ないので本番環境で API と同じ SQL の explain を見た方が良いかもしれないです。
Category.joins(:courses_categories)
.where(courses_categories: { course_id: 1 })
.includes(practices: [{ started_students: { avatar_attachment: :blob } }, :learning_minute_statistic, :practices_books])
.order('courses_categories.position')
.explain
