Skip to content

Eliminate Profiles::NotificationsController N+1

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:

image

After:

image

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

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 by Adam Hegyi

Merge request reports

Loading