Skip to content

Resolve SELECT N+1 issue on Group Level Dependency List

mo khan requested to merge mokhax/422086/fix-n-plus-one into master

What does this MR do and why?

This change removes a SELECT N+1 issue on the group level dependency list.

Before:

Unpermitted parameters: :page, :filter, :group_id, :format. Context: { controller: Groups::DependenciesController, action: index, request: #<ActionDispatch::Request:0x00007f5da8e54928>, params: {"sort_by"=>"packager", "sort"=>"asc", "page"=>"1", "filter"=>"all", "controller"=>"groups/dependencies", "action"=>"index", "group_id"=>"gitlab-examples", "format"=>"json"} }
  Sbom::Occurrence Count (1.3ms)  SELECT COUNT(*) FROM (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 sbom_occurrences
            WHERE 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}'))
              )
            )
            GROUP BY component_id
          ) agg_occurrences ON sbom_occurrences.component_id = agg_occurrences.component_id 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}')))) LIMIT 10001) subquery_for_count /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,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'
  Sbom::Occurrence Exists? (1.1ms)  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 sbom_occurrences
            WHERE 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}'))
              )
            )
            GROUP BY component_id
          ) agg_occurrences ON sbom_occurrences.component_id = agg_occurrences.component_id 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}')))) LIMIT 1 OFFSET 0 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/serializers/report_list_entity.rb:36:in `status'*/
  ↳ ee/app/serializers/report_list_entity.rb:36:in `status'
  Sbom::Occurrence Load (1.7ms)  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 sbom_occurrences
            WHERE 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}'))
              )
            )
            GROUP BY component_id
          ) agg_occurrences ON sbom_occurrences.component_id = agg_occurrences.component_id 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}')))) ORDER BY "sbom_occurrences"."package_manager" ASC LIMIT 20 OFFSET 0 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/app/serializers/base_serializer.rb:16:in `represent'*/
  ↳ app/serializers/base_serializer.rb:16:in `represent'
  Project Load (0.6ms)  SELECT "projects"."id", "projects"."name", "projects"."path", "projects"."description", "projects"."created_at", "projects"."updated_at", "projects"."creator_id", "projects"."namespace_id", "projects"."last_activity_at", "projects"."import_url", "projects"."visibility_level", "projects"."archived", "projects"."avatar", "projects"."merge_requests_template", "projects"."star_count", "projects"."merge_requests_rebase_enabled", "projects"."import_type", "projects"."import_source", "projects"."approvals_before_merge", "projects"."reset_approvals_on_push", "projects"."merge_requests_ff_only_enabled", "projects"."issues_template", "projects"."mirror", "projects"."mirror_last_update_at", "projects"."mirror_last_successful_update_at", "projects"."mirror_user_id", "projects"."shared_runners_enabled", "projects"."runners_token", "projects"."build_allow_git_fetch", "projects"."build_timeout", "projects"."mirror_trigger_builds", "projects"."pending_delete", "projects"."public_builds", "projects"."last_repository_check_failed", "projects"."last_repository_check_at", "projects"."only_allow_merge_if_pipeline_succeeds", "projects"."has_external_issue_tracker", "projects"."repository_storage", "projects"."repository_read_only", "projects"."request_access_enabled", "projects"."has_external_wiki", "projects"."ci_config_path", "projects"."lfs_enabled", "projects"."description_html", "projects"."only_allow_merge_if_all_discussions_are_resolved", "projects"."repository_size_limit", "projects"."printing_merge_request_link_enabled", "projects"."auto_cancel_pending_pipelines", "projects"."service_desk_enabled", "projects"."cached_markdown_version", "projects"."delete_error", "projects"."last_repository_updated_at", "projects"."disable_overriding_approvers_per_merge_request", "projects"."storage_version", "projects"."resolve_outdated_diff_discussions", "projects"."remote_mirror_available_overridden", "projects"."only_mirror_protected_branches", "projects"."pull_mirror_available_overridden", "projects"."jobs_cache_index", "projects"."external_authorization_classification_label", "projects"."mirror_overwrites_diverged_branches", "projects"."pages_https_only", "projects"."external_webhook_token", "projects"."packages_enabled", "projects"."merge_requests_author_approval", "projects"."pool_repository_id", "projects"."runners_token_encrypted", "projects"."bfg_object_map", "projects"."detected_repository_languages", "projects"."merge_requests_disable_committers_approval", "projects"."require_password_to_approve", "projects"."max_pages_size", "projects"."max_artifacts_size", "projects"."pull_mirror_branch_prefix", "projects"."remove_source_branch_after_merge", "projects"."marked_for_deletion_at", "projects"."marked_for_deletion_by_user_id", "projects"."autoclose_referenced_issues", "projects"."suggestion_commit_message", "projects"."project_namespace_id", "projects"."hidden", "projects"."organization_id" FROM "projects" WHERE "projects"."id" = 19 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/app/serializers/base_serializer.rb:16:in `represent'*/
  ↳ app/serializers/base_serializer.rb:16:in `represent'
  Route Load (0.4ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" = 19 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/app/serializers/base_serializer.rb:16:in `represent'*/
  ↳ app/serializers/base_serializer.rb:16:in `represent'
  Sbom::Component Load (0.5ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 6 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:20:in `name'*/
  ↳ ee/app/models/sbom/occurrence.rb:20:in `name'
  Sbom::Source Load (0.3ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:24:in `packager'*/
  ↳ ee/app/models/sbom/occurrence.rb:24:in `packager'
  Sbom::ComponentVersion Load (0.2ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" = 625 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:23:in `version'*/
  ↳ ee/app/models/sbom/occurrence.rb:23:in `version'
  Namespace Load (0.3ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 72 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/config/routes.rb:311:in `block (4 levels) in <main>'*/
  ↳ config/routes.rb:311:in `block (4 levels) in <main>'
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 72 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/app/models/concerns/routable.rb:147:in `block in full_attribute'*/
  ↳ app/models/concerns/routable.rb:147:in `block in full_attribute'
  Sbom::Component Load (0.4ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 7 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:20:in `name'*/
  ↳ ee/app/models/sbom/occurrence.rb:20:in `name'
  CACHE Sbom::Source Load (0.0ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 LIMIT 1
  ↳ ee/app/models/sbom/occurrence.rb:24:in `packager'
  Sbom::ComponentVersion Load (0.4ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" = 626 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:23:in `version'*/
  ↳ ee/app/models/sbom/occurrence.rb:23:in `version'
  Sbom::Component Load (0.2ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 9 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:20:in `name'*/
  ↳ ee/app/models/sbom/occurrence.rb:20:in `name'
  CACHE Sbom::Source Load (0.0ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 LIMIT 1
  ↳ ee/app/models/sbom/occurrence.rb:24:in `packager'
  Sbom::ComponentVersion Load (0.3ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" = 627 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:23:in `version'*/
  ↳ ee/app/models/sbom/occurrence.rb:23:in `version'
  Sbom::Component Load (0.4ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 10 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:20:in `name'*/
  ↳ ee/app/models/sbom/occurrence.rb:20:in `name'
  CACHE Sbom::Source Load (0.0ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 LIMIT 1
  ↳ ee/app/models/sbom/occurrence.rb:24:in `packager'
  Sbom::ComponentVersion Load (0.2ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" = 628 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:23:in `version'*/
  ↳ ee/app/models/sbom/occurrence.rb:23:in `version'
  Sbom::Component Load (0.3ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 11 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:20:in `name'*/
  ↳ ee/app/models/sbom/occurrence.rb:20:in `name'
  CACHE Sbom::Source Load (0.0ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 LIMIT 1
  ↳ ee/app/models/sbom/occurrence.rb:24:in `packager'
  Sbom::ComponentVersion Load (0.3ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" = 629 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:23:in `version'*/
  ↳ ee/app/models/sbom/occurrence.rb:23:in `version'
  Sbom::Component Load (0.3ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 12 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:20:in `name'*/
  ↳ ee/app/models/sbom/occurrence.rb:20:in `name'
  CACHE Sbom::Source Load (0.0ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 LIMIT 1
  ↳ ee/app/models/sbom/occurrence.rb:24:in `packager'
  Sbom::ComponentVersion Load (0.3ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" = 630 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:23:in `version'*/
  ↳ ee/app/models/sbom/occurrence.rb:23:in `version'
  Sbom::Component Load (0.2ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 15 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:20:in `name'*/
  ↳ ee/app/models/sbom/occurrence.rb:20:in `name'
  CACHE Sbom::Source Load (0.0ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 LIMIT 1
  ↳ ee/app/models/sbom/occurrence.rb:24:in `packager'
  Sbom::ComponentVersion Load (0.3ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" = 631 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:23:in `version'*/
  ↳ ee/app/models/sbom/occurrence.rb:23:in `version'
  Sbom::Component Load (0.3ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 17 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:20:in `name'*/
  ↳ ee/app/models/sbom/occurrence.rb:20:in `name'
  CACHE Sbom::Source Load (0.0ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 LIMIT 1
  ↳ ee/app/models/sbom/occurrence.rb:24:in `packager'
  Sbom::ComponentVersion Load (0.3ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" = 632 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:23:in `version'*/
  ↳ ee/app/models/sbom/occurrence.rb:23:in `version'
  Sbom::Component Load (0.3ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 633 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:20:in `name'*/
  ↳ ee/app/models/sbom/occurrence.rb:20:in `name'
  CACHE Sbom::Source Load (0.0ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 LIMIT 1
  ↳ ee/app/models/sbom/occurrence.rb:24:in `packager'
  Sbom::ComponentVersion Load (0.3ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" = 633 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:23:in `version'*/
  ↳ ee/app/models/sbom/occurrence.rb:23:in `version'
  Sbom::Component Load (0.3ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 64 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:20:in `name'*/
  ↳ ee/app/models/sbom/occurrence.rb:20:in `name'
  CACHE Sbom::Source Load (0.0ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 LIMIT 1
  ↳ ee/app/models/sbom/occurrence.rb:24:in `packager'
  Sbom::ComponentVersion Load (0.2ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" = 635 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:23:in `version'*/
  ↳ ee/app/models/sbom/occurrence.rb:23:in `version'
  Sbom::Component Load (0.2ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 67 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:20:in `name'*/
  ↳ ee/app/models/sbom/occurrence.rb:20:in `name'
  CACHE Sbom::Source Load (0.0ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 LIMIT 1
  ↳ ee/app/models/sbom/occurrence.rb:24:in `packager'
  Sbom::ComponentVersion Load (0.2ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" = 636 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:23:in `version'*/
  ↳ ee/app/models/sbom/occurrence.rb:23:in `version'
  Sbom::Component Load (0.2ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 68 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:20:in `name'*/
  ↳ ee/app/models/sbom/occurrence.rb:20:in `name'
  CACHE Sbom::Source Load (0.0ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 LIMIT 1
  ↳ ee/app/models/sbom/occurrence.rb:24:in `packager'
  Sbom::ComponentVersion Load (0.1ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" = 637 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:23:in `version'*/
  ↳ ee/app/models/sbom/occurrence.rb:23:in `version'
  Sbom::Component Load (0.4ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 85 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:20:in `name'*/
  ↳ ee/app/models/sbom/occurrence.rb:20:in `name'
  CACHE Sbom::Source Load (0.0ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 LIMIT 1
  ↳ ee/app/models/sbom/occurrence.rb:24:in `packager'
  Sbom::ComponentVersion Load (0.3ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" = 642 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:23:in `version'*/
  ↳ ee/app/models/sbom/occurrence.rb:23:in `version'
  Sbom::Component Load (0.3ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 91 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:20:in `name'*/
  ↳ ee/app/models/sbom/occurrence.rb:20:in `name'
  CACHE Sbom::Source Load (0.0ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 LIMIT 1
  ↳ ee/app/models/sbom/occurrence.rb:24:in `packager'
  Sbom::ComponentVersion Load (0.3ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" = 643 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:23:in `version'*/
  ↳ ee/app/models/sbom/occurrence.rb:23:in `version'
  Sbom::Component Load (0.3ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 649 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:20:in `name'*/
  ↳ ee/app/models/sbom/occurrence.rb:20:in `name'
  CACHE Sbom::Source Load (0.0ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 LIMIT 1
  ↳ ee/app/models/sbom/occurrence.rb:24:in `packager'
  Sbom::ComponentVersion Load (0.2ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" = 649 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:23:in `version'*/
  ↳ ee/app/models/sbom/occurrence.rb:23:in `version'
  Sbom::Component Load (0.3ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 308 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:20:in `name'*/
  ↳ ee/app/models/sbom/occurrence.rb:20:in `name'
  CACHE Sbom::Source Load (0.0ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 LIMIT 1
  ↳ ee/app/models/sbom/occurrence.rb:24:in `packager'
  Sbom::ComponentVersion Load (0.3ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" = 650 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:23:in `version'*/
  ↳ ee/app/models/sbom/occurrence.rb:23:in `version'
  Sbom::Component Load (0.2ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 313 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:20:in `name'*/
  ↳ ee/app/models/sbom/occurrence.rb:20:in `name'
  CACHE Sbom::Source Load (0.0ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 LIMIT 1
  ↳ ee/app/models/sbom/occurrence.rb:24:in `packager'
  Sbom::ComponentVersion Load (0.3ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" = 651 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:23:in `version'*/
  ↳ ee/app/models/sbom/occurrence.rb:23:in `version'
  Sbom::Component Load (0.3ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 317 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:20:in `name'*/
  ↳ ee/app/models/sbom/occurrence.rb:20:in `name'
  CACHE Sbom::Source Load (0.0ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 LIMIT 1
  ↳ ee/app/models/sbom/occurrence.rb:24:in `packager'
  Sbom::ComponentVersion Load (0.3ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" = 652 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:23:in `version'*/
  ↳ ee/app/models/sbom/occurrence.rb:23:in `version'
  Sbom::Component Load (0.2ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 323 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:20:in `name'*/
  ↳ ee/app/models/sbom/occurrence.rb:20:in `name'
  CACHE Sbom::Source Load (0.0ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 LIMIT 1
  ↳ ee/app/models/sbom/occurrence.rb:24:in `packager'
  Sbom::ComponentVersion Load (0.2ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" = 653 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:23:in `version'*/
  ↳ ee/app/models/sbom/occurrence.rb:23:in `version'
  Sbom::Component Load (0.3ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" = 4 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:20:in `name'*/
  ↳ ee/app/models/sbom/occurrence.rb:20:in `name'
  CACHE Sbom::Source Load (0.0ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 LIMIT 1
  ↳ ee/app/models/sbom/occurrence.rb:24:in `packager'
  Sbom::ComponentVersion Load (0.3ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" = 624 LIMIT 1 /*application:web,correlation_id:01H9P4ATV5T3JT559G12N4QTFX,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/models/sbom/occurrence.rb:23:in `version'*/
  ↳ ee/app/models/sbom/occurrence.rb:23:in `version'
Completed 200 OK in 211ms (Views: 0.8ms | ActiveRecord: 17.4ms | Elasticsearch: 0.0ms | Allocations: 283110)

After:

Unpermitted parameters: :filter, :group_id, :format. Context: { controller: Groups::DependenciesController, action: index, request: #<ActionDispatch::Request:0x00007f5e31a08840>, params: {"sort_by"=>"packager", "sort"=>"asc", "page"=>"1", "filter"=>"all", "controller"=>"groups/dependencies", "action"=>"index", "group_id"=>"gitlab-examples", "format"=>"json"} }
  Sbom::Occurrence Count (2.4ms)  SELECT COUNT(*) FROM (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 sbom_occurrences
            WHERE 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}'))
              )
            )
            GROUP BY component_id
          ) agg_occurrences ON sbom_occurrences.component_id = agg_occurrences.component_id 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}')))) LIMIT 10001) subquery_for_count /*application:web,correlation_id:01H9P6NKRDCQSQ6ZWEMQZSKKRA,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'
  Sbom::Occurrence Exists? (1.0ms)  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 sbom_occurrences
            WHERE 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}'))
              )
            )
            GROUP BY component_id
          ) agg_occurrences ON sbom_occurrences.component_id = agg_occurrences.component_id 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}')))) LIMIT 1 OFFSET 0 /*application:web,correlation_id:01H9P6NKRDCQSQ6ZWEMQZSKKRA,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/ee/app/serializers/report_list_entity.rb:36:in `status'*/
  ↳ ee/app/serializers/report_list_entity.rb:36:in `status'
  Sbom::Occurrence Load (1.7ms)  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 sbom_occurrences
            WHERE 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}'))
              )
            )
            GROUP BY component_id
          ) agg_occurrences ON sbom_occurrences.component_id = agg_occurrences.component_id 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}')))) ORDER BY "sbom_occurrences"."package_manager" ASC LIMIT 20 OFFSET 0 /*application:web,correlation_id:01H9P6NKRDCQSQ6ZWEMQZSKKRA,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/app/serializers/base_serializer.rb:16:in `represent'*/
  ↳ app/serializers/base_serializer.rb:16:in `represent'
  Project Load (0.7ms)  SELECT "projects"."id", "projects"."name", "projects"."path", "projects"."description", "projects"."created_at", "projects"."updated_at", "projects"."creator_id", "projects"."namespace_id", "projects"."last_activity_at", "projects"."import_url", "projects"."visibility_level", "projects"."archived", "projects"."avatar", "projects"."merge_requests_template", "projects"."star_count", "projects"."merge_requests_rebase_enabled", "projects"."import_type", "projects"."import_source", "projects"."approvals_before_merge", "projects"."reset_approvals_on_push", "projects"."merge_requests_ff_only_enabled", "projects"."issues_template", "projects"."mirror", "projects"."mirror_last_update_at", "projects"."mirror_last_successful_update_at", "projects"."mirror_user_id", "projects"."shared_runners_enabled", "projects"."runners_token", "projects"."build_allow_git_fetch", "projects"."build_timeout", "projects"."mirror_trigger_builds", "projects"."pending_delete", "projects"."public_builds", "projects"."last_repository_check_failed", "projects"."last_repository_check_at", "projects"."only_allow_merge_if_pipeline_succeeds", "projects"."has_external_issue_tracker", "projects"."repository_storage", "projects"."repository_read_only", "projects"."request_access_enabled", "projects"."has_external_wiki", "projects"."ci_config_path", "projects"."lfs_enabled", "projects"."description_html", "projects"."only_allow_merge_if_all_discussions_are_resolved", "projects"."repository_size_limit", "projects"."printing_merge_request_link_enabled", "projects"."auto_cancel_pending_pipelines", "projects"."service_desk_enabled", "projects"."cached_markdown_version", "projects"."delete_error", "projects"."last_repository_updated_at", "projects"."disable_overriding_approvers_per_merge_request", "projects"."storage_version", "projects"."resolve_outdated_diff_discussions", "projects"."remote_mirror_available_overridden", "projects"."only_mirror_protected_branches", "projects"."pull_mirror_available_overridden", "projects"."jobs_cache_index", "projects"."external_authorization_classification_label", "projects"."mirror_overwrites_diverged_branches", "projects"."pages_https_only", "projects"."external_webhook_token", "projects"."packages_enabled", "projects"."merge_requests_author_approval", "projects"."pool_repository_id", "projects"."runners_token_encrypted", "projects"."bfg_object_map", "projects"."detected_repository_languages", "projects"."merge_requests_disable_committers_approval", "projects"."require_password_to_approve", "projects"."max_pages_size", "projects"."max_artifacts_size", "projects"."pull_mirror_branch_prefix", "projects"."remove_source_branch_after_merge", "projects"."marked_for_deletion_at", "projects"."marked_for_deletion_by_user_id", "projects"."autoclose_referenced_issues", "projects"."suggestion_commit_message", "projects"."project_namespace_id", "projects"."hidden", "projects"."organization_id" FROM "projects" WHERE "projects"."id" = 19 /*application:web,correlation_id:01H9P6NKRDCQSQ6ZWEMQZSKKRA,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/app/serializers/base_serializer.rb:16:in `represent'*/
  ↳ app/serializers/base_serializer.rb:16:in `represent'
  Route Load (0.4ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" = 19 /*application:web,correlation_id:01H9P6NKRDCQSQ6ZWEMQZSKKRA,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/app/serializers/base_serializer.rb:16:in `represent'*/
  ↳ app/serializers/base_serializer.rb:16:in `represent'
  Sbom::Component Load (0.4ms)  SELECT "sbom_components".* FROM "sbom_components" WHERE "sbom_components"."id" IN (6, 7, 9, 10, 11, 12, 15, 17, 633, 64, 67, 68, 85, 91, 649, 308, 313, 317, 323, 4) /*application:web,correlation_id:01H9P6NKRDCQSQ6ZWEMQZSKKRA,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/app/serializers/base_serializer.rb:16:in `represent'*/
  ↳ app/serializers/base_serializer.rb:16:in `represent'
  Sbom::ComponentVersion Load (0.4ms)  SELECT "sbom_component_versions".* FROM "sbom_component_versions" WHERE "sbom_component_versions"."id" IN (625, 626, 627, 628, 629, 630, 631, 632, 633, 635, 636, 637, 642, 643, 649, 650, 651, 652, 653, 624) /*application:web,correlation_id:01H9P6NKRDCQSQ6ZWEMQZSKKRA,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/app/serializers/base_serializer.rb:16:in `represent'*/
  ↳ app/serializers/base_serializer.rb:16:in `represent'
  Sbom::Source Load (0.3ms)  SELECT "sbom_sources".* FROM "sbom_sources" WHERE "sbom_sources"."id" = 64 /*application:web,correlation_id:01H9P6NKRDCQSQ6ZWEMQZSKKRA,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/app/serializers/base_serializer.rb:16:in `represent'*/
  ↳ app/serializers/base_serializer.rb:16:in `represent'
  Namespace Load (0.3ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 72 LIMIT 1 /*application:web,correlation_id:01H9P6NKRDCQSQ6ZWEMQZSKKRA,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/config/routes.rb:311:in `block (4 levels) in <main>'*/
  ↳ config/routes.rb:311:in `block (4 levels) in <main>'
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 72 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:web,correlation_id:01H9P6NKRDCQSQ6ZWEMQZSKKRA,endpoint_id:Groups::DependenciesController#index,db_config_name:main,line:/app/models/concerns/routable.rb:147:in `block in full_attribute'*/
  ↳ app/models/concerns/routable.rb:147:in `block in full_attribute'
Completed 200 OK in 191ms (Views: 0.7ms | ActiveRecord: 27.9ms | Elasticsearch: 0.0ms | Allocations: 277337)

#422086 (closed)

MR acceptance checklist

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

Edited by mo khan

Merge request reports