Skip to content

Allow filtering Duo workflows by status group and goal/title

Wanderson Policarporequested to merge
571931-allow-filtering-duo-workflows into master

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

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 by Wanderson Policarpo

Merge request reports