Skip epics confidentiality check when param is false

What does this MR do and why?

We can avoid the expensive operation of checking confidentiality access for subgroups if the request only expects public epics (confidential filter is set to false).

Database changes

previous query

SELECT "epics".*
FROM "epics"
WHERE "epics"."group_id" IN 
    (SELECT "namespaces"."id"
    FROM "namespaces"
    WHERE "namespaces"."type" = 'Group'
            AND (traversal_ids @> ('{22}'))
            AND "namespaces"."visibility_level" IN (10, 20))
        AND ("epics"."confidential" = FALSE
        OR "epics"."confidential" = TRUE
        AND "epics"."group_id" IN 
    (SELECT "namespaces"."id"
    FROM "namespaces"
    WHERE "namespaces"."type" = 'Group'
            AND "namespaces"."id" IN 
        (SELECT "namespaces"."id"
        FROM "namespaces"
        WHERE "namespaces"."type" = 'Group'
                AND (traversal_ids @> ('{22}')))
                AND (id IN (
            (SELECT "namespaces"."id"
            FROM "namespaces"
            WHERE "namespaces"."type" = 'Group'
                    AND (traversal_ids && ARRAY
                (SELECT "members"."source_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"."source_id" IN 
                    (SELECT "namespaces"."id"
                    FROM "namespaces"
                    WHERE "namespaces"."type" = 'Group'
                            AND (traversal_ids @> ('{22}')))
                            AND (access_level >= 15)
                            AND "members"."user_id" = 95)))
                    UNION
                        ::~(SELECT "namespaces"."id"
                        FROM "namespaces"
                        WHERE "namespaces"."type" = 'Group'
                                AND (traversal_ids && ARRAY
                            (SELECT "group_group_links"."shared_group_id"
                            FROM "group_group_links"
                            WHERE "group_group_links"."shared_group_id" IN 
                                (SELECT "namespaces"."id"
                                FROM "namespaces"
                                WHERE "namespaces"."type" = 'Group'
                                        AND (traversal_ids @> ('{22}')))
                                        AND (group_access >= 15)
                                        AND "group_group_links"."shared_with_group_id" IN 
                                    (SELECT "members"."source_id"
                                    FROM "members"
                                    WHERE "members"."type" = 'GroupMember'
                                            AND "members"."source_type" = 'Namespace'
                                            AND "members"."user_id" = 95
                                            AND "members"."user_id" IS NOT NULL
                                            AND "members"."state" = 0
                                            AND "members"."requested_at" IS NULL
                                            AND (members.access_level > 5)))::bigint[]))))))
                                        AND "epics"."confidential" = FALSE
                            ORDER BY  "epics"."created_at" DESC LIMIT 20 OFFSET 0

new query

SELECT "epics".*
FROM "epics"
WHERE "epics"."group_id" IN 
    (SELECT "namespaces"."id"
    FROM "namespaces"
    WHERE "namespaces"."type" = 'Group'
            AND (traversal_ids @> ('{22}'))
            AND "namespaces"."visibility_level" IN (10, 20))
        AND "epics"."confidential" = FALSE
        AND "epics"."confidential" = FALSE
ORDER BY  "epics"."created_at" DESC LIMIT 20 OFFSET 0

Query plan before	Query plan after
console.postgres.ai (internal)	console.postgres.ai (internal)

References

Related to #558778 (closed)

Screen recording

epics_finder_with_confidential

How to replicate

1. Create a public group and add two epics, one confidential and one non-confidential
2. Use rails console to verify that the finder skips the confidentiality check when confidential: false and includes it when confidential is true or not present

Click to expand

    > group, non_member = Group.last, User.last
    > EpicsFinder.new(non_member, group_id: group.id, confidential: false, include_descendant_groups: true).execute
    
    #=>
    SELECT "epics".* FROM "epics" WHERE "epics"."group_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{24}'))) AND "epics"."confidential" = TRUE ORDER BY "epics"."id" DESC


    > EpicsFinder.new(non_member, group_id: group.id, confidential: true, include_descendant_groups: true).execute

    #=> 
    SELECT "epics".* FROM "epics" WHERE "epics"."group_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{24}')) AND "namespaces"."visibility_level" IN (10, 20)) AND ("epics"."confidential" = FALSE OR "epics"."confidential" = TRUE AND "epics"."group_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{24}'))) AND (id IN ((SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids && ARRAY(SELECT "members"."source_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"."source_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{24}'))) AND (access_level >= 15) AND "members"."user_id" = 95)))
UNION
(SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids && ARRAY(SELECT "group_group_links"."shared_group_id" FROM "group_group_links" WHERE "group_group_links"."shared_group_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{24}'))) AND (group_access >= 15) AND "group_group_links"."shared_with_group_id" IN (SELECT "members"."source_id" FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."user_id" = 95 AND "members"."user_id" IS NOT NULL AND "members"."state" = 0 AND "members"."requested_at" IS NULL AND (members.access_level > 5)))::bigint[])))))) AND "epics"."confidential" = TRUE ORDER BY "epics"."created_at" DESC LIMIT 20 OFFSET 0

1. Alternately, login with a user that is not a member of the group and make a REST request to fetch all non-confidential epics for the group, for example, visiting the URL https://{gdk_root}/api/v4/groups/{group_path}/epics?confidential=false
2. Observe the logs with tail -f log/development.log in the gitlab directory and verify the SQL query for SELECT "epics".* FROM "epics"

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.