Fix namespace vulnerability statistics schedule worker missing ids

What does this MR do and why?

Fixes Bug: Namespace vulnerability statistics schedul... (#537074 - closed) • Gal Katz • 18.0 by refactoring the existing namespaces extraction logic, and moving the new logic to a new dedicated service.

Changelog: fixed
EE: true

Query plan

Raw SQL

explain WITH namespace_data (id, traversal_ids, next_traversal_ids) AS (
  VALUES
    (97058478, '{9970,96981785,97058478}'::bigint[], '{9970,96981785,97058479}'::bigint[]),
    (100569068, '{9970,96981785,97058478,98867842,100569068}'::bigint[], '{9970,96981785,97058478,98867842,100569069}'::bigint[]),
    (3, '{24, 3}'::bigint[], '{24, 3}'::bigint[] )
)
SELECT
  namespace_data.id
FROM
  namespace_data,
  LATERAL (
    SELECT
      1
    FROM
      vulnerability_statistics
    WHERE
      vulnerability_statistics.archived = false
      AND vulnerability_statistics.traversal_ids >= namespace_data.traversal_ids
      AND vulnerability_statistics.traversal_ids < namespace_data.next_traversal_ids
    LIMIT 1
  ) does_exist

Plan

See UPDATED full plan here ( or view old plan )

 Nested Loop  (cost=0.42..1.57 rows=3 width=4) (actual time=3.448..4.089 rows=2 loops=1)
   Buffers: shared hit=5 read=5
   I/O Timings: read=4.002 write=0.000
   ->  Values Scan on "*VALUES*"  (cost=0.00..0.04 rows=3 width=68) (actual time=0.001..0.004 rows=3 loops=1)
         I/O Timings: read=0.000 write=0.000
   ->  Limit  (cost=0.42..0.49 rows=1 width=4) (actual time=1.360..1.360 rows=1 loops=3)
         Buffers: shared hit=5 read=5
         I/O Timings: read=4.002 write=0.000
         ->  Index Only Scan using idx_vulnerability_statistics_on_traversal_ids_and_letter_grade on public.vulnerability_statistics  (cost=0.42..117.50 rows=1729 width=4) (actual time=1.359..1.359 rows=1 loops=3)
               Index Cond: ((vulnerability_statistics.traversal_ids >= "*VALUES*".column2) AND (vulnerability_statistics.traversal_ids < "*VALUES*".column3))
               Heap Fetches: 0
               Buffers: shared hit=5 read=5
               I/O Timings: read=4.002 write=0.000
Settings: seq_page_cost = '4', work_mem = '100MB', effective_cache_size = '472585MB', jit = 'off', random_page_cost = '1.5'