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.
- Update
Gitlab::Serilizer::Pagination
so that it can use either offset or keyset pagination depending on the params provided. (seeAPI::Helpers::PaginationStrategies
for reference). - Change the ordering scopes on
Sbom::Occurrence
to useGitlab::Pagination::Keyset::Order.build
. - 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) for updates and estimated timeframes.
Edited by Neil McCorrison