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.
-
I have evaluated the MR acceptance checklist for this MR.
Edited by Mehmet Emin INAC