Backend: Implement instrumentation to track performance of Ci::Catalog::Listing queries

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

• Close this issue

Summary

Currently we have unoptimized queries in Ci::Catalog::Listing. We have made improvements to the performance by denormalizing some data (e.g. #429056 (closed)). However, the query plans are still inefficient because they are unable to fully leverage indexes effectively (i.e. they are "non-sargable"). In the long run, slower queries may affect our ability to scale.

Since it's uncertain when or if the performance will become a problem, we should implement instrumentation to track performance metrics. These will help guide us in prioritizing optimization efforts.

Proposal

Implement performance metrics on the queries in Ci::Catalog::Listing, which can be tracked with Prometheus and then displayed in Grafana. It may be useful to measure the performance of different common queries, e.g.

• all scope query sorted by x
• namespaces query sorted by x
• all scope query filtered by search term
• namespaces query filtered by search term
• etc.

See #435440 (comment 1703291897) for guidance on the approach.