Skip to content

Add new index to support age filtering

Marcos Rocha requested to merge mc_rocha-add-detected-at-id-index-vulnerabilities-399116 into master

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.

Edited by Marcos Rocha

Merge request reports