Drop index_for_owasp_top_10_group_level_reports index on vulnerability_reads

What does this MR do and why?

Drops the index_for_owasp_top_10_group_level_reports index. This index was added to improve the group.vulnerabilites GraphQL query. This index was later revised and an improved index was added !164252 (merged). So this MR removes the unused index.

Database

bundle exec rails db:migrate:up:main VERSION=20240909115859

main: == [advisory_lock_connection] object_id: 128080, pg_backend_pid: 80561
main: == 20240909115859 DropIndexForOwaspTop10GroupLevelReports: migrating ==========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0103s
main: -- indexes(:vulnerability_reads)
main:    -> 0.0077s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- remove_index(:vulnerability_reads, {:algorithm=>:concurrently, :name=>"index_for_owasp_top_10_group_level_reports"})
main:    -> 0.0039s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20240909115859 DropIndexForOwaspTop10GroupLevelReports: migrated (0.0333s) =

main: == [advisory_lock_connection] object_id: 128080, pg_backend_pid: 80561

bundle exec rails db:migrate:down:main VERSION=20240909115859

main: == [advisory_lock_connection] object_id: 128080, pg_backend_pid: 81027
main: == 20240909115859 DropIndexForOwaspTop10GroupLevelReports: reverting ==========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0110s
main: -- index_exists?(:vulnerability_reads, [:owasp_top_10, :state, :report_type, :severity, :traversal_ids, :vulnerability_id, :resolved_on_default_branch], {:where=>"archived = false", :name=>"index_for_owasp_top_10_group_level_reports", :algorithm=>:concurrently})
main:    -> 0.0060s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- add_index(:vulnerability_reads, [:owasp_top_10, :state, :report_type, :severity, :traversal_ids, :vulnerability_id, :resolved_on_default_branch], {:where=>"archived = false", :name=>"index_for_owasp_top_10_group_level_reports", :algorithm=>:concurrently})
main:    -> 0.0050s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20240909115859 DropIndexForOwaspTop10GroupLevelReports: reverted (0.0328s) =

main: == [advisory_lock_connection] object_id: 128080, pg_backend_pid: 81027

MR acceptance checklist

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

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

1. Should be able to drop/restore the index locally
2. Dropping: Run bundle exec rails db:migrate:up:main VERSION=20240909115859 to run the migration and check that the index is dropped using psql by running \d+ vulnerability_reads
3. Rollback: Run bundle exec rails db:migrate:down:main VERSION=20240909115859 to rollback the migration and check that the index is restored using psql by running \d+ vulnerability_reads

Validating that the index is no longer being used

Go to https://gitlab.com/groups/gitlab-org/-/security/vulnerabilities/?groupBy=owasp_2017&expanded=NONE. Notice the queries performing faster. Run the query mentioned below in DB Lab and notice that the revised_idx_for_owasp_top_10_group_level_reports index is getting used and not the index being removed in the current MR index_for_owasp_top_10_group_level_reports

Query to test in DB lab

SELECT "vulnerability_reads"."id",
       "vulnerability_reads"."vulnerability_id",
       "vulnerability_reads"."project_id",
       "vulnerability_reads"."scanner_id",
       "vulnerability_reads"."report_type",
       "vulnerability_reads"."severity",
       "vulnerability_reads"."state",
       "vulnerability_reads"."has_issues",
       "vulnerability_reads"."resolved_on_default_branch",
       "vulnerability_reads"."uuid",
       "vulnerability_reads"."location_image",
       "vulnerability_reads"."cluster_agent_id",
       "vulnerability_reads"."casted_cluster_agent_id",
       "vulnerability_reads"."dismissal_reason",
       "vulnerability_reads"."has_merge_request",
       "vulnerability_reads"."has_remediations",
       "vulnerability_reads"."owasp_top_10",
       "vulnerability_reads"."traversal_ids",
       "vulnerability_reads"."archived",
       "vulnerability_reads"."identifier_names"
FROM "vulnerability_reads"
WHERE "vulnerability_reads"."vulnerability_id" IN
    (SELECT "vulnerability_reads"."vulnerability_id"
     FROM unnest('{6,2,5,3,1,0,4,99}'::smallint[]) AS "report_types"("report_type"), unnest('{1,2,4,5,6,7}'::smallint[]) AS "severities"("severity"), unnest('{1,4}'::smallint[]) AS "states"("state"), LATERAL
       (SELECT "vulnerability_reads"."archived",
               "vulnerability_reads"."report_type",
               "vulnerability_reads"."severity",
               "vulnerability_reads"."state",
               "vulnerability_reads"."resolved_on_default_branch",
               "vulnerability_reads"."owasp_top_10",
               "vulnerability_reads"."traversal_ids",
               "vulnerability_reads"."vulnerability_id"
        FROM "vulnerability_reads"
        WHERE "vulnerability_reads"."traversal_ids" >= '{9970}'
          AND "vulnerability_reads"."traversal_ids" < '{9971}'
          AND "vulnerability_reads"."archived" = FALSE
          AND "vulnerability_reads"."resolved_on_default_branch" = FALSE
          AND "vulnerability_reads"."owasp_top_10" = -1
          AND (vulnerability_reads."report_type" = "report_types"."report_type")
          AND (vulnerability_reads."severity" = "severities"."severity")
          AND (vulnerability_reads."state" = "states"."state")
        ORDER BY "vulnerability_reads"."severity" DESC,
                 "vulnerability_reads"."traversal_ids" DESC,
                 "vulnerability_reads"."vulnerability_id" DESC
        LIMIT 101) AS vulnerability_reads
     ORDER BY "vulnerability_reads"."severity" DESC,
              "vulnerability_reads"."traversal_ids" DESC,
              "vulnerability_reads"."vulnerability_id" DESC
     LIMIT 101)
ORDER BY "vulnerability_reads"."severity" DESC,
         "vulnerability_reads"."traversal_ids" DESC,
         "vulnerability_reads"."vulnerability_id" DESC
LIMIT 101

Relates to: #483367 (closed)