Adjust vulnerability dashboard to use namespace_historical_statistic table

What does this MR do and why?

Updates group security dashboard to use the new vulnerability_namespace_historical_statistics table instead of vulnerability_historical_statistics. The new table holds records aggregated by traversal_ids and date which should improve query performance.

This is a draft to ensure it isn't merged by mistake. This MR should not be merged until the backfill migration has completed

The new table is backfilled by Backfill vulnerabilities namespace historical s... (!165928 - merged) • Gal Katz, Yuval Siev • 17.7
This is the forth step in Customer Unable to load Security Dashboard at G... (#440712 - closed) • Gal Katz • 17.9 • At risk

Added scopes:

NamespaceHistoricalStatistic - for_namespace_and_descendants

SQL

Generated using group.vulnerability_historical_statistics.allow_cross_joins_across_databases(url: 'https://gitlab.com/gitlab-org/gitlab/-/issues/473014').to_sql. Ids taken from here.

SELECT
    "vulnerability_namespace_historical_statistics".*
FROM
    "vulnerability_namespace_historical_statistics"
WHERE (traversal_ids >= ('{9970}'))
    AND (traversal_ids < ('{9971}'))

Query plan

Link here.

 Index Scan using index_vuln_namespace_historical_statistics_traversal_ids_date on public.vulnerability_namespace_historical_statistics  (cost=0.56..103303.52 rows=72913 width=108) (actual time=5.888..38344.726 rows=79766 loops=1)
   Index Cond: ((vulnerability_namespace_historical_statistics.traversal_ids >= '{9970}'::bigint[]) AND (vulnerability_namespace_historical_statistics.traversal_ids < '{9971}'::bigint[]))
   Buffers: shared hit=8303 read=71848 dirtied=1012
   WAL: records=1583 fpi=1012 bytes=7955726
   I/O Timings: read=37909.086 write=0.000
Settings: work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off'

NamespaceHistoricalStatistic - between_dates

SQL

Generated using ::Vulnerabilities::NamespaceHistoricalStatistic.between_dates(Date.parse('2024-10-27'), Date.parse('2024-11-26')).to_sql

SELECT
    "vulnerability_namespace_historical_statistics".*
FROM
    "vulnerability_namespace_historical_statistics"
WHERE
    "vulnerability_namespace_historical_statistics"."date" BETWEEN '2024-10-27' AND '2024-11-26'

Query plan

Link here.

Index Scan using index_vuln_namespace_historical_statistics_traversal_ids_date on public.vulnerability_namespace_historical_statistics  (cost=0.56..1236711.60 rows=2003160 width=108) (actual time=3.310..73560.482 rows=2005783 loops=1)
   Index Cond: ((vulnerability_namespace_historical_statistics.date >= '2024-10-27'::date) AND (vulnerability_namespace_historical_statistics.date <= '2024-11-26'::date))
   Buffers: shared hit=1942008 read=282731 dirtied=6348 written=1027
   WAL: records=8070 fpi=6314 bytes=47559451
   I/O Timings: read=68879.344 write=16.986
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off', work_mem = '100MB'

NamespaceHistoricalStatistic - aggregated_by_date & grouped_by_date

SQL

Generated using ::Vulnerabilities::NamespaceHistoricalStatistic.grouped_by_date.aggregated_by_date.to_sql

SELECT
    "vulnerability_namespace_historical_statistics"."date",
    SUM("vulnerability_namespace_historical_statistics"."total") AS total,
    SUM("vulnerability_namespace_historical_statistics"."info") AS info,
    SUM("vulnerability_namespace_historical_statistics"."unknown") AS unknown,
    SUM("vulnerability_namespace_historical_statistics"."low") AS low,
    SUM("vulnerability_namespace_historical_statistics"."medium") AS medium,
    SUM("vulnerability_namespace_historical_statistics"."high") AS high,
    SUM("vulnerability_namespace_historical_statistics"."critical") AS critical
FROM
    "vulnerability_namespace_historical_statistics"
GROUP BY
    "vulnerability_namespace_historical_statistics"."date"
ORDER BY
    "vulnerability_namespace_historical_statistics"."date" ASC

Query plan

Link here.

Aggregate  (cost=2036669.86..2036773.85 rows=367 width=60) (actual time=39078.372..39121.534 rows=367 loops=1)
   Group Key: vulnerability_namespace_historical_statistics.date
   Buffers: shared hit=59635 read=374126 dirtied=1320 written=5
   WAL: records=2243 fpi=1320 bytes=10452021
   I/O Timings: read=107348.193 write=0.124
   ->  Gather Merge  (cost=2036669.86..2036755.50 rows=734 width=60) (actual time=39078.357..39121.203 rows=1101 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=59635 read=374126 dirtied=1320 written=5
         WAL: records=2243 fpi=1320 bytes=10452021
         I/O Timings: read=107348.193 write=0.124
         ->  Sort  (cost=2035669.84..2035670.76 rows=367 width=60) (actual time=39072.684..39072.707 rows=367 loops=3)
               Sort Key: vulnerability_namespace_historical_statistics.date
               Sort Method: quicksort  Memory: 56kB
               Buffers: shared hit=59635 read=374126 dirtied=1320 written=5
               WAL: records=2243 fpi=1320 bytes=10452021
               I/O Timings: read=107348.193 write=0.124
               ->  HashAggregate  (cost=2035650.53..2035654.20 rows=367 width=60) (actual time=39072.439..39072.516 rows=367 loops=3)
                     Group Key: vulnerability_namespace_historical_statistics.date
                     Buffers: shared hit=59617 read=374126 dirtied=1320 written=5
                     WAL: records=2243 fpi=1320 bytes=10452021
                     I/O Timings: read=107348.193 write=0.124
                     ->  Parallel Seq Scan on public.vulnerability_namespace_historical_statistics  (cost=0.00..1835198.18 rows=10022618 width=32) (actual time=0.062..37524.656 rows=8022254 loops=3)
                           Buffers: shared hit=59617 read=374126 dirtied=1320 written=5
                           WAL: records=2243 fpi=1320 bytes=10452021
                           I/O Timings: read=107348.193 write=0.124
Settings: work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off'