models: optimize with_tag_counts using case-when

Tales da Aparecida · stephenfin · commit 52877f86048b · 2026-06-07T16:40:22.000+01:00
Leverage PatchTag index replacing the subquery counter with a JOIN.

The current code is too slow on MySQL, as it doesn't use indexes.
The new approach is more direct and allows proper index usage.

Signed-off-by: Tales da Aparecida &lt;tales.aparecida@redhat.com&gt;
diff --git a/patchwork/models.py b/patchwork/models.py
@@ -5,7 +5,6 @@
 # SPDX-License-Identifier: GPL-2.0-or-later
 
 from collections import Counter
-from collections import OrderedDict
 import datetime
 import random
 import re
@@ -312,25 +311,31 @@ def with_tag_counts(self, project=None):
         # Project, and share the project.tags cache between all patch.project
         # references.
         qs = self.prefetch_related('project')
-        select = OrderedDict()
-        select_params = []
 
         # All projects have the same tags, so we're good to go here
         if project:
             tags = project.tags
         else:
             tags = Tag.objects.all()
 
+        # Annotate the count of each Tag in a column
         for tag in tags:
-            select[tag.attr_name] = (
-                'coalesce('
-                '(SELECT count FROM patchwork_patchtag'
-                ' WHERE patchwork_patchtag.patch_id=patchwork_patch.id'
-                ' AND patchwork_patchtag.tag_id=%s), 0)'
+            qs = qs.annotate(
+                **{
+                    tag.attr_name: models.Sum(
+                        models.Case(
+                            models.When(
+                                patchtag__tag_id=tag.id,
+                                then=models.F('patchtag__count'),
+                            ),
+                            default=models.Value(0),
+                            output_field=models.IntegerField(),
+                        )
+                    )
+                }
             )
-            select_params.append(tag.id)
 
-        return qs.extra(select=select, select_params=select_params)
+        return qs
 
 
 class PatchManager(models.Manager):
