Why am I getting so many SQL calls for a table that I'm not querying much?

Question

I noticed that I'm getting multiple SQL calls for my editions table, even though I only load it once in the controller. What's weirder to me is that each call is to a different edition even though I'm loading the show page for a single edition.

The console output is here:

Started GET "/editions/48" for 127.0.0.1 at 2020-03-07 08:37:53 +0530
Processing by EditionsController#show as HTML
  Parameters: {"id"=>"48"}
  Edition Load (0.5ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 48], ["LIMIT", 1]]
  User Load (0.8ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."id" ASC LIMIT $2  [["id", 1], ["LIMIT", 1]]
  Rendering editions/show.html.erb within layouts/application
  Product Load (0.4ms)  SELECT  "products".* FROM "products" WHERE "products"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  Rendered shared/_arrow.html.erb (0.3ms)
  Edition Load (0.6ms)  SELECT  "editions".* FROM "editions" WHERE (product_id = 1 AND date < '2018-02-18') ORDER BY "editions"."date" DESC LIMIT $1  [["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE (product_id = 1 AND date < '2018-02-18') ORDER BY "editions"."date" DESC LIMIT $1  [["LIMIT", 1]]
  Edition Load (0.5ms)  SELECT  "editions".* FROM "editions" WHERE (product_id = 1 AND date > '2018-02-18') ORDER BY "editions"."date" ASC LIMIT $1  [["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE (product_id = 1 AND date > '2018-02-18') ORDER BY "editions"."date" ASC LIMIT $1  [["LIMIT", 1]]
  Rendered editions/_admin_controls.html.erb (0.9ms)
  Section Load (0.8ms)  SELECT DISTINCT "sections".* FROM "sections" INNER JOIN "contents" "contents_sections" ON "contents_sections"."section_id" = "sections"."id" INNER JOIN "contents" ON "sections"."id" = "contents"."section_id" WHERE "contents"."edition_id" = $1 AND "contents"."top_story" = $2 ORDER BY "sections"."position" ASC  [["edition_id", 48], ["top_story", "f"]]
  Content Load (0.6ms)  SELECT "contents".* FROM "contents" WHERE "contents"."edition_id" = $1 AND "contents"."top_story" = $2  [["edition_id", 48], ["top_story", "t"]]
  Content Load (0.6ms)  SELECT "contents".* FROM "contents" WHERE "contents"."edition_id" = $1 AND "contents"."top_story" = $2 ORDER BY "contents"."position" ASC  [["edition_id", 48], ["top_story", "t"]]
  Rendered contents/_admin_controls.html.erb (0.5ms)
  Source Load (0.4ms)  SELECT  "sources".* FROM "sources" WHERE "sources"."id" = $1 LIMIT $2  [["id", 5], ["LIMIT", 1]]
  Rendered shared/_content.html.erb (7.0ms)
  Rendered contents/_admin_controls.html.erb (0.3ms)
  Source Load (0.4ms)  SELECT  "sources".* FROM "sources" WHERE "sources"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
  Rendered shared/_content.html.erb (6.9ms)
  Rendered contents/_admin_controls.html.erb (0.3ms)
  CACHE Source Load (0.0ms)  SELECT  "sources".* FROM "sources" WHERE "sources"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
  Rendered shared/_content.html.erb (7.9ms)
  Rendered contents/_admin_controls.html.erb (0.3ms)
  CACHE Source Load (0.0ms)  SELECT  "sources".* FROM "sources" WHERE "sources"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
  Rendered shared/_content.html.erb (7.0ms)
  Content Load (0.6ms)  SELECT "contents".* FROM "contents" WHERE "contents"."section_id" = $1 ORDER BY "contents"."position" ASC  [["section_id", 2]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 40], ["LIMIT", 1]]
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 22], ["LIMIT", 1]]
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 14], ["LIMIT", 1]]
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 41], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 14], ["LIMIT", 1]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 4], ["LIMIT", 1]]
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 38], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 4], ["LIMIT", 1]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 34], ["LIMIT", 1]]
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 36], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 36], ["LIMIT", 1]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 27], ["LIMIT", 1]]
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 48], ["LIMIT", 1]]
  Rendered contents/_admin_controls.html.erb (0.4ms)
  Source Load (0.4ms)  SELECT  "sources".* FROM "sources" WHERE "sources"."id" = $1 LIMIT $2  [["id", 4], ["LIMIT", 1]]
  Rendered shared/_content.html.erb (7.4ms)
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 24], ["LIMIT", 1]]
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 13], ["LIMIT", 1]]
  Edition Load (0.2ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 20], ["LIMIT", 1]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 37], ["LIMIT", 1]]
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 34], ["LIMIT", 1]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 23], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 24], ["LIMIT", 1]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 45], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 36], ["LIMIT", 1]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 3], ["LIMIT", 1]]
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 15], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 24], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 37], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 40], ["LIMIT", 1]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 47], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 14], ["LIMIT", 1]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 10], ["LIMIT", 1]]
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 29], ["LIMIT", 1]]
  Rendered editions/_edition_sections.html.erb (142.3ms)
  Rendered editions/show.html.erb within layouts/application (246.2ms)
  Rendered shared/_nav_searchbar.html.erb (0.8ms)
  Rendered shared/_nav.html.erb (4.1ms)
  Visit Load (0.5ms)  SELECT  "visits".* FROM "visits" WHERE "visits"."visit_token" = $1 ORDER BY "visits"."id" ASC LIMIT $2  [["visit_token", "40979cb1-0c92-486a-a274-c937560c03b1"], ["LIMIT", 1]]
   (0.2ms)  BEGIN
  Visit Load (0.3ms)  SELECT  "visits".* FROM "visits" WHERE "visits"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  SQL (0.4ms)  INSERT INTO "ahoy_events" ("visit_id", "user_id", "name", "properties", "time") VALUES ($1, $2, $3, $4, $5) RETURNING "id"  [["visit_id", 1], ["user_id", 1], ["name", "Load edition"], ["properties", "{\"controller\":\"editions\",\"action\":\"show\",\"id\":\"48\"}"], ["time", "2020-03-07 03:07:53.513209"]]
   (0.9ms)  COMMIT
Completed 200 OK in 400ms (Views: 367.2ms | ActiveRecord: 16.8ms)

The rest of my code can be found here

My best guess is that some logic within the show view is creating more queries to the database, but I'm not sure where or how to optimize that (I'm sort of a beginner in SQL stuff and Rails). This has actually become a bigger issue on my production site, where it's causing Heroku to put out errors saying R14 - Memory Quota Exceeded

Any advice?

Answer 1

This is called N + 1 Problem.

In _editions_sections.html.erb you have:

if (content.edition == @edition) && (content.top_story == false)

what this does is for every content in the loop it loads edition to compare with @edition.

You can solve it in 2 ways:

• 1 (not the usual solution)

  if (content.edition_id == @edition.id) && (content.top_story == false)
  

  This will only check edition_id column and not load all the fields in edition.

• 2 use includes

  in edition.rb, make sections_with_regular_content look like this:

  def sections_with_regular_content
    sections.includes(contents: :edition).where(contents: { top_story: false })
  end
  

includes effectively figures out all ids needed and load them in a single query or through the join.