Add keyset pagination to dependency aggregations
What does this MR do and why?
Relates to: #437647 (closed)
This MR adds keyset pagination onto the query introduced in !150476 (merged).
SQL
Before:
Click to expand
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/28786/commands/89808
WITH namespaces AS MATERIALIZED (
SELECT
namespaces.traversal_ids
FROM
namespaces
WHERE
namespaces.type = 'Group' AND
traversal_ids >= '{9970}' AND
traversal_ids < '{9971}'
)
SELECT
min( outer_occurrences.id )::pg_catalog.int8 AS id,
outer_occurrences.component_id,
outer_occurrences.component_version_id,
min( outer_occurrences.package_manager ) AS package_manager,
min( outer_occurrences.input_file_path ) AS input_file_path,
jsonb_agg( outer_occurrences.licenses -> 0 ) AS licenses,
sum( counts.occurrence_count )::pg_catalog.int4 AS occurrence_count,
sum( counts.vulnerability_count )::pg_catalog.int4 AS vulnerability_count,
sum( counts.project_count )::pg_catalog.int4 AS project_count
FROM
(
SELECT
DISTINCT ON ( inner_occurrences.component_id, inner_occurrences.component_version_id )
inner_occurrences.id,
inner_occurrences.created_at,
inner_occurrences.updated_at,
inner_occurrences.component_version_id,
inner_occurrences.project_id,
inner_occurrences.pipeline_id,
inner_occurrences.source_id,
inner_occurrences.commit_sha,
inner_occurrences.component_id,
inner_occurrences.uuid,
inner_occurrences.package_manager,
inner_occurrences.component_name,
inner_occurrences.input_file_path,
inner_occurrences.licenses,
inner_occurrences.highest_severity,
inner_occurrences.vulnerability_count,
inner_occurrences.source_package_id,
inner_occurrences.archived,
inner_occurrences.traversal_ids,
inner_occurrences.ancestors
FROM
namespaces,
LATERAL (
SELECT
DISTINCT ON ( sbom_occurrences.component_id, sbom_occurrences.component_version_id )
sbom_occurrences.id,
sbom_occurrences.created_at,
sbom_occurrences.updated_at,
sbom_occurrences.component_version_id,
sbom_occurrences.project_id,
sbom_occurrences.pipeline_id,
sbom_occurrences.source_id,
sbom_occurrences.commit_sha,
sbom_occurrences.component_id,
sbom_occurrences.uuid,
sbom_occurrences.package_manager,
sbom_occurrences.component_name,
sbom_occurrences.input_file_path,
sbom_occurrences.licenses,
sbom_occurrences.highest_severity,
sbom_occurrences.vulnerability_count,
sbom_occurrences.source_package_id,
sbom_occurrences.archived,
sbom_occurrences.traversal_ids,
sbom_occurrences.ancestors
FROM
sbom_occurrences
WHERE
sbom_occurrences.traversal_ids = namespaces.traversal_ids::pg_catalog.int8[] AND
sbom_occurrences.archived = false
ORDER BY
sbom_occurrences.component_id ASC,
sbom_occurrences.component_version_id ASC
LIMIT 20
) AS inner_occurrences
ORDER BY
inner_occurrences.component_id ASC,
inner_occurrences.component_version_id ASC
LIMIT 20
) AS outer_occurrences,
LATERAL (
SELECT
count( project_id ) AS occurrence_count,
count( project_id ) AS project_count,
sum( vulnerability_count ) AS vulnerability_count
FROM
sbom_occurrences
WHERE
traversal_ids >= '{9970}' AND
traversal_ids < '{9971}' AND
sbom_occurrences.archived = false AND
sbom_occurrences.component_version_id = outer_occurrences.component_version_id
) AS counts
GROUP BY
outer_occurrences.component_id,
outer_occurrences.component_version_id
ORDER BY
min( outer_occurrences.component_id ) ASC,
min( outer_occurrences.component_version_id ) ASC;
After:
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/28786/commands/89789
WITH namespaces AS MATERIALIZED (
SELECT
namespaces.traversal_ids
FROM
namespaces
WHERE
namespaces.type = 'Group' AND
traversal_ids >= '{9970}' AND
traversal_ids < '{9971}'
)
SELECT
min( outer_occurrences.id )::pg_catalog.int8 AS id,
outer_occurrences.component_id,
outer_occurrences.component_version_id,
min( outer_occurrences.package_manager ) AS package_manager,
min( outer_occurrences.input_file_path ) AS input_file_path,
jsonb_agg( outer_occurrences.licenses -> 0 ) AS licenses,
sum( counts.occurrence_count )::pg_catalog.int4 AS occurrence_count,
sum( counts.vulnerability_count )::pg_catalog.int4 AS vulnerability_count,
sum( counts.project_count )::pg_catalog.int4 AS project_count
FROM
(
SELECT
DISTINCT ON ( inner_occurrences.component_id, inner_occurrences.component_version_id )
inner_occurrences.id,
inner_occurrences.created_at,
inner_occurrences.updated_at,
inner_occurrences.component_version_id,
inner_occurrences.project_id,
inner_occurrences.pipeline_id,
inner_occurrences.source_id,
inner_occurrences.commit_sha,
inner_occurrences.component_id,
inner_occurrences.uuid,
inner_occurrences.package_manager,
inner_occurrences.component_name,
inner_occurrences.input_file_path,
inner_occurrences.licenses,
inner_occurrences.highest_severity,
inner_occurrences.vulnerability_count,
inner_occurrences.source_package_id,
inner_occurrences.archived,
inner_occurrences.traversal_ids,
inner_occurrences.ancestors
FROM
namespaces,
LATERAL (
SELECT
DISTINCT ON ( sbom_occurrences.component_id, sbom_occurrences.component_version_id )
sbom_occurrences.id,
sbom_occurrences.created_at,
sbom_occurrences.updated_at,
sbom_occurrences.component_version_id,
sbom_occurrences.project_id,
sbom_occurrences.pipeline_id,
sbom_occurrences.source_id,
sbom_occurrences.commit_sha,
sbom_occurrences.component_id,
sbom_occurrences.uuid,
sbom_occurrences.package_manager,
sbom_occurrences.component_name,
sbom_occurrences.input_file_path,
sbom_occurrences.licenses,
sbom_occurrences.highest_severity,
sbom_occurrences.vulnerability_count,
sbom_occurrences.source_package_id,
sbom_occurrences.archived,
sbom_occurrences.traversal_ids,
sbom_occurrences.ancestors
FROM
sbom_occurrences
WHERE
sbom_occurrences.traversal_ids = namespaces.traversal_ids::pg_catalog.int8[] AND
sbom_occurrences.archived = false AND
(
sbom_occurrences.component_id,
sbom_occurrences.component_version_id
) > ( 153, 1775 )
ORDER BY
sbom_occurrences.component_id ASC,
sbom_occurrences.component_version_id ASC
LIMIT 21
) AS inner_occurrences
ORDER BY
inner_occurrences.component_id ASC,
inner_occurrences.component_version_id ASC
LIMIT 21
) AS outer_occurrences,
LATERAL (
SELECT
count( project_id ) AS occurrence_count,
count( project_id ) AS project_count,
sum( vulnerability_count ) AS vulnerability_count
FROM
sbom_occurrences
WHERE
traversal_ids >= '{9970}' AND
traversal_ids < '{9971}' AND
sbom_occurrences.archived = false AND
sbom_occurrences.component_version_id = outer_occurrences.component_version_id
) AS counts
WHERE
(
outer_occurrences.component_id,
outer_occurrences.component_version_id
) > ( 153, 1775 )
GROUP BY
outer_occurrences.component_id,
outer_occurrences.component_version_id
ORDER BY
min( outer_occurrences.component_id ) ASC,
min( outer_occurrences.component_version_id ) ASC;
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.
Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
Before | After |
---|---|
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
Edited by Brian Williams