Add (user_id, created_at DESC) index for Duo Workflow session lists

What does this MR do and why?

getUserWorkflows powers the Duo Agent Platform session list in the IDE and web UI, and both clients poll it. The GraphQL resolver chain (Resolvers::Ai::DuoWorkflows::WorkflowsResolver → Ai::DuoWorkflows::WorkflowsFinder) runs a user-scoped query filtered on user_id, excluding chat agent definitions, and sorted by created_at DESC (the default sort order).

duo_workflows_workflows has no index that serves this sort. The existing user-scoped indexes cover (user_id) and (user_id, updated_at DESC) WHERE workflow_definition <> 'chat', neither of which matches created_at DESC. Postgres has to fetch every non-chat workflow the user owns and sort in memory, on every page load and every poll.

Kibana data from 2026-08-28 confirms the cost sits in this shared query: p95 json.db_duration_s for getUserWorkflows holds at roughly 2s regardless of the dw_read_blobs_graphql flag state (2.211s disabled, 1.967s enabled).

This MR adds a post-deployment migration that creates a partial index on (user_id, created_at DESC) WHERE workflow_definition <> 'chat', added concurrently. It mirrors the existing updated_at partial index on the same table and follows the precedent set by index_duo_workflows_workflows_on_namespace_id_created_at. Both query shapes the finder uses, a type: equality filter and the "exclude chat agents" NOT IN list (which contains 'chat'), satisfy the index predicate, so the planner can use it for the common list queries.

db/structure.sql was regenerated by running the migration.

Migration output

Up

main: == 20260828130248 AddIndexToDuoWorkflowsWorkflowsOnUserIdCreatedAt: migrating =
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0236s
main: -- index_exists?(:duo_workflows_workflows, [:user_id, :created_at], {:order=>{:created_at=>:DESC}, :where=>"workflow_definition != 'chat'", :name=>"index_duo_workflows_workflows_on_user_id_created_at", :algorithm=>:concurrently})
main:    -> 0.0065s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- add_index(:duo_workflows_workflows, [:user_id, :created_at], {:order=>{:created_at=>:DESC}, :where=>"workflow_definition != 'chat'", :name=>"index_duo_workflows_workflows_on_user_id_created_at", :algorithm=>:concurrently})
main:    -> 0.0041s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20260828130248 AddIndexToDuoWorkflowsWorkflowsOnUserIdCreatedAt: migrated (0.0391s)

Down

main: == 20260828130248 AddIndexToDuoWorkflowsWorkflowsOnUserIdCreatedAt: reverting =
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0236s
main: -- index_name_exists?(:duo_workflows_workflows, "index_duo_workflows_workflows_on_user_id_created_at")
main:    -> 0.0021s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:duo_workflows_workflows, {:algorithm=>:concurrently, :name=>"index_duo_workflows_workflows_on_user_id_created_at"})
main:    -> 0.0020s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20260828130248 AddIndexToDuoWorkflowsWorkflowsOnUserIdCreatedAt: reverted (0.0435s)

Query plans

Plans were run against a Database Lab clone of gitlab-production-main (postgres.ai) on 2026-08-31, using user_id 9426861, a heavy user with 819 non-chat workflows. The query is the exact SQL WorkflowsFinder#results.to_sql produces:

SELECT "duo_workflows_workflows".*
FROM "duo_workflows_workflows"
WHERE "duo_workflows_workflows"."user_id" = 9426861
  AND "duo_workflows_workflows"."workflow_definition" NOT IN
    ('chat', 'orbit_agent/v1', 'duo_planner/v1', 'security_analyst_agent/v1',
     'analytics_agent/v1', 'ci_expert_agent/v1', 'duo_permissions_assistant/v1',
     'support_assistant/v1', 'agentic_chat/v1', 'onboarding_guide/v1', 'flow_creator/v1')
ORDER BY "duo_workflows_workflows"."created_at" DESC
LIMIT 20

Without the new index, the planner uses index_duo_workflows_on_user_id_and_updated_at, reads all 819 non-chat rows for this user, and sorts to find the top 20. Execution takes 32.9 ms and touches 839 shared buffers (about 6.5 MiB). With index_duo_workflows_workflows_on_user_id_created_at, the planner walks the index in created_at DESC order and stops after 22 rows, with no sort step. Execution drops to 0.33 ms with 28 buffers (about 224 KiB), roughly 100x faster and 30x fewer buffers. The cost scales with the user's total non-chat row count before, and with the page size after.

Before (EXPLAIN ANALYZE)

 Limit  (cost=791.57..791.62 rows=20 width=1221) (actual time=32.879..32.883 rows=20 loops=1)
   Buffers: shared hit=592 read=247
   I/O Timings: read=27.633 write=0.000
   ->  Sort  (cost=791.57..792.84 rows=508 width=1221) (actual time=32.878..32.880 rows=20 loops=1)
         Sort Key: duo_workflows_workflows.created_at DESC
         Sort Method: top-N heapsort  Memory: 38kB
         Buffers: shared hit=592 read=247
         ->  Index Scan using index_duo_workflows_on_user_id_and_updated_at on public.duo_workflows_workflows  (cost=0.58..778.06 rows=508 width=1221) (actual time=4.109..32.372 rows=802 loops=1)
               Index Cond: (duo_workflows_workflows.user_id = 9426861)
               Filter: (duo_workflows_workflows.workflow_definition <> ALL ('{chat,orbit_agent/v1,duo_planner/v1,security_analyst_agent/v1,analytics_agent/v1,ci_expert_agent/v1,duo_permissions_assistant/v1,support_assistant/v1,agentic_chat/v1,onboarding_guide/v1,flow_creator/v1}'::text[]))
               Rows Removed by Filter: 17
               Buffers: shared hit=589 read=247

Time: 56.876 ms (planning: 23.928 ms, execution: 32.948 ms, I/O read: 27.633 ms)
Shared buffers: hits 592 (~4.60 MiB), reads 247 (~1.90 MiB)

After (EXPLAIN ANALYZE with the new index)

 Limit  (cost=0.46..30.36 rows=20 width=1221) (actual time=0.134..0.246 rows=20 loops=1)
   Buffers: shared hit=25 read=3
   ->  Index Scan using index_duo_workflows_workflows_on_user_id_created_at on public.duo_workflows_workflows  (cost=0.46..759.93 rows=508 width=1221) (actual time=0.133..0.242 rows=20 loops=1)
         Index Cond: (duo_workflows_workflows.user_id = 9426861)
         Filter: (duo_workflows_workflows.workflow_definition <> ALL ('{chat,orbit_agent/v1,duo_planner/v1,security_analyst_agent/v1,analytics_agent/v1,ci_expert_agent/v1,duo_permissions_assistant/v1,support_assistant/v1,agentic_chat/v1,onboarding_guide/v1,flow_creator/v1}'::text[]))
         Rows Removed by Filter: 2
         Buffers: shared hit=25 read=3

Time: 4.243 ms (planning: 3.913 ms, execution: 0.330 ms)
Shared buffers: hits 25 (~200 KiB), reads 3 (~24 KiB)

Type-equality variant (workflow_definition = 'software_development')

 Limit  (cost=0.43..758.60 rows=16 width=1221) (actual time=3.913..4.513 rows=14 loops=1)
   Buffers: shared hit=824
   ->  Index Scan using index_duo_workflows_workflows_on_user_id_created_at on public.duo_workflows_workflows  (cost=0.43..758.60 rows=16 width=1221) (actual time=3.911..4.509 rows=14 loops=1)
         Index Cond: (duo_workflows_workflows.user_id = 9426861)
         Filter: (duo_workflows_workflows.workflow_definition = 'software_development'::text)
         Rows Removed by Filter: 805

Time: 9.562 ms (planning: 3.662 ms, execution: 5.900 ms)
Shared buffers: hits 821 (~6.40 MiB), reads 3 (~24 KiB)

The clone was reset after these runs. A plain (non-concurrent) CREATE INDEX on the clone took 5.0 s, consistent with the 11.8 s concurrent build measured by the database-testing pipeline.

MR acceptance checklist

This checklist encourages us to confirm any changes were analyzed for conformity with our guidelines, security, and readability. See the acceptance checklist for details on all points.

Closes #624973 (closed) Related to #624938 (closed)