Remove unindexed condition on label transfer (!37060) · Merge requests · GitLab.org / GitLab

Robert May requested to merge faster-label-transfer-queries into master Jul 16, 2020

What does this MR do?

Removes an errant condition in two Label queries when transferring labels alongside a project. This caused the queries to miss the established indices.

Related #18754 (closed)

Related #229165 (closed)

Big thanks to @mksionek and @engwan for helping find and resolve this 🏆

Original query:

SELECT "labels".*
FROM "labels"
INNER JOIN "label_links"
  ON "label_links"."target_type" = 'Issue'
  AND "label_links"."label_id" = "labels"."id"
INNER JOIN "issues"
  ON "issues"."id" = "label_links"."target_id"
WHERE "issues"."project_id" = 15114652
  AND "labels"."type" = 'GroupLabel'
  AND "labels"."group_id" IN (
    WITH RECURSIVE "base_and_ancestors" AS
      (
        (SELECT "namespaces".*
           FROM "namespaces"
           WHERE "namespaces"."type" = 'Group'
             AND "namespaces"."id" = 2750817
        )
        UNION
        (SELECT "namespaces".*
           FROM "namespaces", "base_and_ancestors"
           WHERE "namespaces"."type" = 'Group'
             AND "namespaces"."id" = "base_and_ancestors"."parent_id"
        )
      )

      SELECT "namespaces"."id"
      FROM "base_and_ancestors"
      AS "namespaces"
    )
ORDER BY "labels"."title" ASC

Original query plan:

 Sort  (cost=971.34..971.35 rows=1 width=85) (actual time=18276.047..18276.047 rows=0 loops=1)
   Sort Key: labels.title
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=3237395 read=310780 written=3
   I/O Timings: read=10670.148 write=0.195
   ->  Nested Loop  (cost=353.50..971.33 rows=1 width=85) (actual time=18276.039..18276.039 rows=0 loops=1)
         Buffers: shared hit=3237395 read=310780 written=3
         I/O Timings: read=10670.148 write=0.195
         ->  Nested Loop  (cost=352.93..949.35 rows=32 width=89) (actual time=3.443..8533.789 rows=547625 loops=1)
               Buffers: shared hit=591562 read=214800 written=3
               I/O Timings: read=5963.205 write=0.195
               ->  Nested Loop  (cost=352.37..853.24 rows=7 width=85) (actual time=3.317..56.707 rows=1130 loops=1)
                     Buffers: shared hit=459 read=675
                     I/O Timings: read=47.277
                     ->  HashAggregate  (cost=351.81..352.02 rows=21 width=4) (actual time=3.064..3.068 rows=2 loops=1)
                           Group Key: namespaces.id
                           Buffers: shared hit=1 read=7
                           I/O Timings: read=2.908
                           ->  CTE Scan on base_and_ancestors namespaces  (cost=351.12..351.54 rows=21 width=4) (actual time=2.827..3.058 rows=2 loops=1)
                                 Buffers: shared hit=1 read=7
                                 I/O Timings: read=2.908
                                 CTE base_and_ancestors
                                   ->  Recursive Union  (cost=0.43..351.12 rows=21 width=347) (actual time=2.822..3.051 rows=2 loops=1)
                                         Buffers: shared hit=1 read=7
                                         I/O Timings: read=2.908
                                         ->  Index Scan using namespaces_pkey on public.namespaces namespaces_1  (cost=0.43..3.45 rows=1 width=347) (actual time=2.807..2.808 rows=1 loops=1)
                                               Index Cond: (namespaces_1.id = 2750817)
                                               Filter: ((namespaces_1.type)::text = 'Group'::text)
                                               Rows Removed by Filter: 0
                                               Buffers: shared read=4
                                               I/O Timings: read=2.732
                                         ->  Nested Loop  (cost=0.43..34.73 rows=2 width=347) (actual time=0.109..0.109 rows=0 loops=2)
                                               Buffers: shared hit=1 read=3
                                               I/O Timings: read=0.176
                                               ->  WorkTable Scan on base_and_ancestors  (cost=0.00..0.20 rows=10 width=4) (actual time=0.003..0.003 rows=1 loops=2)
                                               ->  Index Scan using namespaces_pkey on public.namespaces namespaces_2  (cost=0.43..3.45 rows=1 width=347) (actual time=0.103..0.103 rows=0 loops=2)
                                                     Index Cond: (namespaces_2.id = base_and_ancestors.parent_id)
                                                     Filter: ((namespaces_2.type)::text = 'Group'::text)
                                                     Rows Removed by Filter: 0
                                                     Buffers: shared hit=1 read=3
                                                     I/O Timings: read=0.176
                     ->  Index Scan using index_labels_on_group_id_and_project_id_and_title on public.labels  (cost=0.56..23.86 rows=1 width=85) (actual time=0.243..26.004 rows=565 loops=2)
                           Index Cond: (labels.group_id = namespaces.id)
                           Filter: ((labels.type)::text = 'GroupLabel'::text)
                           Rows Removed by Filter: 0
                           Buffers: shared hit=458 read=668
                           I/O Timings: read=44.370
               ->  Index Scan using index_label_links_on_label_id on public.label_links  (cost=0.56..10.93 rows=280 width=8) (actual time=0.173..7.308 rows=485 loops=1130)
                     Index Cond: (label_links.label_id = labels.id)
                     Filter: ((label_links.target_type)::text = 'Issue'::text)
                     Rows Removed by Filter: 314
                     Buffers: shared hit=591103 read=214125 written=3
                     I/O Timings: read=5915.927 write=0.195
         ->  Index Scan using issues_pkey on public.issues  (cost=0.56..0.69 rows=1 width=4) (actual time=0.017..0.017 rows=0 loops=547625)
               Index Cond: (issues.id = label_links.target_id)
               Filter: (issues.project_id = 15114652)
               Rows Removed by Filter: 1
               Buffers: shared hit=2645833 read=95980
               I/O Timings: read=4706.944

Revised query:

SELECT "labels".*
FROM "labels"
INNER JOIN "label_links"
  ON "label_links"."target_type" = 'Issue'
  AND "label_links"."label_id" = "labels"."id"
INNER JOIN "issues"
  ON "issues"."id" = "label_links"."target_id"
WHERE "issues"."project_id" = 15114652
  AND "labels"."group_id" IN (
    WITH RECURSIVE "base_and_ancestors" AS
      (
        (SELECT "namespaces".*
           FROM "namespaces"
           WHERE "namespaces"."type" = 'Group'
             AND "namespaces"."id" = 2750817
        )
        UNION
        (SELECT "namespaces".*
           FROM "namespaces", "base_and_ancestors"
           WHERE "namespaces"."type" = 'Group'
             AND "namespaces"."id" = "base_and_ancestors"."parent_id"
        )
      )

      SELECT "namespaces"."id"
      FROM "base_and_ancestors"
      AS "namespaces"
    )
ORDER BY "labels"."title" ASC

Revised query plan:

Sort  (cost=1378.43..1378.43 rows=1 width=85) (actual time=0.148..0.148 rows=0 loops=1)
   Sort Key: labels.title
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=33
   ->  Hash Semi Join  (cost=353.58..1378.42 rows=1 width=85) (actual time=0.136..0.136 rows=0 loops=1)
         Hash Cond: (labels.group_id = namespaces.id)
         Buffers: shared hit=33
         ->  Nested Loop  (cost=1.56..1025.86 rows=208 width=85) (actual time=0.135..0.135 rows=0 loops=1)
               Buffers: shared hit=33
               ->  Nested Loop  (cost=1.13..930.62 rows=208 width=4) (actual time=0.134..0.134 rows=0 loops=1)
                     Buffers: shared hit=33
                     ->  Index Only Scan using idx_issues_on_project_id_and_rel_position_and_state_id_and_id on public.issues  (cost=0.56..18.92 rows=207 width=4) (actual time=0.035..0.044 rows=6 loops=1)
                           Index Cond: (issues.project_id = 15114652)
                           Heap Fetches: 1
                           Buffers: shared hit=9
                     ->  Index Scan using index_label_links_on_target_id_and_target_type on public.label_links  (cost=0.56..4.38 rows=2 width=8) (actual time=0.012..0.012 rows=0 loops=6)
                           Index Cond: ((label_links.target_id = issues.id) AND ((label_links.target_type)::text = 'Issue'::text))
                           Buffers: shared hit=24
               ->  Index Scan using labels_pkey on public.labels  (cost=0.43..0.46 rows=1 width=85) (actual time=0.000..0.000 rows=0 loops=0)
                     Index Cond: (labels.id = label_links.label_id)
         ->  Hash  (cost=351.75..351.75 rows=21 width=4) (actual time=0.000..0.000 rows=0 loops=0)
               ->  CTE Scan on base_and_ancestors namespaces  (cost=351.12..351.54 rows=21 width=4) (actual time=0.000..0.000 rows=0 loops=0)
                     CTE base_and_ancestors
                       ->  Recursive Union  (cost=0.43..351.12 rows=21 width=347) (actual time=0.000..0.000 rows=0 loops=0)
                             ->  Index Scan using namespaces_pkey on public.namespaces namespaces_1  (cost=0.43..3.45 rows=1 width=347) (actual time=0.000..0.000 rows=0 loops=0)
                                   Index Cond: (namespaces_1.id = 2750817)
                                   Filter: ((namespaces_1.type)::text = 'Group'::text)
                                   Rows Removed by Filter: 0
                             ->  Nested Loop  (cost=0.43..34.73 rows=2 width=347) (actual time=0.000..0.000 rows=0 loops=0)
                                   ->  WorkTable Scan on base_and_ancestors  (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.000 rows=0 loops=0)
                                   ->  Index Scan using namespaces_pkey on public.namespaces namespaces_2  (cost=0.43..3.45 rows=1 width=347) (actual time=0.000..0.000 rows=0 loops=0)
                                         Index Cond: (namespaces_2.id = base_and_ancestors.parent_id)
                                         Filter: ((namespaces_2.type)::text = 'Group'::text)
                                         Rows Removed by Filter: 0

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
Tested in all supported browsers
Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

Label as security and @ mention @gitlab-com/gl-security/appsec
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
Security reports checked/validated by a reviewer from the AppSec team

Edited Jul 16, 2020 by Robert May

Remove unindexed condition on label transfer