Allow filtering Duo workflows by status group and goal/title (!206756) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

Allow filtering Duo workflows by their status_group or through a fuzzy search against their goal or definition (aka title).

This is needed so users are able to choose how they want to see in the sessions page.

References

Related to #571931 (closed)

GraphQL details

GraphQL Query

query getAgentFlows(
  $projectPath: ID!,
  $sort: DuoWorkflowsWorkflowSort,
  $statusGroup: DuoWorkflowStatusGroup,
  $type: String
) {
  project(fullPath: $projectPath) {
    id
    duoWorkflowWorkflows(
      projectPath: $projectPath
      sort: $sort
      statusGroup: $statusGroup
      type: $type
    ) {
      pageInfo {
        startCursor
        endCursor
        hasNextPage
        hasPreviousPage
      }
      edges {
        node {
          id
          goal
          workflowDefinition
          status
          statusName
          statusGroup
        }
      }
    }
  }
}

Variables

{
  "projectPath": "gitlab-duo/test",
  "sort": "STATUS_DESC",
  "statusGroup": null,
  "type": "code_review/experimental"
}

SQL query plans

When fuzzy searching by `title` or `goal`

Raw SQL

SELECT 
  "duo_workflows_workflows".*
FROM "duo_workflows_workflows" 
WHERE 
  "duo_workflows_workflows"."project_id" = 278964 
  AND "duo_workflows_workflows"."workflow_definition" != 'chat' 
  AND "duo_workflows_workflows"."environment" = 2 
  AND (
      "duo_workflows_workflows"."workflow_definition" ILIKE '%code%' 
      AND "duo_workflows_workflows"."workflow_definition" ILIKE '%review%' OR "duo_workflows_workflows"."goal" ILIKE '%code%' 
      AND "duo_workflows_workflows"."goal" ILIKE '%review%'
  )
ORDER BY 
  "duo_workflows_workflows"."created_at" DESC

SQL plan without new index

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/44288/commands/135743

 Sort  (cost=9441.74..9441.76 rows=8 width=338) (actual time=2466.257..2466.261 rows=19 loops=1)
   Sort Key: duo_workflows_workflows.created_at DESC
   Sort Method: quicksort  Memory: 28kB
   Buffers: shared hit=7 read=5765 dirtied=648
   WAL: records=699 fpi=648 bytes=5146939
   I/O Timings: read=2398.086 write=0.000
   ->  Index Scan using index_duo_workflows_workflows_on_project_id on public.duo_workflows_workflows  (cost=0.29..9441.62 rows=8 width=338) (actual time=1063.636..2466.153 rows=19 loops=1)
         Index Cond: (duo_workflows_workflows.project_id = 278964)
         Filter: ((duo_workflows_workflows.workflow_definition <> 'chat'::text) AND (duo_workflows_workflows.environment = 2) AND (((duo_workflows_workflows.workflow_definition ~~* '%code%'::text) AND (duo_workflows_workflows.workflow_definition ~~* '%review%'::text)) OR ((duo_workflows_workflows.goal ~~* '%code%'::text) AND (duo_workflows_workflows.goal ~~* '%review%'::text))))
         Rows Removed by Filter: 21350
         Buffers: shared hit=4 read=5765 dirtied=648
         WAL: records=699 fpi=648 bytes=5146939
         I/O Timings: read=2398.086 write=0.000
Settings: effective_cache_size = '472585MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', work_mem = '100MB'

SQL plan with new index

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/44420/commands/136146

Index Scan using index_duo_workflows_workflows_project_environment_created_at on public.duo_workflows_workflows  (cost=0.42..7753.65 rows=10 width=338) (actual time=0.294..20.398 rows=269 loops=1)
   Index Cond: ((duo_workflows_workflows.project_id = 278964) AND (duo_workflows_workflows.environment = 2))
   Filter: ((duo_workflows_workflows.workflow_definition <> 'chat'::text) AND (((duo_workflows_workflows.workflow_definition ~~* '%code%'::text) AND (duo_workflows_workflows.workflow_definition ~~* '%review%'::text)) OR ((duo_workflows_workflows.goal ~~* '%code%'::text) AND (duo_workflows_workflows.goal ~~* '%review%'::text))))
   Rows Removed by Filter: 8438
   Buffers: shared hit=8599 read=46 dirtied=489
   WAL: records=526 fpi=489 bytes=3877346
   I/O Timings: read=1.322 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off', work_mem = '100MB'

When filtering by `status_group`

Raw SQL

SELECT 
  "duo_workflows_workflows".*
FROM "duo_workflows_workflows"
WHERE 
  "duo_workflows_workflows"."project_id" = 278964 
  AND "duo_workflows_workflows"."workflow_definition" != 'chat' 
  AND "duo_workflows_workflows"."environment" = 2 
  AND "duo_workflows_workflows"."status" = 5
ORDER BY 
  "duo_workflows_workflows"."created_at" DESC

SQL plan

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/44288/commands/135745

 Sort  (cost=1257.65..1257.74 rows=39 width=338) (actual time=21.865..21.869 rows=0 loops=1)
   Sort Key: duo_workflows_workflows.created_at DESC
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=470 read=29
   I/O Timings: read=17.774 write=0.000
   ->  Bitmap Heap Scan on public.duo_workflows_workflows  (cost=303.71..1256.62 rows=39 width=338) (actual time=21.847..21.851 rows=0 loops=1)
         Filter: ((duo_workflows_workflows.workflow_definition <> 'chat'::text) AND (duo_workflows_workflows.environment = 2))
         Rows Removed by Filter: 484
         Buffers: shared hit=467 read=29
         I/O Timings: read=17.774 write=0.000
         ->  BitmapAnd  (cost=303.71..303.71 rows=450 width=0) (actual time=20.151..20.154 rows=0 loops=1)
               Buffers: shared hit=34 read=29
               I/O Timings: read=17.774 write=0.000
               ->  Bitmap Index Scan using idx_workflows_status_updated_at_id  (cost=0.00..90.71 rows=3039 width=0) (actual time=18.479..18.479 rows=3045 loops=1)
                     Index Cond: (duo_workflows_workflows.status = 5)
                     Buffers: shared hit=3 read=29
                     I/O Timings: read=17.774 write=0.000
               ->  Bitmap Index Scan using index_duo_workflows_workflows_on_project_id  (cost=0.00..212.72 rows=21324 width=0) (actual time=1.498..1.498 rows=21369 loops=1)
                     Index Cond: (duo_workflows_workflows.project_id = 278964)
                     Buffers: shared hit=31
                     I/O Timings: read=0.000 write=0.000
Settings: jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', work_mem = '100MB', effective_cache_size = '472585MB'

SQL plan with new index

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/44420/commands/136148

 Sort  (cost=29.37..29.38 rows=1 width=338) (actual time=27.859..27.861 rows=0 loops=1)
   Sort Key: duo_workflows_workflows.created_at DESC
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=286 read=12 dirtied=9
   WAL: records=15 fpi=9 bytes=43767
   I/O Timings: read=26.094 write=0.000
   ->  Index Scan using idx_workflows_status_updated_at_id on public.duo_workflows_workflows  (cost=0.42..29.36 rows=1 width=338) (actual time=27.846..27.846 rows=0 loops=1)
         Index Cond: (duo_workflows_workflows.status = 1)
         Filter: ((duo_workflows_workflows.workflow_definition <> 'chat'::text) AND (duo_workflows_workflows.project_id = 278964) AND (duo_workflows_workflows.environment = 2))
         Rows Removed by Filter: 26
         Buffers: shared hit=283 read=12 dirtied=9
         WAL: records=15 fpi=9 bytes=43767
         I/O Timings: read=26.094 write=0.000
Settings: effective_cache_size = '472585MB', jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4'

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 Oct 13, 2025 by Wanderson Policarpo

Allow filtering Duo workflows by status group and goal/title