Skip to content

Add container_images to Project/Group GraphQL API

Alan (Maciej) Paruszewski requested to merge 337883-add-container-images-to-graphql-vuln-reads into master

What does this MR do and why?

We need to add ability to list all container images related to given Vulnerabilities for Project, Group or Instance Security Dashboard. To do it we are adding new field for related GraphQL types and we will be able to prepare this list in each context. Additionally to perform this query quicker we are adding new index in database.

Migration

⋊> env VERBOSE=true bundle exec rake db:migrate:up:main VERSION=202205030044712
main: == 202205030044712 AddIndexForVulnerabilityReadsLocationImage: migrating ======
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:vulnerability_reads, [:project_id, :location_image], {:where=>"report_type in (2, 7) AND location_image IS NOT NULL", :name=>:index_vulnerability_reads_on_location_image_partial, :algorithm=>:concurrently})
main:    -> 0.0306s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0010s
main: -- add_index(:vulnerability_reads, [:project_id, :location_image], {:where=>"report_type in (2, 7) AND location_image IS NOT NULL", :name=>:index_vulnerability_reads_on_location_image_partial, :algorithm=>:concurrently})
main:    -> 0.0302s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: == 202205030044712 AddIndexForVulnerabilityReadsLocationImage: migrated (0.0789s)
⋊> env VERBOSE=true bundle exec rake db:migrate:down:main VERSION=202205030044712
main: == 202205030044712 AddIndexForVulnerabilityReadsLocationImage: reverting ======
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:vulnerability_reads)
main:    -> 0.0238s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0010s
main: -- remove_index(:vulnerability_reads, {:algorithm=>:concurrently, :name=>:index_vulnerability_reads_on_location_image_partial})
main:    -> 0.0080s
main: -- execute("RESET statement_timeout")
main:    -> 0.0007s
main: == 202205030044712 AddIndexForVulnerabilityReadsLocationImage: reverted (0.0529s)

Queries

Project Query

Before

explain SELECT DISTINCT "vulnerability_reads"."location_image" FROM "vulnerability_reads" WHERE "vulnerability_reads"."project_id" = 32199269 AND "vulnerability_reads"."report_type" IN (2, 7) AND "vulnerability_reads"."location_image" IS NOT NULL ORDER BY "vulnerability_reads"."location_image" ASC;
Time: 110.676 ms
  - planning: 1.987 ms
  - execution: 108.689 ms
    - I/O read: 102.842 ms
    - I/O write: 0.000 ms

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/10331/commands/36485

After

explain SELECT DISTINCT "vulnerability_reads"."location_image" FROM "vulnerability_reads" WHERE "vulnerability_reads"."project_id" = 32199269 AND "vulnerability_reads"."report_type" IN (2, 7) AND "vulnerability_reads"."location_image" IS NOT NULL ORDER BY "vulnerability_reads"."location_image" ASC;
Time: 4.296 ms
  - planning: 1.821 ms
  - execution: 2.475 ms
    - I/O read: 1.290 ms
    - I/O write: 0.000 ms

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/10331/commands/36487

Group Query

Before

explain SELECT DISTINCT “vulnerability_reads”.“location_image” FROM “vulnerability_reads” WHERE “vulnerability_reads”.“project_id” IN (SELECT “projects”.“id” FROM “projects” WHERE “projects”.“namespace_id” IN (WITH RECURSIVE “base_and_descendants” AS ((SELECT “namespaces”.“id”, “namespaces”.“name”, “namespaces”.“path”, “namespaces”.“owner_id”, “namespaces”.“created_at”, “namespaces”.“updated_at”, “namespaces”.“type”, “namespaces”.“description”, “namespaces”.“avatar”, “namespaces”.“membership_lock”, “namespaces”.“share_with_group_lock”, “namespaces”.“visibility_level”, “namespaces”.“request_access_enabled”, “namespaces”.“ldap_sync_status”, “namespaces”.“ldap_sync_error”, “namespaces”.“ldap_sync_last_update_at”, “namespaces”.“ldap_sync_last_successful_update_at”, “namespaces”.“ldap_sync_last_sync_at”, “namespaces”.“description_html”, “namespaces”.“lfs_enabled”, “namespaces”.“parent_id”, “namespaces”.“shared_runners_minutes_limit”, “namespaces”.“repository_size_limit”, “namespaces”.“require_two_factor_authentication”, “namespaces”.“two_factor_grace_period”, “namespaces”.“cached_markdown_version”, “namespaces”.“project_creation_level”, “namespaces”.“runners_token”, “namespaces”.“file_template_project_id”, “namespaces”.“saml_discovery_token”, “namespaces”.“runners_token_encrypted”, “namespaces”.“custom_project_templates_group_id”, “namespaces”.“auto_devops_enabled”, “namespaces”.“extra_shared_runners_minutes_limit”, “namespaces”.“last_ci_minutes_notification_at”, “namespaces”.“last_ci_minutes_usage_notification_level”, “namespaces”.“subgroup_creation_level”, “namespaces”.“emails_disabled”, “namespaces”.“max_pages_size”, “namespaces”.“max_artifacts_size”, “namespaces”.“mentions_disabled”, “namespaces”.“default_branch_protection”, “namespaces”.“unlock_membership_to_ldap”, “namespaces”.“max_personal_access_token_lifetime”, “namespaces”.“push_rule_id”, “namespaces”.“shared_runners_enabled”, “namespaces”.“allow_descendants_override_disabled_shared_runners”, “namespaces”.“traversal_ids” FROM “namespaces” WHERE “namespaces”.“type” = ‘Group’ AND “namespaces”.“id” = 11787569)
UNION
(SELECT “namespaces”.“id”, “namespaces”.“name”, “namespaces”.“path”, “namespaces”.“owner_id”, “namespaces”.“created_at”, “namespaces”.“updated_at”, “namespaces”.“type”, “namespaces”.“description”, “namespaces”.“avatar”, “namespaces”.“membership_lock”, “namespaces”.“share_with_group_lock”, “namespaces”.“visibility_level”, “namespaces”.“request_access_enabled”, “namespaces”.“ldap_sync_status”, “namespaces”.“ldap_sync_error”, “namespaces”.“ldap_sync_last_update_at”, “namespaces”.“ldap_sync_last_successful_update_at”, “namespaces”.“ldap_sync_last_sync_at”, “namespaces”.“description_html”, “namespaces”.“lfs_enabled”, “namespaces”.“parent_id”, “namespaces”.“shared_runners_minutes_limit”, “namespaces”.“repository_size_limit”, “namespaces”.“require_two_factor_authentication”, “namespaces”.“two_factor_grace_period”, “namespaces”.“cached_markdown_version”, “namespaces”.“project_creation_level”, “namespaces”.“runners_token”, “namespaces”.“file_template_project_id”, “namespaces”.“saml_discovery_token”, “namespaces”.“runners_token_encrypted”, “namespaces”.“custom_project_templates_group_id”, “namespaces”.“auto_devops_enabled”, “namespaces”.“extra_shared_runners_minutes_limit”, “namespaces”.“last_ci_minutes_notification_at”, “namespaces”.“last_ci_minutes_usage_notification_level”, “namespaces”.“subgroup_creation_level”, “namespaces”.“emails_disabled”, “namespaces”.“max_pages_size”, “namespaces”.“max_artifacts_size”, “namespaces”.“mentions_disabled”, “namespaces”.“default_branch_protection”, “namespaces”.“unlock_membership_to_ldap”, “namespaces”.“max_personal_access_token_lifetime”, “namespaces”.“push_rule_id”, “namespaces”.“shared_runners_enabled”, “namespaces”.“allow_descendants_override_disabled_shared_runners”, “namespaces”.“traversal_ids” FROM “namespaces”, “base_and_descendants” WHERE “namespaces”.“type” = ‘Group’ AND “namespaces”.“parent_id” = “base_and_descendants”.“id”)) SELECT “id” FROM “base_and_descendants” AS “namespaces”) AND “projects”.“archived” = FALSE AND “projects”.“pending_delete” = FALSE) AND “vulnerability_reads”.“report_type” IN (2, 7) AND “vulnerability_reads”.“location_image” IS NOT NULL ORDER BY “vulnerability_reads”.“location_image” ASC;
Time: 1.220 s
  - planning: 8.366 ms
  - execution: 1.212 s
    - I/O read: 1.183 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1740 (~13.60 MiB) from the buffer pool
  - reads: 1385 (~10.80 MiB) from the OS file cache, including disk I/O
  - dirtied: 86 (~688.00 KiB)
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/10425/commands/37111

After

explain SELECT DISTINCT “vulnerability_reads”.“location_image” FROM “vulnerability_reads” WHERE “vulnerability_reads”.“project_id” IN (SELECT “projects”.“id” FROM “projects” WHERE “projects”.“namespace_id” IN (WITH RECURSIVE “base_and_descendants” AS ((SELECT “namespaces”.“id”, “namespaces”.“name”, “namespaces”.“path”, “namespaces”.“owner_id”, “namespaces”.“created_at”, “namespaces”.“updated_at”, “namespaces”.“type”, “namespaces”.“description”, “namespaces”.“avatar”, “namespaces”.“membership_lock”, “namespaces”.“share_with_group_lock”, “namespaces”.“visibility_level”, “namespaces”.“request_access_enabled”, “namespaces”.“ldap_sync_status”, “namespaces”.“ldap_sync_error”, “namespaces”.“ldap_sync_last_update_at”, “namespaces”.“ldap_sync_last_successful_update_at”, “namespaces”.“ldap_sync_last_sync_at”, “namespaces”.“description_html”, “namespaces”.“lfs_enabled”, “namespaces”.“parent_id”, “namespaces”.“shared_runners_minutes_limit”, “namespaces”.“repository_size_limit”, “namespaces”.“require_two_factor_authentication”, “namespaces”.“two_factor_grace_period”, “namespaces”.“cached_markdown_version”, “namespaces”.“project_creation_level”, “namespaces”.“runners_token”, “namespaces”.“file_template_project_id”, “namespaces”.“saml_discovery_token”, “namespaces”.“runners_token_encrypted”, “namespaces”.“custom_project_templates_group_id”, “namespaces”.“auto_devops_enabled”, “namespaces”.“extra_shared_runners_minutes_limit”, “namespaces”.“last_ci_minutes_notification_at”, “namespaces”.“last_ci_minutes_usage_notification_level”, “namespaces”.“subgroup_creation_level”, “namespaces”.“emails_disabled”, “namespaces”.“max_pages_size”, “namespaces”.“max_artifacts_size”, “namespaces”.“mentions_disabled”, “namespaces”.“default_branch_protection”, “namespaces”.“unlock_membership_to_ldap”, “namespaces”.“max_personal_access_token_lifetime”, “namespaces”.“push_rule_id”, “namespaces”.“shared_runners_enabled”, “namespaces”.“allow_descendants_override_disabled_shared_runners”, “namespaces”.“traversal_ids” FROM “namespaces” WHERE “namespaces”.“type” = ‘Group’ AND “namespaces”.“id” = 11787569)
UNION
(SELECT “namespaces”.“id”, “namespaces”.“name”, “namespaces”.“path”, “namespaces”.“owner_id”, “namespaces”.“created_at”, “namespaces”.“updated_at”, “namespaces”.“type”, “namespaces”.“description”, “namespaces”.“avatar”, “namespaces”.“membership_lock”, “namespaces”.“share_with_group_lock”, “namespaces”.“visibility_level”, “namespaces”.“request_access_enabled”, “namespaces”.“ldap_sync_status”, “namespaces”.“ldap_sync_error”, “namespaces”.“ldap_sync_last_update_at”, “namespaces”.“ldap_sync_last_successful_update_at”, “namespaces”.“ldap_sync_last_sync_at”, “namespaces”.“description_html”, “namespaces”.“lfs_enabled”, “namespaces”.“parent_id”, “namespaces”.“shared_runners_minutes_limit”, “namespaces”.“repository_size_limit”, “namespaces”.“require_two_factor_authentication”, “namespaces”.“two_factor_grace_period”, “namespaces”.“cached_markdown_version”, “namespaces”.“project_creation_level”, “namespaces”.“runners_token”, “namespaces”.“file_template_project_id”, “namespaces”.“saml_discovery_token”, “namespaces”.“runners_token_encrypted”, “namespaces”.“custom_project_templates_group_id”, “namespaces”.“auto_devops_enabled”, “namespaces”.“extra_shared_runners_minutes_limit”, “namespaces”.“last_ci_minutes_notification_at”, “namespaces”.“last_ci_minutes_usage_notification_level”, “namespaces”.“subgroup_creation_level”, “namespaces”.“emails_disabled”, “namespaces”.“max_pages_size”, “namespaces”.“max_artifacts_size”, “namespaces”.“mentions_disabled”, “namespaces”.“default_branch_protection”, “namespaces”.“unlock_membership_to_ldap”, “namespaces”.“max_personal_access_token_lifetime”, “namespaces”.“push_rule_id”, “namespaces”.“shared_runners_enabled”, “namespaces”.“allow_descendants_override_disabled_shared_runners”, “namespaces”.“traversal_ids” FROM “namespaces”, “base_and_descendants” WHERE “namespaces”.“type” = ‘Group’ AND “namespaces”.“parent_id” = “base_and_descendants”.“id”)) SELECT “id” FROM “base_and_descendants” AS “namespaces”) AND “projects”.“archived” = FALSE AND “projects”.“pending_delete” = FALSE) AND “vulnerability_reads”.“report_type” IN (2, 7) AND “vulnerability_reads”.“location_image” IS NOT NULL ORDER BY “vulnerability_reads”.“location_image” ASC;
Time: 31.628 ms
  - planning: 8.160 ms
  - execution: 23.468 ms
    - I/O read: 13.144 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 291 (~2.30 MiB) from the buffer pool
  - reads: 332 (~2.60 MiB) from the OS file cache, including disk I/O
  - dirtied: 21 (~168.00 KiB)
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/10425/commands/37117

Creating index

exec CREATE INDEX index_vulnerability_reads_on_location_image_partial ON vulnerability_reads USING btree (project_id, location_image) WHERE ((report_type = ANY (ARRAY[2, 7])) AND (location_image IS NOT NULL));
The query has been executed. Duration: 1.275 min

MR acceptance checklist

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

Related to #337883 (closed)

Edited by Alan (Maciej) Paruszewski

Merge request reports