Improve the index for latest_channel_message on p_duo_workflows_checkpoint_blobs

Problem

Merge request !251388 (merged) changed Ai::DuoWorkflows::Workflow#latest_channel_message in ee/app/models/ai/duo_workflows/workflow.rb.

During database review, @l.rosa approved the change but flagged the index. Their comment:

The index will cover only workflow_id, everything else is applied as a filter, so the cost grows with the workflow's checkpoint count. Fine for now with LIMIT 1, but we should consider a better index in a follow-up.

The query needs an index that limits the scan and the sort, not one that scales with the whole session.

Evidence

The query reads the newest checkpoint blob for one channel of one session.

SELECT "p_duo_workflows_checkpoint_blobs".*
FROM "p_duo_workflows_checkpoint_blobs"
WHERE "workflow_id" = 6405842
  AND "workflow_created_at" >= '2026-08-17 09:04:38+00'
  AND "workflow_created_at" < '2026-08-17 09:04:39+00'
  AND "project_id" = 39903947
  AND "thread_ts" IN (<172 values, one per checkpoint in the session>)
  AND "channel" = 'ui_chat_log'
ORDER BY "id" DESC
LIMIT 1

Production plan from Database Lab (28M rows in the table, about 1M rows per daily partition): https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/55275/commands/158801

Plan numbers:

  • The planner uses p_duo_workflows_checkpoint_blobs_20260817_workflow_id_idx, which covers workflow_id alone.
  • Index Cond is only workflow_id = 6405842.
  • workflow_created_at, project_id, channel, and the thread_ts list are all applied as a Filter.
  • The index scan returns 122 rows and removes 434 by filter, so it visits 556 rows to return 1.
  • Those rows go through a top-N heapsort on id DESC before LIMIT 1.
  • Buffers: shared hit=6, read=202. I/O read time: 113.223 ms. Total execution time: 116.362 ms.
  • The session has 172 checkpoints, which is a normal length, not an outlier.

Rows visited scale with the session's total blob count (checkpoints times channels). A long session costs proportionally more. This read path sits behind the dw_read_blobs_graphql feature flag, not yet enabled on GitLab.com, so there's no user impact today. It needs a fix before that flag rolls out at scale.

Existing indexes

On p_duo_workflows_checkpoint_blobs:

  • idx_duo_wf_checkpoint_blobs_dedup, unique, on (project_id, workflow_id, thread_ts, channel, version, step_action, workflow_created_at), with NULLS NOT DISTINCT.
  • index_duo_wf_checkpoint_blobs_on_workflow_id on (workflow_id).
  • index_duo_wf_checkpoint_blobs_on_namespace_id on (namespace_id).

Neither of the first two can serve ORDER BY id DESC, so the current query always sorts.

Direction

This is a candidate to measure, not a decided design. One option: an index leading with workflow_id and channel and ending in id, for example (workflow_id, channel, id).

That shape would restrict the scan to one channel's rows and let the planner walk id backward, stopping at the first row whose thread_ts is in the list, instead of sorting the whole result set.

Notes for whoever picks this up:

  • thread_ts is matched with an IN list, so adding it to the index doesn't help the ordering.
  • The partition key is constant within a partition, so it doesn't need to be in the index.
  • Check GitLab's conventions for sharding key placement in indexes on partitioned tables before finalizing the column order.
  • Confirm the choice against Database Lab. Don't assume the shape works without a real plan.

Any new index should serve the whole read family on this table, not just this query. Other callers in the same file: #accumulated_blobs_for, #history_blobs_for, #full_history_blobs, and #chat_log_thread_ts. They filter on combinations of project_id, workflow_id, current_thread, thread_ts, and channel, and most order by id.

Related concern: IN list growth

A second, separate concern from the same reviewer on the same query: the thread_ts IN (...) list grows with the session's checkpoint count. PostgreSQL 18 CI validation confirmed that IN lists over roughly 624 parameters can flip plans to sequential scans.

This query's LIMIT 1 and lack of joins make it less exposed than most queries with this pattern. Still, a fix should consider bounding the list size.

See https://docs.gitlab.com/development/database/lateral_planner_fence/ for the LATERAL join rewrite that addresses this class of problem.

Acceptance criteria

  • latest_channel_message no longer visits a number of rows proportional to the whole session's blob count.
  • The ORDER BY id DESC sort is removed or bounded to a small, fixed set of rows.
  • A Database Lab plan is attached showing before and after execution for this query.
  • The other readers on p_duo_workflows_checkpoint_blobs (#accumulated_blobs_for, #history_blobs_for, #full_history_blobs, #chat_log_thread_ts) are checked against the new index for regressions.
  • A decision is recorded on whether to bound the thread_ts IN (...) list, and why.

Follow-up for !251388 (comment 3727948213).