Exclude issues in archived ancestor groups from non_archived (!264225) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

Issue.non_archived only checks projects.archived, so issues in projects and subgroups under an archived group still show up, for example on group boards and in group issue lists. Closes #585677 (closed).

The first ancestor-aware version was reverted in !218420 (merged) because it timed out boards (https://gitlab.com/gitlab-com/request-for-help/-/issues/4001). It joined namespaces with ON namespaces.id = projects.namespace_id OR namespaces.id = issues.namespace_id, and the OR join led PostgreSQL to a bad plan (2.8 minutes in the RFH).

This MR checks ancestors without that join. Every issue row already has namespace_traversal_ids, so the scope adds:

AND NOT EXISTS (
  SELECT 1 FROM namespace_settings
  WHERE namespace_settings.namespace_id = ANY (issues.namespace_traversal_ids)
    AND namespace_settings.archived = TRUE
)

It runs as an index-only scan on the partial index index_namespace_settings_on_namespace_id_where_archived_true.

The change is behind the issue_non_archived_ancestor_scoping gitlab_com_derisk flag. Rollout: #586241

Decisions

Choice	Why
`namespace_settings.archived`, not `namespaces.state`	It's the source of truth for `Namespace#archived?` and `Project.self_and_ancestors_non_archived`. On production, 117 archived groups have `state` deletion-scheduled or deletion-in-progress, so a `state` check would miss them.
`issues.namespace_traversal_ids`	No join needed. `IssuesFinder` already relies on this column for group hierarchy filtering.
Keep `projects.archived = false`	Archiving a project doesn't write `namespace_settings`, so the project check is still needed.

Database

Plans from postgres.ai (gitlab-production-main), second run of each query in the same session.

Query	Master	This MR
`IssuesFinder` group list, `gitlab-org` (9970), `LIMIT 20`	22.0 ms, 8,749 hits	28.7 ms, 8,855 hits
Board "Open" list, `gitlab-org`, no filters, `LIMIT 21`	2.76 min, 1.29M hits	1.34 min, 1.97M hits

List query: the new check adds 106 buffer hits for 20 rows. The plan is unchanged.
Board query: this is the worst case, the board query from https://gitlab.com/gitlab-com/request-for-help/-/issues/4001 with every filter removed, so it sorts every open issue in gitlab-org (about 130,000 rows). It's slow on master too. The new check adds about 680,000 buffer hits, all from cache, as an index-only scan on index_namespace_settings_on_namespace_id_where_archived_true. The plan shape is unchanged. Both runs spent most of their time on disk reads, so the time difference is noise.
Reverted version: the version reverted in !218420 (merged) took 2.8 minutes on the RFH query because of its OR join on namespaces. This MR has no join on namespaces.

On Database Lab, the RFH incident query itself (milestone=Started board) took 1.2 ms on master and 3.3 ms with this MR. A count of all open issues in gitlab-org is slow on master as well. Its timings mostly reflect caching, and its cost estimate goes up by about 9%.

IssuesFinder group list, master

SELECT issues.id
FROM issues
INNER JOIN projects ON projects.id = issues.project_id
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE issues.namespace_traversal_ids[1] = 9970
  AND (
    project_features.issues_access_level > 0
    OR project_features.issues_access_level IS NULL
  )
  AND issues.state_id = 1
  AND (
    issues.project_id IS NULL
    OR projects.archived = FALSE
  )
ORDER BY issues.id DESC
LIMIT 20;

IssuesFinder group list, this MR

SELECT issues.id
FROM issues
INNER JOIN projects ON projects.id = issues.project_id
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE issues.namespace_traversal_ids[1] = 9970
  AND (
    project_features.issues_access_level > 0
    OR project_features.issues_access_level IS NULL
  )
  AND issues.state_id = 1
  AND (
    issues.project_id IS NULL
    OR projects.archived = FALSE
  )
  AND NOT EXISTS (
    SELECT 1
    FROM namespace_settings
    WHERE namespace_settings.namespace_id = ANY (issues.namespace_traversal_ids)
      AND namespace_settings.archived = TRUE
  )
ORDER BY issues.id DESC
LIMIT 20;

Board list, master

SELECT issues.id
FROM issues
INNER JOIN projects ON projects.id = issues.project_id
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE NOT EXISTS (
    SELECT 1
    FROM banned_users
    WHERE banned_users.user_id = (issues.author_id + 0)
  )
  AND projects.namespace_id IN (
    SELECT namespaces.id
    FROM unnest(
      COALESCE(
        (
          SELECT namespace_descendants.self_and_descendant_group_ids
          FROM namespace_descendants
          WHERE namespace_descendants.outdated_at IS NULL
            AND namespace_descendants.namespace_id = 9970
        ),
        (
          SELECT array_agg(namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)])
          FROM namespaces
          WHERE namespaces.type = 'Group'
            AND namespaces.traversal_ids @> '{9970}'
        )
      )
    ) AS namespaces(id)
  )
  AND (
    EXISTS (
      SELECT 1
      FROM project_authorizations
      WHERE project_authorizations.user_id = 3379682
        AND project_authorizations.project_id = projects.id
        AND project_authorizations.access_level >= 10
    )
    OR projects.visibility_level IN (10, 20)
  )
  AND (
    project_features.issues_access_level IS NULL
    OR project_features.issues_access_level IN (20, 30)
    OR (
      project_features.issues_access_level = 10
      AND EXISTS (
        SELECT 1
        FROM project_authorizations
        WHERE project_authorizations.user_id = 3379682
          AND project_authorizations.project_id = project_features.project_id
          AND project_authorizations.access_level >= 10
      )
    )
  )
  AND issues.state_id = 1
  AND (
    (issues.project_id IS NOT NULL AND projects.archived = FALSE)
    OR issues.project_id IS NULL
  )
  AND issues.work_item_type_id IN (1, 2, 5)
ORDER BY issues.relative_position ASC NULLS LAST, issues.id ASC
LIMIT 21;

Board list, this MR

SELECT issues.id
FROM issues
INNER JOIN projects ON projects.id = issues.project_id
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE NOT EXISTS (
    SELECT 1
    FROM banned_users
    WHERE banned_users.user_id = (issues.author_id + 0)
  )
  AND projects.namespace_id IN (
    SELECT namespaces.id
    FROM unnest(
      COALESCE(
        (
          SELECT namespace_descendants.self_and_descendant_group_ids
          FROM namespace_descendants
          WHERE namespace_descendants.outdated_at IS NULL
            AND namespace_descendants.namespace_id = 9970
        ),
        (
          SELECT array_agg(namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)])
          FROM namespaces
          WHERE namespaces.type = 'Group'
            AND namespaces.traversal_ids @> '{9970}'
        )
      )
    ) AS namespaces(id)
  )
  AND (
    EXISTS (
      SELECT 1
      FROM project_authorizations
      WHERE project_authorizations.user_id = 3379682
        AND project_authorizations.project_id = projects.id
        AND project_authorizations.access_level >= 10
    )
    OR projects.visibility_level IN (10, 20)
  )
  AND (
    project_features.issues_access_level IS NULL
    OR project_features.issues_access_level IN (20, 30)
    OR (
      project_features.issues_access_level = 10
      AND EXISTS (
        SELECT 1
        FROM project_authorizations
        WHERE project_authorizations.user_id = 3379682
          AND project_authorizations.project_id = project_features.project_id
          AND project_authorizations.access_level >= 10
      )
    )
  )
  AND issues.state_id = 1
  AND NOT EXISTS (
    SELECT 1
    FROM namespace_settings
    WHERE namespace_settings.namespace_id = ANY (issues.namespace_traversal_ids)
      AND namespace_settings.archived = TRUE
  )
  AND (
    (issues.project_id IS NOT NULL AND projects.archived = FALSE)
    OR issues.project_id IS NULL
  )
  AND issues.work_item_type_id IN (1, 2, 5)
ORDER BY issues.relative_position ASC NULLS LAST, issues.id ASC
LIMIT 21;

How to set up and validate locally

Enable the flag: Feature.enable(:issue_non_archived_ancestor_scoping)
Create a group with a subgroup and a project in the subgroup, and add an issue to the project.
Archive the top-level group.
Open the group board or call GET /api/v4/groups/:id/issues. The issue no longer shows. With the flag disabled, or with non_archived=false in the API, it shows again.

References

#585677 (closed)
https://gitlab.com/gitlab-com/request-for-help/-/issues/4001
!218420 (merged) (revert)
!218891 (closed) (earlier attempt)

🤖 Generated with Claude Code

Edited Oct 22, 2026 by Shubham Kumar

Exclude issues in archived ancestor groups from non_archived

What does this MR do and why?

Decisions

Database

How to set up and validate locally

References

Merge request reports