Remove unnecessary join to sbom_components
What does this MR do and why?
Remove unnecessary join to sbom_components
EE: true
Query
I haven't been able to run any query on console.postgres.ai due to its instability for the last few days. Hopefully this change is simple enough not to get blocked by it.
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" = 'git'
ORDER BY
"sbom_occurrences"."id" ASC
LIMIT
20 OFFSET 0After
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" = 'git'
ORDER BY
"sbom_occurrences"."id" ASC
LIMIT
20 OFFSET 0| Before | After |
|---|---|
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
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 by Zamir Martins