Add (user_id, created_at DESC) index for the getUserWorkflows session list query

Problem

The getUserWorkflows GraphQL operation (Duo Agent Platform session list, polled by the IDE and web UI) resolves through Resolvers::Ai::DuoWorkflows::WorkflowsResolver → Ai::DuoWorkflows::WorkflowsFinder.

For the user-scoped path, the query shape is:

SELECT ... FROM duo_workflows_workflows
WHERE user_id = ?
  AND workflow_definition NOT IN (<chat agent definitions>)
  [AND environment = ?]
ORDER BY created_at DESC
LIMIT n

The default sort is created_desc.

The only user-scoped indexes on this table are:

  • index_duo_workflows_workflows_on_user_id on (user_id)
  • index_duo_workflows_on_user_id_and_updated_at on (user_id, updated_at DESC) WHERE workflow_definition <> 'chat'

Neither index serves the created_at DESC sort. Postgres fetches every non-chat row the user owns and sorts in memory, on every page load and every poll. Heavy users pay for their whole session history on each request.

Evidence

Kibana data from 2026-08-28 shows p95 json.db_duration_s for getUserWorkflows:

  • 2.211s with dw_read_blobs_graphql disabled
  • 1.967s with dw_read_blobs_graphql enabled

The cost is close to 2s in both flag states, so it comes from the shared base query, not the checkpoint read path.

The former per-row stalled N+1 was already fixed in commit b1956457 (batch loader via Workflow.ids_with_checkpoints). These measurements post-date that fix, so the base query is now the main remaining cost.

Proposal

Add a partial index on duo_workflows_workflows:

(user_id, created_at DESC) WHERE workflow_definition <> 'chat'

This mirrors the existing updated_at partial index.

Both current filter forms imply the index predicate:

  • a type: equality filter (for example workflow_definition = 'software_development')
  • the "exclude chat agents" NOT IN list, which contains 'chat'

So the planner can use the partial index for the common list queries.

Acceptance criteria

  • Migration adds the index (use the async index creation process per GitLab guidelines, if the table size requires it)
  • db/structure.sql is regenerated via scripts/regenerate-schema
  • Before/after EXPLAIN (ANALYZE, BUFFERS) output, run on postgres.ai with a heavy user's id, posted on the MR
  • Post-deploy: re-measure getUserWorkflows p95 db_duration in Kibana

Related to #624938 (closed) (filter pushdown for the blob read path), found in the same Kibana p95 analysis. The remaining stalled legacy-table scan (Workflow.checkpoint_workflow_ids, bounded by the page's oldest workflow across daily partitions) ages out once duo_workflow_write_incremental_only rolls out, so it is intentionally out of scope here.