Add new index to support age filtering
What does this MR do and why?
This MR adds new indexes to support the new query to filter vulnerabilities by age.
We discussed the additions of theses indexes in this thread.
New query
SELECT
COUNT(*)
FROM
"vulnerabilities"
WHERE (detected_at >= '2023-01-09 19:56:51.902449')
AND "vulnerabilities"."id" IN (
SELECT
"vulnerability_reads"."vulnerability_id"
FROM
"vulnerability_reads"
WHERE
"vulnerability_reads"."project_id" = 43831199
AND "vulnerability_reads"."uuid" IN ('03008860-d99c-5db5-9d7e-696e281a666e', '16da8d31-cc9a-53b3-ba80-ce5c9a2d014c', '29887843-b0c3-51eb-ae32-6541ce126d8c', '7eec5d87-b1a2-530e-b63c-84ea61d2fe3c', 'b7ee0340-1677-5e1f-9d67-bbb39d361671', 'c6a62a41-8ae2-5ce8-b2c1-a9a3d01bd870', 'cb5d498a-c216-5278-85fd-364793805495', 'd09bc1f6-1c75-5016-89b5-3b5e43851117', 'ef7da7cd-095b-56d6-964e-da65da71bb7f', '1ffb3985-a983-5162-af9b-e6dbeb81deeb', '7514bafd-d628-53cb-ad32-85e77844f5d2', '85651ee3-01cd-5b0a-bc23-00c494b45b6a', '04ebf666-c4ef-50b4-b62e-313b4e325d48', '0bf4f16d-8aea-5e4c-b4df-1681baf405e8', '10ae5b79-a964-5bb2-a867-6b3a59236939', '11b31277-fce6-5bb3-874a-1bf3d5c25d3c', '12a53708-2711-5788-8922-5bf2c0de6eaa', '2b2e1d00-1c3c-5185-be54-1c49f69ad166', '2bfa606c-9962-5877-9857-ba6b25f0a914', '2c6944e3-4335-50c9-856c-4b818ee714c2', '2ea4f566-10a8-5d30-95af-ded9b566d801', '3b3c81d4-dd31-5b52-8b57-5fb6e68bfedf', '3fd16cbf-73b2-54d5-8fdc-c6ee775e4498', '41289776-b162-56d8-9054-7db0e7d12146', '46f624da-37f1-500c-a434-b3c65af91fa4', '47174b58-041b-51da-b9f0-99bd9a434f37', '5037e0bd-12d4-523e-91f0-412c93989974', '50d634ab-4ee1-53c5-a4c9-fc05c766be18', '51f1c4c3-23ea-5d98-9a7d-ce279b14f10b', '5b68c2e0-7451-5950-93b7-60ba04235b48', '5c5738c2-193f-5677-a635-ed75c6e5518b', '64fb668c-971f-57c1-8df3-877277e706b7', '6b737a49-7c0f-5391-9185-9a570ab83156', '70104250-aafa-540f-ae36-6ed6d68e3306', '7012f101-97fb-5aa5-ab3e-09b470265864', '79a83d10-0c09-58da-b5d0-9362d2b05837', '82e25ab8-1705-53e2-8fb7-9305e157a84b', '8c07ce7a-ed01-58d4-82f3-981f4954c16e', '9343b6dd-ec32-5cca-bc0b-8bfb2d2bfde1', '97e49d98-a8d4-580a-aaf2-4da860a650c7', '9c701f87-96d3-54d9-a406-aa91d0e61611', 'a2abac6f-c509-5465-8053-aaf5a8213e18', 'a80bc127-b1a5-551c-8126-99235c91c956', 'b334d494-9175-50dd-add3-886df3094986', 'b953f9f0-6dcd-542a-a22f-c8f8b85b1d1c', 'b98df0db-81e8-53ef-84e6-6dd99e05ee3b', 'bcd56f27-2096-59e8-b1ac-7321ddd16aa1', 'c501814e-f79a-5f77-bab5-d1cd70ae3eb7', 'd109ce06-5c79-5118-a48a-4bbb1be24ae3', 'd6a14526-ee08-5d46-ad4a-e51d5d9f6234')
AND "vulnerability_reads"."state" IN (1, 4, 2, 3))
Before Index
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/19160/commands/63219
After Index
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/19160/commands/63238
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 Marcos Rocha