Change occurrences column type to join feedback table

What does this MR do and why?

This MR fixes a query performance issue by making a change to utilize an existing index.

Related to #391419 (closed) and gitlab-com/gl-infra/production#8527 (closed)

Database review

The old version of the query
SELECT
    "vulnerability_occurrences".*
FROM
    "vulnerability_occurrences"
WHERE
    "vulnerability_occurrences"."vulnerability_id" = 76821038
    AND (EXISTS (
            SELECT
                1
            FROM
                "vulnerability_feedback"
            WHERE (vulnerability_occurrences.uuid = vulnerability_feedback.finding_uuid::text)
            AND "vulnerability_feedback"."feedback_type" = 0))

Execution plan(~800ms): https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/16341/commands/56105

The new version of the query
SELECT
    "vulnerability_occurrences".*
FROM
    "vulnerability_occurrences"
WHERE
    "vulnerability_occurrences"."vulnerability_id" = 76821038
    AND (EXISTS (
            SELECT
                1
            FROM
                "vulnerability_feedback"
            WHERE (vulnerability_occurrences.uuid::uuid = vulnerability_feedback.finding_uuid)
            AND "vulnerability_feedback"."feedback_type" = 0))

Execution plan(~9ms): https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/16341/commands/56106

MR acceptance checklist

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

Edited by Mehmet Emin INAC

Merge request reports

Loading