Clean up old topic indexes for supporting organizations

Leonardo da Rosarequested to merge
537280-cleanup-indexes-from-topics into master

What does this MR do and why?

Clean up old topic indexes for supporting organizations

It cleans up old topic indexes that are not used anymore, as the queries related to topics now include the organization_id and have new indexes supporting them

  1. Replace index_topics_on_lower_name with index_topics_on_org_id_and_lower_name_and_projects_count, which cover these two queries:
SELECT "topics".* FROM "topics" WHERE "topics"."organization_id" = 1 AND (LOWER(name) = 'ruby') LIMIT 1;

SELECT "topics".* FROM "topics" WHERE "topics"."organization_id" = 1 AND (lower(name) = 'ruby') ORDER BY "topics"."total_projects_count" DESC LIMIT 1;
Old Index New Index Query Covered Plan
index_topics_on_lower_name index_topics_on_org_id_and_lower_name_and_projects_count SELECT "topics".* FROM "topics" WHERE "topics"."organization_id" = 1 AND (LOWER(name) = 'ruby') LIMIT 1; https://console.postgres.ai/gitlab/gitlab-production-main/sessions/43102/commands/131769
index_topics_on_lower_name index_topics_on_org_id_and_lower_name_and_projects_count SELECT "topics".* FROM "topics" WHERE "topics"."organization_id" = 1 AND (lower(name) = 'ruby') ORDER BY "topics"."total_projects_count" DESC LIMIT 1; https://console.postgres.ai/gitlab/gitlab-production-main/sessions/43102/commands/131768
  1. Remove index_topics_on_slug as it is covered by index_topics_on_organization_id_slug_and and it's not used anymore: https://dashboards.gitlab.net/goto/7qXA8OrNg?orgId=1

Related to #537280 (closed)

Merge request reports