Remove `COUNT(*)` query
What does this MR do and why?
This change removes two queries from each invocation to GET /<group>/-/dependencies.json
.
-
COUNT(*)
query that is used for displaying the pagination links. The equivalent of anEXISTS
query to compute the report status in the JSON response. i.e.{ report: { status: ok }, dependencies: [] }
.
The following queries have been removed:
Sbom::Occurrence Count (2.2ms) SELECT COUNT(*) FROM (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 @> ('{71}'))) AND "projects"."marked_for_deletion_at" IS NULL AND "projects"."pending_delete" = FALSE)) SELECT 1 AS one 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") LIMIT 10001) subquery_for_count /*application:web,correlation_id:01HBV8P7KXCA942CQPV23TYCDH,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/lib/gitlab/pagination/offset_pagination.rb:84:in `exceeeds_count?'*/
↳ config/initializers/kaminari_active_record_relation_methods_with_limit.rb:31:in `total_count_with_limit'
SELECT COUNT(*)
FROM (
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 @> ('{9970}'))
)
AND "projects"."marked_for_deletion_at" IS NULL
AND "projects"."pending_delete" = FALSE
)
) SELECT 1 AS one 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"
)
LIMIT 10001
)
The following query is still a problem.
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 @> ('{1130}'))) AND "projects"."marked_for_deletion_at" IS NULL AND "projects"."pending_delete" = FALSE)) SELECT 1 AS one 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") /* allow_cross_joins_across_databases */ LIMIT 1 OFFSET 0 /*application:test,correlation_id:c81efeec-8167-445a-b12a-c39efac7c95c,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/serializers/report_list_entity.rb:40:in `status'*/
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 @> ('{9970}'))
)
AND "projects"."marked_for_deletion_at" IS NULL
AND "projects"."pending_delete" = FALSE
)
)
SELECT 1 AS one
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"
)
LIMIT 1 OFFSET 0;
Screenshots or screen recordings
Before | After |
---|---|
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 mo khan