launchpad-reviewers team mailing list archive

[William Grant <me@xxxxxxxxxxxxxxxxxx>]

William Grant has proposed merging lp:~wgrant/launchpad/cte-it-up into lp:launchpad.

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)

For more details, see:
https://code.launchpad.net/~wgrant/launchpad/cte-it-up/+merge/80767

Despite taking ~1ms in an EXPLAIN ANALYZE, the queries produced by the new Bug.userCanView take 300-400ms to execute on production and qastaging. This branch extracts the TeamParticipation calculation into a CTE, reduce execution time to ~10ms. This should also cut 400ms from the bugtask search query that's already on BugTask:+index on prod.
-- 
https://code.launchpad.net/~wgrant/launchpad/cte-it-up/+merge/80767
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~wgrant/launchpad/cte-it-up into lp:launchpad.

=== modified file 'lib/lp/bugs/model/bugtask.py'
--- lib/lp/bugs/model/bugtask.py	2011-10-28 17:09:49 +0000
+++ lib/lp/bugs/model/bugtask.py	2011-10-31 00:41:27 +0000
@@ -1461,50 +1461,47 @@
         pillar_privacy_filters = """
              UNION
              SELECT BugTask.bug
-             FROM BugTask, TeamParticipation, Product
-             WHERE TeamParticipation.person = %(personid)s AND
-                   TeamParticipation.team = Product.owner AND
+             FROM BugTask, Product
+             WHERE Product.owner IN (SELECT team FROM teams) AND
                    BugTask.product = Product.id AND
                    BugTask.bug = Bug.id AND
                    Bug.security_related IS False
              UNION
              SELECT BugTask.bug
-             FROM BugTask, TeamParticipation, ProductSeries
-             WHERE TeamParticipation.person = %(personid)s AND
-                   TeamParticipation.team = ProductSeries.owner AND
+             FROM BugTask, ProductSeries
+             WHERE ProductSeries.owner IN (SELECT team FROM teams) AND
                    BugTask.productseries = ProductSeries.id AND
                    BugTask.bug = Bug.id AND
                    Bug.security_related IS False
              UNION
              SELECT BugTask.bug
-             FROM BugTask, TeamParticipation, Distribution
-             WHERE TeamParticipation.person = %(personid)s AND
-                   TeamParticipation.team = Distribution.owner AND
+             FROM BugTask, Distribution
+             WHERE Distribution.owner IN (SELECT team FROM teams) AND
                    BugTask.distribution = Distribution.id AND
                    BugTask.bug = Bug.id AND
                    Bug.security_related IS False
              UNION
              SELECT BugTask.bug
-             FROM BugTask, TeamParticipation, DistroSeries, Distribution
-             WHERE TeamParticipation.person = %(personid)s AND
-                   TeamParticipation.team = Distribution.owner AND
+             FROM BugTask, DistroSeries, Distribution
+             WHERE Distribution.owner IN (SELECT team FROM teams) AND
                    DistroSeries.distribution = Distribution.id AND
                    BugTask.distroseries = DistroSeries.id AND
                    BugTask.bug = Bug.id AND
                    Bug.security_related IS False
-        """ % sqlvalues(personid=user.id)
+        """
     query = """
         (Bug.private = FALSE OR EXISTS (
+             WITH teams AS (
+                SELECT team from TeamParticipation WHERE person = %(personid)s
+             )
              SELECT BugSubscription.bug
-             FROM BugSubscription, TeamParticipation
-             WHERE TeamParticipation.person = %(personid)s AND
-                   TeamParticipation.team = BugSubscription.person AND
+             FROM BugSubscription
+             WHERE BugSubscription.person IN (SELECT team FROM teams) AND
                    BugSubscription.bug = Bug.id
              UNION
              SELECT BugTask.bug
-             FROM BugTask, TeamParticipation
-             WHERE TeamParticipation.person = %(personid)s AND
-                   TeamParticipation.team = BugTask.assignee AND
+             FROM BugTask
+             WHERE BugTask.assignee IN (SELECT team FROM teams) AND
                    BugTask.bug = Bug.id
              %(extra_filters)s
                    ))