Preload work item and merge request rows for the flows list

Eduardo Bonetrequested to merge
629328-graphql-n-1-triage-getuseragentflows into master

What does this MR do and why?

The getUserAgentFlows GraphQL operation (the Sessions list in the GitLab Duo side panel, page size 20) selects workItem and mergeRequest on every flow. The duoWorkflowWorkflows resolver never preloaded them, so every flow on a page cost 6 extra queries. This MR preloads those rows only when the fields are selected, and preloads the project rows the read_duo_workflow policy reads in before_connection_authorization. It also adds a permanent N+1 request spec that runs the real frontend query file.

Per-row queries found and design notes

  • Per-row queries (6 per flow, in one project): issues/merge_requests by id (the belongs_to), projects by id (issue.project for the read_work_item / read_merge_request policy), users by id (issue.author via hidden?), namespaces by id (issue.namespace for the work item type lookup), projects by project_namespace_id (ProjectNamespace#project from the work item type provider), routes for the namespace (webUrl through project.namespace.full_path).

  • Per distinct project (about 15 queries each, before): read_duo_workflow evaluates duo_workflow on each project's ProjectPolicy with nothing preloaded: organizations, group and project namespaces, project_settings, the cascading duo_features_enabled namespace_settings lookup, saml_providers, project_authorizations, project_features, and three routes lookups for project { fullPath webUrl }.

  • Design notes: DeclarativePolicy caches a ProjectPolicy on the first project instance it sees, which is the workflow's project. The work item and merge request policies reuse that cached policy, so project_feature is preloaded on the workflow's project (under the workItem and mergeRequest lookahead keys), not on the issue's project. Preloads for workItem/mergeRequest/project are conditional through LooksAhead so other clients that don't select those fields (the IDE, duo-cli) don't pay for them. Preloaders::ProjectPolicyPreloader is the standard policy preloader and runs for every caller because type authorization runs for every caller: it adds 4 fixed queries per request and saves 4 per distinct project on the page (minimal nodes { id title } query, 2 vs 6 flows: 37/37 → 41/41 in one project, 37/53 → 41/43 across three projects).

  • Remaining cost, not fixed here: about 3 queries per distinct project remain. One is the cascading duo_features_enabled lookup (CascadingProjectSettingAttribute#locked_ancestor, one per project). The others come from WorkItemType shared code (WorkItems::TypesFramework::Provider instantiated per namespace instance, Project#work_item_tasks_on_boards_feature_flag_enabled? loading the group). Namespace-level flows (project nil) get no policy preloading; the list is dominated by project-level flows.

  • Measurements (cached queries counted, page of 2 vs 6 flows):

    Scenario Before (2 / 6) After (2 / 6)
    All flows in one project 63 / 87 67 / 67
    Flows across three projects 63 / 117 67 / 79

References

Screenshots or screen recordings

No UI change.

How to set up and validate locally

Setup: a GDK with Duo Agent Platform enabled and a user who has run a few flows.

  1. As that user, in a project, create two issues and two merge requests, and run one flow from each (for example from the issue page's "Duo Agent" action or via the flows page), so each flow links a work item or a merge request. Alternatively create them in a rails console: 
    user = User.find_by(username: 'root'); project = Project.find_by_full_path('gitlab-org/gitlab-test')
2.times { Ai::DuoWorkflows::Workflow.create!(user: user, project: project, goal: 'x', workflow_definition: 'software_development', work_item: project.issues.first) }
2.times { Ai::DuoWorkflows::Workflow.create!(user: user, project: project, goal: 'x', workflow_definition: 'software_development', merge_request: project.merge_requests.first) }
    (Note: a workflow links either a work item or a merge request, not both.)
  2. Open any page on GDK as that user, for example http://gdk.test:3000/gitlab-org/gitlab-test, and enable the performance bar (type p then b). The duo_panel_session_inbox feature flag must be disabled so the panel renders the Sessions list.
  3. Open the GitLab Duo side panel from the top bar and switch to the Sessions list. In the performance bar's request dropdown, select the getUserAgentFlows request and read its SQL count. Expect on master: the count grows by about 6 for every extra flow with a linked work item or merge request.
  4. Check out this branch, reload, and repeat step 3. Expect: the count no longer grows with the number of flows; each row still shows its work item or merge request link.
  5. Run the spec: bin/rspec ee/spec/requests/api/graphql/ai/duo_workflows/workflows_spec.rb -e 'the user flows list query'. Expect: passes on this branch and fails on master with "Expected a maximum of N queries".
Raw SQL and Database Lab plans for the new preload queries

Every query below is an ActiveRecord preload of an existing association: a primary-key or unique-index IN lookup with one id per row on the page (page size 20). Plans from postgres.ai (gitlab-production-main), cold cache, using ids from the gitlab-org group. Preloaders::ProjectPolicyPreloader is shared code already used by projects, starredProjects, and contributedProjects; its queries are not repeated here.

work_item (LooksAhead, when workItem is selected)

SELECT "issues".* FROM "issues" WHERE "issues"."id" IN (202219370, 202219366, 202219361, 202219358, 202219355, 202219353)
 Index Scan using issues_pkey on public.issues  (cost=0.57..20.33 rows=6 width=1078) (actual time=5.830..22.692 rows=6 loops=1)
   Index Cond: (issues.id = ANY ('{202219370,202219366,202219361,202219358,202219355,202219353}'::integer[]))
   Buffers: shared hit=2 read=8
Time: 27.905 ms

merge_request (LooksAhead, when mergeRequest is selected)

SELECT "merge_requests".* FROM "merge_requests" WHERE "merge_requests"."id" IN (529197577, 529184332, 529175725, 529171299, 529171122, 529170836)
 Index Scan using merge_requests_pkey on public.merge_requests  (cost=0.57..17.35 rows=6 width=1026) (actual time=14.249..38.264 rows=6 loops=1)
   Index Cond: (merge_requests.id = ANY ('{529197577,529184332,529175725,529171299,529171122,529170836}'::bigint[]))
   Buffers: shared hit=16 read=14 dirtied=1
Time: 44.375 ms

author of the work item or merge request

SELECT "users".* FROM "users" WHERE "users"."id" IN (421631, 674121, 1607849, 5918904, 11214192)
 Index Scan using users_pkey on public.users  (cost=0.44..15.89 rows=5 width=1683) (actual time=4.506..36.740 rows=5 loops=1)
   Index Cond: (users.id = ANY ('{421631,674121,1607849,5918904,11214192}'::integer[]))
   Buffers: shared hit=6 read=18 dirtied=2
Time: 41.223 ms

namespace of the work item, and project.namespace for the URL helpers

SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" IN (10127741, 74288765, 98461187, 6382519, 9970)
 Index Scan using namespaces_pkey on public.namespaces  (cost=0.57..14.59 rows=5 width=366) (actual time=3.420..24.993 rows=5 loops=1)
   Index Cond: (namespaces.id = ANY ('{10127741,74288765,98461187,6382519,9970}'::integer[]))
   Buffers: shared hit=6 read=19
Time: 27.203 ms

project_namespace (before_connection_authorization, and nested under the issue's or MR's project)

The generated query is SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Project' AND "namespaces"."id" IN (...). The plan below resolves the ids through projects so it runs against real project namespace ids; the inner loop is the query of interest.

SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Project' AND "namespaces"."id" IN (SELECT project_namespace_id FROM projects WHERE id IN (278964, 40916776, 7937396, 13831684, 2890326, 62674588))
 Nested Loop  (cost=1.13..41.34 rows=3 width=366) (actual time=15.860..84.611 rows=6 loops=1)
   ->  Index Scan using projects_pkey on public.projects  (cost=0.56..19.82 rows=6 width=8) (actual time=11.776..56.290 rows=6 loops=1)
         Index Cond: (projects.id = ANY ('{278964,40916776,7937396,13831684,2890326,62674588}'::integer[]))
   ->  Index Scan using namespaces_pkey on public.namespaces  (cost=0.57..3.59 rows=1 width=366) (actual time=4.714..4.714 rows=1 loops=6)
         Index Cond: (namespaces.id = projects.project_namespace_id)
         Filter: ((namespaces.type)::text = 'Project'::text)
         Buffers: shared hit=20 read=13
Time: 91.874 ms

project_feature of the workflow's project (LooksAhead, when workItem or mergeRequest is selected)

SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" IN (278964, 40916776, 7937396, 13831684, 2890326, 62674588)
 Index Scan using index_project_features_on_project_id on public.project_features  (cost=0.56..16.38 rows=6 width=112) (actual time=11.261..60.978 rows=6 loops=1)
   Index Cond: (project_features.project_id = ANY ('{278964,40916776,7937396,13831684,2890326,62674588}'::integer[]))
   Buffers: shared hit=9 read=21
Time: 61.882 ms

project_setting (before_connection_authorization)

SELECT "project_settings".* FROM "project_settings" WHERE "project_settings"."project_id" IN (278964, 40916776, 7937396, 13831684, 2890326, 62674588)
 Index Scan using project_settings_pkey on public.project_settings  (cost=0.56..21.35 rows=6 width=472) (actual time=15.298..66.549 rows=6 loops=1)
   Index Cond: (project_settings.project_id = ANY ('{278964,40916776,7937396,13831684,2890326,62674588}'::integer[]))
   Buffers: shared hit=9 read=21
Time: 67.867 ms

route of the project and of its namespace (LooksAhead, when project is selected)

The namespace variant is the same shape with source_type = 'Namespace'.

SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" IN (278964, 40916776, 7937396, 13831684, 2890326, 62674588)
 Index Scan using index_routes_on_source_type_and_source_id on public.routes  (cost=0.57..17.99 rows=4 width=96) (actual time=19.826..91.285 rows=6 loops=1)
   Index Cond: (((routes.source_type)::text = 'Project'::text) AND (routes.source_id = ANY ('{278964,40916776,7937396,13831684,2890326,62674588}'::integer[])))
   Buffers: shared hit=7 read=23
Time: 93.142 ms

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 Eduardo Bonet

Merge request reports