Eliminate Profiles::NotificationsController N+1 (!58397) · Merge requests · GitLab.org / GitLab

Adam Hegyi requested to merge 21043-fix-notifications-controller-n-plus-1-queries into master Apr 01, 2021

What does this MR do?

This MR eliminates the N+1 queries on the Profiles::NotificationsController#show page.

Memoize public_verified_emails
Preload group.emails_disabled? strong memoized method.

Query:

To preload emails_disabled? we need to create a quite complex SQL query which does the following:

For each group (fixed size, because of the pagination), look up the ancestor chain and check if any ancestor had the emails_disabled column set to true.

SELECT "id"
FROM
  (SELECT *
   FROM namespaces) AS namespaces_with_emails_disabled
WHERE "namespaces_with_emails_disabled"."id" IN (5437949, 6215292, 11069900, 11345530, 8496641, 4408371, 7055722, 9494491, 5324448, 11177133, 10633276, 9721411, 10127741 ,10313596)
  AND (EXISTS
         (SELECT 1
          FROM
            (SELECT "namespaces".*
             FROM "namespaces"
             INNER JOIN
               (SELECT "id",
                       "depth"
                FROM
                  (WITH RECURSIVE "base_and_ancestors" AS (
                                                             (SELECT "namespaces".*
                                                              FROM "namespaces"
                                                              WHERE "namespaces"."type" = 'Group'
                                                                AND (id = namespaces_with_emails_disabled.id))
                                                           UNION
                                                             (SELECT "namespaces".*
                                                              FROM "namespaces",
                                                                   "base_and_ancestors"
                                                              WHERE "namespaces"."type" = 'Group'
                                                                AND "namespaces"."id" = "base_and_ancestors"."parent_id")) SELECT DISTINCT "namespaces".*,
                                                                                                                                           ROW_NUMBER() OVER () AS depth
                   FROM "base_and_ancestors" AS "namespaces") AS "namespaces"
                WHERE "namespaces"."type" = 'Group') namespaces_join_table ON namespaces_join_table.id = namespaces.id
             WHERE "namespaces"."type" = 'Group'
             ORDER BY "namespaces_join_table"."depth" ASC) AS "namespaces"
          WHERE "namespaces"."type" = 'Group'
            AND "namespaces"."emails_disabled" = TRUE
          LIMIT 1))

Plan (depesz is down):

Index Only Scan using namespaces_pkey on namespaces  (cost=0.43..5048.56 rows=7 width=4) (actual time=6.240..6.240 rows=0 loops=1)
   Index Cond: (id = ANY ('{5437949,6215292,11069900,11345530,8496641,4408371,7055722,9494491,5324448,11177133,10633276,9721411,10127741,10313596}'::integer[]))
   Filter: (SubPlan 2)
   Rows Removed by Filter: 13
   Heap Fetches: 17
   Buffers: shared hit=721 read=3
   I/O Timings: read=0.052
   SubPlan 2
     ->  Subquery Scan on namespaces_3  (cost=358.43..358.44 rows=1 width=0) (actual time=0.365..0.365 rows=0 loops=13)
           Buffers: shared hit=650
           ->  Sort  (cost=358.43..358.43 rows=1 width=2954) (actual time=0.364..0.364 rows=0 loops=13)
                 Sort Key: namespaces_5.depth
                 Sort Method: quicksort  Memory: 25kB
                 Buffers: shared hit=650
                 ->  Nested Loop  (cost=352.44..358.42 rows=1 width=2954) (actual time=0.359..0.359 rows=0 loops=13)
                       Buffers: shared hit=647
                       ->  Subquery Scan on namespaces_5  (cost=352.01..354.95 rows=1 width=12) (actual time=0.234..0.241 rows=3 loops=13)
                             Filter: ((namespaces_5.type)::text = 'Group'::text)
                             Buffers: shared hit=196
                             ->  Unique  (cost=352.01..354.69 rows=21 width=2954) (actual time=0.233..0.239 rows=3 loops=13)
                                   Buffers: shared hit=196
                                   CTE base_and_ancestors
                                     ->  Recursive Union  (cost=0.43..350.87 rows=21 width=344) (actual time=0.070..0.193 rows=3 loops=13)
                                           Buffers: shared hit=171
                                           ->  Index Scan using index_namespaces_on_type_and_id_partial on namespaces namespaces_1  (cost=0.43..3.45 rows=1 width=344) (actual time=0.065..0.082 rows=1 loops=13)
                                                 Index Cond: (((type)::text = 'Group'::text) AND (id = namespaces.id))
                                                 Buffers: shared hit=59
                                           ->  Nested Loop  (cost=0.43..34.70 rows=2 width=344) (actual time=0.030..0.031 rows=1 loops=41)
                                                 Buffers: shared hit=112
                                                 ->  WorkTable Scan on base_and_ancestors  (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.000 rows=1 loops=41)
                                                 ->  Index Scan using index_namespaces_on_type_and_id_partial on namespaces namespaces_2  (cost=0.43..3.45 rows=1 width=344) (actual time=0.030..0.030 rows=1 loops=40)
                                                       Index Cond: (((type)::text = 'Group'::text) AND (id = base_and_ancestors.parent_id))
                                                       Buffers: shared hit=112
                                   ->  Sort  (cost=1.14..1.20 rows=21 width=2954) (actual time=0.232..0.234 rows=3 loops=13)
                                         Sort Key: namespaces_6.id, namespaces_6.name, namespaces_6.path, namespaces_6.owner_id, namespaces_6.created_at, namespaces_6.updated_at, namespaces_6.type, namespaces_6.description, namespaces_6.avatar, namespaces_6.membership_lock, namespaces_6.share_with_group_lock, namespaces_6.visibility_level, namespaces_6.request_access_enabled, namespaces_6.ldap_sync_status, namespaces_6.ldap_sync_error, namespaces_6.ldap_sync_last_update_at, namespaces_6.ldap_sync_last_successful_update_at, namespaces_6.ldap_sync_last_sync_at, namespaces_6.lfs_enabled, namespaces_6.description_html, namespaces_6.parent_id, namespaces_6.shared_runners_minutes_limit, namespaces_6.repository_size_limit, namespaces_6.require_two_factor_authentication, namespaces_6.two_factor_grace_period, namespaces_6.cached_markdown_version, namespaces_6.project_creation_level, namespaces_6.runners_token, namespaces_6.file_template_project_id, namespaces_6.saml_discovery_token, namespaces_6.runners_token_encrypted, namespaces_6.custom_project_templates_group_id, namespaces_6.auto_devops_enabled, namespaces_6.extra_shared_runners_minutes_limit, namespaces_6.last_ci_minutes_notification_at, namespaces_6.last_ci_minutes_usage_notification_level, namespaces_6.subgroup_creation_level, namespaces_6.emails_disabled, namespaces_6.max_pages_size, namespaces_6.max_artifacts_size, namespaces_6.mentions_disabled, namespaces_6.default_branch_protection, namespaces_6.unlock_membership_to_ldap, namespaces_6.max_personal_access_token_lifetime, namespaces_6.push_rule_id, namespaces_6.shared_runners_enabled, namespaces_6.allow_descendants_override_disabled_shared_runners, namespaces_6.traversal_ids, namespaces_6.delayed_project_removal, (row_number() OVER (?))
                                         Sort Method: quicksort  Memory: 26kB
                                         Buffers: shared hit=196
                                         ->  WindowAgg  (cost=0.00..0.68 rows=21 width=2954) (actual time=0.075..0.207 rows=3 loops=13)
                                               Buffers: shared hit=171
                                               ->  CTE Scan on base_and_ancestors namespaces_6  (cost=0.00..0.42 rows=21 width=2946) (actual time=0.072..0.200 rows=3 loops=13)
                                                     Buffers: shared hit=171
                       ->  Index Scan using index_namespaces_on_type_and_id_partial on namespaces namespaces_4  (cost=0.43..3.45 rows=1 width=4) (actual time=0.038..0.038 rows=0 loops=40)
                             Index Cond: (((type)::text = 'Group'::text) AND (id = namespaces_5.id))
                             Filter: emails_disabled
                             Rows Removed by Filter: 1
                             Buffers: shared hit=451
 Planning Time: 7.288 ms
 Execution Time: 6.950 ms
(48 rows)

Screenshots (strongly suggested)

Before:

After:

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

Related to #21043 (closed)

Edited Apr 02, 2021 by Adam Hegyi

Eliminate Profiles::NotificationsController N+1