Database timeout when viewing the group dependency list

Sentry Issue: GITLABCOM-1J6C

UI shows Error fetching the dependency list. Please check your network connection and try again.

SQL

SELECT
    count(*)
FROM
    (
        WITH our_occurrences AS MATERIALIZED (
            SELECT
                DISTINCT ON ( sbom_occurrences.component_version_id )
                sbom_occurrences.*,
                count( sbom_occurrences.id ) OVER ( PARTITION BY sbom_occurrences.component_version_id ) AS occurrence_count,
                dense_rank() OVER (
                    PARTITION BY sbom_occurrences.component_version_id
                    ORDER BY project_id
                ) AS project_count
            FROM
                sbom_occurrences
            WHERE
                sbom_occurrences.project_id IN (
                    SELECT
                        projects.id
                    FROM
                        projects
                    WHERE
                        projects.namespace_id IN (
                            SELECT
                                namespaces.traversal_ids[array_length(
                                    namespaces.traversal_ids,
                                    $3
                                )] AS id
                            FROM
                                namespaces
                            WHERE
                                namespaces.type = $4 AND
                                traversal_ids @> $5
                        ) AND
                        projects.marked_for_deletion_at IS NULL AND
                        projects.pending_delete = $6
                ) AND
                sbom_occurrences.component_version_id IS NOT NULL
            ORDER BY
                sbom_occurrences.component_version_id DESC,
                project_count DESC,
                sbom_occurrences.id DESC
        )
        SELECT
            $1 AS one
        FROM
            our_occurrences AS sbom_occurrences
        LIMIT $2
    ) AS subquery_for_count;

Implementation plan

This timeout is happening because we are using offset pagination which is performing COUNT(*) on a large dataset.

It should be fixed by using keyset pagination.

1. Update Gitlab::Serilizer::Pagination so that it can use either offset or keyset pagination depending on the params provided. (see API::Helpers::PaginationStrategies for reference).
2. Change the ordering scopes on Sbom::Occurrence to use Gitlab::Pagination::Keyset::Order.build.
3. Update frontend to use keyset pagination instead of offset pagination.

Scheduling

There are several components to resolve this. Please follow the epic at Group-level dependency list page performance im... (&12371 - closed) for updates and estimated timeframes.