Backend: Improve CI Catalog Your resources query plan performance

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

• Close this issue

Summary

As part of the CI Catalog Global search feature, we need to support a Your resources tab that shows resources from namespaces the user is a member of. For the first iteration, we must limit the scope to Public+Internal projects for performance reasons. Further details are explained in #429056 (comment 1641496694). --> No longer applicable, see #429056 (closed).

An unoptimized version of the query will be shipped in #429382 (closed). This issue serves to improve the query plan performance.

Proposal

This proposal requires further discussion and refinement.

One idea is to denormalize traversal_ids (see #429376 (closed) on syncing). Then create a concatenated string index in the form of traversal_ids.join("-") + '-' with the condition WHERE visibility_level IN (10, 20) (indexed for only Internal+Public). Further details explained in #429056 (comment 1632689513).

Then update the logic in Ci::Catalog::Listing to support querying resources like so:

# Pseudo code
SELECT * FROM catalog_resources
WHERE <full text search logic>
AND <logic with traversal_ids to only get resources from namespaces the user is a member of>