Skip to content

Preload saml providers for cross-group child epics

Eugenia Grieff requested to merge fix-child-epics-preload into master

What does this MR do and why?

Listing child epics executes N+1 queries for each group's saml_provider when filtering the epics that current_user has access to.

This MR adds the method Group#preload_root_saml_providers that presets the group root_saml_provider to fix the problem.

Database changes

  • Using finder without preload:
Epics::CrossHierarchyChildrenFinder.new(current_user, { parent: parent, sort: 'relative_position' }).execute
SQL queries 
SELECT "saml_providers".*
FROM "saml_providers"
WHERE "saml_providers"."group_id" = 121 LIMIT 1 -- Different query (1)

WITH "base_ancestors_cte" AS MATERIALIZED 
    (SELECT DISTINCT "namespaces"."traversal_ids"
    FROM "namespaces"
    INNER JOIN 
        (SELECT "epics"."group_id"
        FROM "epics"
        WHERE "epics"."parent_id" = 1205) AS epics
            ON epics.group_id = namespaces.id
        WHERE "namespaces"."type" = 'Group'
                AND 1=1)
    SELECT "namespaces"."id",
         "namespaces"."parent_id",
         "gitlab_subscriptions"."hosted_plan_id"
FROM "namespaces"
INNER JOIN 
    (SELECT DISTINCT unnest("base_ancestors_cte"."traversal_ids")
    FROM base_ancestors_cte) AS ancestors(ancestor_id)
    ON namespaces.id = ancestors.ancestor_id LEFT OUTER
JOIN gitlab_subscriptions
    ON gitlab_subscriptions.namespace_id=namespaces.id
WHERE "namespaces"."type" = 'Group' -- Same query (2)

SELECT DISTINCT "namespaces".*
FROM "namespaces"
INNER JOIN 
    (SELECT "epics"."group_id"
    FROM "epics"
    WHERE "epics"."parent_id" = 1205) AS epics
    ON epics.group_id = namespaces.id
WHERE "namespaces"."type" = 'Group' AND 1=1 -- Extra query

SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."id" = 121 LIMIT 1 -- Extra query

SELECT "saml_providers".*
FROM "saml_providers"
WHERE "saml_providers"."group_id" = 121 LIMIT 1 -- Extra query

SELECT MAX("members"."access_level") AS maximum_access_level,
         "members"."user_id" AS members_user_id
FROM (
    (SELECT "members"."id",
         "members"."access_level",
         "members"."source_id",
         "members"."source_type",
         "members"."user_id",
         "members"."notification_level",
         "members"."type",
         "members"."created_at",
         "members"."updated_at",
         "members"."created_by_id",
         "members"."invite_email",
         "members"."invite_token",
         "members"."invite_accepted_at",
         "members"."requested_at",
         "members"."expires_at",
         "members"."ldap",
         "members"."override",
         "members"."state",
         "members"."invite_email_success",
         "members"."member_namespace_id",
         "members"."member_role_id"
    FROM "members" LEFT OUTER
    JOIN "users"
        ON "users"."id" = "members"."user_id"
    WHERE "members"."type" = 'GroupMember'
            AND "members"."source_type" = 'Namespace'
            AND "users"."state" = 'active'
            AND "members"."state" = 0
            AND "members"."requested_at" IS NULL
            AND "members"."invite_token" IS NULL
            AND (members.access_level > 5)
            AND (members.access_level > 5)
            AND "members"."source_id" IN 
        (SELECT "namespaces"."id"
        FROM "namespaces"
        WHERE "namespaces"."type" = 'Group'
                AND "namespaces"."id" IN (121, 122)))
        UNION
        (WITH "group_group_links_cte" AS MATERIALIZED 
            (SELECT "group_group_links".*
            FROM "group_group_links"
            WHERE "group_group_links"."shared_group_id" IN 
                (SELECT "namespaces"."id"
                FROM "namespaces"
                WHERE "namespaces"."type" = 'Group'
                        AND "namespaces"."id" IN (121, 122)))
                SELECT "members"."id",
         LEAST("group_group_links"."group_access",
         "members"."access_level") AS access_level,
         "members"."source_id",
         "members"."source_type",
         "members"."user_id",
         "members"."notification_level",
         "members"."type",
         "members"."created_at",
         "members"."updated_at",
         "members"."created_by_id",
         "members"."invite_email",
         "members"."invite_token",
         "members"."invite_accepted_at",
         "members"."requested_at",
         "members"."expires_at",
         "members"."ldap",
         "members"."override",
         "members"."state",
         "members"."invite_email_success",
         "members"."member_namespace_id",
         "members"."member_role_id"
                FROM "members", "group_group_links_cte" AS "group_group_links"
                WHERE "members"."type" = 'GroupMember'
                        AND "members"."source_type" = 'Namespace'
                        AND "members"."requested_at" IS NULL
                        AND "members"."source_id" = "group_group_links"."shared_with_group_id"
                        AND "members"."source_type" = 'Namespace'
                        AND "members"."state" = 0
                        AND (members.access_level > 5))) members
            WHERE "members"."type" = 'GroupMember'
        AND "members"."source_type" = 'Namespace'
        AND "members"."user_id" = 47
GROUP BY  "members"."user_id" # Same query (3)

SELECT "saml_providers".*
FROM "saml_providers"
WHERE "saml_providers"."group_id" = 123 LIMIT 1 -- Extra query

SELECT MAX("members"."access_level") AS maximum_access_level,
         "members"."user_id" AS members_user_id
FROM (
    (SELECT "members"."id",
         "members"."access_level",
         "members"."source_id",
         "members"."source_type",
         "members"."user_id",
         "members"."notification_level",
         "members"."type",
         "members"."created_at",
         "members"."updated_at",
         "members"."created_by_id",
         "members"."invite_email",
         "members"."invite_token",
         "members"."invite_accepted_at",
         "members"."requested_at",
         "members"."expires_at",
         "members"."ldap",
         "members"."override",
         "members"."state",
         "members"."invite_email_success",
         "members"."member_namespace_id",
         "members"."member_role_id"
    FROM "members" LEFT OUTER
    JOIN "users"
        ON "users"."id" = "members"."user_id"
    WHERE "members"."type" = 'GroupMember'
            AND "members"."source_type" = 'Namespace'
            AND "users"."state" = 'active'
            AND "members"."state" = 0
            AND "members"."requested_at" IS NULL
            AND "members"."invite_token" IS NULL
            AND (members.access_level > 5)
            AND (members.access_level > 5)
            AND "members"."source_id" = 123)
    UNION
    (WITH "group_group_links_cte" AS MATERIALIZED 
        (SELECT "group_group_links".*
        FROM "group_group_links"
        WHERE "group_group_links"."shared_group_id" = 123)
        SELECT "members"."id",
         LEAST("group_group_links"."group_access",
         "members"."access_level") AS access_level,
         "members"."source_id",
         "members"."source_type",
         "members"."user_id",
         "members"."notification_level",
         "members"."type",
         "members"."created_at",
         "members"."updated_at",
         "members"."created_by_id",
         "members"."invite_email",
         "members"."invite_token",
         "members"."invite_accepted_at",
         "members"."requested_at",
         "members"."expires_at",
         "members"."ldap",
         "members"."override",
         "members"."state",
         "members"."invite_email_success",
         "members"."member_namespace_id",
         "members"."member_role_id"
        FROM "members", "group_group_links_cte" AS "group_group_links"
        WHERE "members"."type" = 'GroupMember'
                AND "members"."source_type" = 'Namespace'
                AND "members"."requested_at" IS NULL
                AND "members"."source_id" = "group_group_links"."shared_with_group_id"
                AND "members"."source_type" = 'Namespace'
                AND "members"."state" = 0
                AND (members.access_level > 5))) members
    WHERE "members"."type" = 'GroupMember'
        AND "members"."source_type" = 'Namespace'
        AND "members"."user_id" = 47
GROUP BY  "members"."user_id" -- Same query (4)

SELECT 1 AS one
FROM (
    (SELECT "projects".*
    FROM "projects"
    WHERE "projects"."namespace_id" IN 
        (SELECT "namespaces"."id"
        FROM "namespaces"
        WHERE "namespaces"."type" = 'Group'
                AND (traversal_ids @> ('{123}'))))) projects
    WHERE (EXISTS 
        (SELECT 1
        FROM "project_authorizations"
        WHERE "project_authorizations"."user_id" = 47
                AND (project_authorizations.project_id = projects.id))
                OR projects.visibility_level IN (10,20))
            AND "projects"."pending_delete" = FALSE
        AND "projects"."hidden" = FALSE LIMIT 1 # Same query (5)
SELECT 1 AS one
FROM "epics"
WHERE "epics"."parent_id" = 1205
        AND "epics"."group_id" IN (122, 123)
        AND "epics"."confidential" = TRUE LIMIT 1 -- Same query (6)
        
SELECT "epics".*
FROM "epics"
WHERE "epics"."parent_id" = 1205
        AND "epics"."group_id" IN (122, 123)
ORDER BY  relative_position ASC, id DESC, "epics"."id" DESC -- Same query (7)
  • Using finder with preload:
Epics::CrossHierarchyChildrenFinder.new(current_user, { parent: parent, sort: 'relative_position' }).execute(preload: true)
SQL queries 
SELECT
	"namespaces".*
FROM
	"namespaces"
WHERE
	"namespaces"."id" = 121
LIMIT
	1 -- New query

SELECT
	"saml_providers".*
FROM
	"saml_providers"
WHERE
	"saml_providers"."group_id" IN (121, 123) -- Diffrent query (1)

WITH "base_ancestors_cte" AS MATERIALIZED (
	SELECT
		"namespaces"."traversal_ids"
	FROM
		"namespaces"
	WHERE
		"namespaces"."type" = 'Group'
		AND "namespaces"."id" IN (122, 123)
)
SELECT
	"namespaces"."id",
	"namespaces"."parent_id",
	"gitlab_subscriptions"."hosted_plan_id"
FROM
	"namespaces"
	INNER JOIN (
		SELECT
			DISTINCT unnest("base_ancestors_cte"."traversal_ids")
		FROM
			base_ancestors_cte
	) AS ancestors(ancestor_id) ON namespaces.id = ancestors.ancestor_id
	LEFT OUTER JOIN gitlab_subscriptions ON gitlab_subscriptions.namespace_id = namespaces.id
WHERE
	"namespaces"."type" = 'Group' -- Same query (2)

SELECT
	MAX("members"."access_level") AS maximum_access_level,
	"members"."user_id" AS members_user_id
FROM
	(
		(
			SELECT
				"members"."id",
				"members"."access_level",
				"members"."source_id",
				"members"."source_type",
				"members"."user_id",
				"members"."notification_level",
				"members"."type",
				"members"."created_at",
				"members"."updated_at",
				"members"."created_by_id",
				"members"."invite_email",
				"members"."invite_token",
				"members"."invite_accepted_at",
				"members"."requested_at",
				"members"."expires_at",
				"members"."ldap",
				"members"."override",
				"members"."state",
				"members"."invite_email_success",
				"members"."member_namespace_id",
				"members"."member_role_id"
			FROM
				"members"
				LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
			WHERE
				"members"."type" = 'GroupMember'
				AND "members"."source_type" = 'Namespace'
				AND "users"."state" = 'active'
				AND "members"."state" = 0
				AND "members"."requested_at" IS NULL
				AND "members"."invite_token" IS NULL
				AND (members.access_level > 5)
				AND (members.access_level > 5)
				AND "members"."source_id" IN (
					SELECT
						"namespaces"."id"
					FROM
						"namespaces"
					WHERE
						"namespaces"."type" = 'Group'
						AND "namespaces"."id" IN (121, 122)
				)
		)
		UNION
			(
				WITH "group_group_links_cte" AS MATERIALIZED (
					SELECT
						"group_group_links".*
					FROM
						"group_group_links"
					WHERE
						"group_group_links"."shared_group_id" IN (
							SELECT
								"namespaces"."id"
							FROM
								"namespaces"
							WHERE
								"namespaces"."type" = 'Group'
								AND "namespaces"."id" IN (121, 122)
						)
				)
				SELECT
					"members"."id",
					LEAST(
						"group_group_links"."group_access",
						"members"."access_level"
					) AS access_level,
					"members"."source_id",
					"members"."source_type",
					"members"."user_id",
					"members"."notification_level",
					"members"."type",
					"members"."created_at",
					"members"."updated_at",
					"members"."created_by_id",
					"members"."invite_email",
					"members"."invite_token",
					"members"."invite_accepted_at",
					"members"."requested_at",
					"members"."expires_at",
					"members"."ldap",
					"members"."override",
					"members"."state",
					"members"."invite_email_success",
					"members"."member_namespace_id",
					"members"."member_role_id"
				FROM
					"members",
					"group_group_links_cte" AS "group_group_links"
				WHERE
					"members"."type" = 'GroupMember'
					AND "members"."source_type" = 'Namespace'
					AND "members"."requested_at" IS NULL
					AND "members"."source_id" = "group_group_links"."shared_with_group_id"
					AND "members"."source_type" = 'Namespace'
					AND "members"."state" = 0
					AND (members.access_level > 5)
			)
	) members
WHERE
	"members"."type" = 'GroupMember'
	AND "members"."source_type" = 'Namespace'
	AND "members"."user_id" = 47
GROUP BY
	"members"."user_id" -- Same query (3)

SELECT
	MAX("members"."access_level") AS maximum_access_level,
	"members"."user_id" AS members_user_id
FROM
	(
		(
			SELECT
				"members"."id",
				"members"."access_level",
				"members"."source_id",
				"members"."source_type",
				"members"."user_id",
				"members"."notification_level",
				"members"."type",
				"members"."created_at",
				"members"."updated_at",
				"members"."created_by_id",
				"members"."invite_email",
				"members"."invite_token",
				"members"."invite_accepted_at",
				"members"."requested_at",
				"members"."expires_at",
				"members"."ldap",
				"members"."override",
				"members"."state",
				"members"."invite_email_success",
				"members"."member_namespace_id",
				"members"."member_role_id"
			FROM
				"members"
				LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
			WHERE
				"members"."type" = 'GroupMember'
				AND "members"."source_type" = 'Namespace'
				AND "users"."state" = 'active'
				AND "members"."state" = 0
				AND "members"."requested_at" IS NULL
				AND "members"."invite_token" IS NULL
				AND (members.access_level > 5)
				AND (members.access_level > 5)
				AND "members"."source_id" = 123
		)
		UNION
			(
				WITH "group_group_links_cte" AS MATERIALIZED (
					SELECT
						"group_group_links".*
					FROM
						"group_group_links"
					WHERE
						"group_group_links"."shared_group_id" = 123
				)
				SELECT
					"members"."id",
					LEAST(
						"group_group_links"."group_access",
						"members"."access_level"
					) AS access_level,
					"members"."source_id",
					"members"."source_type",
					"members"."user_id",
					"members"."notification_level",
					"members"."type",
					"members"."created_at",
					"members"."updated_at",
					"members"."created_by_id",
					"members"."invite_email",
					"members"."invite_token",
					"members"."invite_accepted_at",
					"members"."requested_at",
					"members"."expires_at",
					"members"."ldap",
					"members"."override",
					"members"."state",
					"members"."invite_email_success",
					"members"."member_namespace_id",
					"members"."member_role_id"
				FROM
					"members",
					"group_group_links_cte" AS "group_group_links"
				WHERE
					"members"."type" = 'GroupMember'
					AND "members"."source_type" = 'Namespace'
					AND "members"."requested_at" IS NULL
					AND "members"."source_id" = "group_group_links"."shared_with_group_id"
					AND "members"."source_type" = 'Namespace'
					AND "members"."state" = 0
					AND (members.access_level > 5)
			)
	) members
WHERE
	"members"."type" = 'GroupMember'
	AND "members"."source_type" = 'Namespace'
	AND "members"."user_id" = 47
GROUP BY
	"members"."user_id" -- Same query (4)

SELECT
	1 AS one
FROM
	(
		(
			SELECT
				"projects".*
			FROM
				"projects"
			WHERE
				"projects"."namespace_id" IN (
					SELECT
						"namespaces"."id"
					FROM
						"namespaces"
					WHERE
						"namespaces"."type" = 'Group'
						AND (traversal_ids @ > ('{123}'))
				)
		)
	) projects
WHERE
	(
		EXISTS (
			SELECT
				1
			FROM
				"project_authorizations"
			WHERE
				"project_authorizations"."user_id" = 47
				AND (project_authorizations.project_id = projects.id)
		)
		OR projects.visibility_level IN (10, 20)
	)
	AND "projects"."pending_delete" = FALSE
	AND "projects"."hidden" = FALSE
LIMIT
	1 -- Same query (5)

SELECT
	1 AS one
FROM
	"epics"
WHERE
	"epics"."parent_id" = 1205
	AND "epics"."group_id" IN (122, 123)
	AND "epics"."confidential" = TRUE
LIMIT
	1 -- Same query (6)

SELECT
	"epics".*
FROM
	"epics"
WHERE
	"epics"."parent_id" = 1205
	AND "epics"."group_id" IN (122, 123)
ORDER BY
	relative_position ASC,
	id DESC,
	"epics"."id" DESC -- Same query (7)

Main difference: Instead of executing single queries for saml_providers when checking maximum_access_level for each group, this change will query all saml_providers:

SELECT "saml_providers".* FROM "saml_providers" WHERE "saml_providers"."group_id" = $ID1 LIMIT 1

SELECT "saml_providers".* FROM "saml_providers" WHERE "saml_providers"."group_id" = $ID2 LIMIT 1

SELECT "saml_providers".* FROM "saml_providers" WHERE "saml_providers"."group_id" = $ID3 LIMIT 1

SELECT "saml_providers".* FROM "saml_providers" WHERE "saml_providers"."group_id" IN ($ID1, $ID2, $ID3)

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Eugenia Grieff

Merge request reports