Skip to content

Change occurrences column type to join feedback table

Mehmet Emin INAC requested to merge 391419_change_join_column_type into master

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