Add index on security_findings for keyset pagination

What does this MR do and why?

Adds a composite index on security_findings table to improve keyset pagination performance and resolve query timeouts in the Security Dashboard.

Problem

The Security Dashboard's vulnerability report was experiencing significant performance issues due to expensive sorting operations during keyset pagination. The root cause was identified in #585959 as part of a follow-up to !214777 (merged):

1. Query timeouts on deep pagination - Users experienced timeouts when navigating through large sets of security findings
2. Expensive sorting operations - The ORDER BY severity DESC, id ASC clause required PostgreSQL to perform costly sorting operations without proper index support
3. Poor performance with lateral subqueries - The keyset pagination pattern uses lateral subqueries that couldn't leverage existing indexes effectively

Solution

The new composite index on (scan_id, severity, id) directly supports the lateral subquery pattern used in keyset pagination:

• Eliminates sorting overhead - The ORDER BY severity DESC, id ASC can now be satisfied at the index level
• Optimizes lateral subquery performance - The index structure matches the query's access pattern exactly
• Maintains existing query performance - The index includes scan_id as the leading column to support existing queries

Performance Impact

Before: Query required expensive sorting operations and full table scans After: Query uses index-only operations with significant performance improvement

See detailed query plans:

• Before optimization
• After optimization

Database Query

Context: I ran the following query on this test project with a script that generates 20,000 vulnerabilities per pipeline run. There is a total of 100,000 vulnerabilities associated with the project.

SELECT "security_findings".*
FROM "security_scans",
  unnest('{1,2,4,5,6,7}'::smallint[]) AS "severities" ("severity"),
  LATERAL (
    SELECT "security_findings".*
    FROM "security_findings"
    WHERE ("security_findings"."scan_id" = "security_scans"."id")
      AND ("security_findings"."severity" = "severities"."severity")
      AND "security_findings"."partition_number" = 279
      AND "security_findings"."deduplicated" = TRUE
      AND (
        (EXISTS (
          SELECT 1
          FROM "vulnerabilities"
          INNER JOIN "vulnerability_occurrences"
            ON "vulnerability_occurrences"."vulnerability_id" = "vulnerabilities"."id"
          WHERE "vulnerabilities"."state" = 1
            AND vulnerability_occurrences.uuid = security_findings.uuid
        ))
        OR (NOT EXISTS (
          SELECT 1
          FROM "vulnerability_occurrences"
          WHERE vulnerability_occurrences.uuid = security_findings.uuid
        ))
      )
      AND (
        "security_findings"."severity" < 7
        OR "security_findings"."severity" = 7
          AND "security_findings"."id" > 24705938836
      )
    ORDER BY "security_findings"."severity" DESC, "security_findings"."id" ASC
    LIMIT 51
  ) AS "security_findings"
WHERE "security_scans"."pipeline_id" = 2551565733
  AND "security_scans"."latest" = TRUE
  AND "security_scans"."status" = 1
  AND (
    "security_findings"."severity" < 7
    OR "security_findings"."severity" = 7
      AND "security_findings"."id" > 24705938836
  )
ORDER BY "security_findings"."severity" DESC, "security_findings"."id" ASC
LIMIT 51

Closes #585959

MR acceptance checklist

This checklist encourages us all to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.