Loading
Treat placeholder solutions as no fix for fix_available
What does this MR do and why?
Approval policy rules with fix_available: true only treated an empty solution as no fix. Dependency scanning stores a non-empty placeholder ("Unfortunately, there is no solution available ..."), so those findings stayed in scope and kept blocking MRs.
The placeholder now counts as no fix, reusing UNKNOWN_SOLUTION_REGEX translated once to a Postgres pattern ((?n) keeps ^ matching at line starts, as in Ruby). This applies to Security::Finding.fix_available / .no_fix_available and Vulnerabilities::Finding.with_fix_available. It is read-side, so findings already stored are covered without a backfill.
Behind fix_available_excludes_placeholder_solution (gitlab_com_derisk, per project). The scopes default to the old behaviour; only the two policy finders pass the flag value. Rollout: #633412. No changelog entry, as the flag is disabled by default.
How to verify
Specs (flag on is the spec default; stub_feature_flags(fix_available_excludes_placeholder_solution: false) covers the old behaviour):
bundle exec rspec ee/spec/models/security/finding_spec.rb \
ee/spec/models/vulnerabilities/finding_spec.rb \
ee/spec/finders/security/scan_result_policies \
ee/spec/services/security/scan_result_policies/update_approvals_service_spec.rb
bundle exec rspec ee/spec/models/ee/vulnerability_spec.rb -e with_fix_availableEnd to end, update_approvals_service_spec.rb ("when the only findings have a placeholder solution"): an approval policy with fix_available: true on dependency_scanning, vulnerabilities_allowed: 0, and one high-severity new finding whose solution is "Unfortunately, there is no solution available yet.".
| Flag | Result |
|---|---|
| on | rule is satisfied (approvals_required goes to 0), no violation comment |
| off | rule is violated (approvals_required unchanged), violation comment enqueued |
Database
The scopes only add a regex filter on rows the existing indexes already select. No migration, no new index. Both tables live on gitlab_sec (db/docs/), so the plans come from gitlab-production-sec on postgres.ai Database Lab (Joe, EXPLAIN (ANALYZE, BUFFERS), warm cache unless noted). Filters were picked by size only: a project with several hundred thousand vulnerabilities on the default branch, and a succeeded dependency_scanning scan with tens of thousands of findings (all with a non-empty solution, so the regex runs on every row). Ids and per-project row counts below are placeholders (<n_vulns>, <n_findings>, <n_fixable>).
The scopes default to exclude_placeholder_solutions: false; the policy finders pass the flag value. flag on is exclude_placeholder_solutions: true. flag off leaves the placeholder clause out, so its SQL and plan are the pre-MR ones.
Security::Finding.fix_available / .no_fix_available (ee/app/models/security/finding.rb)
Finder context: FindingsFinder, scoped to one pipeline's latest successful scans.
-- fix_available, flag on
SELECT "security_findings".* FROM "security_findings"
INNER JOIN "security_scans" ON "security_findings"."scan_id" = "security_scans"."id"
WHERE "security_scans"."pipeline_id" = <pipeline_id>
AND "security_findings"."partition_number" = <partition_number>
AND "security_scans"."latest" = TRUE AND "security_scans"."status" = 1
AND (jsonb_array_length(finding_data -> 'remediation_byte_offsets')::bigint > 0
OR (COALESCE((finding_data->>'solution')::text, '') <> ''
AND (finding_data->>'solution')::text !~* '(?n)^Unfortunately,?\s*there is no solution available'));
-- no_fix_available, flag on
SELECT "security_findings".* FROM "security_findings"
INNER JOIN "security_scans" ON "security_findings"."scan_id" = "security_scans"."id"
WHERE "security_scans"."pipeline_id" = <pipeline_id>
AND "security_findings"."partition_number" = <partition_number>
AND "security_scans"."latest" = TRUE AND "security_scans"."status" = 1
AND ((finding_data -> 'remediation_byte_offsets' IS NULL
OR jsonb_array_length(finding_data -> 'remediation_byte_offsets')::bigint <= 0)
AND (COALESCE((finding_data->>'solution')::text, '') = ''
OR (finding_data->>'solution')::text ~* '(?n)^Unfortunately,?\s*there is no solution available'));| Query | Flag | Time | Buffers (shared hit) | Rows |
|---|---|---|---|---|
fix_available |
off | 0.54 s | 152,583 | <n_findings> |
fix_available |
on | 0.72 s | 225,489 | <n_findings> |
no_fix_available |
off | 0.27 s | 79,677 | 0 |
no_fix_available |
on | 0.52 s | 152,583 | 0 |
`fix_available`, flag off
Nested Loop (actual time=0.081..541.601 rows=<n_findings> loops=1)
Buffers: shared hit=152583
-> Index Scan using index_security_scans_on_length_of_warnings on security_scans (actual time=0.028..0.030 rows=1 loops=1)
Index Cond: (pipeline_id = <pipeline_id>)
Filter: (latest AND (status = 1))
Buffers: shared hit=5
-> Index Scan using security_findings_<partition_number>_scan_id_deduplicated_idx on security_findings_<partition_number> security_findings (actual time=0.051..535.133 rows=<n_findings> loops=1)
Index Cond: (scan_id = id)
Filter: ((partition_number = <partition_number>) AND (((jsonb_array_length((finding_data -> 'remediation_byte_offsets'::text)))::bigint > 0) OR (COALESCE((finding_data ->> 'solution'::text), ''::text) <> ''::text)))
Buffers: shared hit=152578`fix_available`, flag on
Nested Loop (actual time=0.116..719.268 rows=<n_findings> loops=1)
Buffers: shared hit=225489
-> Index Scan using index_security_scans_on_length_of_warnings on security_scans (actual time=0.025..0.027 rows=1 loops=1)
Index Cond: (pipeline_id = <pipeline_id>)
Filter: (latest AND (status = 1))
Buffers: shared hit=5
-> Index Scan using security_findings_<partition_number>_scan_id_deduplicated_idx on security_findings_<partition_number> security_findings (actual time=0.089..713.265 rows=<n_findings> loops=1)
Index Cond: (scan_id = id)
Filter: ((partition_number = <partition_number>) AND (((jsonb_array_length((finding_data -> 'remediation_byte_offsets'::text)))::bigint > 0) OR ((COALESCE((finding_data ->> 'solution'::text), ''::text) <> ''::text) AND ((finding_data ->> 'solution'::text) !~* '(?n)^Unfortunately,?\s*there is no solution available'::text))))
Buffers: shared hit=225484`no_fix_available`, flag off
Nested Loop (actual time=272.025..272.027 rows=0 loops=1)
Buffers: shared hit=79677
-> Index Scan using index_security_scans_on_length_of_warnings on security_scans (actual time=0.025..0.028 rows=1 loops=1)
Index Cond: (pipeline_id = <pipeline_id>)
Filter: (latest AND (status = 1))
Buffers: shared hit=5
-> Index Scan using security_findings_<partition_number>_scan_id_deduplicated_idx on security_findings_<partition_number> security_findings (actual time=271.993..271.994 rows=0 loops=1)
Index Cond: (scan_id = id)
Filter: ((partition_number = <partition_number>) AND (COALESCE((finding_data ->> 'solution'::text), ''::text) = ''::text) AND (((finding_data -> 'remediation_byte_offsets'::text) IS NULL) OR ((jsonb_array_length((finding_data -> 'remediation_byte_offsets'::text)))::bigint <= 0)))
Rows Removed by Filter: <n_findings>
Buffers: shared hit=79672`no_fix_available`, flag on
Nested Loop (actual time=516.141..516.144 rows=0 loops=1)
Buffers: shared hit=152583
-> Index Scan using index_security_scans_on_length_of_warnings on security_scans (actual time=0.030..0.034 rows=1 loops=1)
Index Cond: (pipeline_id = <pipeline_id>)
Filter: (latest AND (status = 1))
Buffers: shared hit=5
-> Index Scan using security_findings_<partition_number>_scan_id_deduplicated_idx on security_findings_<partition_number> security_findings (actual time=516.104..516.104 rows=0 loops=1)
Index Cond: (scan_id = id)
Filter: ((partition_number = <partition_number>) AND ((COALESCE((finding_data ->> 'solution'::text), ''::text) = ''::text) OR ((finding_data ->> 'solution'::text) ~* '(?n)^Unfortunately,?\s*there is no solution available'::text)) AND (((finding_data -> 'remediation_byte_offsets'::text) IS NULL) OR ((jsonb_array_length((finding_data -> 'remediation_byte_offsets'::text)))::bigint <= 0)))
Rows Removed by Filter: <n_findings>
Buffers: shared hit=152578Verdict: same plan; the regex adds about 4 to 6 microseconds per row, so a 41k-finding scan stays under 1 s, and only with the flag on.
Vulnerabilities::Finding.with_fix_available (ee/app/models/vulnerabilities/finding.rb)
Direct SQL of the scope, flag on:
-- with_fix_available(true)
SELECT "vulnerability_occurrences".* FROM "vulnerability_occurrences"
WHERE ((vulnerability_occurrences.solution IS NOT NULL AND vulnerability_occurrences.solution <> '')
AND (vulnerability_occurrences.solution !~* '(?n)^Unfortunately,?\s*there is no solution available')
OR (EXISTS (SELECT 1 FROM "vulnerability_findings_remediations"
WHERE "vulnerability_findings_remediations"."vulnerability_occurrence_id" = "vulnerability_occurrences"."id")));
-- with_fix_available(false)
SELECT "vulnerability_occurrences".* FROM "vulnerability_occurrences"
WHERE ((vulnerability_occurrences.solution IS NULL OR vulnerability_occurrences.solution = '')
OR (vulnerability_occurrences.solution ~* '(?n)^Unfortunately,?\s*there is no solution available'))
AND (NOT EXISTS (SELECT 1 FROM "vulnerability_findings_remediations"
WHERE "vulnerability_findings_remediations"."vulnerability_occurrence_id" = "vulnerability_occurrences"."id"));The policy path always reaches it through Vulnerability.with_fix_available (next section), so the plans below are for that query.
Vulnerability.with_fix_available (ee/app/models/ee/vulnerability.rb)
Finder context: VulnerabilitiesFinder (project.vulnerabilities.for_default_branch.with_fix_available(...)), flag on:
-- with_fix_available(true)
SELECT "vulnerabilities".* FROM "vulnerabilities"
INNER JOIN "vulnerability_occurrences" ON "vulnerability_occurrences"."vulnerability_id" = "vulnerabilities"."id"
WHERE "vulnerabilities"."project_id" = <project_id> AND "vulnerabilities"."present_on_default_branch" = TRUE
AND ((vulnerability_occurrences.solution IS NOT NULL AND vulnerability_occurrences.solution <> '')
AND (vulnerability_occurrences.solution !~* '(?n)^Unfortunately,?\s*there is no solution available')
OR (EXISTS (SELECT 1 FROM "vulnerability_findings_remediations"
WHERE "vulnerability_findings_remediations"."vulnerability_occurrence_id" = "vulnerability_occurrences"."id")));
-- with_fix_available(false)
SELECT "vulnerabilities".* FROM "vulnerabilities"
INNER JOIN "vulnerability_occurrences" ON "vulnerability_occurrences"."vulnerability_id" = "vulnerabilities"."id"
WHERE "vulnerabilities"."project_id" = <project_id> AND "vulnerabilities"."present_on_default_branch" = TRUE
AND ((vulnerability_occurrences.solution IS NULL OR vulnerability_occurrences.solution = '')
OR (vulnerability_occurrences.solution ~* '(?n)^Unfortunately,?\s*there is no solution available'))
AND (NOT EXISTS (SELECT 1 FROM "vulnerability_findings_remediations"
WHERE "vulnerability_findings_remediations"."vulnerability_occurrence_id" = "vulnerability_occurrences"."id"));Project with several hundred thousand vulnerabilities on the default branch (far above typical):
| Query | Flag | Time | Buffers (shared hit / read) | Rows |
|---|---|---|---|---|
with_fix_available(true) |
off | 47.0 s | 2,644,211 / 580,264 | <n_fixable> |
with_fix_available(true) |
on | 49.0 s | 2,649,945 / 574,545 | <n_fixable> |
with_fix_available(false) |
off | 16.9 s | 2,649,933 / 574,690 | <n_vulns> |
with_fix_available(false) |
on | 16.6 s | 2,650,157 / 574,481 | <n_vulns> |
The 5 rows that move between true and false are the placeholder findings. The first run on a cold clone took 2.5 min, all of it I/O read.
`with_fix_available(true)`, flag off
Nested Loop (actual time=1.290..46957.811 rows=<n_fixable> loops=1)
Buffers: shared hit=2644211 read=580264
-> Index Scan using index_vulnerabilities_on_project_id_and_id on vulnerabilities (actual time=0.855..14843.703 rows=<n_vulns> loops=1)
Index Cond: (project_id = <project_id>)
Filter: present_on_default_branch
Buffers: shared hit=94259 read=230905
-> Index Scan using index_vulnerability_occurrences_on_vulnerability_id on vulnerability_occurrences (actual time=0.088..0.088 rows=0 loops=<n_vulns>)
Index Cond: (vulnerability_id = id)
Filter: (((solution IS NOT NULL) AND (solution <> ''::text)) OR EXISTS(SubPlan 1))
Rows Removed by Filter: 1
Buffers: shared hit=2549952 read=349359
SubPlan 1
-> Index Only Scan using index_vulnerability_findings_remediations_on_unique_keys on vulnerability_findings_remediations (actual time=0.001..0.001 rows=0 loops=<n_vulns>)
Index Cond: (vulnerability_occurrence_id = id)
Buffers: shared hit=1086499 read=647`with_fix_available(true)`, flag on
Nested Loop (actual time=1.495..48960.753 rows=<n_fixable> loops=1)
Buffers: shared hit=2649945 read=574545
-> Index Scan using index_vulnerabilities_on_project_id_and_id on vulnerabilities (actual time=0.913..15365.036 rows=<n_vulns> loops=1)
Index Cond: (project_id = <project_id>)
Filter: present_on_default_branch
Buffers: shared hit=98968 read=226196
-> Index Scan using index_vulnerability_occurrences_on_vulnerability_id on vulnerability_occurrences (actual time=0.092..0.092 rows=0 loops=<n_vulns>)
Index Cond: (vulnerability_id = id)
Filter: (((solution IS NOT NULL) AND (solution <> ''::text) AND (solution !~* '(?n)^Unfortunately,?\s*there is no solution available'::text)) OR EXISTS(SubPlan 1))
Rows Removed by Filter: 1
Buffers: shared hit=2550977 read=348349
SubPlan 1
-> Index Only Scan using index_vulnerability_findings_remediations_on_unique_keys on vulnerability_findings_remediations (actual time=0.001..0.001 rows=0 loops=<n_vulns>)
Index Cond: (vulnerability_occurrence_id = id)
Buffers: shared hit=1086508 read=653`with_fix_available(false)`, flag off
Gather (actual time=5.131..16876.734 rows=<n_vulns> loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2649933 read=574690
-> Nested Loop Anti Join (actual time=3.356..16789.202 rows=<n_vulns_per_worker> loops=3)
Buffers: shared hit=2649933 read=574690
-> Nested Loop (actual time=3.266..16507.444 rows=<n_vulns_per_worker> loops=3)
Buffers: shared hit=1563431 read=574044
-> Parallel Index Scan using index_vulnerabilities_on_project_id_and_id on vulnerabilities (actual time=0.795..5234.064 rows=<n_vulns_per_worker> loops=3)
Index Cond: (project_id = <project_id>)
Filter: present_on_default_branch
Buffers: shared hit=98976 read=226332
-> Index Scan using index_vulnerability_occurrences_on_vulnerability_id on vulnerability_occurrences (actual time=0.093..0.093 rows=1 loops=<n_vulns>)
Index Cond: (vulnerability_id = id)
Filter: ((solution IS NULL) OR (solution = ''::text))
Buffers: shared hit=1464455 read=347712
-> Index Only Scan using index_vulnerability_findings_remediations_on_unique_keys on vulnerability_findings_remediations (actual time=0.002..0.002 rows=0 loops=<n_vulns>)
Index Cond: (vulnerability_occurrence_id = id)
Buffers: shared hit=1086502 read=646`with_fix_available(false)`, flag on
Gather (actual time=2.958..16645.155 rows=<n_vulns> loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2650157 read=574481
-> Nested Loop Anti Join (actual time=2.683..16564.003 rows=<n_vulns_per_worker> loops=3)
Buffers: shared hit=2650157 read=574481
-> Nested Loop (actual time=2.607..16286.706 rows=<n_vulns_per_worker> loops=3)
Buffers: shared hit=1563646 read=573829
-> Parallel Index Scan using index_vulnerabilities_on_project_id_and_id on vulnerabilities (actual time=0.784..5161.392 rows=<n_vulns_per_worker> loops=3)
Index Cond: (project_id = <project_id>)
Filter: present_on_default_branch
Buffers: shared hit=99153 read=226155
-> Index Scan using index_vulnerability_occurrences_on_vulnerability_id on vulnerability_occurrences (actual time=0.091..0.092 rows=1 loops=<n_vulns>)
Index Cond: (vulnerability_id = id)
Filter: ((solution IS NULL) OR (solution = ''::text) OR (solution ~* '(?n)^Unfortunately,?\s*there is no solution available'::text))
Buffers: shared hit=1464493 read=347674
-> Index Only Scan using index_vulnerability_findings_remediations_on_unique_keys on vulnerability_findings_remediations (actual time=0.002..0.002 rows=0 loops=<n_vulns>)
Index Cond: (vulnerability_occurrence_id = id)
Buffers: shared hit=1086511 read=652Verdict: same plan and buffers with the flag on or off; the cost is the existing nested loop over this outlier project, not the regex.
Complete queries executed by the policy callers
The scope-level plans above are the building blocks. The two callers wrap them differently, so this section runs the full statements the callers execute, flag on and off, on the same gitlab-production-sec clone. Every statement below was captured with .to_sql / SQL notifications from the code at this MR's head, with fix_available_excludes_placeholder_solution stubbed on and off (ee/app/finders/security/scan_result_policies/).
Policy shape used for all runs: dependency_scanning, severity critical + high, vulnerabilities_allowed: 0 (and 10 for the LIMIT 11 rows). Enum values in the SQL: severity 7/6 = critical/high, scan_type 2 and report_type 1 = dependency scanning, vulnerabilities.state 2 = dismissed and 1/4 = detected/confirmed. Findings come from a succeeded dependency_scanning scan with tens of thousands of findings, all in the active partition; vulnerabilities come from a project with a large vulnerability count, of which only a small fraction match the policy filter. Ids and row counts are placeholders. Plans are EXPLAIN (ANALYZE, BUFFERS), with the empty per-partition scans of the related-pipelines plan elided.
Caller 1: UpdateApprovalsService#findings_uuids (ee/app/services/security/scan_result_policies/update_approvals_service.rb:450-468). Runs FindingsFinder#execute and then Security::Finding.distinct_context_unaware_uuids (ee/app/models/security/finding.rb:283-285), so the statement is SELECT DISTINCT COALESCE(context_unaware_uuid, uuid). It is called for the source pipeline (:445-448, dismissed derived from the rule's states) and the target pipeline (:441-443). With related pipeline IDs the finder switches from pipeline.security_findings.by_partition_number(...) to by_project_id_and_pipeline_ids (findings_finder.rb:63-69), which drops the partition filter, so the plan appends over every partition.
-- 1a. source pipeline, no related pipelines, fix_available: true, dismissed: false, flag on
SELECT DISTINCT COALESCE(context_unaware_uuid, uuid) FROM "security_findings"
INNER JOIN "security_scans" ON "security_findings"."scan_id" = "security_scans"."id"
INNER JOIN "security_scans" "scans_security_findings" ON "scans_security_findings"."id" = "security_findings"."scan_id"
WHERE "security_scans"."pipeline_id" = <pipeline_id>
AND "security_findings"."partition_number" = <partition_number>
AND "security_scans"."latest" = TRUE
AND "security_scans"."status" = 1
AND "security_findings"."severity" IN (7, 6)
AND "security_scans"."scan_type" = 2
AND (NOT EXISTS (SELECT 1 FROM "vulnerabilities"
INNER JOIN "vulnerability_occurrences" ON "vulnerability_occurrences"."vulnerability_id" = "vulnerabilities"."id"
WHERE "vulnerabilities"."state" = 2 AND (vulnerability_occurrences.uuid = security_findings.uuid)))
AND (jsonb_array_length(finding_data -> 'remediation_byte_offsets')::bigint > 0 OR (COALESCE((finding_data->>'solution')::text, '') <> '' AND (finding_data->>'solution')::text !~* '(?n)^Unfortunately,?\s*there is no solution available'))
-- 1b. same, fix_available: false, flag on
SELECT DISTINCT COALESCE(context_unaware_uuid, uuid) FROM "security_findings"
INNER JOIN "security_scans" ON "security_findings"."scan_id" = "security_scans"."id"
INNER JOIN "security_scans" "scans_security_findings" ON "scans_security_findings"."id" = "security_findings"."scan_id"
WHERE "security_scans"."pipeline_id" = <pipeline_id>
AND "security_findings"."partition_number" = <partition_number>
AND "security_scans"."latest" = TRUE
AND "security_scans"."status" = 1
AND "security_findings"."severity" IN (7, 6)
AND "security_scans"."scan_type" = 2
AND (NOT EXISTS (SELECT 1 FROM "vulnerabilities"
INNER JOIN "vulnerability_occurrences" ON "vulnerability_occurrences"."vulnerability_id" = "vulnerabilities"."id"
WHERE "vulnerabilities"."state" = 2 AND (vulnerability_occurrences.uuid = security_findings.uuid)))
AND ((finding_data -> 'remediation_byte_offsets' IS NULL OR jsonb_array_length(finding_data -> 'remediation_byte_offsets')::bigint <= 0) AND (COALESCE((finding_data->>'solution')::text, '') = '' OR (finding_data->>'solution')::text ~* '(?n)^Unfortunately,?\s*there is no solution available'))
-- 1c. related pipelines, fix_available: true, dismissed: false, flag on
SELECT DISTINCT COALESCE(context_unaware_uuid, uuid) FROM "security_findings"
INNER JOIN "security_scans" ON "security_scans"."id" = "security_findings"."scan_id"
WHERE "security_scans"."project_id" = <project_id>
AND "security_scans"."pipeline_id" IN (<pipeline_id>, <related_pipeline_id>)
AND "security_scans"."latest" = TRUE
AND "security_scans"."status" = 1
AND "security_findings"."severity" IN (7, 6)
AND "security_scans"."scan_type" = 2
AND (NOT EXISTS (SELECT 1 FROM "vulnerabilities"
INNER JOIN "vulnerability_occurrences" ON "vulnerability_occurrences"."vulnerability_id" = "vulnerabilities"."id"
WHERE "vulnerabilities"."state" = 2 AND (vulnerability_occurrences.uuid = security_findings.uuid)))
AND (jsonb_array_length(finding_data -> 'remediation_byte_offsets')::bigint > 0 OR (COALESCE((finding_data->>'solution')::text, '') <> '' AND (finding_data->>'solution')::text !~* '(?n)^Unfortunately,?\s*there is no solution available'))Flag off is the same statement without the placeholder regex clause. The target-pipeline call is the same statement without the NOT EXISTS (...) dismissed clause (dismissed: nil).
Caller 2: SyncPreexistingStatesApprovalRulesService#vulnerabilities (ee/app/services/security/scan_result_policies/sync_preexisting_states_approval_rules_service.rb:104-118). Runs VulnerabilitiesFinder with limit: vulnerabilities_allowed + 1, then .count (:61), which wraps the limited relation in SELECT COUNT(*) FROM (... LIMIT n).
-- 2a. fix_available: true, flag on (LIMIT 1 for vulnerabilities_allowed: 0)
SELECT COUNT(*)
FROM (SELECT 1 AS one FROM "vulnerabilities"
INNER JOIN "vulnerability_occurrences" ON "vulnerability_occurrences"."vulnerability_id" = "vulnerabilities"."id"
WHERE "vulnerabilities"."project_id" = <project_id>
AND "vulnerabilities"."present_on_default_branch" = TRUE
AND "vulnerabilities"."state" IN (1, 4)
AND "vulnerabilities"."severity" IN (7, 6)
AND "vulnerabilities"."report_type" = 1
AND ((vulnerability_occurrences.solution IS NOT NULL AND vulnerability_occurrences.solution <> '') AND (vulnerability_occurrences.solution !~* '(?n)^Unfortunately,?\s*there is no solution available')
OR (EXISTS (SELECT 1 FROM "vulnerability_findings_remediations"
WHERE "vulnerability_findings_remediations"."vulnerability_occurrence_id" = "vulnerability_occurrences"."id")))
LIMIT 1) subquery_for_count
-- 2b. fix_available: false, flag on
SELECT COUNT(*)
FROM (SELECT 1 AS one FROM "vulnerabilities"
INNER JOIN "vulnerability_occurrences" ON "vulnerability_occurrences"."vulnerability_id" = "vulnerabilities"."id"
WHERE "vulnerabilities"."project_id" = <project_id>
AND "vulnerabilities"."present_on_default_branch" = TRUE
AND "vulnerabilities"."state" IN (1, 4)
AND "vulnerabilities"."severity" IN (7, 6)
AND "vulnerabilities"."report_type" = 1
AND ((vulnerability_occurrences.solution IS NULL OR vulnerability_occurrences.solution = '') OR (vulnerability_occurrences.solution ~* '(?n)^Unfortunately,?\s*there is no solution available'))
AND (NOT EXISTS (SELECT 1 FROM "vulnerability_findings_remediations"
WHERE "vulnerability_findings_remediations"."vulnerability_occurrence_id" = "vulnerability_occurrences"."id"))
LIMIT 1) subquery_for_countTimings are cold / warm: cold is the first run after a clone reset, warm is the mean of two repeats with everything in shared buffers. Cold runs mostly measure disk reads and vary between resets (an earlier cold run of the first flag-off query took 36 s, almost all I/O), so use the warm column and the buffer counts for the comparison. Buffers are warm shared hits.
| Caller | Policy | Flag off (cold / warm) | Flag on (cold / warm) | Buffers off | Buffers on |
|---|---|---|---|---|---|
| Update approvals, source pipeline | fix_available: true |
5.17 s / 426.2 ms | 6.06 s / 521.7 ms | 282,926 | 308,920 |
| Update approvals, source pipeline | fix_available: false |
258.2 ms / 109.3 ms | 641.1 ms / 232.7 ms | 33,494 | 65,002 |
| Update approvals, related pipelines | fix_available: true |
6.41 s / 837.9 ms | 6.13 s / 1.07 s | 566,065 | 618,068 |
| Update approvals, related pipelines | fix_available: false |
658.8 ms / 242.7 ms | 990.6 ms / 498.6 ms | 67,186 | 130,217 |
Preexisting sync, LIMIT 1 |
fix_available: true |
6.6 ms / 0.14 ms | 1.9 ms / 0.18 ms | 19 | 19 |
Preexisting sync, LIMIT 1 |
fix_available: false |
1.08 s / 5.3 ms | 11.6 ms / 0.49 ms | 6,819 | 310 |
Preexisting sync, LIMIT 11 |
fix_available: true |
4.1 ms / 0.20 ms | n/a / 0.27 ms | 76 | 76 |
Preexisting sync, LIMIT 11 |
fix_available: false |
324.3 ms / 4.8 ms | n/a / 3.1 ms | 6,819 | 2,245 |
How to read it:
- Plan shape is identical with the flag on and off in every case. The only change is the extra regex term in the
Filter:of thesecurity_findings(caller 1) orvulnerability_occurrences(caller 2) scan. - Caller 1 pays the regex once per scanned finding that has a non-empty solution: about +0.1 s on a scan with tens of thousands of findings (4 to 5 microseconds per row, matching the scope-level plans), and about +0.23 s for the two-pipeline variant. The
Unique/Sort, the partitionAppendand theNOT EXISTSanti join are unchanged. - With
fix_available: falsethe flag-on statement lets the placeholder findings (about 1% of the scan) through the filter, so a few hundred more rows go through the anti join. That, plus running the regex on every row, accounts for the 0.11 s to 0.23 s difference. Flag off returns no rows there because it filters on an empty solution only. - Caller 2 is bounded by
LIMITand usesindex_vulnerabilities_common_finder_query_on_default_branch, so it stays at or under a few milliseconds warm. Withfix_available: falsethe flag-on run is faster because placeholder solutions now count as no fix: the first candidates match and theLIMITstops the scan early, where flag off has to walk every candidate and finds none. That is the intended behaviour change, not a plan change.
Caller 1a, source pipeline, fix_available: true, flag off
Unique (cost=183.92..183.93 rows=1 width=16) (actual time=430.576..434.848 rows=<n_uuids> loops=1)
Buffers: shared hit=282926
-> Sort (cost=183.92..183.92 rows=1 width=16) (actual time=430.573..431.515 rows=<n_uuids> loops=1)
Sort Key: (COALESCE(security_findings.context_unaware_uuid, security_findings.uuid))
Sort Method: quicksort Memory: 769kB
Buffers: shared hit=282926
-> Nested Loop Anti Join (cost=2.28..183.91 rows=1 width=16) (actual time=0.177..425.509 rows=<n_uuids> loops=1)
Buffers: shared hit=282923
-> Nested Loop (cost=1.14..180.38 rows=1 width=32) (actual time=0.145..219.555 rows=<n_uuids> loops=1)
Buffers: shared hit=62202
-> Index Scan using index_security_scans_on_pipeline_id_and_scan_type on public.security_scans scans_security_findings (cost=0.57..4.45 rows=1 width=8) (actual time=0.025..0.037 rows=1 loops=1)
Index Cond: ((scans_security_findings.pipeline_id = '<pipeline_id>'::bigint) AND (scans_security_findings.scan_type = 2))
Filter: (scans_security_findings.latest AND (scans_security_findings.status = 1))
Buffers: shared hit=10
-> Index Scan using security_findings_<partition_number>_scan_id_severity_id_idx on gitlab_partitions_dynamic.security_findings_<partition_number> security_findings (cost=0.56..174.26 rows=167 width=40) (actual time=0.119..216.743 rows=<n_uuids> loops=1)
Index Cond: ((security_findings.scan_id = scans_security_findings.id) AND (security_findings.severity = ANY ('{7,6}'::integer[])))
Filter: ((security_findings.partition_number = <partition_number>) AND (((jsonb_array_length((security_findings.finding_data -> 'remediation_byte_offsets'::text)))::bigint > 0) OR (COALESCE((security_findings.finding_data ->> 'solution'::text), ''::text) <> ''::text)))
Buffers: shared hit=62192
-> Nested Loop (cost=1.15..3.52 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=<n>)
Buffers: shared hit=220721
-> Index Only Scan using index_vuln_findings_on_uuid_including_vuln_id_1 on public.vulnerability_occurrences (cost=0.57..1.41 rows=1 width=24) (actual time=0.005..0.005 rows=1 loops=<n>)
Index Cond: (vulnerability_occurrences.uuid = security_findings.uuid)
Heap Fetches: 2727
Buffers: shared hit=106481
-> Index Scan using vulnerabilities_pkey on public.vulnerabilities (cost=0.57..2.11 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=<n>)
Index Cond: (vulnerabilities.id = vulnerability_occurrences.vulnerability_id)
Filter: (vulnerabilities.state = 2)
Rows Removed by Filter: 1
Buffers: shared hit=114240Caller 1a, source pipeline, fix_available: true, flag on
Unique (cost=184.76..184.77 rows=1 width=16) (actual time=524.691..528.843 rows=<n_uuids> loops=1)
Buffers: shared hit=308920
-> Sort (cost=184.76..184.76 rows=1 width=16) (actual time=524.683..525.559 rows=<n_uuids> loops=1)
Sort Key: (COALESCE(security_findings.context_unaware_uuid, security_findings.uuid))
Sort Method: quicksort Memory: 769kB
Buffers: shared hit=308920
-> Nested Loop Anti Join (cost=2.28..184.75 rows=1 width=16) (actual time=0.239..519.871 rows=<n_uuids> loops=1)
Buffers: shared hit=308917
-> Nested Loop (cost=1.14..181.22 rows=1 width=32) (actual time=0.207..306.038 rows=<n_uuids> loops=1)
Buffers: shared hit=90913
-> Index Scan using index_security_scans_on_pipeline_id_and_scan_type on public.security_scans scans_security_findings (cost=0.57..4.45 rows=1 width=8) (actual time=0.030..0.042 rows=1 loops=1)
Index Cond: ((scans_security_findings.pipeline_id = '<pipeline_id>'::bigint) AND (scans_security_findings.scan_type = 2))
Filter: (scans_security_findings.latest AND (scans_security_findings.status = 1))
Buffers: shared hit=10
-> Index Scan using security_findings_<partition_number>_scan_id_severity_id_idx on gitlab_partitions_dynamic.security_findings_<partition_number> security_findings (cost=0.56..175.10 rows=167 width=40) (actual time=0.176..303.405 rows=<n_uuids> loops=1)
Index Cond: ((security_findings.scan_id = scans_security_findings.id) AND (security_findings.severity = ANY ('{7,6}'::integer[])))
Filter: ((security_findings.partition_number = <partition_number>) AND (((jsonb_array_length((security_findings.finding_data -> 'remediation_byte_offsets'::text)))::bigint > 0) OR ((COALESCE((security_findings.finding_data ->> 'solution'::text), ''::text) <> ''::text) AND ((security_findings.finding_data ->> 'solution'::text) !~* '(?n)^Unfortunately,?\s*there is no solution available'::text))))
Rows Removed by Filter: <n_placeholder>
Buffers: shared hit=90903
-> Nested Loop (cost=1.15..3.52 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=<n_rows>)
Buffers: shared hit=218004
-> Index Only Scan using index_vuln_findings_on_uuid_including_vuln_id_1 on public.vulnerability_occurrences (cost=0.57..1.41 rows=1 width=24) (actual time=0.005..0.005 rows=1 loops=<n_rows>)
Index Cond: (vulnerability_occurrences.uuid = security_findings.uuid)
Heap Fetches: 2684
Buffers: shared hit=105174
-> Index Scan using vulnerabilities_pkey on public.vulnerabilities (cost=0.57..2.11 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=<n_rows>)
Index Cond: (vulnerabilities.id = vulnerability_occurrences.vulnerability_id)
Filter: (vulnerabilities.state = 2)
Rows Removed by Filter: 1
Buffers: shared hit=112830Caller 1b, source pipeline, fix_available: false, flag off
Unique (cost=183.37..183.38 rows=1 width=16) (actual time=114.720..114.724 rows=0 loops=1)
Buffers: shared hit=33494
-> Sort (cost=183.37..183.38 rows=1 width=16) (actual time=114.719..114.722 rows=0 loops=1)
Sort Key: (COALESCE(security_findings.context_unaware_uuid, security_findings.uuid))
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=33494
-> Nested Loop Anti Join (cost=2.28..183.36 rows=1 width=16) (actual time=114.695..114.698 rows=0 loops=1)
Buffers: shared hit=33491
-> Nested Loop (cost=1.14..179.14 rows=1 width=32) (actual time=114.694..114.695 rows=0 loops=1)
Buffers: shared hit=33491
-> Index Scan using index_security_scans_on_pipeline_id_and_scan_type on public.security_scans scans_security_findings (cost=0.57..4.45 rows=1 width=8) (actual time=0.025..0.031 rows=1 loops=1)
Index Cond: ((scans_security_findings.pipeline_id = '<pipeline_id>'::bigint) AND (scans_security_findings.scan_type = 2))
Filter: (scans_security_findings.latest AND (scans_security_findings.status = 1))
Buffers: shared hit=10
-> Index Scan using security_findings_<partition_number>_scan_id_severity_id_idx on gitlab_partitions_dynamic.security_findings_<partition_number> security_findings (cost=0.56..174.68 rows=1 width=40) (actual time=114.660..114.660 rows=0 loops=1)
Index Cond: ((security_findings.scan_id = scans_security_findings.id) AND (security_findings.severity = ANY ('{7,6}'::integer[])))
Filter: ((security_findings.partition_number = <partition_number>) AND (COALESCE((security_findings.finding_data ->> 'solution'::text), ''::text) = ''::text) AND (((security_findings.finding_data -> 'remediation_byte_offsets'::text) IS NULL) OR ((jsonb_array_length((security_findings.finding_data -> 'remediation_byte_offsets'::text)))::bigint <= 0)))
Rows Removed by Filter: <n_rows>
Buffers: shared hit=33481
-> Nested Loop (cost=1.15..4.21 rows=1 width=16) (never executed)
-> Index Only Scan using index_vuln_findings_on_uuid_including_vuln_id_1 on public.vulnerability_occurrences (cost=0.57..2.10 rows=1 width=24) (never executed)
Index Cond: (vulnerability_occurrences.uuid = security_findings.uuid)
Heap Fetches: 0
-> Index Scan using vulnerabilities_pkey on public.vulnerabilities (cost=0.57..2.11 rows=1 width=8) (never executed)
Index Cond: (vulnerabilities.id = vulnerability_occurrences.vulnerability_id)
Filter: (vulnerabilities.state = 2)Caller 1b, source pipeline, fix_available: false, flag on
Unique (cost=184.21..184.22 rows=1 width=16) (actual time=231.054..231.109 rows=<n_placeholder> loops=1)
Buffers: shared hit=65002
-> Sort (cost=184.21..184.22 rows=1 width=16) (actual time=231.046..231.059 rows=<n_placeholder> loops=1)
Sort Key: (COALESCE(security_findings.context_unaware_uuid, security_findings.uuid))
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=65002
-> Nested Loop Anti Join (cost=2.28..184.20 rows=1 width=16) (actual time=2.264..230.899 rows=<n_placeholder> loops=1)
Buffers: shared hit=64999
-> Nested Loop (cost=1.14..179.98 rows=1 width=32) (actual time=2.215..227.113 rows=<n_placeholder> loops=1)
Buffers: shared hit=62202
-> Index Scan using index_security_scans_on_pipeline_id_and_scan_type on public.security_scans scans_security_findings (cost=0.57..4.45 rows=1 width=8) (actual time=0.020..0.025 rows=1 loops=1)
Index Cond: ((scans_security_findings.pipeline_id = '<pipeline_id>'::bigint) AND (scans_security_findings.scan_type = 2))
Filter: (scans_security_findings.latest AND (scans_security_findings.status = 1))
Buffers: shared hit=10
-> Index Scan using security_findings_<partition_number>_scan_id_severity_id_idx on gitlab_partitions_dynamic.security_findings_<partition_number> security_findings (cost=0.56..175.52 rows=1 width=40) (actual time=2.194..227.024 rows=<n_placeholder> loops=1)
Index Cond: ((security_findings.scan_id = scans_security_findings.id) AND (security_findings.severity = ANY ('{7,6}'::integer[])))
Filter: ((security_findings.partition_number = <partition_number>) AND ((COALESCE((security_findings.finding_data ->> 'solution'::text), ''::text) = ''::text) OR ((security_findings.finding_data ->> 'solution'::text) ~* '(?n)^Unfortunately,?\s*there is no solution available'::text)) AND (((security_findings.finding_data -> 'remediation_byte_offsets'::text) IS NULL) OR ((jsonb_array_length((security_findings.finding_data -> 'remediation_byte_offsets'::text)))::bigint <= 0)))
Rows Removed by Filter: <n_rows>
Buffers: shared hit=62192
-> Nested Loop (cost=1.15..4.21 rows=1 width=16) (actual time=0.013..0.013 rows=0 loops=<n>)
Buffers: shared hit=2797
-> Index Only Scan using index_vuln_findings_on_uuid_including_vuln_id_1 on public.vulnerability_occurrences (cost=0.57..2.10 rows=1 width=24) (actual time=0.008..0.008 rows=1 loops=<n>)
Index Cond: (vulnerability_occurrences.uuid = security_findings.uuid)
Heap Fetches: 43
Buffers: shared hit=1387
-> Index Scan using vulnerabilities_pkey on public.vulnerabilities (cost=0.57..2.11 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=<n>)
Index Cond: (vulnerabilities.id = vulnerability_occurrences.vulnerability_id)
Filter: (vulnerabilities.state = 2)
Rows Removed by Filter: 1
Buffers: shared hit=1410Caller 1c, related pipelines, fix_available: true, flag off
Unique (cost=6035.83..6035.84 rows=1 width=16) (actual time=800.785..806.791 rows=<n_uuids> loops=1)
Buffers: shared hit=566065
-> Sort (cost=6035.83..6035.83 rows=1 width=16) (actual time=800.783..802.597 rows=<n_rows> loops=1)
Sort Key: (COALESCE(security_findings.context_unaware_uuid, security_findings.uuid))
Sort Method: quicksort Memory: 1537kB
Buffers: shared hit=566065
-> Nested Loop Anti Join (cost=2.27..6035.82 rows=1 width=16) (actual time=0.564..790.755 rows=<n_rows> loops=1)
Buffers: shared hit=566062
-> Nested Loop (cost=1.13..6030.64 rows=1 width=32) (actual time=0.533..417.219 rows=<n_rows> loops=1)
Buffers: shared hit=124620
-> Index Scan using index_for_security_scans_scan_type on public.security_scans (cost=0.56..5.66 rows=1 width=8) (actual time=0.037..0.042 rows=2 loops=1)
Index Cond: ((security_scans.scan_type = 2) AND (security_scans.project_id = <project_id>) AND (security_scans.pipeline_id = ANY ('{<pipeline_id>,<related_pipeline_id>}'::bigint[])))
Filter: security_scans.latest
Buffers: shared hit=12
-> Append (cost=0.57..5968.44 rows=5654 width=40) (actual time=0.382..206.088 rows=<n_uuids> loops=2)
Buffers: shared hit=124608
-> (30 other security_findings partitions elided: Index Scan on (scan_id, severity), rows=0 loops=2, shared hit=8 each)
-> Index Scan using security_findings_<partition_number>_scan_id_severity_id_idx on gitlab_partitions_dynamic.security_findings_<partition_number> security_findings_31 (cost=0.56..173.84 rows=167 width=40) (actual time=0.085..203.671 rows=<n_uuids> loops=2)
Index Cond: ((security_findings_31.scan_id = security_scans.id) AND (security_findings_31.severity = ANY ('{7,6}'::integer[])))
Filter: (((jsonb_array_length((security_findings_31.finding_data -> 'remediation_byte_offsets'::text)))::bigint > 0) OR (COALESCE((security_findings_31.finding_data ->> 'solution'::text), ''::text) <> ''::text))
Buffers: shared hit=124368
-> Nested Loop (cost=1.15..5.17 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=<n_rows>)
Buffers: shared hit=441442
-> Index Only Scan using index_vuln_findings_on_uuid_including_vuln_id_1 on public.vulnerability_occurrences (cost=0.57..1.93 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=<n_rows>)
Index Cond: (vulnerability_occurrences.uuid = security_findings.uuid)
Heap Fetches: 5454
Buffers: shared hit=212962
-> Index Scan using vulnerabilities_pkey on public.vulnerabilities (cost=0.57..3.24 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=<n_rows>)
Index Cond: (vulnerabilities.id = vulnerability_occurrences.vulnerability_id)
Filter: (vulnerabilities.state = 2)
Rows Removed by Filter: 1
Buffers: shared hit=228480Caller 1c, related pipelines, fix_available: true, flag on
Unique (cost=6064.17..6064.18 rows=1 width=16) (actual time=1041.044..1046.809 rows=<n_uuids> loops=1)
Buffers: shared hit=618068
-> Sort (cost=6064.17..6064.18 rows=1 width=16) (actual time=1041.042..1042.695 rows=<n_rows> loops=1)
Sort Key: (COALESCE(security_findings.context_unaware_uuid, security_findings.uuid))
Sort Method: quicksort Memory: 1537kB
Buffers: shared hit=618068
-> Nested Loop Anti Join (cost=2.27..6064.16 rows=1 width=16) (actual time=0.691..1031.162 rows=<n_rows> loops=1)
Buffers: shared hit=618065
-> Nested Loop (cost=1.13..6058.98 rows=1 width=32) (actual time=0.649..657.170 rows=<n_rows> loops=1)
Buffers: shared hit=182057
-> Index Scan using index_for_security_scans_scan_type on public.security_scans (cost=0.56..5.66 rows=1 width=8) (actual time=0.040..0.055 rows=2 loops=1)
Index Cond: ((security_scans.scan_type = 2) AND (security_scans.project_id = <project_id>) AND (security_scans.pipeline_id = ANY ('{<pipeline_id>,<related_pipeline_id>}'::bigint[])))
Filter: security_scans.latest
Buffers: shared hit=12
-> Append (cost=0.57..5996.78 rows=5654 width=40) (actual time=0.422..325.994 rows=<n_uuids> loops=2)
Buffers: shared hit=182045
-> (30 other security_findings partitions elided: Index Scan on (scan_id, severity), rows=0 loops=2, shared hit=8 each)
-> Index Scan using security_findings_<partition_number>_scan_id_severity_id_idx on gitlab_partitions_dynamic.security_findings_<partition_number> security_findings_31 (cost=0.56..174.68 rows=167 width=40) (actual time=0.120..323.517 rows=<n_uuids> loops=2)
Index Cond: ((security_findings_31.scan_id = security_scans.id) AND (security_findings_31.severity = ANY ('{7,6}'::integer[])))
Filter: (((jsonb_array_length((security_findings_31.finding_data -> 'remediation_byte_offsets'::text)))::bigint > 0) OR ((COALESCE((security_findings_31.finding_data ->> 'solution'::text), ''::text) <> ''::text) AND ((security_findings_31.finding_data ->> 'solution'::text) !~* '(?n)^Unfortunately,?\s*there is no solution available'::text)))
Rows Removed by Filter: <n_placeholder>
Buffers: shared hit=181805
-> Nested Loop (cost=1.15..5.17 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=<n>)
Buffers: shared hit=436008
-> Index Only Scan using index_vuln_findings_on_uuid_including_vuln_id_1 on public.vulnerability_occurrences (cost=0.57..1.93 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=<n>)
Index Cond: (vulnerability_occurrences.uuid = security_findings.uuid)
Heap Fetches: 5368
Buffers: shared hit=210348
-> Index Scan using vulnerabilities_pkey on public.vulnerabilities (cost=0.57..3.24 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=<n>)
Index Cond: (vulnerabilities.id = vulnerability_occurrences.vulnerability_id)
Filter: (vulnerabilities.state = 2)
Rows Removed by Filter: 1
Buffers: shared hit=225660Caller 1c, related pipelines, fix_available: false, flag off
Unique (cost=5965.68..5965.69 rows=1 width=16) (actual time=228.212..228.235 rows=0 loops=1)
Buffers: shared hit=67186
-> Sort (cost=5965.68..5965.69 rows=1 width=16) (actual time=228.212..228.234 rows=0 loops=1)
Sort Key: (COALESCE(security_findings.context_unaware_uuid, security_findings.uuid))
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=67186
-> Nested Loop Anti Join (cost=2.27..5965.67 rows=1 width=16) (actual time=228.184..228.205 rows=0 loops=1)
Buffers: shared hit=67183
-> Nested Loop (cost=1.13..5960.46 rows=1 width=32) (actual time=228.183..228.204 rows=0 loops=1)
Buffers: shared hit=67183
-> Index Scan using index_for_security_scans_scan_type on public.security_scans (cost=0.56..5.66 rows=1 width=8) (actual time=0.051..0.059 rows=2 loops=1)
Index Cond: ((security_scans.scan_type = 2) AND (security_scans.project_id = <project_id>) AND (security_scans.pipeline_id = ANY ('{<pipeline_id>,<related_pipeline_id>}'::bigint[])))
Filter: security_scans.latest
Buffers: shared hit=12
-> Append (cost=0.57..5954.49 rows=31 width=40) (actual time=114.055..114.064 rows=0 loops=2)
Buffers: shared hit=67171
-> Nested Loop (cost=1.15..5.20 rows=1 width=16) (never executed)
-> Index Only Scan using index_vuln_findings_on_uuid_including_vuln_id_1 on public.vulnerability_occurrences (cost=0.57..1.96 rows=1 width=24) (never executed)
Index Cond: (vulnerability_occurrences.uuid = security_findings.uuid)
Heap Fetches: 0
-> Index Scan using vulnerabilities_pkey on public.vulnerabilities (cost=0.57..3.24 rows=1 width=8) (never executed)
Index Cond: (vulnerabilities.id = vulnerability_occurrences.vulnerability_id)
Filter: (vulnerabilities.state = 2)Caller 1c, related pipelines, fix_available: false, flag on
Unique (cost=5994.03..5994.03 rows=1 width=16) (actual time=490.298..490.445 rows=<n_placeholder> loops=1)
Buffers: shared hit=130217
-> Sort (cost=5994.03..5994.03 rows=1 width=16) (actual time=490.295..490.352 rows=<n_rows> loops=1)
Sort Key: (COALESCE(security_findings.context_unaware_uuid, security_findings.uuid))
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=130217
-> Nested Loop Anti Join (cost=2.27..5994.02 rows=1 width=16) (actual time=2.775..490.018 rows=<n_rows> loops=1)
Buffers: shared hit=130214
-> Nested Loop (cost=1.13..5988.81 rows=1 width=32) (actual time=2.711..481.037 rows=<n_rows> loops=1)
Buffers: shared hit=124620
-> Index Scan using index_for_security_scans_scan_type on public.security_scans (cost=0.56..5.66 rows=1 width=8) (actual time=0.033..0.041 rows=2 loops=1)
Index Cond: ((security_scans.scan_type = 2) AND (security_scans.project_id = <project_id>) AND (security_scans.pipeline_id = ANY ('{<pipeline_id>,<related_pipeline_id>}'::bigint[])))
Filter: security_scans.latest
Buffers: shared hit=12
-> Append (cost=0.57..5982.84 rows=31 width=40) (actual time=2.569..240.423 rows=<n_placeholder> loops=2)
Buffers: shared hit=124608
-> (30 other security_findings partitions elided: Index Scan on (scan_id, severity), rows=0 loops=2, shared hit=8 each)
-> Index Scan using security_findings_<partition_number>_scan_id_severity_id_idx on gitlab_partitions_dynamic.security_findings_<partition_number> security_findings_31 (cost=0.56..175.10 rows=1 width=40) (actual time=2.221..240.007 rows=<n_placeholder> loops=2)
Index Cond: ((security_findings_31.scan_id = security_scans.id) AND (security_findings_31.severity = ANY ('{7,6}'::integer[])))
Filter: (((COALESCE((security_findings_31.finding_data ->> 'solution'::text), ''::text) = ''::text) OR ((security_findings_31.finding_data ->> 'solution'::text) ~* '(?n)^Unfortunately,?\s*there is no solution available'::text)) AND (((security_findings_31.finding_data -> 'remediation_byte_offsets'::text) IS NULL) OR ((jsonb_array_length((security_findings_31.finding_data -> 'remediation_byte_offsets'::text)))::bigint <= 0)))
Rows Removed by Filter: <n_rows>
Buffers: shared hit=124368
-> Nested Loop (cost=1.15..5.20 rows=1 width=16) (actual time=0.015..0.015 rows=0 loops=<n_rows>)
Buffers: shared hit=5594
-> Index Only Scan using index_vuln_findings_on_uuid_including_vuln_id_1 on public.vulnerability_occurrences (cost=0.57..1.96 rows=1 width=24) (actual time=0.009..0.009 rows=1 loops=<n_rows>)
Index Cond: (vulnerability_occurrences.uuid = security_findings.uuid)
Heap Fetches: 86
Buffers: shared hit=2774
-> Index Scan using vulnerabilities_pkey on public.vulnerabilities (cost=0.57..3.24 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=<n_rows>)
Index Cond: (vulnerabilities.id = vulnerability_occurrences.vulnerability_id)
Filter: (vulnerabilities.state = 2)
Rows Removed by Filter: 1
Buffers: shared hit=2820Caller 2a, fix_available: true, LIMIT 1, flag off
Aggregate (cost=8.18..8.19 rows=1 width=8) (actual time=0.055..0.056 rows=1 loops=1)
Buffers: shared hit=19
-> Limit (cost=1.15..8.17 rows=1 width=4) (actual time=0.052..0.053 rows=1 loops=1)
Buffers: shared hit=19
-> Nested Loop (cost=1.15..31585.46 rows=4496 width=4) (actual time=0.051..0.052 rows=1 loops=1)
Buffers: shared hit=19
-> Index Only Scan using index_vulnerabilities_common_finder_query_on_default_branch on public.vulnerabilities (cost=0.57..516.38 rows=6154 width=8) (actual time=0.033..0.033 rows=1 loops=1)
Index Cond: ((vulnerabilities.project_id = <project_id>) AND (vulnerabilities.state = ANY ('{1,4}'::integer[])) AND (vulnerabilities.report_type = 1) AND (vulnerabilities.present_on_default_branch = true) AND (vulnerabilities.severity = ANY ('{7,6}'::integer[])))
Heap Fetches: 0
Buffers: shared hit=14
-> Index Scan using index_vulnerability_occurrences_on_vulnerability_id on public.vulnerability_occurrences (cost=0.57..5.04 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=1)
Index Cond: (vulnerability_occurrences.vulnerability_id = vulnerabilities.id)
Filter: (((vulnerability_occurrences.solution IS NOT NULL) AND (vulnerability_occurrences.solution <> ''::text)) OR EXISTS(SubPlan 1))
Buffers: shared hit=5
SubPlan 1
-> Index Only Scan using index_vulnerability_findings_remediations_on_unique_keys on public.vulnerability_findings_remediations (cost=0.43..3.48 rows=3 width=0) (never executed)
Index Cond: (vulnerability_findings_remediations.vulnerability_occurrence_id = vulnerability_occurrences.id)
Heap Fetches: 0Caller 2a, fix_available: true, LIMIT 1, flag on
Aggregate (cost=9.19..9.20 rows=1 width=8) (actual time=0.076..0.077 rows=1 loops=1)
Buffers: shared hit=19
-> Limit (cost=1.15..9.18 rows=1 width=4) (actual time=0.074..0.074 rows=1 loops=1)
Buffers: shared hit=19
-> Nested Loop (cost=1.15..31600.85 rows=3932 width=4) (actual time=0.072..0.073 rows=1 loops=1)
Buffers: shared hit=19
-> Index Only Scan using index_vulnerabilities_common_finder_query_on_default_branch on public.vulnerabilities (cost=0.57..516.38 rows=6154 width=8) (actual time=0.042..0.043 rows=1 loops=1)
Index Cond: ((vulnerabilities.project_id = <project_id>) AND (vulnerabilities.state = ANY ('{1,4}'::integer[])) AND (vulnerabilities.report_type = 1) AND (vulnerabilities.present_on_default_branch = true) AND (vulnerabilities.severity = ANY ('{7,6}'::integer[])))
Heap Fetches: 0
Buffers: shared hit=14
-> Index Scan using index_vulnerability_occurrences_on_vulnerability_id on public.vulnerability_occurrences (cost=0.57..5.04 rows=1 width=8) (actual time=0.028..0.028 rows=1 loops=1)
Index Cond: (vulnerability_occurrences.vulnerability_id = vulnerabilities.id)
Filter: (((vulnerability_occurrences.solution IS NOT NULL) AND (vulnerability_occurrences.solution <> ''::text) AND (vulnerability_occurrences.solution !~* '(?n)^Unfortunately,?\s*there is no solution available'::text)) OR EXISTS(SubPlan 1))
Buffers: shared hit=5
SubPlan 1
-> Index Only Scan using index_vulnerability_findings_remediations_on_unique_keys on public.vulnerability_findings_remediations (cost=0.43..3.48 rows=3 width=0) (never executed)
Index Cond: (vulnerability_findings_remediations.vulnerability_occurrence_id = vulnerability_occurrences.id)
Heap Fetches: 0Caller 2b, fix_available: false, LIMIT 1, flag off
Aggregate (cost=13.63..13.64 rows=1 width=8) (actual time=4.463..4.465 rows=1 loops=1)
Buffers: shared hit=6819
-> Limit (cost=1.58..13.62 rows=1 width=4) (actual time=4.458..4.460 rows=0 loops=1)
Buffers: shared hit=6819
-> Nested Loop Anti Join (cost=1.58..23970.96 rows=1991 width=4) (actual time=4.457..4.458 rows=0 loops=1)
Buffers: shared hit=6819
-> Nested Loop (cost=1.15..22662.16 rows=2006 width=8) (actual time=4.456..4.457 rows=0 loops=1)
Buffers: shared hit=6819
-> Index Only Scan using index_vulnerabilities_common_finder_query_on_default_branch on public.vulnerabilities (cost=0.57..516.38 rows=6154 width=8) (actual time=0.038..0.578 rows=<n_candidates> loops=1)
Index Cond: ((vulnerabilities.project_id = <project_id>) AND (vulnerabilities.state = ANY ('{1,4}'::integer[])) AND (vulnerabilities.report_type = 1) AND (vulnerabilities.present_on_default_branch = true) AND (vulnerabilities.severity = ANY ('{7,6}'::integer[])))
Heap Fetches: 262
Buffers: shared hit=974
-> Index Scan using index_vulnerability_occurrences_on_vulnerability_id on public.vulnerability_occurrences (cost=0.57..3.59 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=<n_candidates>)
Index Cond: (vulnerability_occurrences.vulnerability_id = vulnerabilities.id)
Filter: ((vulnerability_occurrences.solution IS NULL) OR (vulnerability_occurrences.solution = ''::text))
Rows Removed by Filter: 1
Buffers: shared hit=5845
-> Index Only Scan using index_vulnerability_findings_remediations_on_unique_keys on public.vulnerability_findings_remediations (cost=0.43..1.09 rows=3 width=8) (never executed)
Index Cond: (vulnerability_findings_remediations.vulnerability_occurrence_id = vulnerability_occurrences.id)
Heap Fetches: 0Caller 2b, fix_available: false, LIMIT 1, flag on
Aggregate (cost=11.70..11.71 rows=1 width=8) (actual time=0.398..0.399 rows=1 loops=1)
Buffers: shared hit=310
-> Limit (cost=1.58..11.68 rows=1 width=4) (actual time=0.395..0.395 rows=1 loops=1)
Buffers: shared hit=310
-> Nested Loop Anti Join (cost=1.58..24255.12 rows=2400 width=4) (actual time=0.393..0.394 rows=1 loops=1)
Buffers: shared hit=310
-> Nested Loop (cost=1.15..22677.55 rows=2418 width=8) (actual time=0.370..0.371 rows=1 loops=1)
Buffers: shared hit=307
-> Index Only Scan using index_vulnerabilities_common_finder_query_on_default_branch on public.vulnerabilities (cost=0.57..516.38 rows=6154 width=8) (actual time=0.034..0.075 rows=49 loops=1)
Index Cond: ((vulnerabilities.project_id = <project_id>) AND (vulnerabilities.state = ANY ('{1,4}'::integer[])) AND (vulnerabilities.report_type = 1) AND (vulnerabilities.present_on_default_branch = true) AND (vulnerabilities.severity = ANY ('{7,6}'::integer[])))
Heap Fetches: 18
Buffers: shared hit=62
-> Index Scan using index_vulnerability_occurrences_on_vulnerability_id on public.vulnerability_occurrences (cost=0.57..3.59 rows=1 width=16) (actual time=0.006..0.006 rows=0.02 loops=49)
Index Cond: (vulnerability_occurrences.vulnerability_id = vulnerabilities.id)
Filter: ((vulnerability_occurrences.solution IS NULL) OR (vulnerability_occurrences.solution = ''::text) OR (vulnerability_occurrences.solution ~* '(?n)^Unfortunately,?\s*there is no solution available'::text))
Rows Removed by Filter: 1
Buffers: shared hit=245
-> Index Only Scan using index_vulnerability_findings_remediations_on_unique_keys on public.vulnerability_findings_remediations (cost=0.43..1.09 rows=3 width=8) (actual time=0.022..0.022 rows=0 loops=1)
Index Cond: (vulnerability_findings_remediations.vulnerability_occurrence_id = vulnerability_occurrences.id)
Heap Fetches: 0
Buffers: shared hit=3Caller 2 when the rule is not violated (no early exit)
The LIMIT only shortens the scan when the rule is violated. When fewer than vulnerabilities_allowed + 1 vulnerabilities match, .count walks every candidate, so I also ran 2a on the outlier project from the scope-level section (several hundred thousand default-branch vulnerabilities, none with a fix, so nothing matches). Same statement as 2a, with report_type = 4 because that is the only report type in this project's data, LIMIT 1, fix_available: true.
| Flag | Cold | Warm | Buffers (warm) |
|---|---|---|---|
| off | 3.57 min | 36.4 s | 4,707,581 hit / 491,345 read |
| on | 45.5 s (see note) | 36.0 s | 4,707,591 hit / 491,335 read |
The flag-on cold run came straight after the flag-off one, so the OS cache was already warm; compare the warm column. Plan shape and buffers match: the cost is the existing nested loop over every candidate (one vulnerability_occurrences probe per row), which the flag does not change. solution is empty on every row here, so solution <> '' fails before the regex runs. This run shows the plan does not change; the per-row regex cost is in the caller 1 plans above.
Caller 2a, no early exit, flag off
Aggregate (cost=8.19..8.20 rows=1 width=8) (actual time=36436.189..36436.196 rows=1 loops=1)
Buffers: shared hit=4707581 read=491345 written=954
-> Limit (cost=1.15..8.18 rows=1 width=4) (actual time=36436.182..36436.188 rows=0 loops=1)
Buffers: shared hit=4707581 read=491345 written=954
-> Nested Loop (cost=1.15..11365.49 rows=1616 width=4) (actual time=36436.178..36436.184 rows=0 loops=1)
Buffers: shared hit=4707581 read=491345 written=954
-> Index Only Scan using index_vulnerabilities_common_finder_query_on_default_branch on public.vulnerabilities (cost=0.57..192.36 rows=2212 width=8) (actual time=0.601..2564.217 rows=<n> loops=1)
Index Cond: ((vulnerabilities.project_id = <project_id>) AND (vulnerabilities.state = ANY ('{1,4}'::integer[])) AND (vulnerabilities.report_type = 4) AND (vulnerabilities.present_on_default_branch = true) AND (vulnerabilities.severity = ANY ('{7,6}'::integer[])))
Heap Fetches: <n>
Buffers: shared hit=120446 read=29512 written=92
-> Index Scan using index_vulnerability_occurrences_on_vulnerability_id on public.vulnerability_occurrences (cost=0.57..5.04 rows=1 width=8) (actual time=0.053..0.053 rows=0 loops=<n>)
Index Cond: (vulnerability_occurrences.vulnerability_id = vulnerabilities.id)
Filter: (((vulnerability_occurrences.solution IS NOT NULL) AND (vulnerability_occurrences.solution <> ''::text)) OR EXISTS(SubPlan 1))
Rows Removed by Filter: <n>
Buffers: shared hit=4587135 read=461833 written=862
SubPlan 1
-> Index Only Scan using index_vulnerability_findings_remediations_on_unique_keys on public.vulnerability_findings_remediations (cost=0.43..3.48 rows=3 width=0) (actual time=0.001..0.001 rows=0 loops=<n>)
Index Cond: (vulnerability_findings_remediations.vulnerability_occurrence_id = vulnerability_occurrences.id)
Heap Fetches: <n>
Buffers: shared hit=1893291 read=72
Caller 2a, no early exit, flag on
Aggregate (cost=9.20..9.21 rows=1 width=8) (actual time=35986.943..35986.945 rows=1 loops=1)
Buffers: shared hit=4707591 read=491335
-> Limit (cost=1.15..9.19 rows=1 width=4) (actual time=35986.939..35986.941 rows=0 loops=1)
Buffers: shared hit=4707591 read=491335
-> Nested Loop (cost=1.15..11371.02 rows=1413 width=4) (actual time=35986.927..35986.929 rows=0 loops=1)
Buffers: shared hit=4707591 read=491335
-> Index Only Scan using index_vulnerabilities_common_finder_query_on_default_branch on public.vulnerabilities (cost=0.57..192.36 rows=2212 width=8) (actual time=0.819..2546.356 rows=<n> loops=1)
Index Cond: ((vulnerabilities.project_id = <project_id>) AND (vulnerabilities.state = ANY ('{1,4}'::integer[])) AND (vulnerabilities.report_type = 4) AND (vulnerabilities.present_on_default_branch = true) AND (vulnerabilities.severity = ANY ('{7,6}'::integer[])))
Heap Fetches: <n>
Buffers: shared hit=120448 read=29510
-> Index Scan using index_vulnerability_occurrences_on_vulnerability_id on public.vulnerability_occurrences (cost=0.57..5.04 rows=1 width=8) (actual time=0.053..0.053 rows=0 loops=<n>)
Index Cond: (vulnerability_occurrences.vulnerability_id = vulnerabilities.id)
Filter: (((vulnerability_occurrences.solution IS NOT NULL) AND (vulnerability_occurrences.solution <> ''::text) AND (vulnerability_occurrences.solution !~* '(?n)^Unfortunately,?\s*there is no solution available'::text)) OR EXISTS(SubPlan 1))
Rows Removed by Filter: <n>
Buffers: shared hit=4587143 read=461825
SubPlan 1
-> Index Only Scan using index_vulnerability_findings_remediations_on_unique_keys on public.vulnerability_findings_remediations (cost=0.43..3.48 rows=3 width=0) (actual time=0.001..0.001 rows=0 loops=<n>)
Index Cond: (vulnerability_findings_remediations.vulnerability_occurrence_id = vulnerability_occurrences.id)
Heap Fetches: <n>
Buffers: shared hit=1893291 read=72
References
- Resolves #612864
- Supersedes the approach in !250658 (closed) (regex only, no string allowlist)
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist.
Edited by Alan (Maciej) Paruszewski