Merge lp:~wgrant/launchpad/upcoming-optimisation into lp:launchpad

[William Grant]

Optimise Person.getAssignedSpecificationWorkItemsDueBefore main query.

Optimise Person.getAssignedSpecificationWorkItemsDueBefore main query.

PostgreSQL was unable to effectively estimate the number of assigned
workitems when the conditions were combined. Moving that calculation
into a CTE acts as an optimisation barrier and forces PostgreSQL to
calculate the assigned set up front, making the rest much faster.

[Colin Watson (cjwatson)]

review: Approve

=== modified file 'lib/lp/registry/model/person.py'
--- lib/lp/registry/model/person.py	2017-04-22 13:09:01 +0000
+++ lib/lp/registry/model/person.py	2017-06-06 11:13:31 +0000
@@ -1433,25 +1433,51 @@
         from lp.registry.model.product import Product
         from lp.registry.model.distribution import Distribution
         store = Store.of(self)
-        WorkItem = SpecificationWorkItem
+
+        # Since a workitem's assignee defaults to its specification's
+        # assignee, the PostgreSQL planner isn't always able to work out
+        # the selectivity of the filter. Put that in a CTE to force it
+        # to calculate the workitems up front, rather than doing a hash
+        # join over all of Specification and SpecificationWorkItem.
+        assigned_specificationworkitem = With(
+            'assigned_specificationworkitem',
+            Union(
+                Select(
+                    SpecificationWorkItem.id,
+                    where=And(
+                        SpecificationWorkItem.assignee_id.is_in(
+                            self.participant_ids),
+                        Not(SpecificationWorkItem.deleted))),
+                Select(
+                    SpecificationWorkItem.id,
+                    where=And(
+                        SpecificationWorkItem.specification_id.is_in(
+                            Select(
+                                Specification.id,
+                                where=Specification._assigneeID.is_in(
+                                    self.participant_ids))),
+                        Not(SpecificationWorkItem.deleted))),
+                all=True))
+
         origin = [Specification]
         productjoin, query = get_specification_active_product_filter(self)
         origin.extend(productjoin)
         query.extend(get_specification_privacy_filter(user))
         origin.extend([
-            Join(WorkItem, WorkItem.specification == Specification.id),
+            Join(SpecificationWorkItem,
+                 SpecificationWorkItem.specification == Specification.id),
             # WorkItems may not have a milestone and in that case they inherit
             # the one from the spec.
             Join(Milestone,
-                 Coalesce(WorkItem.milestone_id,
+                 Coalesce(SpecificationWorkItem.milestone_id,
                           Specification.milestoneID) == Milestone.id)])
         today = datetime.today().date()
         query.extend([
             Milestone.dateexpected <= date, Milestone.dateexpected >= today,
-            WorkItem.deleted == False,
-            Or(WorkItem.assignee_id.is_in(self.participant_ids),
-               Specification._assigneeID.is_in(self.participant_ids))])
-        result = store.using(*origin).find(WorkItem, *query)
+            SpecificationWorkItem.id.is_in(Select(
+                SQL('id'), tables='assigned_specificationworkitem'))])
+        result = store.with_(assigned_specificationworkitem).using(
+            *origin).find(SpecificationWorkItem, *query)
         result.config(distinct=True)
 
         def eager_load(workitems):