Add component_names filter to group level dependency list (!131824) · Merge requests · GitLab.org / GitLab

mo khan requested to merge mokhax/422088/filter-by-component-names into master Sep 14, 2023

What does this MR do and why?

This change parses the component_names[]= query string parameter and uses it to filter results by dependencies that match the given names. This filter is only available when the group_level_dependencies_filtering feature flag is enabled and if the max depth of the group hierarchy is below a certain threshold.

#422088 (closed)

Before:

WITH "our_occurrences" AS MATERIALIZED (
  SELECT "sbom_occurrences".*
  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, 1)] AS id
      FROM "namespaces"
      WHERE "namespaces"."type" = 'Group'
      AND (traversal_ids @> ('{6543}'))
    )
    AND "projects"."marked_for_deletion_at" IS NULL
    AND "projects"."pending_delete" = FALSE
  )
)
SELECT sbom_occurrences.*, agg_occurrences.occurrence_count, agg_occurrences.project_count, "sbom_occurrences"."id" AS t0_r0, "projects"."id" AS t1_r0, "projects"."name" AS t1_r1, "projects"."path" AS t1_r2, "projects"."description" AS t1_r3, "projects"."created_at" AS t1_r4, "projects"."updated_at" AS t1_r5, "projects"."creator_id" AS t1_r6, "projects"."namespace_id" AS t1_r7, "projects"."last_activity_at" AS t1_r8, "projects"."import_url" AS t1_r9, "projects"."visibility_level" AS t1_r10, "projects"."archived" AS t1_r11, "projects"."avatar" AS t1_r12, "projects"."merge_requests_template" AS t1_r13, "projects"."star_count" AS t1_r14, "projects"."merge_requests_rebase_enabled" AS t1_r15, "projects"."import_type" AS t1_r16, "projects"."import_source" AS t1_r17, "projects"."approvals_before_merge" AS t1_r18, "projects"."reset_approvals_on_push" AS t1_r19, "projects"."merge_requests_ff_only_enabled" AS t1_r20, "projects"."issues_template" AS t1_r21, "projects"."mirror" AS t1_r22, "projects"."mirror_last_update_at" AS t1_r23, "projects"."mirror_last_successful_update_at" AS t1_r24, "projects"."mirror_user_id" AS t1_r25, "projects"."shared_runners_enabled" AS t1_r26, "projects"."runners_token" AS t1_r27, "projects"."build_allow_git_fetch" AS t1_r28, "projects"."build_timeout" AS t1_r29, "projects"."mirror_trigger_builds" AS t1_r30, "projects"."pending_delete" AS t1_r31, "projects"."public_builds" AS t1_r32, "projects"."last_repository_check_failed" AS t1_r33, "projects"."last_repository_check_at" AS t1_r34, "projects"."only_allow_merge_if_pipeline_succeeds" AS t1_r35, "projects"."has_external_issue_tracker" AS t1_r36, "projects"."repository_storage" AS t1_r37, "projects"."repository_read_only" AS t1_r38, "projects"."request_access_enabled" AS t1_r39, "projects"."has_external_wiki" AS t1_r40, "projects"."ci_config_path" AS t1_r41, "projects"."lfs_enabled" AS t1_r42, "projects"."description_html" AS t1_r43, "projects"."only_allow_merge_if_all_discussions_are_resolved" AS t1_r44, "projects"."repository_size_limit" AS t1_r45, "projects"."printing_merge_request_link_enabled" AS t1_r46, "projects"."auto_cancel_pending_pipelines" AS t1_r47, "projects"."service_desk_enabled" AS t1_r48, "projects"."cached_markdown_version" AS t1_r49, "projects"."delete_error" AS t1_r50, "projects"."last_repository_updated_at" AS t1_r51, "projects"."disable_overriding_approvers_per_merge_request" AS t1_r52, "projects"."storage_version" AS t1_r53, "projects"."resolve_outdated_diff_discussions" AS t1_r54, "projects"."remote_mirror_available_overridden" AS t1_r55, "projects"."only_mirror_protected_branches" AS t1_r56, "projects"."pull_mirror_available_overridden" AS t1_r57, "projects"."jobs_cache_index" AS t1_r58, "projects"."external_authorization_classification_label" AS t1_r59, "projects"."mirror_overwrites_diverged_branches" AS t1_r60, "projects"."pages_https_only" AS t1_r61, "projects"."external_webhook_token" AS t1_r62, "projects"."packages_enabled" AS t1_r63, "projects"."merge_requests_author_approval" AS t1_r64, "projects"."pool_repository_id" AS t1_r65, "projects"."runners_token_encrypted" AS t1_r66, "projects"."bfg_object_map" AS t1_r67, "projects"."detected_repository_languages" AS t1_r68, "projects"."merge_requests_disable_committers_approval" AS t1_r69, "projects"."require_password_to_approve" AS t1_r70, "projects"."max_pages_size" AS t1_r71, "projects"."max_artifacts_size" AS t1_r72, "projects"."pull_mirror_branch_prefix" AS t1_r73, "projects"."remove_source_branch_after_merge" AS t1_r74, "projects"."marked_for_deletion_at" AS t1_r75, "projects"."marked_for_deletion_by_user_id" AS t1_r76, "projects"."autoclose_referenced_issues" AS t1_r77, "projects"."suggestion_commit_message" AS t1_r78, "projects"."project_namespace_id" AS t1_r79, "projects"."hidden" AS t1_r80, "projects"."organization_id" AS t1_r81, "routes"."id" AS t2_r0, "routes"."source_id" AS t2_r1, "routes"."source_type" AS t2_r2, "routes"."path" AS t2_r3, "routes"."created_at" AS t2_r4, "routes"."updated_at" AS t2_r5, "routes"."name" AS t2_r6, "routes"."namespace_id" AS t2_r7, "sbom_components"."id" AS t3_r0, "sbom_components"."created_at" AS t3_r1, "sbom_components"."updated_at" AS t3_r2, "sbom_components"."component_type" AS t3_r3, "sbom_components"."name" AS t3_r4, "sbom_components"."purl_type" AS t3_r5, "sbom_component_versions"."id" AS t4_r0, "sbom_component_versions"."created_at" AS t4_r1, "sbom_component_versions"."updated_at" AS t4_r2, "sbom_component_versions"."component_id" AS t4_r3, "sbom_component_versions"."version" AS t4_r4, "sbom_sources"."id" AS t5_r0, "sbom_sources"."created_at" AS t5_r1, "sbom_sources"."updated_at" AS t5_r2, "sbom_sources"."source_type" AS t5_r3, "sbom_sources"."source" AS t5_r4
FROM "sbom_occurrences"
INNER JOIN "sbom_components" ON "sbom_components"."id" = "sbom_occurrences"."component_id"
LEFT OUTER JOIN "projects" ON "projects"."id" = "sbom_occurrences"."project_id"
LEFT OUTER JOIN "routes" ON "routes"."source_type" = 'Project' AND "routes"."source_id" = "projects"."id"
LEFT OUTER JOIN "sbom_component_versions" ON "sbom_component_versions"."id" = "sbom_occurrences"."component_version_id"
LEFT OUTER JOIN "sbom_sources" ON "sbom_sources"."id" = "sbom_occurrences"."source_id"
INNER JOIN (
  SELECT component_id,
        COUNT(DISTINCT id) AS occurrence_count,
        COUNT(DISTINCT project_id) AS project_count
  FROM our_occurrences
  GROUP BY component_id
) agg_occurrences ON sbom_occurrences.component_id = agg_occurrences.component_id
WHERE "sbom_occurrences"."id" IN (
  SELECT "our_occurrences"."id"
  FROM "our_occurrences"
)
AND "sbom_components"."name" = 'bundler'
ORDER BY "sbom_occurrences"."id" ASC
LIMIT 20
OFFSET 0;

Time: 573.115 ms
  - planning: 25.914 ms
  - execution: 547.201 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 248111 (~1.90 GiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/22603/commands/72703

After:

WITH "our_occurrences" AS MATERIALIZED (
  SELECT "sbom_occurrences".*
  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, 1)] AS id
      FROM "namespaces"
      WHERE "namespaces"."type" = 'Group'
      AND (traversal_ids @> ('{6543}'))
    )
    AND "projects"."marked_for_deletion_at" IS NULL
    AND "projects"."pending_delete" = FALSE
  )
)
SELECT sbom_occurrences.*, agg_occurrences.occurrence_count, agg_occurrences.project_count
FROM "sbom_occurrences"
INNER JOIN (
  SELECT component_id,
        COUNT(DISTINCT id) AS occurrence_count,
        COUNT(DISTINCT project_id) AS project_count
  FROM our_occurrences
  GROUP BY component_id
) agg_occurrences ON sbom_occurrences.component_id = agg_occurrences.component_id
WHERE "sbom_occurrences"."id" IN (
  SELECT "our_occurrences"."id" FROM "our_occurrences"
)
AND "sbom_occurrences"."component_name" = 'activerecord' 
ORDER BY "sbom_occurrences"."id" ASC 
LIMIT 20 OFFSET 0;

Time: 535.041 ms
  - planning: 10.753 ms
  - execution: 524.288 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 248033 (~1.90 GiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/22603/commands/72704

This query is being optimized further in !132281 (merged). When that MR is merged this query will become:

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,
    COUNT(sbom_occurrences.project_id) OVER (PARTITION BY sbom_occurrences.component_version_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, 1)] AS id
      FROM "namespaces"
      WHERE "namespaces"."type" = 'Group'
      AND (traversal_ids @> ('{6543}'))
    )
    AND "projects"."marked_for_deletion_at" IS NULL
    AND "projects"."pending_delete" = FALSE
  )
  AND "sbom_occurrences"."component_version_id" IS NOT NULL
  ORDER BY 
    "sbom_occurrences"."component_version_id" DESC,
    "sbom_occurrences"."id" DESC
)
SELECT "sbom_occurrences".*
FROM "our_occurrences" AS "sbom_occurrences" 
WHERE "sbom_occurrences"."component_name" = 'activerecord' 
ORDER BY sbom_occurrences.id asc 
LIMIT 20 OFFSET 0;

Time: 275.955 ms
  - planning: 10.302 ms
  - execution: 265.653 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 54593 (~426.50 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/22603/commands/72711

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

I have evaluated the MR acceptance checklist for this MR.

Edited Sep 21, 2023 by mo khan

Add component_names filter to group level dependency list

What does this MR do and why?

MR acceptance checklist

Merge request reports