Add for_catalog_resource_with_component_versions scope

Rajendra Kadamrequested to merge
579460-component-scopes into master

What does this MR do and why?

Add for_catalog_resource_with_component_versions scope

This change adds a new database query method to help efficiently retrieve usage tracking data for catalog resources. The new method allows the system to find all usage records for a specific catalog resource while also pre-loading related component and version information in a single database query, which improves performance by avoiding multiple separate database calls. The accompanying tests verify that the method correctly filters results to only return usage data for the requested catalog resource and confirms that the performance optimization works as expected.

This is done as a prework for Component usage details in catalog resource view (#579460) • Rajendra Kadam, Anna Vovchenko • 19.0 where we want to use the scope in the new GraphQL query that we will add.

Database

Queries

The scope generates 3 queries (Rails includes uses separate queries for eager loading):

Query 1 — Fetch last usages for a catalog resource:

SELECT "catalog_resource_component_last_usages".*
FROM "catalog_resource_component_last_usages"
WHERE "catalog_resource_component_last_usages"."catalog_resource_id" = <catalog_resource_id>;

Query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/49005/commands/146562

Query 2 — Eager load components:

SELECT "catalog_resource_components".*
FROM "catalog_resource_components"
WHERE "catalog_resource_components"."id" IN (<component_ids from query 1>);

Query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/49005/commands/146563

Query 3 — Eager load versions:

SELECT "catalog_resource_versions".*
FROM "catalog_resource_versions"
WHERE "catalog_resource_versions"."id" IN (<version_ids from query 2>);

Query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/49005/commands/146564

Notes

  • All queries use existing indexes. No new indexes or migrations required.
  • Query 2 and 3 use primary key lookups (Index Scan / Index Only Scan).
  • Table catalog_resource_component_last_usages currently has ~342K rows.

References

Screenshots or screen recordings

Before After

How to set up and validate locally

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #579460

Edited by Rajendra Kadam

Merge request reports