Add container_images to Project/Group GraphQL API
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.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #337883 (closed)