Reduce queries on Controller Projects::LabelsController#index [RUN AS-IF-FOSS] [RUN ALL RSPEC] (!57864) · Merge requests · GitLab.org / GitLab

Terri Chu requested to merge 21078-controller-projects-labelscontroller-index-executes-more-than-100-sql-queries into master Mar 30, 2021

Related to #21078 (closed)

What does this MR do?

Create a new Preloader for Labels that preloads data for group (for GroupLabel), project (for ProjectLabel), and subscription (for all Label) using lazy_subscription? method introduced in !57517 (merged)
The Preloader uses ActiveRecord::Associations::Preloader.new for group and project data. I went this route vs. using preloads or includes because there are two types of data (GroupLabel and ProjectLabel) and the preloading needs to know the type.
Use the new Preloader in the Group::LabelsController
added some specs

How to test

Log in
Navigate to a Project where there are labels
Load the Project Labels screen (Sidebar --> Issues --> Labels)
Verify using the performance bar that only 1 call is made to the subscriptions table

Database

Query used from project labels page: https://gitlab.com/gitlab-org/gitlab/-/labels

The Subscriptions table was queried 159 times per the performance bar

SQL

before sql (subset of 159 calls)

SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" = 11832684
    AND "subscriptions"."subscribable_type" = 'Label'
    AND "subscriptions"."user_id" = 5708766
    AND "subscriptions"."project_id" = 278964
LIMIT 1
SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" = 3857370
    AND "subscriptions"."subscribable_type" = 'Label'
    AND "subscriptions"."user_id" = 5708766
    AND "subscriptions"."project_id" IS NULL
LIMIT 1
SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" = 18275109
    AND "subscriptions"."subscribable_type" = 'Label'
    AND "subscriptions"."user_id" = 5708766
    AND "subscriptions"."project_id" IS NULL
LIMIT 1
SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" = 11832684
    AND "subscriptions"."subscribable_type" = 'Label'
    AND "subscriptions"."user_id" = 5708766
    AND "subscriptions"."project_id" IS NULL
LIMIT 1
SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" = 16198756
    AND "subscriptions"."subscribable_type" = 'Label'
    AND "subscriptions"."user_id" = 5708766
    AND "subscriptions"."project_id" IS NULL
LIMIT 1
SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" = 15379995
    AND "subscriptions"."subscribable_type" = 'Label'
    AND "subscriptions"."user_id" = 5708766
    AND "subscriptions"."project_id" IS NULL
LIMIT 1
SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" = 11832671
    AND "subscriptions"."subscribable_type" = 'Label'
    AND "subscriptions"."user_id" = 5708766
    AND "subscriptions"."project_id" IS NULL
LIMIT 1
SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" = 11832683
    AND "subscriptions"."subscribable_type" = 'Label'
    AND "subscriptions"."user_id" = 5708766
    AND "subscriptions"."project_id" IS NULL
LIMIT 1
SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" = 16558712
    AND "subscriptions"."subscribable_type" = 'Label'
    AND "subscriptions"."user_id" = 5708766
    AND "subscriptions"."project_id" = 278964
LIMIT 1
SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" = 2936308
    AND "subscriptions"."subscribable_type" = 'Label'
    AND "subscriptions"."user_id" = 5708766
    AND "subscriptions"."project_id" IS NULL
LIMIT 1
SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" = 10438591
    AND "subscriptions"."subscribable_type" = 'Label'
    AND "subscriptions"."user_id" = 5708766
    AND "subscriptions"."project_id" = 278964
LIMIT 1
SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" = 15904538
    AND "subscriptions"."subscribable_type" = 'Label'
    AND "subscriptions"."user_id" = 5708766
    AND "subscriptions"."project_id" IS NULL
LIMIT 1
SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" = 15714199
    AND "subscriptions"."subscribable_type" = 'Label'
    AND "subscriptions"."user_id" = 5708766
    AND "subscriptions"."project_id" IS NULL
LIMIT 1
SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" = 8745578
    AND "subscriptions"."subscribable_type" = 'Label'
    AND "subscriptions"."user_id" = 5708766
    AND "subscriptions"."project_id" = 278964
LIMIT 1
SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" = 16558712
    AND "subscriptions"."subscribable_type" = 'Label'
    AND "subscriptions"."user_id" = 5708766
    AND "subscriptions"."project_id" IS NULL
LIMIT 1
SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" = 3121561
    AND "subscriptions"."subscribable_type" = 'Label'
    AND "subscriptions"."user_id" = 5708766
    AND "subscriptions"."project_id" = 278964
LIMIT 1
SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" = 16648665
    AND "subscriptions"."subscribable_type" = 'Label'
    AND "subscriptions"."user_id" = 5708766
    AND "subscriptions"."project_id" IS NULL
LIMIT 1
SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" = 16876290
    AND "subscriptions"."subscribable_type" = 'Label'
    AND "subscriptions"."user_id" = 5708766
    AND "subscriptions"."project_id" IS NULL
LIMIT 1
SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" = 11832677
    AND "subscriptions"."subscribable_type" = 'Label'
    AND "subscriptions"."user_id" = 5708766
    AND "subscriptions"."project_id" IS NULL
LIMIT 1
SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" = 15714199
    AND "subscriptions"."subscribable_type" = 'Label'
    AND "subscriptions"."user_id" = 5708766
    AND "subscriptions"."project_id" = 278964
LIMIT 1

sql after

SELECT
    "subscriptions".*
FROM
    "subscriptions"
WHERE
    "subscriptions"."subscribable_id" IN (11832684, 3857370, 18275109, 11832684, 16198756, 15379995, 11832671, 11832683, 16558712, 2936308, 10438591, 15904538, 15714199, 16558712, 8745578, 3121561, 16876290, 16648665, 11832677, 15714199, 16648665, 1890178, 13668544, 14931416, 16085447, 15379986, 15379986, 10438591, 3857543, 14781167, 11832434, 3079031, 3704995)
    AND "subscriptions"."subscribable_type" = 'Label'
    AND ("subscriptions"."project_id" = 278964
        OR "subscriptions"."project_id" IS NULL)
    AND "subscriptions"."user_id" = 5708766

Explain plan

link: https://explain.depesz.com/s/FyXk

  Index Scan using index_subscriptions_on_subscribable_and_user_id_and_project_id on public.subscriptions  (cost=0.42..65.45 rows=1 width=41) (actual time=3.861..11.306 rows=3 loops=1)
   Index Cond: ((subscriptions.subscribable_id = ANY ('{11832684,3857370,18275109,11832684,16198756,15379995,11832671,11832683,16558712,2936308,10438591,15904538,15714199,16558712,8745578,3121561,16876290,16648665,11832677,15714199,16648665,1890178,13668544,14931416,16085447,15379986,15379986,10438591,3857543,14781167,11832434,3079031,3704995}'::integer[])) AND ((subscriptions.subscribable_type)::text = 'Label'::text) AND (subscriptions.user_id = 5708766))
   Filter: ((subscriptions.project_id = 278964) OR (subscriptions.project_id IS NULL))
   Rows Removed by Filter: 0
   Buffers: shared hit=84 read=3
   I/O Timings: read=10.742

Screenshots (strongly suggested)

performance bar - before (cached) query count

performance bar - after (cached) query count

Does this MR meet the acceptance criteria?

Conformity

📋 Does this MR need a changelog?
- I have included a changelog entry.
- I have not included a changelog entry because _____.
[-] Documentation (if required)
Code review guidelines
Merge request performance guidelines
Style guides
Database guides
[-] Separation of EE specific content

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
[-] Tested in all supported browsers
[-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

[-] Label as security and @ mention @gitlab-com/gl-security/appsec
[-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
[-] Security reports checked/validated by a reviewer from the AppSec team

Edited Apr 06, 2021 by Terri Chu

Reduce queries on Controller Projects::LabelsController#index [RUN AS-IF-FOSS] [RUN ALL RSPEC]