Loading
Sample Tasks by type top labels by created_at
What does this MR do and why?
Groups::Analytics::TasksByTypeController#top_labels picks its 100 sample targets with ORDER BY id DESC. On large groups PostgreSQL walks issues by id and filters the group afterwards, and the request times out (15 s) for gitlab-org. Sampling by created_at DESC lets idx_issues_root_namespace_created_at serve the LIMIT, so the same request reads 749 buffers instead of 227,708.
Part of https://gitlab.com/gitlab-org/gitlab/-/work_items/636171 (Project Bulkhead, https://gitlab.com/gitlab-org/gitlab/-/work_items/635036). The show endpoint is a separate change.
The change is one line in Gitlab::Analytics::TypeOfWork::TasksByType#top_labels: order_by(:id_desc) becomes order_by(:created_desc), plus a spec.
Evidence (Database Lab, gitlab-production-main, group 9970, 2026-05-09 to 2026-11-04)
ORDER BY id DESC (current) |
ORDER BY created_at DESC |
|
|---|---|---|
Scan on issues |
Parallel Index Scan Backward on index_issues_on_id_and_weight, 67,696 rows removed by filter per worker (3 workers) |
Index Scan Backward on idx_issues_root_namespace_created_at, stops at 103 rows |
| Shared buffers read | 227,708 | 749 |
| Shared buffers hit | 51,469 | 4,649 |
99.6% of the reads in the current plan are in the LIMIT 100 subquery (226,863 of 227,708). The label_links and labels lookups are under 1k reads in both plans.
Wall times in Database Lab were noisy (cold cache, hint-bit writes on first access), so buffer counts are compared and not times.
Reproduced in production with the performance bar (gitlab-org, 2026-08-07 to 2026-11-04, subject=Issue): the top_labels main query took 15,047 ms, which is the statement timeout.
Behaviour change
The sample is now the 100 most recent targets by creation time instead of by id. The two differ only for records whose created_at was set explicitly, such as imported issues.
Overlap check
For group 9970 the two samples share 98 of 100 issues. This was measured by joining the two top-100 subqueries by id (Hash Join ... actual rows=98). The top 10 labels are computed from this sample, so the output is expected to match apart from near-ties.
Merge requests
top_labels also runs for merge requests, so this change applies to both subjects. For merge requests it is neutral: the plan is the same either way. The SQL and full plans for both orderings are under "Queries and plans" below.
Merge request plans (group 9970, warm cache, same date range)
ORDER BY id DESC |
ORDER BY created_at DESC |
|
|---|---|---|
Rows read from merge_requests |
71,376 | 71,376 |
| Shared buffers hit | 701,976 | 701,979 |
| Execution | 447 ms | 412 ms |
Both versions read every merge request in the group's projects in the date range through index_merge_requests_on_target_project_id_and_created_at_and_id, then take the top 100 with a top-N heapsort, so the sort column makes no difference. The merge request query is still slow on a cold cache (a first run read 97,772 buffers and took about 2 minutes). Fixing that needs a different query shape and is out of scope here.
Queries and plans
Before (issues)
SELECT DISTINCT "labels".*, count("labels"."id") OVER (PARTITION BY "labels"."id") AS count_by_id
FROM "labels"
INNER JOIN "label_links" ON "label_links"."label_id" = "labels"."id"
WHERE "labels"."type" = 'GroupLabel' AND "label_links"."target_type" = 'Issue'
AND "label_links"."target_id" IN (
SELECT "issues"."id" FROM "issues"
INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (NOT EXISTS (SELECT 1 FROM "banned_users" WHERE (banned_users.user_id = (issues.author_id + 0))))
AND (namespace_traversal_ids[1] = 9970)
AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL)
AND "issues"."state_id" IN (1, 2)
AND "issues"."created_at" <= '2026-11-04 23:59:59.999999'
AND "issues"."created_at" >= '2026-05-09 00:00:00'
ORDER BY "issues"."id" DESC LIMIT 100)
ORDER BY "count_by_id" DESC LIMIT 10Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/81080/commands/188938 (shared buffers: 51,469 hits, 227,708 reads)
Full plan (before)
Limit (cost=36548.95..36549.35 rows=10 width=213) (actual time=84896.643..85123.908 rows=10 loops=1)
Buffers: shared hit=51469 read=227708 dirtied=56764 written=24955
WAL: records=58061 fpi=56735 bytes=433546787
I/O Timings: read=177803.386 write=2077.925
-> Unique (cost=36548.95..36553.47 rows=113 width=213) (actual time=84896.642..85123.904 rows=10 loops=1)
Buffers: shared hit=51469 read=227708 dirtied=56764 written=24955
WAL: records=58061 fpi=56735 bytes=433546787
I/O Timings: read=177803.386 write=2077.925
-> Sort (cost=36548.95..36549.23 rows=113 width=213) (actual time=84896.640..85123.850 rows=196 loops=1)
Sort Key: (count(labels.id) OVER (?)) DESC, labels.id, labels.title, labels.color, labels.project_id, labels.created_at, labels.updated_at, labels.template, labels.description, labels.description_html, labels.group_id, labels.cached_markdown_version, labels.lock_on_merge, labels.archived, labels.organization_id
Sort Method: quicksort Memory: 218kB
Buffers: shared hit=51469 read=227708 dirtied=56764 written=24955
WAL: records=58061 fpi=56735 bytes=433546787
I/O Timings: read=177803.386 write=2077.925
-> WindowAgg (cost=36543.12..36545.10 rows=113 width=213) (actual time=84895.689..85123.189 rows=470 loops=1)
Buffers: shared hit=51455 read=227708 dirtied=56764 written=24955
WAL: records=58061 fpi=56735 bytes=433546787
I/O Timings: read=177803.386 write=2077.925
-> Sort (cost=36543.12..36543.40 rows=113 width=205) (actual time=84895.666..85122.897 rows=470 loops=1)
Sort Key: labels.id
Sort Method: quicksort Memory: 214kB
Buffers: shared hit=51455 read=227708 dirtied=56764 written=24955
WAL: records=58061 fpi=56735 bytes=433546787
I/O Timings: read=177803.386 write=2077.925
-> Nested Loop (cost=35574.26..36539.27 rows=113 width=205) (actual time=84284.173..85122.082 rows=470 loops=1)
Buffers: shared hit=51455 read=227708 dirtied=56764 written=24955
WAL: records=58061 fpi=56735 bytes=433546787
I/O Timings: read=177803.386 write=2077.925
-> Nested Loop (cost=35573.69..35825.87 rows=1208 width=4) (actual time=84280.880..84781.391 rows=496 loops=1)
Buffers: shared hit=49279 read=227404 dirtied=56764 written=24850
WAL: records=58061 fpi=56735 bytes=433546787
I/O Timings: read=177472.829 write=2073.812
-> HashAggregate (cost=35573.12..35574.12 rows=100 width=4) (actual time=84273.514..84500.815 rows=100 loops=1)
Group Key: issues.id
Batches: 1 Memory Usage: 24kB
Buffers: shared hit=48815 read=227056 dirtied=56555 written=24707
WAL: records=57843 fpi=56526 bytes=431881104
I/O Timings: read=177207.105 write=2067.089
-> Limit (cost=1002.16..35572.87 rows=100 width=4) (actual time=84051.354..84498.331 rows=100 loops=1)
Buffers: shared hit=48815 read=227056 dirtied=56555 written=24707
WAL: records=57843 fpi=56526 bytes=431881104
I/O Timings: read=177207.105 write=2067.089
-> Nested Loop Left Join (cost=1002.16..33978828.39 rows=98285 width=4) (actual time=84050.425..84497.371 rows=100 loops=1)
Filter: ((project_features.issues_access_level > 0) OR (project_features.issues_access_level IS NULL))
Buffers: shared hit=48815 read=227056 dirtied=56555 written=24707
WAL: records=57843 fpi=56526 bytes=431881104
I/O Timings: read=177207.105 write=2067.089
-> Nested Loop (cost=1001.59..33938976.91 rows=60894 width=8) (actual time=84041.936..84405.092 rows=100 loops=1)
Buffers: shared hit=48377 read=226994 dirtied=56554 written=24701
WAL: records=57842 fpi=56525 bytes=431873279
I/O Timings: read=177116.009 write=2066.950
-> Nested Loop Anti Join (cost=1001.02..33831566.16 rows=60894 width=8) (actual time=84025.905..84330.090 rows=103 loops=1)
Buffers: shared hit=47937 read=226914 dirtied=56543 written=24693
WAL: records=57831 fpi=56514 bytes=431799132
I/O Timings: read=177042.539 write=2066.754
-> Gather Merge (cost=1000.59..33768544.03 rows=121788 width=12) (actual time=84019.222..84278.262 rows=103 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=47676 read=226863 dirtied=56543 written=24687
WAL: records=57831 fpi=56514 bytes=431799132
I/O Timings: read=176993.414 write=2066.614
-> Parallel Index Scan Backward using index_issues_on_id_and_weight on public.issues (cost=0.57..33753486.65 rows=50745 width=12) (actual time=10076.666..60706.909 rows=303 loops=3)
Filter: ((issues.state_id = ANY ('{1,2}'::integer[])) AND (issues.created_at <= '2026-11-04 23:59:59.999999+00'::timestamp with time zone) AND (issues.created_at >= '2026-05-09 00:00:00+00'::timestamp with time zone) AND (issues.namespace_traversal_ids[1] = 9970))
Rows Removed by Filter: 67696
Buffers: shared hit=47676 read=226863 dirtied=56543 written=24687
WAL: records=57831 fpi=56514 bytes=431799132
I/O Timings: read=176993.414 write=2066.614
-> Index Only Scan using banned_users_pkey on public.banned_users (cost=0.43..0.53 rows=1 width=8) (actual time=0.494..0.494 rows=0 loops=103)
Index Cond: (banned_users.user_id = (issues.author_id + 0))
Heap Fetches: 0
Buffers: shared hit=261 read=51 written=6
I/O Timings: read=49.124 write=0.140
-> Index Only Scan using projects_pkey on public.projects (cost=0.56..1.76 rows=1 width=4) (actual time=0.726..0.726 rows=1 loops=103)
Index Cond: (projects.id = issues.project_id)
Heap Fetches: 66
Buffers: shared hit=440 read=80 dirtied=3 written=8
WAL: records=3 fpi=3 bytes=23155
I/O Timings: read=73.470 write=0.196
-> Index Scan using index_project_features_on_project_id on public.project_features (cost=0.57..0.64 rows=1 width=8) (actual time=0.921..0.921 rows=1 loops=100)
Index Cond: (project_features.project_id = projects.id)
Buffers: shared hit=438 read=62 dirtied=1 written=6
WAL: records=1 fpi=1 bytes=7825
I/O Timings: read=91.096 write=0.139
-> Index Only Scan using index_on_label_links_all_columns on public.label_links (cost=0.57..2.40 rows=12 width=8) (actual time=1.407..2.803 rows=5 loops=100)
Index Cond: ((label_links.target_id = issues.id) AND (label_links.target_type = 'Issue'::text))
Heap Fetches: 512
Buffers: shared hit=464 read=348 dirtied=209 written=143
WAL: records=218 fpi=209 bytes=1665683
I/O Timings: read=265.724 write=6.723
-> Index Scan using labels_pkey on public.labels (cost=0.56..0.59 rows=1 width=205) (actual time=0.686..0.686 rows=1 loops=496)
Index Cond: (labels.id = label_links.label_id)
Filter: ((labels.type)::text = 'GroupLabel'::text)
Buffers: shared hit=2176 read=304 written=105
I/O Timings: read=330.557 write=4.114
Settings: work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB'
Query ID: 8435771132489180729After (issues)
SELECT DISTINCT "labels".*, count("labels"."id") OVER (PARTITION BY "labels"."id") AS count_by_id
FROM "labels"
INNER JOIN "label_links" ON "label_links"."label_id" = "labels"."id"
WHERE "labels"."type" = 'GroupLabel' AND "label_links"."target_type" = 'Issue'
AND "label_links"."target_id" IN (
SELECT "issues"."id" FROM "issues"
INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (NOT EXISTS (SELECT 1 FROM "banned_users" WHERE (banned_users.user_id = (issues.author_id + 0))))
AND (namespace_traversal_ids[1] = 9970)
AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL)
AND "issues"."state_id" IN (1, 2)
AND "issues"."created_at" <= '2026-11-04 23:59:59.999999'
AND "issues"."created_at" >= '2026-05-09 00:00:00'
ORDER BY "issues"."created_at" DESC LIMIT 100)
ORDER BY "count_by_id" DESC LIMIT 10Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/81080/commands/188939 (shared buffers: 4,649 hits, 749 reads)
Full plan (after)
Limit (cost=1379.97..1380.37 rows=10 width=213) (actual time=223.950..224.025 rows=10 loops=1)
Buffers: shared hit=4649 read=749 dirtied=39
WAL: records=38 fpi=38 bytes=287738
I/O Timings: read=211.511 write=0.000
-> Unique (cost=1379.97..1384.49 rows=113 width=213) (actual time=223.949..224.022 rows=10 loops=1)
Buffers: shared hit=4649 read=749 dirtied=39
WAL: records=38 fpi=38 bytes=287738
I/O Timings: read=211.511 write=0.000
-> Sort (cost=1379.97..1380.25 rows=113 width=213) (actual time=223.948..223.961 rows=196 loops=1)
Sort Key: (count(labels.id) OVER (?)) DESC, labels.id, labels.title, labels.color, labels.project_id, labels.created_at, labels.updated_at, labels.template, labels.description, labels.description_html, labels.group_id, labels.cached_markdown_version, labels.lock_on_merge, labels.archived, labels.organization_id
Sort Method: quicksort Memory: 218kB
Buffers: shared hit=4649 read=749 dirtied=39
WAL: records=38 fpi=38 bytes=287738
I/O Timings: read=211.511 write=0.000
-> WindowAgg (cost=1374.14..1376.12 rows=113 width=213) (actual time=223.050..223.310 rows=470 loops=1)
Buffers: shared hit=4632 read=749 dirtied=39
WAL: records=38 fpi=38 bytes=287738
I/O Timings: read=211.511 write=0.000
-> Sort (cost=1374.14..1374.42 rows=113 width=205) (actual time=223.036..223.065 rows=470 loops=1)
Sort Key: labels.id
Sort Method: quicksort Memory: 214kB
Buffers: shared hit=4632 read=749 dirtied=39
WAL: records=38 fpi=38 bytes=287738
I/O Timings: read=211.511 write=0.000
-> Nested Loop (cost=405.28..1370.29 rows=113 width=205) (actual time=217.816..222.764 rows=470 loops=1)
Buffers: shared hit=4632 read=749 dirtied=39
WAL: records=38 fpi=38 bytes=287738
I/O Timings: read=211.511 write=0.000
-> Nested Loop (cost=404.71..656.89 rows=1208 width=4) (actual time=217.787..220.026 rows=496 loops=1)
Buffers: shared hit=2152 read=749 dirtied=39
WAL: records=38 fpi=38 bytes=287738
I/O Timings: read=211.511 write=0.000
-> HashAggregate (cost=404.14..405.14 rows=100 width=4) (actual time=217.729..217.748 rows=100 loops=1)
Group Key: issues.id
Batches: 1 Memory Usage: 24kB
Buffers: shared hit=1355 read=749 dirtied=39
WAL: records=38 fpi=38 bytes=287738
I/O Timings: read=211.511 write=0.000
-> Limit (cost=2.13..402.89 rows=100 width=12) (actual time=10.399..217.634 rows=100 loops=1)
Buffers: shared hit=1355 read=749 dirtied=39
WAL: records=38 fpi=38 bytes=287738
I/O Timings: read=211.511 write=0.000
-> Nested Loop Left Join (cost=2.13..393886.01 rows=98285 width=12) (actual time=10.398..217.611 rows=100 loops=1)
Filter: ((project_features.issues_access_level > 0) OR (project_features.issues_access_level IS NULL))
Buffers: shared hit=1355 read=749 dirtied=39
WAL: records=38 fpi=38 bytes=287738
I/O Timings: read=211.511 write=0.000
-> Nested Loop (cost=1.56..354034.53 rows=60894 width=16) (actual time=10.375..216.875 rows=100 loops=1)
Buffers: shared hit=855 read=749 dirtied=39
WAL: records=38 fpi=38 bytes=287738
I/O Timings: read=211.511 write=0.000
-> Nested Loop Anti Join (cost=1.00..246623.77 rows=60894 width=16) (actual time=10.333..216.074 rows=103 loops=1)
Buffers: shared hit=348 read=749 dirtied=39
WAL: records=38 fpi=38 bytes=287738
I/O Timings: read=211.511 write=0.000
-> Index Scan Backward using idx_issues_root_namespace_created_at on public.issues (cost=0.57..183601.65 rows=121788 width=20) (actual time=10.307..215.354 rows=103 loops=1)
Index Cond: ((issues.namespace_traversal_ids[1] = 9970) AND (issues.created_at <= '2026-11-04 23:59:59.999999+00'::timestamp with time zone) AND (issues.created_at >= '2026-05-09 00:00:00+00'::timestamp with time zone))
Filter: (issues.state_id = ANY ('{1,2}'::integer[]))
Buffers: shared hit=39 read=749 dirtied=39
WAL: records=38 fpi=38 bytes=287738
I/O Timings: read=211.511 write=0.000
-> Index Only Scan using banned_users_pkey on public.banned_users (cost=0.43..0.53 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=103)
Index Cond: (banned_users.user_id = (issues.author_id + 0))
Heap Fetches: 0
Buffers: shared hit=309
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using projects_pkey on public.projects (cost=0.56..1.76 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=103)
Index Cond: (projects.id = issues.project_id)
Heap Fetches: 55
Buffers: shared hit=507
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_project_features_on_project_id on public.project_features (cost=0.57..0.64 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=100)
Index Cond: (project_features.project_id = projects.id)
Buffers: shared hit=500
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_on_label_links_all_columns on public.label_links (cost=0.57..2.40 rows=12 width=8) (actual time=0.011..0.022 rows=5 loops=100)
Index Cond: ((label_links.target_id = issues.id) AND (label_links.target_type = 'Issue'::text))
Heap Fetches: 496
Buffers: shared hit=797
I/O Timings: read=0.000 write=0.000
-> Index Scan using labels_pkey on public.labels (cost=0.56..0.59 rows=1 width=205) (actual time=0.005..0.005 rows=1 loops=496)
Index Cond: (labels.id = label_links.label_id)
Filter: ((labels.type)::text = 'GroupLabel'::text)
Buffers: shared hit=2480
I/O Timings: read=0.000 write=0.000
Settings: jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB'
Query ID: -4867575387974753772Before (merge requests)
The SQL was generated from the application code for group 9970 with no signed-in user, so it has projects.visibility_level = 20. In production a signed-in user adds project_authorizations conditions, which this plan does not include.
SELECT DISTINCT "labels".*, count("labels"."id") OVER (PARTITION BY "labels"."id") AS count_by_id FROM "labels" INNER JOIN "label_links" ON "label_links"."label_id" = "labels"."id" WHERE "labels"."type" = 'GroupLabel' AND "label_links"."target_type" = 'MergeRequest' AND "label_links"."target_id" IN (SELECT "merge_requests"."id" FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (NOT EXISTS (SELECT 1 FROM "banned_users" WHERE (banned_users.user_id = (merge_requests.author_id + 0)))) AND "projects"."namespace_id" IN (SELECT "namespaces"."id" FROM UNNEST(
COALESCE(
(SELECT ids FROM (SELECT "namespace_descendants"."self_and_descendant_group_ids" AS ids FROM "namespace_descendants" WHERE "namespace_descendants"."outdated_at" IS NULL AND "namespace_descendants"."namespace_id" = 9970) cached_query),
(SELECT ids FROM (SELECT ARRAY_AGG("namespaces"."id") AS ids FROM (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}'))) namespaces) consistent_query))
) AS namespaces(id)
) AND "projects"."visibility_level" = 20 AND ("project_features"."merge_requests_access_level" IN (20, 30) OR "project_features"."merge_requests_access_level" IS NULL) AND "merge_requests"."created_at" <= '2026-11-04 23:59:59' AND "merge_requests"."created_at" >= '2026-05-09 00:00:00' ORDER BY "merge_requests"."id" DESC LIMIT 100) ORDER BY "count_by_id" DESC LIMIT 10Shared buffers: warm run 701,976 hits, 0 reads (447 ms); first run on a cold cache 606,720 hits, 97,772 reads (about 2 minutes).
Full plan (before, warm cache)
Limit (cost=3597.25..3597.65 rows=10 width=213) (actual time=447.336..447.436 rows=10 loops=1)
Buffers: shared hit=701976
I/O Timings: read=0.000 write=0.000
-> Unique (cost=3597.25..3598.05 rows=20 width=213) (actual time=447.335..447.433 rows=10 loops=1)
Buffers: shared hit=701976
I/O Timings: read=0.000 write=0.000
-> Sort (cost=3597.25..3597.30 rows=20 width=213) (actual time=447.334..447.355 rows=256 loops=1)
Sort Key: (count(labels.id) OVER (?)) DESC, labels.id, labels.title, labels.color, labels.project_id, labels.created_at, labels.updated_at, labels.template, labels.description, labels.description_html, labels.group_id, labels.cached_markdown_version, labels.lock_on_merge, labels.archived, labels.organization_id
Sort Method: quicksort Memory: 302kB
Buffers: shared hit=701976
I/O Timings: read=0.000 write=0.000
-> WindowAgg (cost=3596.47..3596.82 rows=20 width=213) (actual time=445.871..446.302 rows=654 loops=1)
Buffers: shared hit=701959
I/O Timings: read=0.000 write=0.000
-> Sort (cost=3596.47..3596.52 rows=20 width=205) (actual time=445.850..445.901 rows=654 loops=1)
Sort Key: labels.id
Sort Method: quicksort Memory: 297kB
Buffers: shared hit=701959
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=3376.69..3596.04 rows=20 width=205) (actual time=442.400..445.429 rows=654 loops=1)
Buffers: shared hit=701959
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=3376.12..3467.21 rows=216 width=4) (actual time=442.377..443.433 rows=750 loops=1)
Buffers: shared hit=698209
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=3375.55..3375.93 rows=38 width=8) (actual time=442.356..442.381 rows=100 loops=1)
Group Key: merge_requests.id
Batches: 1 Memory Usage: 32kB
Buffers: shared hit=696997
I/O Timings: read=0.000 write=0.000
-> Limit (cost=3375.36..3375.46 rows=38 width=8) (actual time=442.301..442.324 rows=100 loops=1)
Buffers: shared hit=696997
I/O Timings: read=0.000 write=0.000
InitPlan 1
-> Index Scan using namespace_descendants_12_pkey on gitlab_partitions_static.namespace_descendants_12 namespace_descendants (cost=0.14..3.16 rows=1 width=690) (actual time=0.030..0.031 rows=1 loops=1)
Index Cond: (namespace_descendants.namespace_id = 9970)
Filter: (namespace_descendants.outdated_at IS NULL)
Buffers: shared hit=5
I/O Timings: read=0.000 write=0.000
InitPlan 2
-> Aggregate (cost=1987.25..1987.26 rows=1 width=32) (never executed)
I/O Timings: read=0.000 write=0.000
-> Bitmap Heap Scan on public.namespaces namespaces_1 (cost=58.93..1981.14 rows=1220 width=28) (never executed)
Recheck Cond: ((namespaces_1.traversal_ids @> '{9970}'::integer[]) AND ((namespaces_1.type)::text = 'Group'::text))
I/O Timings: read=0.000 write=0.000
-> Bitmap Index Scan on index_namespaces_on_traversal_ids_for_groups (cost=0.00..58.62 rows=1220 width=0) (never executed)
Index Cond: (namespaces_1.traversal_ids @> '{9970}'::integer[])
I/O Timings: read=0.000 write=0.000
-> Sort (cost=1384.94..1385.04 rows=38 width=8) (actual time=442.300..442.310 rows=100 loops=1)
Sort Key: merge_requests.id DESC
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=696997
I/O Timings: read=0.000 write=0.000
-> Nested Loop Left Join (cost=2.29..1383.95 rows=38 width=8) (actual time=1.313..437.703 rows=71376 loops=1)
Filter: ((project_features.merge_requests_access_level = ANY ('{20,30}'::integer[])) OR (project_features.merge_requests_access_level IS NULL))
Rows Removed by Filter: 2432
Buffers: shared hit=696997
I/O Timings: read=0.000 write=0.000
-> Nested Loop Anti Join (cost=1.72..1357.81 rows=25 width=12) (actual time=1.291..317.745 rows=73808 loops=1)
Buffers: shared hit=327957
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=1.29..1334.96 rows=51 width=20) (actual time=1.259..209.054 rows=73813 loops=1)
Buffers: shared hit=106517
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.72..527.66 rows=46 width=4) (actual time=0.719..24.008 rows=3791 loops=1)
Buffers: shared hit=15960
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=0.15..0.26 rows=10 width=8) (actual time=0.644..1.115 rows=1650 loops=1)
Group Key: namespaces.id
Batches: 1 Memory Usage: 217kB
Buffers: shared hit=38
I/O Timings: read=0.000 write=0.000
-> Function Scan on pg_catalog.unnest namespaces (cost=0.03..0.13 rows=10 width=8) (actual time=0.235..0.313 rows=1650 loops=1)
Function Call: unnest(COALESCE((InitPlan 1).col1, ((InitPlan 2).col1)::bigint[]))
Buffers: shared hit=38
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.56..52.69 rows=5 width=8) (actual time=0.009..0.013 rows=2 loops=1650)
Index Cond: (projects.namespace_id = namespaces.id)
Filter: (projects.visibility_level = 20)
Rows Removed by Filter: 3
Buffers: shared hit=15922
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_merge_requests_on_target_project_id_and_created_at_and_id on public.merge_requests (cost=0.57..16.31 rows=124 width=24) (actual time=0.006..0.046 rows=19 loops=3791)
Index Cond: ((merge_requests.target_project_id = projects.id) AND (merge_requests.created_at <= '2026-11-04 23:59:59+00'::timestamp with time zone) AND (merge_requests.created_at >= '2026-05-09 00:00:00+00'::timestamp with time zone))
Buffers: shared hit=90557
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using banned_users_pkey on public.banned_users (cost=0.43..0.45 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=73813)
Index Cond: (banned_users.user_id = (merge_requests.author_id + 0))
Heap Fetches: 0
Buffers: shared hit=221440
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_project_features_on_project_id on public.project_features (cost=0.57..1.03 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=73808)
Index Cond: (project_features.project_id = projects.id)
Buffers: shared hit=369040
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_on_label_links_all_columns on public.label_links (cost=0.57..2.34 rows=6 width=8) (actual time=0.004..0.009 rows=8 loops=100)
Index Cond: ((label_links.target_id = merge_requests.id) AND (label_links.target_type = 'MergeRequest'::text))
Heap Fetches: 750
Buffers: shared hit=1212
I/O Timings: read=0.000 write=0.000
-> Index Scan using labels_pkey on public.labels (cost=0.56..0.60 rows=1 width=205) (actual time=0.002..0.002 rows=1 loops=750)
Index Cond: (labels.id = label_links.label_id)
Filter: ((labels.type)::text = 'GroupLabel'::text)
Buffers: shared hit=3750
I/O Timings: read=0.000 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off'
Query ID: 2783925266591550776After (merge requests)
Same SQL as "Before (merge requests)", with the end of the subquery changed to ORDER BY "merge_requests"."created_at" DESC LIMIT 100.
Shared buffers: warm run 701,979 hits, 0 reads (412 ms).
Full plan (after, warm cache)
Limit (cost=3597.63..3598.03 rows=10 width=213) (actual time=412.195..412.283 rows=10 loops=1)
Buffers: shared hit=701979
I/O Timings: read=0.000 write=0.000
-> Unique (cost=3597.63..3598.43 rows=20 width=213) (actual time=412.193..412.280 rows=10 loops=1)
Buffers: shared hit=701979
I/O Timings: read=0.000 write=0.000
-> Sort (cost=3597.63..3597.68 rows=20 width=213) (actual time=412.192..412.211 rows=256 loops=1)
Sort Key: (count(labels.id) OVER (?)) DESC, labels.id, labels.title, labels.color, labels.project_id, labels.created_at, labels.updated_at, labels.template, labels.description, labels.description_html, labels.group_id, labels.cached_markdown_version, labels.lock_on_merge, labels.archived, labels.organization_id
Sort Method: quicksort Memory: 302kB
Buffers: shared hit=701979
I/O Timings: read=0.000 write=0.000
-> WindowAgg (cost=3596.85..3597.20 rows=20 width=213) (actual time=410.860..411.245 rows=654 loops=1)
Buffers: shared hit=701962
I/O Timings: read=0.000 write=0.000
-> Sort (cost=3596.85..3596.90 rows=20 width=205) (actual time=410.843..410.886 rows=654 loops=1)
Sort Key: labels.id
Sort Method: quicksort Memory: 297kB
Buffers: shared hit=701962
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=3377.07..3596.42 rows=20 width=205) (actual time=407.651..410.439 rows=654 loops=1)
Buffers: shared hit=701962
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=3376.50..3467.59 rows=216 width=4) (actual time=407.634..408.622 rows=750 loops=1)
Buffers: shared hit=698212
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=3375.93..3376.31 rows=38 width=8) (actual time=407.602..407.624 rows=100 loops=1)
Group Key: merge_requests.id
Batches: 1 Memory Usage: 32kB
Buffers: shared hit=697000
I/O Timings: read=0.000 write=0.000
-> Limit (cost=3375.36..3375.46 rows=38 width=16) (actual time=407.555..407.576 rows=100 loops=1)
Buffers: shared hit=697000
I/O Timings: read=0.000 write=0.000
InitPlan 1
-> Index Scan using namespace_descendants_12_pkey on gitlab_partitions_static.namespace_descendants_12 namespace_descendants (cost=0.14..3.16 rows=1 width=690) (actual time=0.023..0.024 rows=1 loops=1)
Index Cond: (namespace_descendants.namespace_id = 9970)
Filter: (namespace_descendants.outdated_at IS NULL)
Buffers: shared hit=5
I/O Timings: read=0.000 write=0.000
InitPlan 2
-> Aggregate (cost=1987.25..1987.26 rows=1 width=32) (never executed)
I/O Timings: read=0.000 write=0.000
-> Bitmap Heap Scan on public.namespaces namespaces_1 (cost=58.93..1981.14 rows=1220 width=28) (never executed)
Recheck Cond: ((namespaces_1.traversal_ids @> '{9970}'::integer[]) AND ((namespaces_1.type)::text = 'Group'::text))
I/O Timings: read=0.000 write=0.000
-> Bitmap Index Scan on index_namespaces_on_traversal_ids_for_groups (cost=0.00..58.62 rows=1220 width=0) (never executed)
Index Cond: (namespaces_1.traversal_ids @> '{9970}'::integer[])
I/O Timings: read=0.000 write=0.000
-> Sort (cost=1384.94..1385.04 rows=38 width=16) (actual time=407.554..407.564 rows=100 loops=1)
Sort Key: merge_requests.created_at DESC
Sort Method: top-N heapsort Memory: 32kB
Buffers: shared hit=697000
I/O Timings: read=0.000 write=0.000
-> Nested Loop Left Join (cost=2.29..1383.95 rows=38 width=16) (actual time=1.747..399.316 rows=71376 loops=1)
Filter: ((project_features_1.merge_requests_access_level = ANY ('{20,30}'::integer[])) OR (project_features_1.merge_requests_access_level IS NULL))
Rows Removed by Filter: 2432
Buffers: shared hit=696997
I/O Timings: read=0.000 write=0.000
-> Nested Loop Anti Join (cost=1.72..1357.81 rows=25 width=20) (actual time=1.722..292.542 rows=73808 loops=1)
Buffers: shared hit=327957
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=1.29..1334.96 rows=51 width=28) (actual time=1.684..199.370 rows=73813 loops=1)
Buffers: shared hit=106517
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.72..527.66 rows=46 width=4) (actual time=0.894..22.965 rows=3791 loops=1)
Buffers: shared hit=15960
I/O Timings: read=0.000 write=0.000
-> HashAggregate (cost=0.15..0.26 rows=10 width=8) (actual time=0.820..1.253 rows=1650 loops=1)
Group Key: namespaces.id
Batches: 1 Memory Usage: 217kB
Buffers: shared hit=38
I/O Timings: read=0.000 write=0.000
-> Function Scan on pg_catalog.unnest namespaces (cost=0.03..0.13 rows=10 width=8) (actual time=0.380..0.463 rows=1650 loops=1)
Function Call: unnest(COALESCE((InitPlan 1).col1, ((InitPlan 2).col1)::bigint[]))
Buffers: shared hit=38
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.56..52.69 rows=5 width=8) (actual time=0.008..0.013 rows=2 loops=1650)
Index Cond: (projects.namespace_id = namespaces.id)
Filter: (projects.visibility_level = 20)
Rows Removed by Filter: 3
Buffers: shared hit=15922
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_merge_requests_on_target_project_id_and_created_at_and_id on public.merge_requests (cost=0.57..16.31 rows=124 width=32) (actual time=0.005..0.044 rows=19 loops=3791)
Index Cond: ((merge_requests.target_project_id = projects.id) AND (merge_requests.created_at <= '2026-11-04 23:59:59+00'::timestamp with time zone) AND (merge_requests.created_at >= '2026-05-09 00:00:00+00'::timestamp with time zone))
Buffers: shared hit=90557
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using banned_users_pkey on public.banned_users banned_users_1 (cost=0.43..0.45 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=73813)
Index Cond: (banned_users_1.user_id = (merge_requests.author_id + 0))
Heap Fetches: 0
Buffers: shared hit=221440
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_project_features_on_project_id on public.project_features project_features_1 (cost=0.57..1.03 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=73808)
Index Cond: (project_features_1.project_id = projects.id)
Buffers: shared hit=369040
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_on_label_links_all_columns on public.label_links (cost=0.57..2.34 rows=6 width=8) (actual time=0.004..0.009 rows=8 loops=100)
Index Cond: ((label_links.target_id = merge_requests.id) AND (label_links.target_type = 'MergeRequest'::text))
Heap Fetches: 750
Buffers: shared hit=1212
I/O Timings: read=0.000 write=0.000
-> Index Scan using labels_pkey on public.labels (cost=0.56..0.60 rows=1 width=205) (actual time=0.002..0.002 rows=1 loops=750)
Index Cond: (labels.id = label_links.label_id)
Filter: ((labels.type)::text = 'GroupLabel'::text)
Buffers: shared hit=3750
I/O Timings: read=0.000 write=0.000
Settings: effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: -8613816996368156423Not covered yet
- The sort has no
idtie-breaker. This matches the plan that was tested. If the database review asks forcreated_at DESC, id DESC, that variant needs its own plan. - Merge request
top_labelscan still be slow on a cold cache (see above). In production (performance bar,gitlab-org), switching the Tasks by type chart to merge requests sent a newtasks_by_typerequest and no newtop_labelsrequest, so the UI does not calltop_labelsfor merge requests. I did not open a follow-up issue for that reason.
How to test
- Open Value stream analytics for a group with issues and labels, with the Tasks by type chart.
- The top labels list should match what it showed before.
Groups::Analytics::TasksByTypeController#top_labelsshould issue theORDER BY created_at DESC LIMIT 100subquery.
Checklist
- Spec added (
tasks_by_type_spec.rb, runs for issues and merge requests); the file passes locally - Query plans added (Database Lab links and full plans for issues, full plans for merge requests)
- Database review requested
- Remove
[skip ci]from the title and start a pipeline when ready
Edited by Fardeen Khan