Thank you all for the approval.

Congrats @div.ya for being CDot backend maintainer! Happy Merging! 🎉

Hi @qzhaogitlab , as discussed in our 1:1, also adding you to this SPIKE issue to explore entitlements current friction points and future architecture from groupprovision .

Hi @qzhaogitlab , could you please review this issue and see what would be the expected behaviour?

Thanks @peanutandlevi for the query link.

Query performance concern with positioned scope ordering

The query plan without the composite index shows that even with LIMIT 21 (as applied by pagination), all child rows are fetched before the limit takes effect:

• https://console.postgres.ai/gitlab/gitlab-production-main/sessions/49857/commands/148300
• Execution: 972ms (cold cache), 789 nested loop iterations
• The existing index index_work_item_parent_links_on_work_item_parent_id finds all children for the parent, but returns them in index order (by work_item_parent_id), not in relative_position order
• PostgreSQL must fetch all 789 rows, do 789 PK lookups on issues, then top-N heapsort to take 21
• The LIMIT 21 only saves time on the sort step, not on the row fetching

With a composite index on (work_item_parent_id, relative_position ASC NULLS LAST, id ASC):

• https://console.postgres.ai/gitlab/gitlab-production-main/sessions/49857/commands/148306
• Execution: 1.7ms (cold cache)
• The index returns rows already in the correct order, so PostgreSQL reads only 21 entries from the index, does 21 PK lookups, and stops immediately.
• Nested loop iterations drop from 789 to 21, buffer reads from 838 to 30

CREATE INDEX CONCURRENTLY index_work_item_parent_links_on_parent_id_position_and_id ON work_item_parent_links (work_item_parent_id, relative_position ASC NULLS LAST, id ASC);

The existing single-column index on work_item_parent_id is a prefix of this composite index, so it could potentially be dropped since the new composite index would cover the same lookups. That should be verified against existing queries before removing it.

It would be helpful to see the query plan for the current implementation on master (Epic.related_issues through epic_issues) for the same epic to compare the baseline. Depending on the regression, this could either be included as a migration in this MR or tracked as a follow-up. WDYT?