Huge increase in DB primary CPU utilization

Status

1. We disabled assign_custom_roles_to_group_links_saas, and re-enabled for select namespaces. Still need to investigate why it contributed to CPU scheduling delay. #507925 (comment 2242552303)
2. Possible JSONB filtering !172706 (merged)

Problem description

This increase in CPU is likely contributing to incidents like gitlab-com/gl-infra/production#18940 (closed)

-- source

We had a huge increase in primary DB CPU.

-- source

It looks like -1073226668820777184 is new. Although the pg_stat_kcache_exec_total_time does not really correlate - this is known because we are missing the planning time (/cc @NikolayS )

The query text is:

/*application:web,correlation_id:398e4eb68efb7d6ff6142ed2ee1f1d3e,endpoint_id:GET /api/:version/projects,db_config_database:gitlabhq_production,db_config_name:main*/
SELECT
    project_ids.project_id,
    custom_permissions.permissions
FROM (
    VALUES ($1, ARRAY[$2, $3])) AS project_ids (project_id, namespace_ids),
    LATERAL (
        SELECT
            "member_roles"."permissions"
        FROM
            "members"
            JOIN group_group_links ON members.source_id = group_group_links.shared_with_group_id
            JOIN member_roles ON member_roles.id = group_group_links.member_role_id
        WHERE
            "members"."user_id" = $4
            AND (group_group_links.shared_group_id IN (
                    SELECT
                        UNNEST(project_ids.namespace_ids) AS ids))
                AND (access_level > group_access)
            UNION ALL
            SELECT
                "member_roles"."permissions"
            FROM
                "members"
                JOIN group_group_links ON members.source_id = group_group_links.shared_with_group_id
                JOIN member_roles ON member_roles.id = members.member_role_id
            WHERE
                "members"."user_id" = $5
                AND (group_group_links.shared_group_id IN (
                        SELECT
                            UNNEST(project_ids.namespace_ids) AS ids))
                    AND ((access_level < group_access)
                        OR (access_level = group_access
                            AND group_group_links.member_role_id IS NOT NULL))
                UNION ALL
                SELECT
                    "member_roles"."permissions"
                FROM
                    "members"
                    INNER JOIN "member_roles" ON "member_roles"."id" = "members"."member_role_id"
                WHERE
                    "members"."user_id" = $6
                    AND "members"."source_type" = $7
                    AND (members.source_id = project_ids.project_id)
                UNION ALL
                SELECT
                    "member_roles"."permissions"
                FROM
                    "members"
                    INNER JOIN "member_roles" ON "member_roles"."id" = "members"."member_role_id"
                WHERE
                    "members"."user_id" = $8
                    AND "members"."source_type" = $9
                    AND (members.source_id IN (
                            SELECT
                                UNNEST(project_ids.namespace_ids) AS ids))
                    UNION ALL
                    SELECT
                        $10::jsonb AS permissions) AS custom_permissions

I think this is the same jsonb problem again. We should find and revert that change

/cc @jon_jenkins @mattkasa