Simplify query to find existing alert
What does this MR do and why?
There is a unique index for non-resolved alerts on project_id
& fingerprint
. When processing new alert notifications, we check for an existing alert record with a matching fingerprint first. This query currently has an ORDER BY
clause, but as there can only be one matching record (or none), the ordering does not need to be present.
Related issue: https://gitlab.com/gitlab-org/gitlab/-/issues/348676
Before
SELECT "alert_management_alerts".* FROM "alert_management_alerts"
WHERE ("alert_management_alerts"."status" NOT IN (2))
AND "alert_management_alerts"."project_id" = <PROJECT_ID>
AND "alert_management_alerts"."fingerprint" = <FINGERPRINT>
ORDER BY "alert_management_alerts"."id" ASC
LIMIT 1
Limit (cost=2.17..2.18 rows=1 width=67) (actual time=1.124..1.125 rows=1 loops=1)
-> Sort (cost=2.17..2.18 rows=1 width=67) (actual time=1.124..1.124 rows=1 loops=1)
Sort Key: id
Sort Method: quicksort Memory: 25kB
-> Index Scan using index_partial_am_alerts_on_project_id_and_fingerprint on alert_management_alerts (cost=0.14..2.16 rows=1 width=67) (actual time=1.112..1.114 rows=1 loops=1)
Index Cond: ((project_id = 21) AND (fingerprint = '\x6d22d10d758340a970007528918fb69f2e14b2a0'::bytea))
Planning Time: 0.132 ms
Execution Time: 1.143 ms
(8 rows)
After
SELECT "alert_management_alerts".* FROM "alert_management_alerts"
WHERE "alert_management_alerts"."project_id" = <PROJECT_ID>
AND "alert_management_alerts"."fingerprint" = <FINGERPRINT>
AND ("alert_management_alerts"."status" NOT IN (2))
LIMIT 1
Limit (cost=0.14..2.16 rows=1 width=67) (actual time=0.012..0.013 rows=1 loops=1)
-> Index Scan using index_partial_am_alerts_on_project_id_and_fingerprint on alert_management_alerts (cost=0.14..2.16 rows=1 width=67) (actual time=0.012..0.012 rows=1 loops=1)
Index Cond: ((project_id = 21) AND (fingerprint = '\x6d22d10d758340a970007528918fb69f2e14b2a0'::bytea))
Planning Time: 0.117 ms
Execution Time: 0.026 ms
(5 rows)
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 Sarah Yasonik