Loading
Read SBOM reachability from occurrence refs
What does this MR do and why?
This MR updates the API and GraphQL layers to read SBOM reachability from the branch-specific occurrence refs (added in the previous MR). Every read uses COALESCE(ref value, occurrence value). If a ref has no value yet, the read falls back to sbom_occurrences.reachability.
Vulnerability reachability (GraphQL Vulnerability.reachability, the REST vulnerability entity, Vulnerability#reachability, and both Elasticsearch vulnerability preloaders) starts from the occurrence. It resolves each finding's tracked context once, joins the ref on that context, and takes COALESCE(ref, occurrence). Each vulnerability comes back as one row with its strongest value. If a project still has more than one default context, only the newest is read. A finding with no tracked context is treated as being on the default branch, matching how the rest of the codebase handles these older findings. If the occurrence has no matching ref, or the ref is not written yet, the vulnerability gets the occurrence value, exactly as on master.
Filtering the dependency list rows by tracked ref is done by !257982 (merged) (Elasticsearch, behind the project_dependency_tracked_ref feature flag). This MR does not change which rows are returned. It only makes the reachability field reflect the refs the list is filtered to. The trackedRefIds and trackedRefsScope arguments still also require the existing vulnerabilities_across_contexts feature flag.
The dependency reachability field now reflects the strongest value found across the filtered refs (priority: in_use, then not_found, then unknown). Each ref uses the same COALESCE(ref, occurrence) rule. A dependency with no refs uses the occurrence value. With no filter, it uses the default branch's value, falling back to any branch if the dependency is not found on the default branch. This field is loaded with one batched query per page of results, and a spec was added confirming there is no N+1 query problem.
The Elasticsearch document for Sbom::OccurrenceRef now uses the ref value if present, otherwise the occurrence value. The index already has a reachability field, so no mapping change is needed.
This MR can merge before the backfill (BackfillSbomOccurrenceRefsReachability) finishes, because refs without a value fall back to the occurrence.
This MR has some intentional scope limits. With no branch filter, the PostgreSQL-backed dependency list still returns occurrences from every branch, while the Elasticsearch-backed list returns only default-branch ones — making these consistent would also change the REST dependency list and CSV/JSON exports, so that is out of scope. Group-level dependency lists (both Group.dependencies and the dependency aggregation) still ignore the branch arguments, as on master. The column sbom_occurrences_vulnerabilities.sbom_occurrence_ref_id exists but is not populated yet; this MR joins through the finding's tracked context (via vulnerabilities.finding_id) instead.
Database
Three queries are new or changed. The first loads vulnerability reachability:
WITH "vulnerability_contexts" AS MATERIALIZED (
SELECT "vulnerabilities"."id",
COALESCE(vulnerability_occurrences.security_project_tracked_context_id, default_context.id) AS tracked_context_id
FROM "vulnerabilities"
LEFT JOIN vulnerability_occurrences ON vulnerability_occurrences.id = vulnerabilities.finding_id
LEFT JOIN LATERAL (
SELECT security_project_tracked_contexts.id
FROM security_project_tracked_contexts
WHERE security_project_tracked_contexts.project_id = vulnerabilities.project_id
AND security_project_tracked_contexts.is_default
ORDER BY security_project_tracked_contexts.id DESC
LIMIT 1
) default_context ON vulnerability_occurrences.security_project_tracked_context_id IS NULL
WHERE "vulnerabilities"."id" IN (...)
)
SELECT CASE
WHEN bool_or(COALESCE("sbom_occurrence_refs"."reachability", "sbom_occurrences"."reachability") = 1) THEN 1
WHEN bool_or(COALESCE("sbom_occurrence_refs"."reachability", "sbom_occurrences"."reachability") = 2) THEN 2
ELSE 0
END AS reachability,
"occurrences_vulnerabilities"."vulnerability_id"
FROM "sbom_occurrences"
INNER JOIN "sbom_occurrences_vulnerabilities" "occurrences_vulnerabilities"
ON "occurrences_vulnerabilities"."sbom_occurrence_id" = "sbom_occurrences"."id"
LEFT JOIN vulnerability_contexts
ON vulnerability_contexts.id = occurrences_vulnerabilities.vulnerability_id
LEFT JOIN sbom_occurrence_refs
ON sbom_occurrence_refs.sbom_occurrence_id = sbom_occurrences.id
AND sbom_occurrence_refs.security_project_tracked_context_id = vulnerability_contexts.tracked_context_id
WHERE "occurrences_vulnerabilities"."vulnerability_id" IN (...)
AND COALESCE("sbom_occurrence_refs"."reachability", "sbom_occurrences"."reachability") IN (1, 2)
GROUP BY "occurrences_vulnerabilities"."vulnerability_id"1, 2 and 0 are the in_use, not_found and unknown enum values. GraphQL and Vulnerability#reachability add the IN (1, 2) condition. The Elasticsearch vulnerability preloader runs the same query without it.
The second loads reachability per ref for the dependency list:
SELECT "sbom_occurrence_refs"."sbom_occurrence_id",
COALESCE("sbom_occurrence_refs"."reachability", "sbom_occurrences"."reachability"),
"security_project_tracked_contexts"."is_default"
FROM "sbom_occurrence_refs"
INNER JOIN "security_project_tracked_contexts"
ON "security_project_tracked_contexts"."id" = "sbom_occurrence_refs"."security_project_tracked_context_id"
INNER JOIN "sbom_occurrences"
ON "sbom_occurrences"."id" = "sbom_occurrence_refs"."sbom_occurrence_id"
WHERE "sbom_occurrence_refs"."sbom_occurrence_id" IN (...)The third is the same lookup in the VulnerabilityRead preloader, which filters on vulnerability_occurrence_id (finding IDs) instead of vulnerability_id:
WITH "vulnerability_contexts" AS MATERIALIZED (
SELECT "vulnerability_occurrences"."id",
COALESCE(vulnerability_occurrences.security_project_tracked_context_id, default_context.id) AS tracked_context_id
FROM "vulnerability_occurrences"
LEFT JOIN LATERAL (
SELECT security_project_tracked_contexts.id
FROM security_project_tracked_contexts
WHERE security_project_tracked_contexts.project_id = vulnerability_occurrences.project_id
AND security_project_tracked_contexts.is_default
ORDER BY security_project_tracked_contexts.id DESC
LIMIT 1
) default_context ON vulnerability_occurrences.security_project_tracked_context_id IS NULL
WHERE "vulnerability_occurrences"."id" IN (...)
)
SELECT CASE
WHEN bool_or(COALESCE("sbom_occurrence_refs"."reachability", "sbom_occurrences"."reachability") = 1) THEN 1
WHEN bool_or(COALESCE("sbom_occurrence_refs"."reachability", "sbom_occurrences"."reachability") = 2) THEN 2
ELSE 0
END AS reachability,
"occurrences_vulnerabilities"."vulnerability_occurrence_id"
FROM "sbom_occurrences"
INNER JOIN "sbom_occurrences_vulnerabilities" "occurrences_vulnerabilities"
ON "occurrences_vulnerabilities"."sbom_occurrence_id" = "sbom_occurrences"."id"
LEFT JOIN vulnerability_contexts
ON vulnerability_contexts.id = occurrences_vulnerabilities.vulnerability_occurrence_id
LEFT JOIN sbom_occurrence_refs
ON sbom_occurrence_refs.sbom_occurrence_id = sbom_occurrences.id
AND sbom_occurrence_refs.security_project_tracked_context_id = vulnerability_contexts.tracked_context_id
WHERE "occurrences_vulnerabilities"."vulnerability_occurrence_id" IN (...)
GROUP BY "occurrences_vulnerabilities"."vulnerability_occurrence_id"All tables are on the gitlab_sec database.
Query plans were captured on Database Lab (gitlab-production-sec), with the new column added on the clone. The inputs are the largest pages found: 100 vulnerabilities that link to 23,842 sbom_occurrences_vulnerabilities rows (project 33756109), and 100 findings that link to 34,000 rows (project 75108181). Cold timings on the shared clone mostly show what is already cached, so the table compares warm runs. Times include planning.
| Query | Before (OR join) | After |
|---|---|---|
| By vulnerability ids, 23,842 links, warm | 151 ms, 4.40 GiB (command 510075) | 105 and 110 ms, 1.80 GiB (commands 510390, 510392) |
| By finding ids, 34,000 links, warm | 223 ms, 6.20 GiB (command 510147) | 121 ms, 2.60 GiB (command 510386) |
The before run by vulnerability ids includes the IN (1, 2) condition that GraphQL adds. The after runs are the Elasticsearch preloader query, which has no condition. In the before plan that condition is a filter on the top join, after the ref lookup, so it does not change the buffers.
The first runs after the change took 185 ms by vulnerability ids (command 510388) and 656 ms by finding ids (command 510384). The second read 3,836 pages from disk. The old query's first run read 1,232 and took 511 ms (command 510143).
The old join looked up the vulnerability, the finding and the tracked context once per link. It also joined security_project_tracked_contexts to every ref to read is_default for the OR. The query now resolves each vulnerability's context once in a materialized CTE. Findings without a context take the project's newest default context. The ref is found through idx_sbom_occurrence_refs_on_sbom_occ_id_and_tracked_context_id on sbom_occurrence_id and checked with a plain equality on the context. The per-link lookups and the context join are gone. The query also groups by vulnerability and returns one row each with the strongest value, so callers no longer load every link into Ruby.
Before the newest-default lookup and the grouping were added, the new join's rows matched the old query exactly on 10 inputs (EXCEPT ALL in both directions on vulnerability, occurrence, ref and reachability). The final query differs on purpose in one case. If a project has more than one default context, the old join matched refs on all of them, and the new one reads only the newest. The clone has 21 such projects, and in 3 of them the defaults have different numbers of refs.
The dependency list query is not changed by this. For 100 occurrences with 1 to 4 refs each it takes 8 to 15 ms warm (commands 510168, 510170).
Each link still costs one occurrence lookup and one ref lookup. That is tracked in #632199.
By vulnerability ids, warm (command 510390)
HashAggregate (cost=67335.45..67495.42 rows=15997 width=12) (actual time=90.941..91.294 rows=100 loops=1)
Group Key: occurrences_vulnerabilities.vulnerability_id
Batches: 1 Memory Usage: 545kB
Buffers: shared hit=239861
I/O Timings: read=0.000 write=0.000
CTE vulnerability_contexts
-> Nested Loop Left Join (cost=4.73..993.57 rows=100 width=16) (actual time=0.241..1.848 rows=100 loops=1)
Join Filter: (vulnerability_occurrences.security_project_tracked_context_id IS NULL)
Rows Removed by Join Filter: 100
Buffers: shared hit=1082
I/O Timings: read=0.000 write=0.000
-> Nested Loop Left Join (cost=1.15..633.07 rows=100 width=24) (actual time=0.131..1.460 rows=100 loops=1)
Buffers: shared hit=579
I/O Timings: read=0.000 write=0.000
-> Index Scan using vulnerabilities_pkey on public.vulnerabilities (cost=0.57..274.07 rows=100 width=24) (actual time=0.090..0.589 rows=100 loops=1)
Index Cond: (vulnerabilities.id = ANY ('{397483465,397483463,397483461,397483454,397483449,397483448,397483439,397483436,397483413,397483410,397483404,397483401,397483398,397482445,397482435,397482434,397482428,397482422,397482411,397482392,397482054,397482044,397482018,397482012,397481912,397481797,397481764,397481750,397481665,397481576,397481575,397481573,397481572,397481571,397481569,397481568,397481566,397481563,397481562,397481558,397481557,397481556,397481552,397481551,397481549,397481548,397481545,397481544,397481543,397481541,397481540,397481539,397481536,397481534,397481531,397481529,397481528,397481526,397481525,397481524,397481520,397481519,397481518,397481517,397481516,397481515,397481514,397481513,397481512,397481510,397481507,397481506,397481505,397481504,397481502,397481500,397481499,397481498,397481497,397481496,397481494,397481487,397481486,397481485,397481484,397481483,397481482,397481481,397481480,397481479,397481478,397481477,397481476,397481475,397481474,397481473,397481472,397481471,397481470,397481469}'::bigint[]))
Index Searches: 4
Buffers: shared hit=79
I/O Timings: read=0.000 write=0.000
-> Index Scan using vulnerability_occurrences_pkey on public.vulnerability_occurrences (cost=0.57..3.59 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=100)
Index Cond: (vulnerability_occurrences.id = vulnerabilities.finding_id)
Index Searches: 100
Buffers: shared hit=500
I/O Timings: read=0.000 write=0.000
-> Limit (cost=3.59..3.59 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=100)
Buffers: shared hit=503
I/O Timings: read=0.000 write=0.000
-> Sort (cost=3.59..3.59 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=100)
Sort Key: security_project_tracked_contexts.id DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=503
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_security_project_tracked_contexts_on_project_context on public.security_project_tracked_contexts (cost=0.56..3.58 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=100)
Index Cond: (security_project_tracked_contexts.project_id = vulnerabilities.project_id)
Index Searches: 100
Filter: security_project_tracked_contexts.is_default
Buffers: shared hit=500
I/O Timings: read=0.000 write=0.000
-> Nested Loop Left Join (cost=1336.93..66139.69 rows=16175 width=12) (actual time=3.653..86.626 rows=23842 loops=1)
Join Filter: (sbom_occurrence_refs.security_project_tracked_context_id = vulnerability_contexts.tracked_context_id)
Buffers: shared hit=239861
I/O Timings: read=0.000 write=0.000
-> Hash Left Join (cost=1336.36..56031.09 rows=16175 width=26) (actual time=3.617..14.913 rows=23842 loops=1)
Hash Cond: (occurrences_vulnerabilities.vulnerability_id = vulnerability_contexts.id)
Buffers: shared hit=120651
I/O Timings: read=0.000 write=0.000
-> Gather (cost=1333.11..55967.17 rows=16175 width=18) (actual time=1.696..8.318 rows=23842 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=119569
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=333.11..53349.67 rows=6740 width=18) (actual time=0.577..25.480 rows=7947.33 loops=3)
Buffers: shared hit=119569
I/O Timings: read=0.000 write=0.000
-> Parallel Bitmap Heap Scan on public.sbom_occurrences_vulnerabilities occurrences_vulnerabilities (cost=332.54..29394.28 rows=6740 width=16) (actual time=0.529..1.969 rows=7947.33 loops=3)
Recheck Cond: (occurrences_vulnerabilities.vulnerability_id = ANY ('{397483465,397483463,397483461,397483454,397483449,397483448,397483439,397483436,397483413,397483410,397483404,397483401,397483398,397482445,397482435,397482434,397482428,397482422,397482411,397482392,397482054,397482044,397482018,397482012,397481912,397481797,397481764,397481750,397481665,397481576,397481575,397481573,397481572,397481571,397481569,397481568,397481566,397481563,397481562,397481558,397481557,397481556,397481552,397481551,397481549,397481548,397481545,397481544,397481543,397481541,397481540,397481539,397481536,397481534,397481531,397481529,397481528,397481526,397481525,397481524,397481520,397481519,397481518,397481517,397481516,397481515,397481514,397481513,397481512,397481510,397481507,397481506,397481505,397481504,397481502,397481500,397481499,397481498,397481497,397481496,397481494,397481487,397481486,397481485,397481484,397481483,397481482,397481481,397481480,397481479,397481478,397481477,397481476,397481475,397481474,397481473,397481472,397481471,397481470,397481469}'::bigint[]))
Heap Blocks: exact=11
Buffers: shared hit=357
I/O Timings: read=0.000 write=0.000
-> Bitmap Index Scan on index_sbom_occurrences_vulnerabilities_on_vulnerability_id (cost=0.00..328.25 rows=16175 width=0) (actual time=1.010..1.010 rows=23842 loops=1)
Index Cond: (occurrences_vulnerabilities.vulnerability_id = ANY ('{397483465,397483463,397483461,397483454,397483449,397483448,397483439,397483436,397483413,397483410,397483404,397483401,397483398,397482445,397482435,397482434,397482428,397482422,397482411,397482392,397482054,397482044,397482018,397482012,397481912,397481797,397481764,397481750,397481665,397481576,397481575,397481573,397481572,397481571,397481569,397481568,397481566,397481563,397481562,397481558,397481557,397481556,397481552,397481551,397481549,397481548,397481545,397481544,397481543,397481541,397481540,397481539,397481536,397481534,397481531,397481529,397481528,397481526,397481525,397481524,397481520,397481519,397481518,397481517,397481516,397481515,397481514,397481513,397481512,397481510,397481507,397481506,397481505,397481504,397481502,397481500,397481499,397481498,397481497,397481496,397481494,397481487,397481486,397481485,397481484,397481483,397481482,397481481,397481480,397481479,397481478,397481477,397481476,397481475,397481474,397481473,397481472,397481471,397481470,397481469}'::bigint[]))
Index Searches: 11
Buffers: shared hit=67
I/O Timings: read=0.000 write=0.000
-> Index Scan using sbom_occurrences_pkey on public.sbom_occurrences (cost=0.57..3.55 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=23842)
Index Cond: (sbom_occurrences.id = occurrences_vulnerabilities.sbom_occurrence_id)
Index Searches: 23842
Buffers: shared hit=119212
I/O Timings: read=0.000 write=0.000
-> Hash (cost=2.00..2.00 rows=100 width=16) (actual time=1.901..1.903 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
Buffers: shared hit=1082
I/O Timings: read=0.000 write=0.000
-> CTE Scan on vulnerability_contexts (cost=0.00..2.00 rows=100 width=16) (actual time=0.245..1.877 rows=100 loops=1)
Storage: Memory Maximum Storage: 20kB
Buffers: shared hit=1082
I/O Timings: read=0.000 write=0.000
-> Index Scan using idx_sbom_occurrence_refs_on_sbom_occ_id_and_tracked_context_id on public.sbom_occurrence_refs (cost=0.57..0.61 rows=1 width=18) (actual time=0.003..0.003 rows=1 loops=23842)
Index Cond: (sbom_occurrence_refs.sbom_occurrence_id = sbom_occurrences.id)
Index Searches: 23842
Buffers: shared hit=119210
I/O Timings: read=0.000 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB'
Query ID: -748328594374736868
stats:
Time: 105.182 ms
- planning: 13.261 ms
- execution: 91.921 ms
- I/O read: 0.000 ms
- I/O write: 0.000 msBy finding ids, warm (command 510386)
HashAggregate (cost=81558.13..81751.98 rows=19385 width=12) (actual time=106.639..109.782 rows=100 loops=1)
Group Key: occurrences_vulnerabilities.vulnerability_occurrence_id
Batches: 1 Memory Usage: 545kB
Buffers: shared hit=343332
I/O Timings: read=0.000 write=0.000
CTE vulnerability_contexts
-> Nested Loop Left Join (cost=4.16..671.03 rows=100 width=16) (actual time=0.182..0.987 rows=100 loops=1)
Join Filter: (vulnerability_occurrences.security_project_tracked_context_id IS NULL)
Rows Removed by Join Filter: 100
Buffers: shared hit=601
I/O Timings: read=0.000 write=0.000
-> Index Scan using vulnerability_occurrences_pkey on public.vulnerability_occurrences (cost=0.57..310.53 rows=100 width=20) (actual time=0.074..0.608 rows=100 loops=1)
Index Cond: (vulnerability_occurrences.id = ANY ('{10926077683,10926077681,10926077674,10926077668,10926077664,10926077663,10926077660,10926077658,10926077657,10926077654,10926077647,10926077646,10926077644,10926077635,10926077632,10926077630,10926077628,10926077626,10926077621,10926077616,10926077614,10926077611,10926077610,10926077594,10926077592,10926077591,10926075532,10926075530,10926075528,10926075527,10926075526,10926075522,10926075520,10926075515,10926075499,10926075454,10926075448,10926075443,10926075442,10926075440,10926075433,10926075423,10926075419,10926075415,10926075406,10926075405,10926075403,10926075393,10926075392,10926075391,10926075389,10926075386,10926074398,10926074397,10926074396,10926074395,10926074394,10926074393,10926074392,10926074391,10926074390,10926074389,10926074388,10926074387,10926074386,10926074385,10926074384,10926074383,10926074382,10926074381,10926074380,10926074379,10926074378,10926074377,10926074376,10926074375,10926074374,10926074373,10926074372,10926074371,10926074370,10926074369,10926074368,10926074367,10926074366,10926074365,10926074364,10926074363,10926074362,10926074361,10926074360,10926074359,10926074358,10926074357,10926074356,10926074355,10926074354,10926074353,10926074352,10926074351}'::bigint[]))
Index Searches: 1
Buffers: shared hit=95
I/O Timings: read=0.000 write=0.000
-> Limit (cost=3.59..3.59 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=100)
Buffers: shared hit=506
I/O Timings: read=0.000 write=0.000
-> Sort (cost=3.59..3.59 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=100)
Sort Key: security_project_tracked_contexts.id DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=506
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_security_project_tracked_contexts_on_project_context on public.security_project_tracked_contexts (cost=0.56..3.58 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=100)
Index Cond: (security_project_tracked_contexts.project_id = vulnerability_occurrences.project_id)
Index Searches: 100
Filter: security_project_tracked_contexts.is_default
Buffers: shared hit=503
I/O Timings: read=0.000 write=0.000
-> Nested Loop Left Join (cost=1364.05..80641.28 rows=19665 width=12) (actual time=3.544..102.978 rows=34000 loops=1)
Join Filter: (sbom_occurrence_refs.security_project_tracked_context_id = vulnerability_contexts.tracked_context_id)
Buffers: shared hit=343332
I/O Timings: read=0.000 write=0.000
-> Hash Left Join (cost=1363.48..68351.60 rows=19665 width=26) (actual time=3.511..19.975 rows=34000 loops=1)
Hash Cond: (occurrences_vulnerabilities.vulnerability_occurrence_id = vulnerability_contexts.id)
Buffers: shared hit=173332
I/O Timings: read=0.000 write=0.000
-> Gather (cost=1360.23..68274.58 rows=19665 width=18) (actual time=2.456..11.989 rows=34000 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=172731
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=360.23..65308.08 rows=8194 width=18) (actual time=0.877..36.002 rows=11333.33 loops=3)
Buffers: shared hit=172731
I/O Timings: read=0.000 write=0.000
-> Parallel Bitmap Heap Scan on public.sbom_occurrences_vulnerabilities occurrences_vulnerabilities (cost=359.67..36237.60 rows=8194 width=16) (actual time=0.794..6.112 rows=11333.33 loops=3)
Recheck Cond: (occurrences_vulnerabilities.vulnerability_occurrence_id = ANY ('{10926077683,10926077681,10926077674,10926077668,10926077664,10926077663,10926077660,10926077658,10926077657,10926077654,10926077647,10926077646,10926077644,10926077635,10926077632,10926077630,10926077628,10926077626,10926077621,10926077616,10926077614,10926077611,10926077610,10926077594,10926077592,10926077591,10926075532,10926075530,10926075528,10926075527,10926075526,10926075522,10926075520,10926075515,10926075499,10926075454,10926075448,10926075443,10926075442,10926075440,10926075433,10926075423,10926075419,10926075415,10926075406,10926075405,10926075403,10926075393,10926075392,10926075391,10926075389,10926075386,10926074398,10926074397,10926074396,10926074395,10926074394,10926074393,10926074392,10926074391,10926074390,10926074389,10926074388,10926074387,10926074386,10926074385,10926074384,10926074383,10926074382,10926074381,10926074380,10926074379,10926074378,10926074377,10926074376,10926074375,10926074374,10926074373,10926074372,10926074371,10926074370,10926074369,10926074368,10926074367,10926074366,10926074365,10926074364,10926074363,10926074362,10926074361,10926074360,10926074359,10926074358,10926074357,10926074356,10926074355,10926074354,10926074353,10926074352,10926074351}'::bigint[]))
Heap Blocks: exact=122
Buffers: shared hit=2729
I/O Timings: read=0.000 write=0.000
-> Bitmap Index Scan on idx_sbom_occ_vulns_on_vulnerability_occurrence_id (cost=0.00..354.50 rows=19665 width=0) (actual time=1.516..1.517 rows=34000 loops=1)
Index Cond: (occurrences_vulnerabilities.vulnerability_occurrence_id = ANY ('{10926077683,10926077681,10926077674,10926077668,10926077664,10926077663,10926077660,10926077658,10926077657,10926077654,10926077647,10926077646,10926077644,10926077635,10926077632,10926077630,10926077628,10926077626,10926077621,10926077616,10926077614,10926077611,10926077610,10926077594,10926077592,10926077591,10926075532,10926075530,10926075528,10926075527,10926075526,10926075522,10926075520,10926075515,10926075499,10926075454,10926075448,10926075443,10926075442,10926075440,10926075433,10926075423,10926075419,10926075415,10926075406,10926075405,10926075403,10926075393,10926075392,10926075391,10926075389,10926075386,10926074398,10926074397,10926074396,10926074395,10926074394,10926074393,10926074392,10926074391,10926074390,10926074389,10926074388,10926074387,10926074386,10926074385,10926074384,10926074383,10926074382,10926074381,10926074380,10926074379,10926074378,10926074377,10926074376,10926074375,10926074374,10926074373,10926074372,10926074371,10926074370,10926074369,10926074368,10926074367,10926074366,10926074365,10926074364,10926074363,10926074362,10926074361,10926074360,10926074359,10926074358,10926074357,10926074356,10926074355,10926074354,10926074353,10926074352,10926074351}'::bigint[]))
Index Searches: 1
Buffers: shared hit=49
I/O Timings: read=0.000 write=0.000
-> Index Scan using sbom_occurrences_pkey on public.sbom_occurrences (cost=0.57..3.55 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=34000)
Index Cond: (sbom_occurrences.id = occurrences_vulnerabilities.sbom_occurrence_id)
Index Searches: 34000
Buffers: shared hit=170002
I/O Timings: read=0.000 write=0.000
-> Hash (cost=2.00..2.00 rows=100 width=16) (actual time=1.036..1.038 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
Buffers: shared hit=601
I/O Timings: read=0.000 write=0.000
-> CTE Scan on vulnerability_contexts (cost=0.00..2.00 rows=100 width=16) (actual time=0.185..1.018 rows=100 loops=1)
Storage: Memory Maximum Storage: 20kB
Buffers: shared hit=601
I/O Timings: read=0.000 write=0.000
-> Index Scan using idx_sbom_occurrence_refs_on_sbom_occ_id_and_tracked_context_id on public.sbom_occurrence_refs (cost=0.57..0.61 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=34000)
Index Cond: (sbom_occurrence_refs.sbom_occurrence_id = sbom_occurrences.id)
Index Searches: 34000
Buffers: shared hit=170000
I/O Timings: read=0.000 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB'
Query ID: -1645388898260612379
stats:
Time: 120.934 ms
- planning: 10.558 ms
- execution: 110.376 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 343332 (~2.60 GiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0Dependency list, 100 occurrences x 4 refs, warm (command 510168)
Nested Loop (cost=1.57..1061.92 rows=101 width=11) (actual time=0.129..3.244 rows=400 loops=1)
Buffers: shared hit=4007
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=1.00..699.84 rows=101 width=11) (actual time=0.091..2.016 rows=400 loops=1)
Buffers: shared hit=2007
I/O Timings: read=0.000 write=0.000
-> Index Scan using idx_sbom_occurrence_refs_on_sbom_occ_id_and_tracked_context_id on public.sbom_occurrence_refs (cost=0.57..350.88 rows=101 width=18) (actual time=0.066..1.328 rows=400 loops=1)
Index Cond: (sbom_occurrence_refs.sbom_occurrence_id = ANY ('{...ids elided...}'::bigint[]))
Index Searches: 1
Buffers: shared hit=407
I/O Timings: read=0.000 write=0.000
-> Index Scan using security_project_tracked_contexts_pkey on public.security_project_tracked_contexts (cost=0.44..3.46 rows=1 width=9) (actual time=0.001..0.001 rows=1 loops=400)
Index Cond: (security_project_tracked_contexts.id = sbom_occurrence_refs.security_project_tracked_context_id)
Index Searches: 400
Buffers: shared hit=1600
I/O Timings: read=0.000 write=0.000
-> Index Scan using sbom_occurrences_pkey on public.sbom_occurrences (cost=0.57..3.58 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=400)
Index Cond: (sbom_occurrences.id = sbom_occurrence_refs.sbom_occurrence_id)
Index Searches: 400
Buffers: shared hit=2000
I/O Timings: read=0.000 write=0.000
Settings: work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off'
Query ID: -5666721551633890197
stats:
Time: 8.333 ms
- planning: 5.005 ms
- execution: 3.328 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 4007 (~31.30 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0References
This MR depends on the previous MR, which adds the nullable reachability column to sbom_occurrence_refs and updates ingestion to write branch-specific values.
Depends on: !258536 (merged)
Depends on: !257982 (merged) (moves tracked-ref filtering of the dependency list to Elasticsearch)
Screenshots or screen recordings
Not applicable — this MR changes the GraphQL and REST API layers but has no dedicated UI. GraphQL query results could demonstrate the per-branch reachability values, but none are included in this description.
How to set up and validate locally
- Set up advanced search, make sure advanced dependency management is available, and enable both the
vulnerabilities_across_contextsandproject_dependency_tracked_reffeature flags. !257982 (merged) must be merged. - Query a project's dependencies with
dependencies(trackedRefIds: [...]) { nodes { name reachability } }for two different branches. This is answered by the Elasticsearch-backed finder. Confirm you get different reachability values. - Query a dependency scanning vulnerability on each of the two branches and confirm each shows its own branch's reachability value.
Tests: the existing vulnerability reachability specs from master pass unchanged, and this MR does not modify them (ee/spec/graphql/types/vulnerability_type_spec.rb, ee/spec/models/ee/vulnerability_spec.rb, ee/spec/lib/search/elastic/preloaders/vulnerability/reachability_spec.rb, ee/spec/lib/search/elastic/preloaders/vulnerability_read/reachability_spec.rb). Per-branch behavior, the finding id path, the strongest-value aggregation and the duplicate default case are tested on Sbom::Occurrence.vulnerability_reachabilities in ee/spec/models/sbom/occurrence_spec.rb. The filtered reachability cases (a single tracked ref, and all refs) are tested on the field itself in ee/spec/graphql/types/sbom/dependency_interface_spec.rb, so they do not depend on which finder serves the list. Fallback cases are covered in the ref, Elasticsearch reference and GraphQL dependencies specs.
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.
Edited by Gregory Havenga