Use CTE for Group access_level search

What does this MR do and why?

Use CTE for Group access_level search

The query performed bad after upgrade to PostgreSQL 16. This change will increase performance

The change is behind the feature flag cte_for_group_search_access_level

Query plan:

Limit  (cost=1106102.04..1106102.09 rows=20 width=2951) (actual time=13.038..13.045 rows=20 loops=1)
  Buffers: shared hit=4488
  CTE groups_with_min_access_level_cte
    ->  Unique  (cost=985333.76..1071802.01 rows=735900 width=376) (actual time=9.119..11.520 rows=3907 loops=1)
          Buffers: shared hit=4488
          CTE descendants_base_cte
            ->  Nested Loop  (cost=1.13..29.21 rows=1 width=32) (actual time=0.027..0.419 rows=49 loops=1)
                  Buffers: shared hit=318
                  ->  Index Scan using idx_members_on_user_and_source_and_source_type_and_member_role on members  (cost=0.56..22.04 rows=2 width=4) (actual time=0.018..0.107 rows=49 loops=1)
                        Index Cond: ((user_id = 1) AND ((source_type)::text = 'Namespace'::text))
                        Filter: ((requested_at IS NULL) AND (access_level >= 10) AND (access_level >= 20) AND ((type)::text = 'GroupMember'::text))
                        Rows Removed by Filter: 19
                        Buffers: shared hit=73
                  ->  Index Scan using namespaces_pkey on namespaces namespaces_1  (cost=0.56..3.58 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=49)
                        Index Cond: (id = members.source_id)
                        Filter: ((type)::text = 'Group'::text)
                        Buffers: shared hit=245
          ->  Sort  (cost=985304.55..987144.30 rows=735900 width=376) (actual time=9.118..9.341 rows=3907 loops=1)
                Sort Key: namespaces_2.id, namespaces_2.name, namespaces_2.path, namespaces_2.owner_id, namespaces_2.created_at, namespaces_2.updated_at, namespaces_2.description, namespaces_2.avatar, namespaces_2.membership_lock, namespaces_2.share_with_group_lock, namespaces_2.visibility_level, namespaces_2.request_access_enabled, namespaces_2.ldap_sync_status, namespaces_2.ldap_sync_error, namespaces_2.ldap_sync_last_update_at, namespaces_2.ldap_sync_last_successful_update_at, namespaces_2.ldap_sync_last_sync_at, namespaces_2.description_html, namespaces_2.lfs_enabled, namespaces_2.parent_id, namespaces_2.shared_runners_minutes_limit, namespaces_2.repository_size_limit, namespaces_2.require_two_factor_authentication, namespaces_2.two_factor_grace_period, namespaces_2.cached_markdown_version, namespaces_2.project_creation_level, namespaces_2.runners_token, namespaces_2.file_template_project_id, namespaces_2.saml_discovery_token, namespaces_2.runners_token_encrypted, namespaces_2.custom_project_templates_group_id, namespaces_2.auto_devops_enabled, namespaces_2.extra_shared_runners_minutes_limit, namespaces_2.last_ci_minutes_notification_at, namespaces_2.last_ci_minutes_usage_notification_level, namespaces_2.subgroup_creation_level, namespaces_2.max_pages_size, namespaces_2.max_artifacts_size, namespaces_2.mentions_disabled, namespaces_2.default_branch_protection, namespaces_2.max_personal_access_token_lifetime, namespaces_2.push_rule_id, namespaces_2.shared_runners_enabled, namespaces_2.allow_descendants_override_disabled_shared_runners, namespaces_2.traversal_ids, namespaces_2.organization_id
                Sort Method: quicksort  Memory: 1190kB
                Buffers: shared hit=4488
                ->  Nested Loop  (cost=0.81..671046.47 rows=735900 width=376) (actual time=0.500..6.901 rows=3907 loops=1)
                      Buffers: shared hit=4488
                      ->  Nested Loop Anti Join  (cost=0.00..0.07 rows=1 width=32) (actual time=0.448..0.871 rows=46 loops=1)
                            Join Filter: ((d2.id <> d1.id) AND (d2.id = ANY (d1.traversal_ids)))
                            Rows Removed by Join Filter: 2257
                            Buffers: shared hit=318
                            ->  CTE Scan on descendants_base_cte d1  (cost=0.00..0.02 rows=1 width=36) (actual time=0.028..0.033 rows=49 loops=1)
                                  Buffers: shared hit=10
                            ->  CTE Scan on descendants_base_cte d2  (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.011 rows=46 loops=49)
                                  Buffers: shared hit=308
                      ->  Index Scan using index_namespaces_on_traversal_ids_for_groups_btree on namespaces namespaces_2  (cost=0.81..663687.41 rows=735900 width=376) (actual time=0.008..0.116 rows=85 loops=46)
                            Index Cond: ((traversal_ids < next_traversal_ids_sibling(d1.traversal_ids)) AND (traversal_ids >= d1.traversal_ids))
                            Buffers: shared hit=4170
  ->  Sort  (cost=34300.03..36139.78 rows=735900 width=2951) (actual time=13.037..13.038 rows=20 loops=1)
        Sort Key: namespaces.name, namespaces.id
        Sort Method: top-N heapsort  Memory: 38kB
        Buffers: shared hit=4488
        ->  CTE Scan on groups_with_min_access_level_cte namespaces  (cost=0.00..14718.00 rows=735900 width=2951) (actual time=9.121..12.468 rows=3907 loops=1)
              Buffers: shared hit=4488
Planning:
  Buffers: shared hit=25
Planning Time: 0.827 ms
Execution Time: 13.176 ms

MR acceptance checklist

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

Related to #502465 (closed)