Skip to content

Optimize todos query when filtering by group

Heinrich Lee Yu requested to merge 442797-optimize-todos-query into master

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

  1. Visit /dashboard/todos
  2. Filter by a group

Related to #442797 (closed)

Edited by Heinrich Lee Yu

Merge request reports