Optimize todos query when filtering by group
What does this MR do and why?
Extracts group query into a CTE so that it can be reused by the UNION querying project-level and group-level todos.
Old query: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/28284/commands/88278
New query: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/28284/commands/88279
The old query was slow because it was fetching all 35M project rows:
-> Index Only Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.56..710889.10 rows=35683220 width=8) (actual time=3.530..29507.733 rows=35680326 loops=1)
Heap Fetches: 1986145
Buffers: shared hit=35308498 read=108012
I/O Timings: read=15737.504 write=0.000
Extracting the group IDs into the CTEs improves the plan so that it only fetches projects with the matching group ids:
-> Index Only Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.56..2.70 rows=26 width=8) (actual time=0.165..0.167 rows=1 loops=21)
Index Cond: (projects.namespace_id = groups_and_descendants_ids.id)
Heap Fetches: 2
Buffers: shared hit=80 read=27
I/O Timings: read=3.140 write=0.000
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
How to set up and validate locally
- Visit
/dashboard/todos
- Filter by a group
Related to #442797 (closed)
Edited by Heinrich Lee Yu