Add advisory resolvers

Florence Schwartzrequested to merge
591069-create-package_metadata-advisory_resolver into master

What does this MR do and why?

Add advisory resolvers

In a separate MR we have added the package metadata advisory type, this adds the related resolvers. The query type will be added as a next step.

References

New scopes DB queries

ee/app/models/package_metadata/advisory.rb#L37 ordered_by_id:

Full database query 
[1] pry(main)> PackageMetadata::Advisory.ordered_by_id.to_sql
=> "SELECT \"pm_advisories\".* FROM \"pm_advisories\" ORDER BY \"pm_advisories\".\"id\" ASC"
[2] pry(main)> PackageMetadata::Advisory.ordered_by_id.explain
  PackageMetadata::Advisory Load (1.7ms)  SELECT "pm_advisories".* FROM "pm_advisories" ORDER BY "pm_advisories"."id" ASC /*application:console,db_config_database:gitlabhq_development,db_config_name:main,console_hostname:fschwartz--20250512-HL90J,console_username:florence,line:<internal:kernel>:187:in `loop'*/
=> EXPLAIN SELECT "pm_advisories".* FROM "pm_advisories" ORDER BY "pm_advisories"."id" ASC /*application:console,db_config_database:gitlabhq_development,db_config_name:main,console_hostname:fschwartz--20250512-HL90J,console_username:florence,line:<internal:kernel>:187:in `loop'*/
                             QUERY PLAN
---------------------------------------------------------------------
 Sort  (cost=1.03..1.03 rows=2 width=286)
   Sort Key: id
   ->  Seq Scan on pm_advisories  (cost=0.00..1.02 rows=2 width=286)
(3 rows)

Using postgres.ai

explain SELECT * FROM pm_advisories ORDER BY id ASC

 Index Scan using pm_advisories_pkey on public.pm_advisories  (cost=0.42..45972.16 rows=121223 width=1407) (actual time=1.378..537.714 rows=121223 loops=1)  
   Buffers: shared hit=112286 read=675 dirtied=52  
   WAL: records=63 fpi=52 bytes=275854  
Settings: effective_cache_size = '472585MB', jit = 'off', random_page_cost = '1.5', work_mem = '230MB', seq_page_cost = '4'  

Summary:
  
Time: 545.708 ms  
  - planning: 2.647 ms  
  - execution: 543.061 ms  
    - I/O read: N/A  
    - I/O write: N/A  
  
Shared buffers:  
  - hits: 112286 (~877.20 MiB) from the buffer pool  
  - reads: 675 (~5.30 MiB) from the OS file cache, including disk I/O  
  - dirtied: 52 (~416.00 KiB)  
  - writes: 0

Details and visualization: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/50114/commands/148791.

ee/app/models/package_metadata/advisory.rb#L38 by_identifiers:

Full database query 
[3] pry(main)> PackageMetadata::Advisory.by_identifiers([{ name: "CVE-2021-1234" }]).to_sql
=> "SELECT \"pm_advisories\".* FROM \"pm_advisories\" WHERE (identifiers @> '[{\"name\":{\"name\":\"CVE-2021-1234\"}}]')"
[4] pry(main)> PackageMetadata::Advisory.by_identifiers([{ name: "CVE-2021-1234" }]).explain
  PackageMetadata::Advisory Load (0.6ms)  SELECT "pm_advisories".* FROM "pm_advisories" WHERE (identifiers @> '[{"name":{"name":"CVE-2021-1234"}}]') /*application:console,db_config_database:gitlabhq_development,db_config_name:main,console_hostname:fschwartz--20250512-HL90J,console_username:florence,line:<internal:kernel>:187:in `loop'*/
=> EXPLAIN SELECT "pm_advisories".* FROM "pm_advisories" WHERE (identifiers @> '[{"name":{"name":"CVE-2021-1234"}}]') /*application:console,db_config_database:gitlabhq_development,db_config_name:main,console_hostname:fschwartz--20250512-HL90J,console_username:florence,line:<internal:kernel>:187:in `loop'*/
                                QUERY PLAN
---------------------------------------------------------------------------
 Seq Scan on pm_advisories  (cost=0.00..1.02 rows=1 width=286)
   Filter: (identifiers @> '[{"name": {"name": "CVE-2021-1234"}}]'::jsonb)
(2 rows)

Using postgres.ai

# with only one identifier given 

explain SELECT * FROM pm_advisories WHERE identifiers @> '[{"name": "CVE-2021-1234"}]'::jsonb

 Bitmap Heap Scan on public.pm_advisories  (cost=12.62..29.80 rows=11 width=1407) (actual time=0.122..0.123 rows=0 loops=1)
   Buffers: shared hit=9
   ->  Bitmap Index Scan using index_pm_advisories_on_identifiers  (cost=0.00..12.62 rows=11 width=0) (actual time=0.115..0.115 rows=0 loops=1)
         Index Cond: (pm_advisories.identifiers @> '[{"name": "CVE-2021-1234"}]'::jsonb)
         Buffers: shared hit=9
Settings: random_page_cost = '1.5', work_mem = '230MB', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off'

Summary 

Time: 8.772 ms  
  - planning: 8.551 ms  
  - execution: 0.221 ms  
    - I/O read: N/A  
    - I/O write: N/A  
  
Shared buffers:  
  - hits: 9 (~72.00 KiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0  

# with multiple identifiers given 

explain SELECT * FROM pm_advisories WHERE (identifiers @> '[{"name": "CVE-2021-1234"}]'::jsonb) OR (identifiers @> '[{"name": "CVE-2021-5678"}]'::jsonb)

 Bitmap Heap Scan on public.pm_advisories  (cost=25.25..61.72 rows=23 width=1407) (actual time=0.098..0.099 rows=0 loops=1)
   Buffers: shared hit=18
   ->  BitmapOr  (cost=25.25..25.25 rows=23 width=0) (actual time=0.093..0.094 rows=0 loops=1)
         Buffers: shared hit=18
         ->  Bitmap Index Scan using index_pm_advisories_on_identifiers  (cost=0.00..12.62 rows=11 width=0) (actual time=0.066..0.067 rows=0 loops=1)
               Index Cond: (pm_advisories.identifiers @> '[{"name": "CVE-2021-1234"}]'::jsonb)
               Buffers: shared hit=9
         ->  Bitmap Index Scan using index_pm_advisories_on_identifiers  (cost=0.00..12.62 rows=11 width=0) (actual time=0.026..0.026 rows=0 loops=1)
               Index Cond: (pm_advisories.identifiers @> '[{"name": "CVE-2021-5678"}]'::jsonb)
               Buffers: shared hit=9
Settings: random_page_cost = '1.5', work_mem = '230MB', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off'

Summary:
  
Time: 5.860 ms  
  - planning: 5.687 ms  
  - execution: 0.173 ms  
    - I/O read: N/A  
    - I/O write: N/A  
  
Shared buffers:  
  - hits: 18 (~144.00 KiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

ee/app/models/package_metadata/advisory.rb#L45 created_after:

Full database query 
[5] pry(main)> PackageMetadata::Advisory.created_after(1.week.ago).to_sql
=> "SELECT \"pm_advisories\".* FROM \"pm_advisories\" WHERE (created_at > '2026-03-10 16:32:59.940611')"
[6] pry(main)> PackageMetadata::Advisory.created_after(1.week.ago).explain
  PackageMetadata::Advisory Load (0.3ms)  SELECT "pm_advisories".* FROM "pm_advisories" WHERE (created_at > '2026-03-10 16:33:07.856950') /*application:console,db_config_database:gitlabhq_development,db_config_name:main,console_hostname:fschwartz--20250512-HL90J,console_username:florence,line:<internal:kernel>:187:in `loop'*/
=> EXPLAIN SELECT "pm_advisories".* FROM "pm_advisories" WHERE (created_at > '2026-03-10 16:33:07.856950') /*application:console,db_config_database:gitlabhq_development,db_config_name:main,console_hostname:fschwartz--20250512-HL90J,console_username:florence,line:<internal:kernel>:187:in `loop'*/
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Seq Scan on pm_advisories  (cost=0.00..1.02 rows=1 width=286)
   Filter: (created_at > '2026-03-10 16:33:07.85695+00'::timestamp with time zone)
(2 rows)

Using postgres.ai

explain SELECT * FROM pm_advisories WHERE created_at > '2024-01-01'

 Seq Scan on public.pm_advisories  (cost=0.00..116323.29 rows=43008 width=1407) (actual time=0.019..49.540 rows=43036 loops=1)
   Filter: (pm_advisories.created_at > '2024-01-01 00:00:00+00'::timestamp with time zone)
   Rows Removed by Filter: 78187
   Buffers: shared hit=28702
Settings: effective_cache_size = '472585MB', jit = 'off', random_page_cost = '1.5', work_mem = '230MB', seq_page_cost = '4'

Summary:
  
Time: 54.325 ms  
  - planning: 2.674 ms  
  - execution: 51.651 ms  
    - I/O read: N/A  
    - I/O write: N/A  
  
Shared buffers:  
  - hits: 28702 (~224.20 MiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

Details and visualization: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/50114/commands/148794.

ee/app/models/package_metadata/advisory.rb#L46 updated_after:

Full database query 
[7] pry(main)> PackageMetadata::Advisory.updated_after(1.week.ago).to_sql
=> "SELECT \"pm_advisories\".* FROM \"pm_advisories\" WHERE (updated_at > '2026-03-10 16:33:36.205274')"
[8] pry(main)> PackageMetadata::Advisory.updated_after(1.week.ago).explain
  PackageMetadata::Advisory Load (0.2ms)  SELECT "pm_advisories".* FROM "pm_advisories" WHERE (updated_at > '2026-03-10 16:33:37.465931') /*application:console,db_config_database:gitlabhq_development,db_config_name:main,console_hostname:fschwartz--20250512-HL90J,console_username:florence,line:<internal:kernel>:187:in `loop'*/
=> EXPLAIN SELECT "pm_advisories".* FROM "pm_advisories" WHERE (updated_at > '2026-03-10 16:33:37.465931') /*application:console,db_config_database:gitlabhq_development,db_config_name:main,console_hostname:fschwartz--20250512-HL90J,console_username:florence,line:<internal:kernel>:187:in `loop'*/
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Seq Scan on pm_advisories  (cost=0.00..1.02 rows=1 width=286)
   Filter: (updated_at > '2026-03-10 16:33:37.465931+00'::timestamp with time zone)
(2 rows)

Using postgres.ai

explain SELECT * FROM pm_advisories WHERE updated_at > '2024-01-01'

 Seq Scan on public.pm_advisories  (cost=0.00..116323.29 rows=121135 width=1407) (actual time=0.017..57.647 rows=121133 loops=1)
   Filter: (pm_advisories.updated_at > '2024-01-01 00:00:00+00'::timestamp with time zone)
   Rows Removed by Filter: 90
   Buffers: shared hit=28702
Settings: random_page_cost = '1.5', work_mem = '230MB', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off'

Summary:
  
Time: 66.164 ms  
  - planning: 2.622 ms  
  - execution: 63.542 ms  
    - I/O read: N/A  
    - I/O write: N/A  
  
Shared buffers:  
  - hits: 28702 (~224.20 MiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

Details and visualization: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/50114/commands/148795.

Screenshots or screen recordings

Before After

How to set up and validate locally

This is not testable locally yet, it will be in the next step.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #591069 (closed)

Edited by Florence Schwartz

Merge request reports