## Problem Merge request https://gitlab.com/gitlab-org/gitlab/-/merge_requests/251388 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. ```sql 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 https://gitlab.com/gitlab-org/gitlab/-/merge_requests/251388#note_3727948213.