Derive aggregation count from the last page

Brandon Labuschagnerequested to merge
aggregation-count-from-page into master

What does this MR do and why?

The count field on aggregation engine connections (the aggregated field nested under analytics types, for example analytics { duoWorkflows(...) { aggregated { count nodes { ... } } } }) always ran a separate COUNT query over the whole aggregation. For ClickHouse-backed engines that means a full SELECT COUNT(*) wrapped around the same GROUP BY as the main query. GLQL's analytics queries always select count alongside nodes, so every dashboard stat panel, and every chart whose result fits on one page, ran two full ClickHouse aggregations per request instead of one. A stat panel with a trend comparison ran four.

AggregationConnection#count (lib/gitlab/database/aggregation/graphql/aggregation_connection.rb) now returns the page's starting offset plus the number of rows on the loaded page, when the loaded page has no next page and either has rows or starts at offset 0. It falls back to the original COUNT query when a next page exists, or when the page is empty but the offset isn't 0 (an empty page past the start can't prove where the true end is). EngineResponseConnectionType (app/graphql/types/analytics/aggregation/engine_response_connection_type.rb) used to override count to call object.items.count directly, skipping the connection's own logic; that override is removed, so count now resolves through the logic above.

Along the way, this fixes a bug: ClickHouse::Client::QueryBuilder#limit and #offset (click_house-client 0.12.0) mutate the query builder in place instead of returning a copy. AggregationResult#limit/#offset (lib/gitlab/database/aggregation/aggregation_result.rb) passed that same mutated builder on to build the COUNT query, so once a page had applied its own limit and offset, the COUNT query silently counted only that page instead of the whole aggregation. This bug already exists on master for any query that selects nodes before count. The fix: AggregationResult#limit/#offset now clone the query builder before applying limit or offset, so each page gets an independent copy.

One side effect: computing count this way needs the page loaded first. GLQL always requests count and nodes together and reuses the already-loaded page, so this costs nothing extra there. A client that requests only count will now load a page anyway (up to 100 rows), and if that page has a next page, both the page load and a COUNT query run. There is no GraphQL schema or docs change: same field name, type and description. The changed files are all in CE paths, but only EE mounts aggregation engines, so the behavior change is EE only. This reduces ClickHouse query load and doesn't change any SQL query shape, but a database reviewer may still want a look, since it changes how often and when ClickHouse queries run for these analytics fields.

References

  • No related issue. This is not the same change as the GLQL request-queue work referenced in the other two related MRs, and it does not conflict with !256168 (merged): this MR only touches backend Ruby files, with no file overlap.

Screenshots or screen recordings

Not applicable. No visible UI change: this only changes how many ClickHouse queries a GraphQL request runs.

How to set up and validate locally

  1. Enable ClickHouse in GDK, enable the dap_impact_v1 feature flag, and make sure your user has a license with the AI analytics abilities enabled (needed for the duoWorkflows aggregation engine used below).
  2. In /-/graphql-explorer, run: 
    {
  group(fullPath: "gitlab-org") {
    analytics {
      duoWorkflows(createdAtFrom: "2026-08-01T00:00:00Z", createdAtTo: "2026-09-01T00:00:00Z") {
        aggregated(first: 100) {
          count
          nodes { count }
        }
      }
    }
  }
}
  3. Check log/development.log (or the performance bar) for ClickHouse queries: before this change there are two (the page and the COUNT), after it there is one.
  4. Re-run with first: 1 against a result with more than one row, and confirm count still returns the full total, not 1.

Database review

This MR does not change any SQL. What changes is when the full COUNT query runs: now only when the result has more than one page, in either field order.

ClickHouse statements per request

Field order Result fits on one page Master This MR
count first yes page + full COUNT page
count first no page + full COUNT page + full COUNT
nodes first yes page + COUNT of the page page
nodes first no page + COUNT of the page (wrong total) page + full COUNT

On the nodes-first path, the full COUNT now runs only when the page query finds a next page. That is the same COUNT statement master already runs for every count-first request, which is what GLQL sends. It replaces a COUNT that was scoped to the already-limited page and returned the wrong total (for example, first: 1 against 17 rows returned count: 2 instead of 17).

Raw SQL

The statements below come from this GraphQL request (duoWorkflows grouped by user for gitlab-org, 2026-08-29 to 2026-09-28, first: 100). They were captured in GDK with gitlab-org's production traversal path (1/9970/) and UTC time bounds substituted in. Line breaks were added for readability; the tokens are unchanged.

{
  group(fullPath: "gitlab-org") {
    analytics {
      duoWorkflows(createdAtFrom: "2026-08-29", createdAtTo: "2026-09-28") {
        aggregated(first: 100) {
          count
          nodes {
            dimensions { user { id } }
            totalCount
          }
        }
      }
    }
  }
}
Page query 
SELECT `ch_aggregation_inner_query`.`aeq_user_id` AS aeq_user_id, COUNT(*) AS aeq_total_count
FROM (SELECT `ch_aggregation_dedup_query`.`user_id` AS aeq_user_id, `ch_aggregation_dedup_query`.`traversal_path`, `ch_aggregation_dedup_query`.`created_at`, `ch_aggregation_dedup_query`.`id`
FROM (SELECT `duo_workflows_workflows_enriched`.`traversal_path`, `duo_workflows_workflows_enriched`.`created_at`, `duo_workflows_workflows_enriched`.`id`,
  argMax(`duo_workflows_workflows_enriched`.`user_id`, `duo_workflows_workflows_enriched`.`_version`) AS user_id,
  argMax(`duo_workflows_workflows_enriched`.`project_id`, `duo_workflows_workflows_enriched`.`_version`) AS project_id,
  argMax(`duo_workflows_workflows_enriched`.`updated_at`, `duo_workflows_workflows_enriched`.`_version`) AS updated_at,
  argMax(`duo_workflows_workflows_enriched`.`status`, `duo_workflows_workflows_enriched`.`_version`) AS status,
  argMax(`duo_workflows_workflows_enriched`.`goal`, `duo_workflows_workflows_enriched`.`_version`) AS goal,
  argMax(`duo_workflows_workflows_enriched`.`agent_privileges`, `duo_workflows_workflows_enriched`.`_version`) AS agent_privileges,
  argMax(`duo_workflows_workflows_enriched`.`workflow_definition`, `duo_workflows_workflows_enriched`.`_version`) AS workflow_definition,
  argMax(`duo_workflows_workflows_enriched`.`allow_agent_to_request_user`, `duo_workflows_workflows_enriched`.`_version`) AS allow_agent_to_request_user,
  argMax(`duo_workflows_workflows_enriched`.`pre_approved_agent_privileges`, `duo_workflows_workflows_enriched`.`_version`) AS pre_approved_agent_privileges,
  argMax(`duo_workflows_workflows_enriched`.`image`, `duo_workflows_workflows_enriched`.`_version`) AS image,
  argMax(`duo_workflows_workflows_enriched`.`environment`, `duo_workflows_workflows_enriched`.`_version`) AS environment,
  argMax(`duo_workflows_workflows_enriched`.`namespace_id`, `duo_workflows_workflows_enriched`.`_version`) AS namespace_id,
  argMax(`duo_workflows_workflows_enriched`.`ai_catalog_item_version_id`, `duo_workflows_workflows_enriched`.`_version`) AS ai_catalog_item_version_id,
  argMax(`duo_workflows_workflows_enriched`.`issue_id`, `duo_workflows_workflows_enriched`.`_version`) AS issue_id,
  argMax(`duo_workflows_workflows_enriched`.`merge_request_id`, `duo_workflows_workflows_enriched`.`_version`) AS merge_request_id,
  argMax(`duo_workflows_workflows_enriched`.`service_account_id`, `duo_workflows_workflows_enriched`.`_version`) AS service_account_id,
  argMax(`duo_workflows_workflows_enriched`.`tool_call_approvals`, `duo_workflows_workflows_enriched`.`_version`) AS tool_call_approvals,
  argMax(`duo_workflows_workflows_enriched`.`ai_catalog_item_id`, `duo_workflows_workflows_enriched`.`_version`) AS ai_catalog_item_id,
  argMax(`duo_workflows_workflows_enriched`.`messaging_callback_context`, `duo_workflows_workflows_enriched`.`_version`) AS messaging_callback_context,
  argMax(`duo_workflows_workflows_enriched`.`summary`, `duo_workflows_workflows_enriched`.`_version`) AS summary,
  argMax(`duo_workflows_workflows_enriched`.`title`, `duo_workflows_workflows_enriched`.`_version`) AS title,
  argMax(`duo_workflows_workflows_enriched`.`incremental_checkpoints_enabled`, `duo_workflows_workflows_enriched`.`_version`) AS incremental_checkpoints_enabled,
  argMax(`duo_workflows_workflows_enriched`.`agent_type`, `duo_workflows_workflows_enriched`.`_version`) AS agent_type,
  argMax(`duo_workflows_workflows_enriched`.`jsonl_sha256`, `duo_workflows_workflows_enriched`.`_version`) AS jsonl_sha256,
  argMax(`duo_workflows_workflows_enriched`.`idempotency_key`, `duo_workflows_workflows_enriched`.`_version`) AS idempotency_key,
  argMax(`duo_workflows_workflows_enriched`.`sync_type`, `duo_workflows_workflows_enriched`.`_version`) AS sync_type,
  argMax(`duo_workflows_workflows_enriched`.`agent_identity_id`, `duo_workflows_workflows_enriched`.`_version`) AS agent_identity_id,
  argMax(`duo_workflows_workflows_enriched`.`web_search_enabled`, `duo_workflows_workflows_enriched`.`_version`) AS web_search_enabled,
  argMax(`duo_workflows_workflows_enriched`.`trigger_source`, `duo_workflows_workflows_enriched`.`_version`) AS trigger_source,
  argMax(`duo_workflows_workflows_enriched`.`trigger_flow_trigger_id`, `duo_workflows_workflows_enriched`.`_version`) AS trigger_flow_trigger_id,
  argMax(`duo_workflows_workflows_enriched`.`source_type`, `duo_workflows_workflows_enriched`.`_version`) AS source_type,
  argMax(`duo_workflows_workflows_enriched`.`source_link`, `duo_workflows_workflows_enriched`.`_version`) AS source_link,
  argMax(`duo_workflows_workflows_enriched`.`execution_mode`, `duo_workflows_workflows_enriched`.`_version`) AS execution_mode,
  argMax(`duo_workflows_workflows_enriched`.`credits_used`, `duo_workflows_workflows_enriched`.`_version`) AS credits_used,
  argMax(`duo_workflows_workflows_enriched`.`model_used`, `duo_workflows_workflows_enriched`.`_version`) AS model_used,
  argMax(`duo_workflows_workflows_enriched`.`source_merge_request_ids`, `duo_workflows_workflows_enriched`.`_version`) AS source_merge_request_ids,
  argMax(`duo_workflows_workflows_enriched`.`created_merge_request_ids`, `duo_workflows_workflows_enriched`.`_version`) AS created_merge_request_ids,
  argMax(`duo_workflows_workflows_enriched`.`later_closed_merge_request_ids`, `duo_workflows_workflows_enriched`.`_version`) AS later_closed_merge_request_ids,
  argMax(`duo_workflows_workflows_enriched`.`later_merged_merge_request_ids`, `duo_workflows_workflows_enriched`.`_version`) AS later_merged_merge_request_ids,
  argMax(`duo_workflows_workflows_enriched`.`_siphon_deleted`, `duo_workflows_workflows_enriched`.`_version`) AS _siphon_deleted,
  argMax(`duo_workflows_workflows_enriched`.`_version`, `duo_workflows_workflows_enriched`.`_version`) AS _version,
  argMax(`duo_workflows_workflows_enriched`.`_watermark`, `duo_workflows_workflows_enriched`.`_version`) AS _watermark
FROM `duo_workflows_workflows_enriched`
WHERE startsWith(`duo_workflows_workflows_enriched`.`traversal_path`, '1/9970/') AND `duo_workflows_workflows_enriched`.`created_at` BETWEEN 1787961600.0 AND 1790553600.0
GROUP BY ALL
HAVING argMax(`duo_workflows_workflows_enriched`.`_siphon_deleted`, `duo_workflows_workflows_enriched`.`_version`) = 0
) `ch_aggregation_dedup_query`
GROUP BY ALL
) `ch_aggregation_inner_query`
GROUP BY ALL
LIMIT 101 OFFSET 0
COUNT query 
SELECT COUNT() AS rows_count
FROM (SELECT `ch_aggregation_inner_query`.`aeq_user_id` AS aeq_user_id, COUNT(*) AS aeq_total_count
FROM (SELECT `ch_aggregation_dedup_query`.`user_id` AS aeq_user_id, `ch_aggregation_dedup_query`.`traversal_path`, `ch_aggregation_dedup_query`.`created_at`, `ch_aggregation_dedup_query`.`id`
FROM (SELECT `duo_workflows_workflows_enriched`.`traversal_path`, `duo_workflows_workflows_enriched`.`created_at`, `duo_workflows_workflows_enriched`.`id`,
  argMax(`duo_workflows_workflows_enriched`.`user_id`, `duo_workflows_workflows_enriched`.`_version`) AS user_id,
  argMax(`duo_workflows_workflows_enriched`.`project_id`, `duo_workflows_workflows_enriched`.`_version`) AS project_id,
  argMax(`duo_workflows_workflows_enriched`.`updated_at`, `duo_workflows_workflows_enriched`.`_version`) AS updated_at,
  argMax(`duo_workflows_workflows_enriched`.`status`, `duo_workflows_workflows_enriched`.`_version`) AS status,
  argMax(`duo_workflows_workflows_enriched`.`goal`, `duo_workflows_workflows_enriched`.`_version`) AS goal,
  argMax(`duo_workflows_workflows_enriched`.`agent_privileges`, `duo_workflows_workflows_enriched`.`_version`) AS agent_privileges,
  argMax(`duo_workflows_workflows_enriched`.`workflow_definition`, `duo_workflows_workflows_enriched`.`_version`) AS workflow_definition,
  argMax(`duo_workflows_workflows_enriched`.`allow_agent_to_request_user`, `duo_workflows_workflows_enriched`.`_version`) AS allow_agent_to_request_user,
  argMax(`duo_workflows_workflows_enriched`.`pre_approved_agent_privileges`, `duo_workflows_workflows_enriched`.`_version`) AS pre_approved_agent_privileges,
  argMax(`duo_workflows_workflows_enriched`.`image`, `duo_workflows_workflows_enriched`.`_version`) AS image,
  argMax(`duo_workflows_workflows_enriched`.`environment`, `duo_workflows_workflows_enriched`.`_version`) AS environment,
  argMax(`duo_workflows_workflows_enriched`.`namespace_id`, `duo_workflows_workflows_enriched`.`_version`) AS namespace_id,
  argMax(`duo_workflows_workflows_enriched`.`ai_catalog_item_version_id`, `duo_workflows_workflows_enriched`.`_version`) AS ai_catalog_item_version_id,
  argMax(`duo_workflows_workflows_enriched`.`issue_id`, `duo_workflows_workflows_enriched`.`_version`) AS issue_id,
  argMax(`duo_workflows_workflows_enriched`.`merge_request_id`, `duo_workflows_workflows_enriched`.`_version`) AS merge_request_id,
  argMax(`duo_workflows_workflows_enriched`.`service_account_id`, `duo_workflows_workflows_enriched`.`_version`) AS service_account_id,
  argMax(`duo_workflows_workflows_enriched`.`tool_call_approvals`, `duo_workflows_workflows_enriched`.`_version`) AS tool_call_approvals,
  argMax(`duo_workflows_workflows_enriched`.`ai_catalog_item_id`, `duo_workflows_workflows_enriched`.`_version`) AS ai_catalog_item_id,
  argMax(`duo_workflows_workflows_enriched`.`messaging_callback_context`, `duo_workflows_workflows_enriched`.`_version`) AS messaging_callback_context,
  argMax(`duo_workflows_workflows_enriched`.`summary`, `duo_workflows_workflows_enriched`.`_version`) AS summary,
  argMax(`duo_workflows_workflows_enriched`.`title`, `duo_workflows_workflows_enriched`.`_version`) AS title,
  argMax(`duo_workflows_workflows_enriched`.`incremental_checkpoints_enabled`, `duo_workflows_workflows_enriched`.`_version`) AS incremental_checkpoints_enabled,
  argMax(`duo_workflows_workflows_enriched`.`agent_type`, `duo_workflows_workflows_enriched`.`_version`) AS agent_type,
  argMax(`duo_workflows_workflows_enriched`.`jsonl_sha256`, `duo_workflows_workflows_enriched`.`_version`) AS jsonl_sha256,
  argMax(`duo_workflows_workflows_enriched`.`idempotency_key`, `duo_workflows_workflows_enriched`.`_version`) AS idempotency_key,
  argMax(`duo_workflows_workflows_enriched`.`sync_type`, `duo_workflows_workflows_enriched`.`_version`) AS sync_type,
  argMax(`duo_workflows_workflows_enriched`.`agent_identity_id`, `duo_workflows_workflows_enriched`.`_version`) AS agent_identity_id,
  argMax(`duo_workflows_workflows_enriched`.`web_search_enabled`, `duo_workflows_workflows_enriched`.`_version`) AS web_search_enabled,
  argMax(`duo_workflows_workflows_enriched`.`trigger_source`, `duo_workflows_workflows_enriched`.`_version`) AS trigger_source,
  argMax(`duo_workflows_workflows_enriched`.`trigger_flow_trigger_id`, `duo_workflows_workflows_enriched`.`_version`) AS trigger_flow_trigger_id,
  argMax(`duo_workflows_workflows_enriched`.`source_type`, `duo_workflows_workflows_enriched`.`_version`) AS source_type,
  argMax(`duo_workflows_workflows_enriched`.`source_link`, `duo_workflows_workflows_enriched`.`_version`) AS source_link,
  argMax(`duo_workflows_workflows_enriched`.`execution_mode`, `duo_workflows_workflows_enriched`.`_version`) AS execution_mode,
  argMax(`duo_workflows_workflows_enriched`.`credits_used`, `duo_workflows_workflows_enriched`.`_version`) AS credits_used,
  argMax(`duo_workflows_workflows_enriched`.`model_used`, `duo_workflows_workflows_enriched`.`_version`) AS model_used,
  argMax(`duo_workflows_workflows_enriched`.`source_merge_request_ids`, `duo_workflows_workflows_enriched`.`_version`) AS source_merge_request_ids,
  argMax(`duo_workflows_workflows_enriched`.`created_merge_request_ids`, `duo_workflows_workflows_enriched`.`_version`) AS created_merge_request_ids,
  argMax(`duo_workflows_workflows_enriched`.`later_closed_merge_request_ids`, `duo_workflows_workflows_enriched`.`_version`) AS later_closed_merge_request_ids,
  argMax(`duo_workflows_workflows_enriched`.`later_merged_merge_request_ids`, `duo_workflows_workflows_enriched`.`_version`) AS later_merged_merge_request_ids,
  argMax(`duo_workflows_workflows_enriched`.`_siphon_deleted`, `duo_workflows_workflows_enriched`.`_version`) AS _siphon_deleted,
  argMax(`duo_workflows_workflows_enriched`.`_version`, `duo_workflows_workflows_enriched`.`_version`) AS _version,
  argMax(`duo_workflows_workflows_enriched`.`_watermark`, `duo_workflows_workflows_enriched`.`_version`) AS _watermark
FROM `duo_workflows_workflows_enriched`
WHERE startsWith(`duo_workflows_workflows_enriched`.`traversal_path`, '1/9970/') AND `duo_workflows_workflows_enriched`.`created_at` BETWEEN 1787961600.0 AND 1790553600.0
GROUP BY ALL
HAVING argMax(`duo_workflows_workflows_enriched`.`_siphon_deleted`, `duo_workflows_workflows_enriched`.`_version`) = 0
) `ch_aggregation_dedup_query`
GROUP BY ALL
) `ch_aggregation_inner_query`
GROUP BY ALL
) `count_subquery`

ClickHouse timings (gitlab-org, production)

Run Statement Server elapsed Duration (app) Rows read Bytes read
1 page 0.180 s 196 ms 1,082,250 30.9 MB
1 COUNT 0.146 s 162 ms 1,082,250 30.9 MB
2 page 0.116 s 133 ms 1,082,250 30.9 MB
2 COUNT 0.127 s 360 ms 1,082,250 30.9 MB
3 page 0.158 s 182 ms 1,082,250 30.9 MB
3 COUNT 0.121 s 189 ms 1,082,250 30.9 MB

Measured 2026-09-29 on gitlab.com with the performance bar, over three runs of the GraphQL request above. gitlab.com runs master and the request selects count first, so each run executes both the page query and the full COUNT. The result has 1161 rows, so it spans more than one page. With this MR, a multi-page result runs these same two statements in either field order, so this also measures the nodes-first case raised in review. Both statements read the same 1,082,250 rows (30.9 MB) and take 0.12 to 0.18 s on the server, so the full COUNT costs about as much as the page query. That is the statement this MR drops whenever the result fits on one page. Runs 2 and 3 also show an unrelated statement reading 8,192 rows in under 0.03 s, left out of the table.

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.

🤖 Generated with Claude Code

Edited by Brandon Labuschagne

Merge request reports