N+1 query in loading vulnerability feedback
From !56448 (comment 529702753), I found that using the GET /projects/:id/vulnerability_findings?pipeline_id=N?per_page=100
API endpoint yields an N+1 query:
I, [2021-03-16T04:08:01.289456 #5759] INFO -- : Feedback total (100): 170.9ms
I, [2021-03-16T04:08:01.289528 #5759] INFO -- : Project total (4): 4.3ms
I, [2021-03-16T04:08:01.289547 #5759] INFO -- : User total (2): 2.4ms
I, [2021-03-16T04:08:01.289562 #5759] INFO -- : Issue total (2): 2.2ms
I, [2021-03-16T04:08:01.289576 #5759] INFO -- : Route total (3): 2.0ms
I, [2021-03-16T04:08:01.289590 #5759] INFO -- : Namespace total (2): 1.2ms
I, [2021-03-16T04:08:01.289603 #5759] INFO -- : Pipeline total (1): 1.2ms
I, [2021-03-16T04:08:01.289616 #5759] INFO -- : Group total (1): 1.2ms
I, [2021-03-16T04:08:01.289629 #5759] INFO -- : JiraService total (1): 1.1ms
I, [2021-03-16T04:08:01.289642 #5759] INFO -- : Upload total (1): 1.0ms
I, [2021-03-16T04:08:01.289655 #5759] INFO -- : Plan total (1): 1.0ms
I, [2021-03-16T04:08:01.289667 #5759] INFO -- : License total (1): 1.0ms
I, [2021-03-16T04:08:01.289681 #5759] INFO -- : SamlProvider total (1): 0.9ms
I, [2021-03-16T04:08:01.289695 #5759] INFO -- : ProjectFeature total (1): 0.8ms
I, [2021-03-16T04:08:01.289709 #5759] INFO -- : IpRestriction total (1): 0.8ms
Seems like the N+1 query comes from https://gitlab.com/gitlab-org/gitlab/blob/1f4de309dfb1b5df169744b9d5e961a4b7a3e091/ee/app/models/vulnerabilities/finding.rb#L207:
D, [2021-03-16T04:11:32.832524 #5759] DEBUG -- : Vulnerabilities::Feedback Load (1.7ms) /*application:console,correlation_id:ce102f8d-306b-4a6e-9cb5-078947b01ef4,endpoint_id:/api/:version/projects/:id/vulnerability_findings*/ SELECT "vulnerability_feedback".* FROM "vulnerability_feedback" WHERE "vulnerability_feedback"."issue_id" IN (SELECT "issues"."id" FROM "issues" INNER JOIN "vulnerability_issue_links" ON "issues"."id" = "vulnerability_issue_links"."issue_id" WHERE "vulnerability_issue_links"."vulnerability_id" = 6643278) LIMIT 1
D, [2021-03-16T04:11:32.835410 #5759] DEBUG -- : ↳ ee/app/models/vulnerabilities/finding.rb:207:in `issue_feedback'
D, [2021-03-16T04:11:32.835502 #5759] DEBUG -- : ↳ lib/gitlab/json.rb:110:in `dump'
D, [2021-03-16T04:11:32.835537 #5759] DEBUG -- : ↳ lib/gitlab/json.rb:110:in `adapter_dump'
D, [2021-03-16T04:11:32.835570 #5759] DEBUG -- : ↳ lib/gitlab/json.rb:42:in `dump'
D, [2021-03-16T04:11:32.835602 #5759] DEBUG -- : ↳ lib/gitlab/json.rb:193:in `call'
D, [2021-03-16T04:11:32.835660 #5759] DEBUG -- : ↳ ee/lib/gitlab/ip_address_state.rb:10:in `with'
D, [2021-03-16T04:11:32.835702 #5759] DEBUG -- : ↳ lib/api/api_guard.rb:213:in `call'
D, [2021-03-16T04:11:32.835735 #5759] DEBUG -- : ↳ ee/lib/omni_auth/strategies/group_saml.rb:41:in `other_phase'
D, [2021-03-16T04:11:32.835774 #5759] DEBUG -- : ↳ lib/gitlab/jira/middleware.rb:19:in `call'
D, [2021-03-16T04:11:32.835821 #5759] DEBUG -- : ↳ lib/gitlab/with_request_store.rb:8:in `with_request_store'
D, [2021-03-16T04:11:32.835853 #5759] DEBUG -- : ↳ (irb):14:in `irb_binding'
Attached is the SQL profile:
logger = Logger.new('/tmp/vuln.txt')
user = User.find_by(username: 'stanhu')
RequestStore.begin!
Gitlab::Profiler.profile('/api/v4/projects/278964/vulnerability_findings?pipeline_id=269395285', user: user, logger: logger)
Update
As Threat Insights is in the process of deprecating Vulnerability::Feedback, it is possible this may not need optimisation in the near future. Evaluation pending the rollout of the feedback deprecation.
Edited by Gregory Havenga