Launchpad itself

Merge lp:~wgrant/launchpad/gSCFPS-optimise into lp:launchpad

  1. gSCFPS-optimise
  2. Merge into devel
Proposed by William Grant
Status: Merged
Merged at revision: 18188
Proposed branch: lp:~wgrant/launchpad/gSCFPS-optimise
Merge into: lp:launchpad
Diff against target: 159 lines (+29/-34)
4 files modified
lib/lp/blueprints/interfaces/specification.py (+2/-2)
lib/lp/blueprints/model/specification.py (+15/-18)
lib/lp/registry/browser/productseries.py (+3/-7)
lib/lp/registry/model/milestone.py (+9/-7)
To merge this branch: bzr merge lp:~wgrant/launchpad/gSCFPS-optimise
Reviewer Review Type Date Requested Status
Colin Watson (community) Approve
Review via email: mp+306070@code.launchpad.net

Commit message

Optimise SpecificationSet.getStatusCountsForProductSeries, fixing Product:+series timeouts.

Description of the change

Optimise SpecificationSet.getStatusCountsForProductSeries, fixing Product:+series timeouts.

postgres is inappropriately reluctant to use a BitmapOr with a subquery arm, so materialise the relatively small set of milestone IDs first.

To post a comment you must log in.
Revision history for this message
Colin Watson (cjwatson) :
review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'lib/lp/blueprints/interfaces/specification.py'
2--- lib/lp/blueprints/interfaces/specification.py 2015-09-30 01:51:52 +0000
3+++ lib/lp/blueprints/interfaces/specification.py 2016-09-19 10:08:16 +0000
4@@ -1,4 +1,4 @@
5-# Copyright 2009-2013 Canonical Ltd. This software is licensed under the
6+# Copyright 2009-2016 Canonical Ltd. This software is licensed under the
7 # GNU Affero General Public License version 3 (see the file LICENSE).
8
9 """Specification interfaces."""
10@@ -749,7 +749,7 @@
11 in the count.
12
13 :param product_series: ProductSeries object.
14- :return: A list of tuples containing (status_id, count).
15+ :return: A list of tuples containing (status, count).
16 """
17
18 def getByURL(url):
19
20=== modified file 'lib/lp/blueprints/model/specification.py'
21--- lib/lp/blueprints/model/specification.py 2016-08-09 10:43:45 +0000
22+++ lib/lp/blueprints/model/specification.py 2016-09-19 10:08:16 +0000
23@@ -26,9 +26,11 @@
24 SQLRelatedJoin,
25 StringCol,
26 )
27-from storm.expr import Join
28-from storm.locals import (
29+from storm.expr import (
30+ Count,
31 Desc,
32+ Join,
33+ Or,
34 SQL,
35 )
36 from storm.store import Store
37@@ -87,6 +89,7 @@
38 from lp.registry.interfaces.person import validate_public_person
39 from lp.registry.interfaces.product import IProduct
40 from lp.registry.interfaces.productseries import IProductSeries
41+from lp.registry.model.milestone import Milestone
42 from lp.services.database import bulk
43 from lp.services.database.constants import (
44 DEFAULT,
45@@ -97,7 +100,6 @@
46 from lp.services.database.interfaces import IStore
47 from lp.services.database.sqlbase import (
48 convert_storm_clause_to_string,
49- cursor,
50 SQLBase,
51 sqlvalues,
52 )
53@@ -1026,21 +1028,16 @@
54
55 def getStatusCountsForProductSeries(self, product_series):
56 """See `ISpecificationSet`."""
57- cur = cursor()
58- condition = """
59- (Specification.productseries = %s
60- OR Milestone.productseries = %s)
61- """ % sqlvalues(product_series, product_series)
62- query = """
63- SELECT Specification.implementation_status, count(*)
64- FROM Specification
65- LEFT JOIN Milestone ON Specification.milestone = Milestone.id
66- WHERE
67- %s
68- GROUP BY Specification.implementation_status
69- """ % condition
70- cur.execute(query)
71- return cur.fetchall()
72+ # Find specs targeted to the series or a milestone in the
73+ # series. The milestone set is materialised client-side to
74+ # get a good plan for the specification query.
75+ return list(IStore(Specification).find(
76+ (Specification.implementation_status, Count()),
77+ Or(
78+ Specification.productseries == product_series,
79+ Specification.milestoneID.is_in(list(
80+ product_series.all_milestones.values(Milestone.id)))))
81+ .group_by(Specification.implementation_status))
82
83 def specifications(self, user, sort=None, quantity=None, filter=None,
84 need_people=True, need_branches=True,
85
86=== modified file 'lib/lp/registry/browser/productseries.py'
87--- lib/lp/registry/browser/productseries.py 2015-09-29 00:05:21 +0000
88+++ lib/lp/registry/browser/productseries.py 2016-09-19 10:08:16 +0000
89@@ -1,4 +1,4 @@
90-# Copyright 2009-2015 Canonical Ltd. This software is licensed under the
91+# Copyright 2009-2016 Canonical Ltd. This software is licensed under the
92 # GNU Affero General Public License version 3 (see the file LICENSE).
93
94 """View classes for `IProductSeries`."""
95@@ -61,7 +61,6 @@
96 from lp.blueprints.browser.specificationtarget import (
97 HasSpecificationsMenuMixin,
98 )
99-from lp.blueprints.enums import SpecificationImplementationStatus
100 from lp.blueprints.interfaces.specification import ISpecificationSet
101 from lp.bugs.browser.bugtask import BugTargetTraversalMixin
102 from lp.bugs.browser.structuralsubscription import (
103@@ -448,11 +447,8 @@
104 def specification_status_counts(self):
105 """A list StatusCounts summarising the targeted specification."""
106 specification_set = getUtility(ISpecificationSet)
107- status_id_counts = specification_set.getStatusCountsForProductSeries(
108- self.context)
109- SpecStatus = SpecificationImplementationStatus
110- status_counts = dict([(SpecStatus.items[status_id], count)
111- for status_id, count in status_id_counts])
112+ status_counts = dict(
113+ specification_set.getStatusCountsForProductSeries(self.context))
114 return [StatusCount(status, status_counts[status])
115 for status in sorted(status_counts,
116 key=attrgetter('sortkey'))]
117
118=== modified file 'lib/lp/registry/model/milestone.py'
119--- lib/lp/registry/model/milestone.py 2015-07-08 16:05:11 +0000
120+++ lib/lp/registry/model/milestone.py 2016-09-19 10:08:16 +0000
121@@ -1,4 +1,4 @@
122-# Copyright 2009-2013 Canonical Ltd. This software is licensed under the
123+# Copyright 2009-2016 Canonical Ltd. This software is licensed under the
124 # GNU Affero General Public License version 3 (see the file LICENSE).
125
126 """Milestone model classes."""
127@@ -38,12 +38,6 @@
128 from zope.interface import implementer
129
130 from lp.app.errors import NotFoundError
131-from lp.blueprints.model.specification import Specification
132-from lp.blueprints.model.specificationsearch import (
133- get_specification_active_product_filter,
134- get_specification_privacy_filter,
135- )
136-from lp.blueprints.model.specificationworkitem import SpecificationWorkItem
137 from lp.bugs.interfaces.bugsummary import IBugSummaryDimension
138 from lp.bugs.interfaces.bugtarget import IHasBugs
139 from lp.bugs.interfaces.bugtask import (
140@@ -161,11 +155,19 @@
141
142 @property
143 def all_specifications(self):
144+ from lp.blueprints.model.specification import Specification
145 return Store.of(self).find(
146 Specification, Specification.milestoneID == self.id)
147
148 def getSpecifications(self, user):
149 """See `IMilestoneData`"""
150+ from lp.blueprints.model.specification import Specification
151+ from lp.blueprints.model.specificationsearch import (
152+ get_specification_active_product_filter,
153+ get_specification_privacy_filter,
154+ )
155+ from lp.blueprints.model.specificationworkitem import (
156+ SpecificationWorkItem)
157 from lp.registry.model.person import Person
158 origin = [Specification]
159 product_origin, clauses = get_specification_active_product_filter(