Merge lp:~rvb/launchpad/bugs-timeout-bug-892820 into lp:launchpad

[Raphaël Badin]

[r=benji][bug=892820] Avoid joins in a subquery to improve performance.

This branch refactors one of the subqueries used by Distribution:+bugs to use subsubqueries instead of joins. This is based on the analysis done by Rob (see bug report). The original and the refactored query https://pastebin.canonical.com/56422/ → results https://pastebin.canonical.com/56424/.

= Tests =

None.

= Q/A =

We can only be sure that this fixes the timeout by testing the performance of the new query on qastaging.

https://bugs.qastaging.launchpad.net/ubuntu/+bugs?field.searchtext=&orderby=datecreated&field.status%3Alist=NEW&field.importance%3Alist=UNDECIDED&assignee_option=any&field.assignee=&field.bug_reporter=&field.bug_supervisor=ubuntu-server&field.bug_commenter=&field.subscriber=&field.component-empty-marker=1&field.status_upstream-empty-marker=1&field.omit_dupes.used=&field.omit_dupes=on&field.has_patch.used=&field.has_cve.used=&field.affects_me.used=&field.tag=-notserv&field.tags_combinator=ANY&field.has_no_package.used=&search=Search

Main query: [8849.0, 8895.0, 8871.0]

https://bugs.qastaging.launchpad.net/ubuntu/+bugs?field.searchtext=&orderby=-importance&search=Search&field.status%3Alist=NEW&field.bug_supervisor=ubuntu-server

Main query: [8898.0, 6006.0, 4913.0]

[Benji York (benji)]

Looks good.

review: Approve (code)

=== modified file 'lib/lp/bugs/model/bugtask.py'
--- lib/lp/bugs/model/bugtask.py	2011-11-28 00:54:24 +0000
+++ lib/lp/bugs/model/bugtask.py	2011-11-28 16:10:21 +0000
@@ -1375,7 +1375,7 @@
         if role.in_admin:
             return True
 
-        # Similar to admins, the Bug Watch Updater, Bug Importer and 
+        # Similar to admins, the Bug Watch Updater, Bug Importer and
         # Janitor can always change bug details.
         if (
             role.in_bug_watch_updater or role.in_bug_importer or
@@ -2213,22 +2213,19 @@
         # is not for subscription to notifications.
         # See bug #191809
         if params.bug_supervisor:
-            bug_supervisor_clause = """BugTask.id IN (
-                SELECT BugTask.id FROM BugTask, Product
-                WHERE BugTask.product = Product.id
-                    AND Product.bug_supervisor = %(bug_supervisor)s
-                UNION ALL
-                SELECT BugTask.id
-                FROM BugTask, StructuralSubscription
-                WHERE
-                  BugTask.distribution = StructuralSubscription.distribution
-                    AND BugTask.sourcepackagename =
-                        StructuralSubscription.sourcepackagename
-                    AND StructuralSubscription.subscriber = %(bug_supervisor)s
-                UNION ALL
-                SELECT BugTask.id FROM BugTask, Distribution
-                WHERE BugTask.distribution = Distribution.id
-                    AND Distribution.bug_supervisor = %(bug_supervisor)s
+            bug_supervisor_clause = """(
+                BugTask.product IN (
+                    SELECT id FROM Product
+                    WHERE Product.bug_supervisor = %(bug_supervisor)s)
+                OR
+                ((BugTask.distribution, Bugtask.sourcepackagename) IN
+                    (SELECT distribution,  sourcepackagename FROM
+                     StructuralSubscription
+                     WHERE subscriber = %(bug_supervisor)s))
+                OR
+                BugTask.distribution IN (
+                    SELECT id from Distribution WHERE
+                    Distribution.bug_supervisor = %(bug_supervisor)s)
                 )""" % sqlvalues(bug_supervisor=params.bug_supervisor)
             extra_clauses.append(bug_supervisor_clause)