How can I optimise this active record query in this filterable, paginated rails API?

Question

I'm building a Rails API from a model Service with several nested relations (many-to-one and many-to-many).

The basic index route has a fast enough response time (~150ms), but when I add a filter query to the URL (eg ?category=MyCategory,MyOtherCategory), the response time is absurdly slow (~20s).

How can I optimise the active record scope and query to fix this?

I can see from the rails log that this absolutely massive query is to blame:

SELECT "services"."id" AS t0_r0, "services"."name" AS t0_r1, "services"."parent_organisation" AS t0_r2, "services"."description" AS t0_r3, "services"."created_at" AS t0_r4, "services"."updated_at" AS t0_r5, "services"."url" AS t0_r6, "services"."contact_name" AS t0_r7, "services"."phone" AS t0_r8, "services"."email" AS t0_r9, "services"."review_status" AS t0_r10, "services"."review_number" AS t0_r11, "services"."review_notes" AS t0_r12, "services"."clo_notes" AS t0_r13, "services"."health_safety" AS t0_r14, "services"."insurance" AS t0_r15, "services"."safeguarding" AS t0_r16, "services"."vol_dbs_check" AS t0_r17, "services"."review_date" AS t0_r18, "services"."laf_area" AS t0_r19, "services"."ccg_locality" AS t0_r20, "services"."venue" AS t0_r21, "services"."area" AS t0_r22, "services"."postcode" AS t0_r23, "services"."latitude" AS t0_r24, "services"."longitude" AS t0_r25, "services"."price" AS t0_r26, "services"."daytime" AS t0_r27, "services"."frequency" AS t0_r28, "services"."assigned_to" AS t0_r29, "services"."services" AS t0_r30, "services"."category_id" AS t0_r31, "days"."id" AS t1_r0, "days"."name" AS t1_r1, "days"."created_at" AS t1_r2, "days"."updated_at" AS t1_r3, "categories"."id" AS t2_r0, "categories"."name" AS t2_r1, "categories"."created_at" AS t2_r2, "categories"."updated_at" AS t2_r3, "keywords"."id" AS t3_r0, "keywords"."name" AS t3_r1, "keywords"."created_at" AS t3_r2, "keywords"."updated_at" AS t3_r3, "accessibilities"."id" AS t4_r0, "accessibilities"."name" AS t4_r1, "accessibilities"."created_at" AS t4_r2, "accessibilities"."updated_at" AS t4_r3, "suitabilities"."id" AS t5_r0, "suitabilities"."name" AS t5_r1, "suitabilities"."created_at" AS t5_r2, "suitabilities"."updated_at" AS t5_r3, "age_groups"."id" AS t6_r0, "age_groups"."name" AS t6_r1, "age_groups"."created_at" AS t6_r2, "age_groups"."updated_at" AS t6_r3 FROM "services" INNER JOIN "categories" ON "categories"."id" = "services"."category_id" LEFT OUTER JOIN "days_services" ON "days_services"."service_id" = "services"."id" LEFT OUTER JOIN "days" ON "days"."id" = "days_services"."day_id" LEFT OUTER JOIN "keywords_services" ON "keywords_services"."service_id" = "services"."id" LEFT OUTER JOIN "keywords" ON "keywords"."id" = "keywords_services"."keyword_id" LEFT OUTER JOIN "accessibilities_services" ON "accessibilities_services"."service_id" = "services"."id" LEFT OUTER JOIN "accessibilities" ON "accessibilities"."id" = "accessibilities_services"."accessibility_id" LEFT OUTER JOIN "services_suitabilities" ON "services_suitabilities"."service_id" = "services"."id" LEFT OUTER JOIN "suitabilities" ON "suitabilities"."id" = "services_suitabilities"."suitability_id" LEFT OUTER JOIN "age_groups_services" ON "age_groups_services"."service_id" = "services"."id" LEFT OUTER JOIN "age_groups" ON "age_groups"."id" = "age_groups_services"."age_group_id" WHERE "categories"."name" = $1 AND "services"."id" IN ($2, $3, $4, $5, $6, $7, $8, $9, $10, $11)

My models:

# models/service.rb
class Service < ApplicationRecord

  belongs_to :category

  has_and_belongs_to_many :days
  has_and_belongs_to_many :keywords
  has_and_belongs_to_many :accessibilities
  has_and_belongs_to_many :suitabilities
  has_and_belongs_to_many :age_groups
  has_and_belongs_to_many :legacy_category

  scope :category, -> (category) {
    category_array = category.split(',')
    joins(:category).where(categories: {name: category_array})
  }

end

# models/category.rb
class Category < ApplicationRecord
    has_many :services
end

My controller:

class ServicesController < ApplicationController

    has_scope :category, only: :index

    def index
        services = apply_scopes(Service.includes(
            :category, 
            :days, 
            :keywords, 
            :accessibilities,
            :suitabilities,
            :age_groups
        ))
        paginate json: services, per_page: 10
    end

end

I'm using has_scope to apply the scopes in the controller.

I should be able to add a query to the end of my API endpoint and filter down the list of services to those matching the given categories.

Note that it's important to be able to filter for presence of any categories in a URL-supplied array, rather than just one.

Answer 1

First place I'd check is whether your Service table in the database properly indexes on Category. I would assume it does, but it's best to verify that to make sure. If not, obviously, add an index there. You may also want to check that Categories indexes on name as well.

Now, I've never used the has_scope library (nor have I used the pagination library you have shown in your example) so I can't speak to those specifically, but as for achieving it through standard means, would this not work for you?

category_names = params[:category].split(",")
per_page = params[:per_page].to_i
page = params[:page].to_i * per_page

category_ids = Category.where(name: category_names).pluck(:id)
services = Service
    .includes(
      :category, 
      :days, 
      :keywords, 
      :accessibilities,
      :suitabilities,
      :age_groups
    ).where(category: category_ids)
    .limit(per_page)
    .offset(page)


render json: services, status: :ok

It sounds like the optimization issue is your database isn't properly indexed, so without more information I think fixing that should address your poor performance there.