Backfill security_inventory_filters table with initial data

What does this MR do and why?

Backfill security_inventory_filters table with initial data from vulnerability_statistics and analyzer_project_statuses and projects table.

Changelog: added
EE: true

Related to Backfill security_inventory_filters table (#554134 - closed) • Gal Katz • 18.5

Query plans

fetch_analyzer_statuses

Raw SQL

SELECT
    "analyzer_project_statuses"."project_id",
    "analyzer_project_statuses"."analyzer_type",
    "analyzer_project_statuses"."status"
FROM
    "analyzer_project_statuses"
WHERE
    "analyzer_project_statuses"."project_id" IN (71329491, 68794259, 69782606, 68571417, 66101996)

Plan

See full plan here.

Index Scan using index_analyzer_project_statuses_status on public.analyzer_project_statuses  (cost=0.43..35.25 rows=20 width=12) (actual time=3.761..24.878 rows=25 loops=1)
   Index Cond: (analyzer_project_statuses.project_id = ANY ('{71329491,68794259,69782606,68571417,66101996}'::bigint[]))
   Buffers: shared hit=8 read=31
   I/O Timings: read=24.746 write=0.000
Settings: random_page_cost = '1.5', effective_cache_size = '338688MB', jit = 'off', seq_page_cost = '4', work_mem = '100MB'

---

bulk_upsert

Raw SQL

INSERT INTO "security_inventory_filters" ("project_id", "project_name", "traversal_ids", "archived", "info", "unknown", "low", "medium", "high", "critical", "total", "sast", "sast_advanced", "sast_iac", "dast", "dependency_scanning", "container_scanning", "secret_detection", "coverage_fuzzing", "api_fuzzing", "cluster_image_scanning", "secret_detection_secret_push_protection", "container_scanning_for_registry", "secret_detection_pipeline_based", "container_scanning_pipeline_based")
    VALUES (71329491, 'project_1', '{112,113}', FALSE, 5, 3, 12, 15, 10, 5, 50, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
    (68794259, 'project_2', '{112,113}', FALSE, 5, 3, 12, 15, 10, 5, 50, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
    (69782606, 'project_3', '{112,113}', FALSE, 5, 3, 12, 15, 10, 5, 50, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
    (68571417, 'project_4', '{112,113}', FALSE, 5, 3, 12, 15, 10, 5, 50, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
    (66101996, 'project_5', '{112,113}', FALSE, 5, 3, 12, 15, 10, 5, 50, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
ON CONFLICT ("project_id")
    DO UPDATE SET
        "project_name" = excluded."project_name",
        "traversal_ids" = excluded."traversal_ids",
        "archived" = excluded."archived",
        "info" = excluded."info",
        "unknown" = excluded."unknown",
        "low" = excluded."low",
        "medium" = excluded."medium",
        "high" = excluded."high",
        "critical" = excluded."critical",
        "total" = excluded."total",
        "sast" = excluded."sast",
        "sast_advanced" = excluded."sast_advanced",
        "sast_iac" = excluded."sast_iac",
        "dast" = excluded."dast",
        "dependency_scanning" = excluded."dependency_scanning",
        "container_scanning" = excluded."container_scanning",
        "secret_detection" = excluded."secret_detection",
        "coverage_fuzzing" = excluded."coverage_fuzzing",
        "api_fuzzing" = excluded."api_fuzzing",
        "cluster_image_scanning" = excluded."cluster_image_scanning",
        "secret_detection_secret_push_protection" = excluded."secret_detection_secret_push_protection",
        "container_scanning_for_registry" = excluded."container_scanning_for_registry",
        "secret_detection_pipeline_based" = excluded."secret_detection_pipeline_based",
        "container_scanning_pipeline_based" = excluded."container_scanning_pipeline_based"
    RETURNING
        "id";

Plan

See full plan here.

 ModifyTable on public.security_inventory_filters  (cost=0.00..0.07 rows=5 width=137) (actual time=0.739..0.832 rows=5 loops=1)
   Buffers: shared hit=54 read=8 dirtied=6 written=3
   WAL: records=23 fpi=0 bytes=2069
   I/O Timings: read=0.215 write=0.071
   ->  Values Scan on "*VALUES*"  (cost=0.00..0.07 rows=5 width=137) (actual time=0.242..0.265 rows=5 loops=1)
         Buffers: shared hit=14 read=5 dirtied=1
         WAL: records=1 fpi=0 bytes=99
         I/O Timings: read=0.147 write=0.000
Settings: random_page_cost = '1.5', effective_cache_size = '338688MB', jit = 'off', seq_page_cost = '4', work_mem = '100MB'