Reduce DB load when resetting CI minute notifications
What does this MR do?
Resetting namespace notifications used to hit every single table row. This MR changes it so it only updates the ones that need to be updated to reduce database load.
Query
UPDATE "namespaces" SET "last_ci_minutes_notification_at" = NULL, "last_ci_minutes_usage_notification_level" = NULL WHERE "namespaces"."id" BETWEEN 11000000 AND 12000000 AND "namespaces"."id" >= 11135000 AND "namespaces"."id" < 11136000 AND ("namespaces"."last_ci_minutes_notification_at" IS NOT NULL OR "namespaces"."last_ci_minutes_usage_notification_level" IS NOT NULL)
Execution plan
ModifyTable on public.namespaces (cost=0.43..1409.13 rows=1 width=371) (actual time=1181.656..1181.657 rows=0 loops=1)
Buffers: shared hit=4501 read=863 dirtied=281
I/O Timings: read=1148.573
-> Index Scan using namespaces_pkey on public.namespaces (cost=0.43..1409.13 rows=1 width=371) (actual time=36.347..591.102 rows=66 loops=1)
Index Cond: ((namespaces.id >= 11000000) AND (namespaces.id <= 12000000) AND (namespaces.id >= 11135000) AND (namespaces.id < 11136000))
Filter: ((namespaces.last_ci_minutes_notification_at IS NOT NULL) OR (namespaces.last_ci_minutes_usage_notification_level IS NOT NULL))
Rows Removed by Filter: 904
Buffers: shared hit=343 read=500 dirtied=11
I/O Timings: read=580.469
Summary
Time: 1.184 s
- planning: 2.314 ms
- execution: 1.182 s (estimated* for prod: 0.021...1.181 s)
- I/O read: 1.149 s
- I/O write: N/A
Shared buffers:
- hits: 4501 (~35.20 MiB) from the buffer pool
- reads: 863 (~6.70 MiB) from the OS file cache, including disk I/O
- dirtied: 281 (~2.20 MiB)
- writes: 0
https://postgres.ai/console/gitlab/gitlab-production-tunnel/sessions/2530/commands/7790
Related #323069 (closed)
Does this MR meet the acceptance criteria?
Conformity
-
📋 Does this MR need a changelog?-
I have included a changelog entry. -
I have not included a changelog entry because _____.
-
- N/A Documentation (if required)
-
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Edited by Matija Čupić